How can I create an Array from mySQL database?

Question

$sql = "SELECT name FROM people WHERE id = '$id'";  
$array = array();       
$q = $pdo->prepare($sql);
$q->execute();
foreach ($pdo->query($sql) as $row) {
$array[] = $row;
}

My result is:

array(4) {
  [0]=>
  array(2) {
    ["name"]=>
    string(4) "fred"
    [0]=>
    string(4) "fred"
  }
  [1]=>
  array(2) {
    ["name"]=>
    string(3) "sam"
    [0]=>
    string(3) "sam"
  }
  [2]=>
  array(2) {
    ["name"]=>
    string(4) "alan"
    [0]=>
    string(4) "alan"
  }
  [3]=>
  array(2) {
    ["name"]=>
    string(63) "john"
    [0]=>
    string(63) "john"
  }
}

But I would need:

Array
(
    [fred] => fred
    [sam] => sam
    [alan] => alan
    [john] => john
)

I tried for example:

foreach ($pdo->query($sql) as $key => $row) {..

and also

foreach ($pdo->query($sql) as $row) {
$array[] = $row;
$row = array_combine(array_values($row), array_values($row));
}

but I always get the same result...

Saty · Accepted Answer · 2016-03-30 10:00:55Z

4

Use fetchAll

$q = $pdo->prepare($sql);
$q->execute();
$result = $q->fetchAll();
print_r($result);

edited Mar 30, 2016 at 10:00

answered Mar 30, 2016 at 10:00

Saty

22.5k7 gold badges35 silver badges52 bronze badges

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

6 Comments

fusion3k Over a year ago

Also you have to specify fetch mode.

peace_love Over a year ago

Thank you! :) Does this method has some advantages to Ashwanis suggestion, or is it up to personal taste which method to choose

Saty Over a year ago

This in in build function of PDO you can direct get your desier result without use of any loop

Saty Over a year ago

@fusion3k in that case no use of any mode

Ashwani Goyal Over a year ago

fetchAll() in itself employs a loop.

|

Your Common Sense · Accepted Answer · 2016-03-30 11:26:09Z

1

Use fetchAll()

But not a vanilla one but with a secret argument called PDO::FETCH_COLUMN

$sql = "SELECT name FROM people WHERE id = ?";  
$q = $pdo->prepare($sql);
$q->execute([$id]);
$array = $q->fetchAll(PDO::FETCH_COLUMN);

It will get you a single-dimensional array with names like this

Array
(
    [0] => fred
    [1] => sam
    [2] => alan
    [3] => john
)

Note that you are using prepared statements completely wrong way making your code vulnerable to SQL injection. It is fixed in the code above.

If you insists on getting both key and value, then change your code as follows

$sql = "SELECT name, name FROM people WHERE id = ?";  
$q = $pdo->prepare($sql);
$q->execute([$id]);
$array = $q->fetchAll(PDO::FETCH_KEY_PAIR);

and it will give you exact array as you asked

edited Mar 30, 2016 at 11:26

answered Mar 30, 2016 at 10:33

Your Common Sense

158k42 gold badges226 silver badges373 bronze badges

3 Comments

Your Common Sense Over a year ago

Out of curiosuty, which one you choose? By the way, there are other magic modes in PDO, worth a quick glance. May be I should make a cheatsheet...

peace_love Over a year ago

I was choosing the second solution. Could you tell me why my statements are insecure?

Your Common Sense Over a year ago

Because you are adding variables directly to the query, which makes it vulnerable to SQL injection.

Ashwani Goyal · Accepted Answer · 2016-03-30 09:58:48Z

0

Make the following changes:

foreach ($pdo->query($sql) as $row) {
    $array[$row['name']] = $row['name'];
}

answered Mar 30, 2016 at 9:58

Ashwani Goyal

6264 silver badges18 bronze badges

3 Comments

peace_love Over a year ago

Great! Working very well :o)

Ashwani Goyal Over a year ago

I am glad that it helped, I would seek your approval/acceptance to my answer :-)

Ashwani Goyal Over a year ago

@Jarla please approve the answer so that people can rely over it.

Collectives™ on Stack Overflow

How can I create an Array from mySQL database?

3 Answers 3

6 Comments

3 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

6 Comments

3 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related