CODEWITHSUNDEEP
phparrayspdo
peace_love
peace_love

Reputation: 6461

How can I create an Array from mySQL database?

$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...

Upvotes: 1

Views: 70

Answers (3)

Your Common Sense
Your Common Sense

Reputation: 157838

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

Upvotes: 1

Saty
Saty

Reputation: 22532

Use fetchAll

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

Upvotes: 4

Ashwani Goyal
Ashwani Goyal

Reputation: 616

Make the following changes:

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

Upvotes: 0

Related Questions