PHP: MySQL query returning array

Question

I'm wondering how I would issue a query from a PDO and have an array returned? Right now, I have

$DBH = new PDO("mysql:host={$host};dbname={$dbname}", $user, $pass);

Using a foreach loop, like so

$query = "SELECT DISTINCT City FROM Locations ORDER BY City";
foreach($DBH->query($query)as $row) {
    echo $row['City'];      
}

I can get all the cities printed. However, I would like to store all the cities into an array. I tried to do it like this

$array = $DBH->query($query);

but that didn't work at all. Any suggestions?

Willi Mentzel · Accepted Answer · 2014-09-16 12:42:41Z

3

You're using PDO, so you should be using PDOStatement::fetchAll().

$stmt = $DBH->query($query);
$array = $stmt->fetchAll(PDO::FETCH_COLUMN);

Done - And you're not using any loops.

edited Sep 16, 2014 at 12:42

Willi Mentzel

30.2k21 gold badges120 silver badges129 bronze badges

answered Jun 25, 2012 at 20:59

nickb

59.7k13 gold badges115 silver badges149 bronze badges

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

2 Comments

Aaron W. Over a year ago

wouldn't just plain fetchAll() return a nested array instead of one array with all city values?

No_name Over a year ago

Though I would prefer a loopless version like this, it simply doesn't work for me. When I tried echoing out the array with a foreach loop like below, I was able to get each city. With this, I don't even get anything.

Willi Mentzel · Accepted Answer · 2014-09-23 12:42:27Z

2

$query = "SELECT DISTINCT City FROM Locations ORDER BY City";

foreach($DBH->query($query) as $row) {
    $array[] = $row['City'];      
}

edited Sep 23, 2014 at 12:42

Willi Mentzel

30.2k21 gold badges120 silver badges129 bronze badges

answered Jun 25, 2012 at 20:58

matchdav

7351 gold badge7 silver badges16 bronze badges

1 Comment

matchdav Over a year ago

Not really possible, you have to iterate through results somehow. If you don't like looking at it in your code, encapsulate it as a function. But I don't see the point.

gen_Eric · Accepted Answer · 2012-06-25 21:03:04Z

0

Possibly try:

$array = array();

foreach($DBH->query($query) as $row){
    array_push($array, $row['City']);     
}

print_r($array);

edited Jun 25, 2012 at 21:03

gen_Eric

228k42 gold badges304 silver badges343 bronze badges

answered Jun 25, 2012 at 20:58

MultiDev

10.7k26 gold badges84 silver badges155 bronze badges

Collectives™ on Stack Overflow

PHP: MySQL query returning array

3 Answers 3

2 Comments

1 Comment

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

1 Comment

Comments

Related