Trouble getting MySQL results into associative array

PHP
1

I’ve got a simple query:

$citygroupquery = "SELECT city,COUNT(city) FROM addresses WHERE state=".$state." GROUP BY city";

The query returns great results. I need to get it into an associative array so I can later look up the city counts.

So it looks like this:

[INDENT][“Amityville”] => [1]
[“Brooklyn”] => [2]
[“Flushing”] => [2]
[“Hempstead”] => [1]
[“Jackson Heights”] => [1]
[“New York”] => [23]
[“Port Washington”] => [1]
[“Riverhead”] => [1]
[“Staten Island”] => [1]
[“Syracuse”] => [4]
[“Woodside”] => [1]
[/INDENT]
So, I have:

while($row = @mysql_fetch_assoc($citylist)){
   $citycounts = $row; }

Which gives me an array, but when I search it, I only get the city name, and then “Array” as the value of the count.

I’ve tried:

$citycounts = array($row['COUNT(city)']=>$row['city'])

without any difference.

So, I set up a PDO connection to try the same thing:

$citylist = new PDO($dsn, $username, $password,array(PDO::ATTR_PERSISTENT => true));
    //$newcitylist = array();
    $citygroupquery = "SELECT city,COUNT(city) FROM addresses WHERE state=".$state." GROUP BY city";
    $newcitylist = $citylist->prepare($citygroupquery);
    $newcitylist->bindParam('city',$city);
     $newcitylist->execute();
    $citycounts = $newcitylist->fetchAll(PDO::FETCH_GROUP);

Again, I get the city list, but “Array” for the associated count field in the array.

In both cases, if I just print the results of the query, I get the results I’m expecting. It’s just putting them into an associative array with city as the key to look up counts.

I have this feeling I’m missing something really really simple (kind of like the semicolon at the end of the line you don’t see). Or, is it more complicated?

2

You need to use a foreach() loop to iterate through the array

3

You need to use a foreach() loop to iterate through the array

Why I’m confused is that the key (or first field) of the array is completely populated. There is only one additional field.

[key]=>[value]

Are you saying it looks like this:

[key] = Array { [0][value]}

As I said, the array appears to be populated, at least with the first set of values.

4

Try this:



$query = "SELECT city,COUNT(city) AS count FROM addresses WHERE state=".$state." GROUP BY city";
$result = mysqli_query($dbc, $query);
	
$citycounts = array();
	
while ($row = mysqli_fetch_array($result, MYSQL_ASSOC)) {
	$citycounts[$row['city']] = $row['count'];
}

Cheers,

Jon