Generate PHP array from MySQL with key value from "id" column

Question

I have a MySQL database that looks like this:

ID        TEXT        PARENTID
20        Item1        null
23        Item2        20
27        Item3        20
80        Item4        27

What I want is to retreive this data in an array like this:

Array ( 
[20] => Array ( [text] => Item1 [parentID] => ) 
[23] => Array ( [text] => Item2 [parentID] => 20 ) 
[27] => Array ( [text] => Item3 [parentID] => 20 ) 
[80] => Array ( [text] => Item4 [parentID] => 27 )
);

Notice that the key values represent the value of the id column in the table.

I have tried makin an array inside a mysql loop (I am still in the process of learning MySQLi, and hopefully it'll stick pretty soon but this example is in the old MySQL):

$hent_folder = mysql_query("SELECT id, text, parentID FROM folders");   
while($row = mysql_fetch_assoc($hent_folder))
    {
        $array[] = $row;
    }

The result here of course generates "automatic" key values and not the values from the ID column as wanted plus it puts the ID column as a part of the inside array.

Is there a way or workaround to succeed here?

I hope someone can lead me in the right direction...

----- EDIT ------

I have replaced

$array[] = $row;

with

$array[$row['ID']] = $row;

But it still does not return the above wanted result. It returns this:

Array ( 
[0] => Array ( [id] => 20 [text] => Item1 [parentID] => ) 
[1] => Array ( [id] => 23 [text] => Item2 [parentID] => 20 ) 
[2] => Array ( [id] => 27 [text] => Item4 [parentID] => 20 ) 
[3] => Array ( [id] => 80 [text] => Item4 [parentID] => 27 )
);

Answer 1

This is doable as an associative array.

All you have to do is change

$array[] = $row

to

$array[$row['ID']] = $row

You need to make sure that the ID column in your database is unique so that the associative array doesn't overwrite keys (in which case only the last record with duplicate ID's would remain)

EDIT (19-11-2015)

Also about your 'mysql loop' - it's just a regular PHP while loop that loops through records given by either mysql_* or mysqli_* functions. You're looping through the returned result from executing an sql statement on the database which just fetches and formats rows for you in a certain way.

MySQL and MySQLi are two different things but the SQL they use is no different (except for maybe prepared statements?). Learning SQL is what you're going to do and to execute it you're going to use mysqli_* from now on since mysql_* functions are deprecated

I just wanted to point those things out to clear out some confusion you might already have or will have in the future. :)

For more reading on mysqli read the php.net manual, it's all a bit complex if you're just starting but that's fine - understand what you can and work to understand what you can't.

---

EDIT 2 (19-11-2015)

the indexes in your $row array are case-sensitive so ID and id are something different entirely. one will add up to an undefined index error.

I noticed in your output that the id key is actually lowercase so I would try changing to that.

Good luck!

Answer 2

You can achieve this result easily using PDO. It has a fetch mode called PDO::FETCH_GROUP and when combined with PDO::FETCH_ASSOC will produce you the array that you want.

$pdo = new PDO("mysql:host=$host;dbname=$db;charset=utf8mb4", $user, $pass, [
    \PDO::ATTR_ERRMODE => \PDO::ERRMODE_EXCEPTION,
    \PDO::ATTR_EMULATE_PREPARES => false
]);

$stmt = $pdo->prepare('SELECT id, text, parentID FROM folders');
$stmt->execute();
$array = $stmt->fetchAll(PDO::FETCH_UNIQUE|PDO::FETCH_ASSOC);

Answer 3

// Multidimensional array. 
// main array by id with text and parent in secondary array
// tryed going by the second diagram

$arr = array();
$hent_folder = mysql_query("SELECT id, text, parentID FROM folders");

while($row = mysql_fetch_array($hent_folder))
{
    $arr[$row['id']]['text']     = $row['text'];
    $arr[$row['id']]['parentID'] = $row['parentID'];
}

Answer 4

Below code can be used to produce an array of the ids from SQL response :

$ids_array = array();

foreach($idsFromDb as $row)
{
    $ids_array[] = $row['id'];
}

echo print_r($ids_array[0]) ;