626 626 - 3 months ago 9
MySQL Question

Multi-dimensional array from sql query

I have an multi-dimensional array that I am trying to build from an SQL query. I am havinf trouble adding the id in the inner array.

Code:

$checkboxes = "SELECT id, name FROM review_sites WHERE active=1 ORDER BY name ASC";
$result = mysql_query($checkboxes) or die(mysql_error());
$names = array();

while ($row = mysql_fetch_array($result)) {
$names[]['name'] = $row['name'];

}


Currently the Array looks like:

Array
(
[0] => Array
(
[name] => 411.ca
)

[1] => Array
(
[name] => AutoMD
)


I Need the array to look like this:

Array
(
[0] => Array
(
[id] => 4
[name] => 411.ca
)

[1] => Array
(
[id] => 9
[name] => AutoMD
)

Answer

You are only selecting id and name in the query so, in the loop:

while ($row = mysql_fetch_assoc($result)) {
    $names[] = $row;
}

Or simply:

while ($names[] = mysql_fetch_assoc($result)) {}

Notice, mysql_fetch_assoc() to only return an associative array. mysql_fetch_array() returns an associative and numerically indexed array.

Also:

This extension is deprecated as of PHP 5.5.0, and will be removed in the future. Instead, the MySQLi or PDO_MySQL extension should be used. See also MySQL: choosing an API guide and related FAQ for more information. Alternatives to this function include: mysqli_fetch_array() PDOStatement::fetch()