如何从mySQL数据库创建一个数组？

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

Answer 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