How can I store data from mySQL database into an array?

Question

I want to make an array from mysql database. This is my table data:

+--------+-------+--------+--------+
| order  |   id  |  name  |   age  |
+--------+-------+--------+--------+
|   12   |  121  |  fred  |   23   |
|   12   |  122  |  sam   |   24   | 
|   12   |  123  |  joe   |   42   |
|   12   |  124  |  alan  |   33   |
+--------+-------+--------+--------+

The array should look in the end like this:

array(4) {
  ["121"]=>
  array(2) {
    ["name"]=>
    string(4) "fred"
    ["age"]=>
    string(2) "23"
  }
  ["122"]=>
  array(2) {
    ["name"]=>
    string(3) "sam"
    ["age"]=>
    string(2) "24"
  }
  ["123"]=>
  array(2) {
    ["name"]=>
    string(3) "joe"
    ["age"]=>
    string(2) "42"
  }
  ["124"]=>
  array(2) {
    ["name"]=>
    string(4) "alan"
    ["age"]=>
    string(2) "33"
  }
  }

This is how I am creating the array:

$sql = "SELECT * FROM data WHERE order = '12'";                 
$q = $pdo->prepare($sql);
$q->execute();
$result = $sql->fetchAll();
var_dump($result); 

But my result is:

array(4) {
  [0]=>
  array(20) {
    ["order"]=>
    string(10) "12"
    [0]=>
    string(10) "12"
    ["id"]=>
    string(32) "121"
    [1]=>
    string(32) "121"
    ["name"]=>
    string(12) "fred"
    [2]=>
    string(12) "fred"
    ["age"]=>
    string(32) "23"
    [3]=>
    string(32) "23"
  }
[0]=>
  array(20) {
    ["order"]=>
    string(10) "12"
    [0]=>
    string(10) "12"
    ["id"]=>
    string(32) "122"
    [1]=>
    string(32) "122"
    ["name"]=>
    string(12) "sam"
    [2]=>
    string(12) "sam"
    ["age"]=>
    string(32) "24"
    [3]=>
    string(32) "24"
  }
and so on...

I do not know how to get the array into the right form

Answer 1

To start, you're combining calls from different APIs, which doesn't work, and second, you're trying to fetch records from a text string, not from a result set.

NOTE: The following replaces a previous answer which did not provide the desired results.

Here's what I've come up with, after changing some column names that my database didn't like:

<pre>
<?php
    $some_value = 12;
    $dbh = new PDO(/*Your connect string goes here*/);
    $sql = "SELECT id, name, age FROM some_data WHERE order_id = :order_key";                 
    $stmt = $dbh->prepare($sql);
    $stmt->execute(array(':order_key'=>$some_value));
    $my_output = array();
    foreach($stmt->fetchAll(PDO::FETCH_ASSOC) as $rec)
    {
        $my_output[$rec['id']] = array('name' => $rec['name'], 'age' => $rec['age']);
    }
    var_dump($my_output);
?>
</pre>

Changing the value of the variable $some_value will allow you to run the query against other values of order_id.

The output produced looks like this:

array(4) {
  [121]=>
  array(2) {
    ["name"]=>
    string(4) "fred"
    ["age"]=>
    int(23)
  }
  [122]=>
  array(2) {
    ["name"]=>
    string(3) "sam"
    ["age"]=>
    int(24)
  }
  [123]=>
  array(2) {
    ["name"]=>
    string(3) "joe"
    ["age"]=>
    int(42)
  }
  [124]=>
  array(2) {
    ["name"]=>
    string(4) "alan"
    ["age"]=>
    int(33)
  }
}

Wrangling the types I leave as an exercise for the reader.

Answer 2

If you using PDO, there is elegant method

$sql = "SELECT `id`, `name`, `age` FROM `data` WHERE `order` = '12'"; 
$q = $pdo->prepare($sql);
$q->execute();
$result = $q->fetchAll(PDO::FETCH_GROUP | PDO::FETCH_UNIQUE | PDO::FETCH_ASSOC);
var_dump($result);

Also, if you need id as keys and as values in $result, then declare id column in SQL query twice:

SELECT `id`, `id`, `name`, `age` FROM `data` WHERE `order` = '12'

Answer 3

I think this will work fine:

$ps = array();
while ($row = $result->fetch_assoc()) {
 $p=array();
 $p["name"]=$row["name"];
 $p["age"]=$row["age"];
 $ps[$row["id"]]=$p;
}