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

@Tristan: Sorry I had to edit something. Updated my question — peace_love
– peace_love, Commented Nov 27, 2015 at 18:54

Nick · Accepted Answer · 2015-11-28 10:49:51Z

1

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'

edited Nov 28, 2015 at 10:49

answered Nov 27, 2015 at 19:56

Nick

10.2k8 gold badges65 silver badges97 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

peace_love Over a year ago

Hello, thank you, this looks very good, but with your method my key is still "order"

Nick Over a year ago

What do you mean, how it looks?

peace_love Over a year ago

i mean, i would like to use your solution, because it looks very clean and simple. But it didn't work out, because I couldn't make the id to the key.

Nick Over a year ago

I see it was mistake in $sql->fetchAll, edited. Maybe your ids are going consequentially and starting from 1? Try to add AND id > 10 to condition and explore result.

peace_love Over a year ago

That's it! Working :) Thank you

Darwin von Corax · Accepted Answer · 2015-11-29 17:50:31Z

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.

edited Nov 29, 2015 at 17:50

answered Nov 27, 2015 at 18:55

Darwin von Corax

5,2663 gold badges20 silver badges33 bronze badges

5 Comments

peace_love Over a year ago

Thank you for your suggestion. What exactly do you mean by "$some_value"?

peace_love Over a year ago

I found out that I need to use $my_result_array = $stmt->fetch(PDO::FETCH_ASSOC);

Darwin von Corax Over a year ago

$some_value would be the variable that holds the value of order_key that you're looking up.

Darwin von Corax Over a year ago

Using `PDO::fetchAll() with no arguments puts the whole result set into a nested array as shown above.

peace_love Over a year ago

Hi, thanks a lot. But what I would need is the id as a key. Instead of [0],[1] and so on

Wajih · Accepted Answer · 2015-11-27 18:58:02Z

0

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;
}

answered Nov 27, 2015 at 18:58

Wajih

4,4152 gold badges28 silver badges42 bronze badges

7 Comments

peace_love Over a year ago

Hi, I tested your code, unfortunately I got a blanc page

peace_love Over a year ago

I already found out that I need to change your second line into while($result = $sql->fetchAll(PDO::FETCH_ASSOC)) {

peace_love Over a year ago

But still var_dump($result); outputs array(0) { }

peace_love Over a year ago

if I write "var_dump($result);" into the while loop, I get some result, but not the right one

Wajih Over a year ago

I tried the code I wrote first and it worked fine with me, But if you need id field wrote as string change this $ps[" ".$row["id"]]=$p; I had mistake with previous comment sorry

|

Collectives™ on Stack Overflow

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

3 Answers 3

5 Comments

5 Comments

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

5 Comments

5 Comments

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related