MYSQL Query Result to Arrays

General PHP Help
#1

Hi… How do you insert MYSQL Query Result to Arrays

[php]$user = $_GET[“user_name”];
$con=mysqli_connect(DB_HOST, DB_USER, DB_PASS, DB_NAME);
if (mysqli_connect_errno()){ echo "Failed to connect to MySQL: " . mysqli_connect_error(); }
$result = mysqli_query($con,“SELECT data1, data2, FROM datatable”);
while($row=mysql_fetch_array($result)){
echo $row[‘data1’];
echo $row[‘data2’];
echo $row[‘data3’];
}

$row[] = “data1”;
$row[] = “data2”;
$row[] = “data2”;
$xls->addRow($row);

or multidimentional array

$row = array();
$row[] = array(0 =>‘data1’, 1=>‘data2’, 2=>‘data3’);
$xls->addRow($row);[/php]

#2

You should look into using PDO or mysqli instead of the old mysql_* library which is deprecated/unsafe and shouldn’t be used any more. With PDO you even have a fetchAll function which will return an array with all the results from the query :slight_smile:

#3

Hi! Thanks for the Reply which PHP version supports PDO again? I’m afraid currently the webspace doesnt meet the minimum requirements…

Server: Localhost via UNIX socket
Server type: MySQL
Server version: 5.5.33-log - MySQL Community Server (GPL) (BetterLinux 01 Aug 1 2013 18:05:26)
Protocol version: 10
Apache
Database client version: libmysql - 5.5.32
PHP extension
Version information: 4.0.5, latest stable version: 4.0.6

#4

It says so in the site I linked to :slight_smile:

Many PHP programmers learned how to access databases by using either the MySQL or MySQLi extensions. [b]As of PHP 5.1[/b], there’s a better way. PHP Data Objects (PDO) provide methods for prepared statements and working with objects that will make you far more productive!
All of these drivers are not necessarily available on your system; here’s a quick way to find out which drivers you have:

[php]print_r(PDO::getAvailableDrivers());[/php]

#5

[php]
if (!defined(‘PDO::ATTR_DRIVER_NAME’)) {
echo ‘PDO unavailable’;
}
elseif (defined(‘PDO::ATTR_DRIVER_NAME’)) {
echo ‘PDO available’;
}
[/php]

It seems working thu… So i need to rewrite this whole thing. can you give me a headstart by answering this question using PDO? ;D

#6

I’ll do better, I’ll give you a stripped down version of my PDO wrapper class :slight_smile:

Save this as db.php
[php]<?php

class DB {

/**
*

  • PDO connection
  • @var PDO
    */
    private $pdoConn = null;

/**

  • Default charset
  • @var string
    */
    private $charset = ‘utf8’;

/**

  • Class constructor
    */
    public function __construct() {
    $this->_initDb();
    }

/**

  • Get PDO database connection
  • @return
    */
    public function getPDOConn() {
    return $this->pdoConn;
    }

/**

  • Init db connection based on config
    */
    private function _initDb() {
    try {
    $this->pdoConn = new \PDO(‘mysql:dbname=’ . DB_NAME . ‘;host=’ . DB_HOST . ‘;charset=’ . $this->charset, DB_USER, DB_PASS);
    $this->pdoConn->exec(“set names utf8”);
    $this->pdoConn->setAttribute(\PDO::ATTR_ERRMODE, \PDO::ERRMODE_EXCEPTION);
    $this->pdoConn->setAttribute(\PDO::ATTR_EMULATE_PREPARES, false);
    } catch (PDOException $e) {
    // Here you should do some error handling
    $errormessage = “Database error occured at: " . strftime(”%Y-%m-%d %H:%M:%S") . “”>\n";
    $errormessage .= "[DB] _initDb: could not get PDO connection: " . $e->getMessage() . “\n”;
    echo ‘
    ’ . $errormessage . ‘
    ’;
    return;
    }
    }

/**

  • Executes parametarized query
  • @param string $query
  • @param array $params
  • @param string $fetch_method
    */
    public function query($query, $params = [], $fetch_method = ‘OBJ’) {
    $stmt = $this->pdoConn->prepare($query);
    try {
    $result = $stmt->execute($params);
    } catch (PDOException $e) {
    // Here you should do some error handling
    $errormessage = “\tDatabase error occured at: " . strftime(”%Y-%m-%d %H:%M:%S") . “”>\n";
    $errormessage .= "\tQuery: " . $query . “\n”;
    $errormessage .= "\tError code: " . $e->getCode() . “\n”;
    $errormessage .= “\tError message: " . $e->getMessage() . “\n”;
    echo ‘
    ’ . $errormessage . ‘
    ’;
    }
    if ($result) {
    $querybit = explode(” ", $query);
    if (trim($querybit[0]) == ‘SELECT’) {
    $ret = $stmt->fetchAll(constant(‘PDO::FETCH_’ . strtoupper($fetch_method)));
    } else {
    return array(TRUE);
    }
    }
    return !empty($ret) ? $ret : null;
    }

/**

  • Get last inserted id
  • @return integer
    */
    public function getLastInsertedId() {
    return $this->pdoConn->lastInsertId();
    }

/**

  • Wrapper for mysql_real_escape_string
  • @param string $string
  • @return string
    */
    protected function _escape($string) {
    return mysql_real_escape_string($string);
    }

}[/php]Except from the error logging, which in this copy only echoes out the error message, you do not have to do any changes to this code :slight_smile:

Then you can start using the DB like this:
[php]<?php
ini_set(‘error_reporting’, E_ALL);
header(“Content-Type: text/html; charset=utf-8”);
session_start();

define(‘DB_HOST’, ‘localhost’);
define(‘DB_USER’, ‘test’);
define(‘DB_PASS’, ‘test’);
define(‘DB_NAME’, ‘test’);

require_once (‘db.php’);

$db = new DB();

$userName = !empty($_GET[‘user_name’]) ? $_GET[‘user_name’] : null;

if ($userName) {
$users = $db->query(‘SELECT id, username, password FROM users WHERE username = ?’, array($userName));

if ($users) {
foreach ($users as $user) {
echo 'User id: ’ . $user->id . ‘
’;
echo 'Username: ’ . $user->username . ‘
’;
echo 'Password: ’ . $user->password . ‘
’;
}
} else {
echo ‘No users width displayname "’ . htmlspecialchars($userName) . ‘" found :(’;
}
} else {
echo ‘No username submitted.’;
}
[/php]

#7

Notice how easy it is to do safe queries with this setup.

You could now do something like this:
[php]$db->query(‘UPDATE users SET password = ? WHERE id = ?’, array($hashedPass, $userId));[/php]
And it is perfectly safe from sql injections, you don’t have to worry about really escaped strings ever again :smiley:

If you wanted an array you simply either change the default fetch method in the db class, or you can do this if you want it only this time:
[php]$user = $db->query(‘SELECT * FROM users WHERE id > ?’, array($userId), ‘ASSOC’);[/php]

Note: you cannot pass table names, column names, order by values (asc, desc), etc as parameters into a parameterized query.

This will not work:
[php]$user = $db->query('SELECT * FROM users WHERE id > ? ORDER BY ? ',
array($userId, $orderBy));[/php]

in these cases you should only input safe values, like this:
[php]
$orderBy = !empty($_GET[‘order’]) && $_GET[‘order’] == ‘desc’ ? ‘desc’ : ‘asc’;
$users = $db->query('SELECT * FROM users WHERE id > ? ORDER BY ’ . $orderBy,
array($userId));[/php]

Never ever ever pass variables directly into a query if you aren’t 100% sure what the data is. Above we make 100% sure the data is a string of either desc or asc, so we know it’s safe. This includes data from your users (duh), data from your own database, data from other services, everything.

#8

Thank You! Ill take it from here… need to learn PDO this time around. Thanks for the Headstart!

#9

Np, just come back if you have any trouble with PDO or anything else :slight_smile:

#10

He didn’t need pdo to solve this, all he’s doing is creating a spreadsheet for excel. you were actually pretty close, just had the array in the wrong place.

[php]$data = array();
while($row=mysql_fetch_array($result)) {
echo $row[‘data1’];
echo $row[‘data2’];
echo $row[‘data3’];
$data[] = $row;
}
[/php]Though $row is already an array. Like any array, you would need to use a loop to move through each part of the array. someone created a php class for creating xls sheets, I use it for exporting sales and trends data from the site I manage.

#11

He didn’t need to use php either, but when using php he might as well use modern and safe standards. Mysql_* should never be used, no matter how small the project is.

#12

I’ve never had to use pdo and never really needed it, mostly because its a pain to learn and understand for me. I can use it if I absolutely have to. But for him to use pdo, he had to add an entire class and completely rewrite his existing code, all to do what he didn’t need to use. He didn’t need to change anything, what he needed was already in an array, it was just a matter of using it for what the purpose was.

#13

Well it’s your choice, but I don’t really see why PDO is any harder than mysqli. I would definitly recommend taking some time (an hour) to sit down with an open mind and actually have a look at it. I honestly believe that PDO is easier than both mysql_* and mysqli, mostly because of the easy way to bind params in parameterized queries.

If you have some small scripts /projects then all of these can extend/include/require the same pdo/db class. It makes for a less cluttered code, and also keeps you within the DRY concept :slight_smile: In addition I seriously mean he did have to change this, mysql_* is outdated and will soon be removed from php. Sooner or later webhosts will therefore not support this extension, and he would have had to rewrite it anyway. On top of it all is the security aspect.

#14

I will eventually. Busy torturing myself with jquery right now. As long as he takes the necessary precautions against injection, he’ll be fine.

Sponsor our Newsletter | Privacy Policy | Terms of Service