Sql And Json

[David John]

Guys,

I am trying to output ALL cities that match the following SQL query: 

SELECT info.state_id, info.address, info.city, info.state, info.zip FROM info INNER JOIN States ON info.state_id = States.state_id WHERE info.state_id=:id GROUP BY info.city 

And unfortunately, it returns only one result.  Here's the try->catch I used:  

 

 

try {
$dbh = new PDO("mysql:host=$host;dbname=$databasename", $user, $pass); 
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$stmt = $dbh->prepare($sql); 
$stmt->bindParam("id", $_GET[id]); 
$stmt->execute();

$cities = $stmt->fetchAll(PDO::FETCH_OBJ);

$dbh = null;
echo '{"items":'. json_encode($cities) .'}'; 
} catch(PDOException $e) {
echo '{"error":{"text":'. $e->getMessage() .'}}'; 
}

Now, I am almost certain that this is a problem with my query and not JSON.  My question is, how do I alter the SQL so it will return EVERY city, not just one?  

 

Would greatly appreciate any input

[Larry]

Could you provide a bit more background about the underlying database tables?

[David John]

Could you provide a bit more background about the underlying database tables?

 

Certainly.  Here's the INFO table:

CREATE TABLE IF NOT EXISTS `info` (
  `info_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `state_id` int(10) NOT NULL,
  `address` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `city` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `state` varchar(30) COLLATE utf8_unicode_ci NOT NULL,
  `zip` varchar(30) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`info_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=938 ;

And here's the STATES table:

CREATE TABLE IF NOT EXISTS `States` (
  `state_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `state_name` varchar(30) COLLATE utf8_unicode_ci NOT NULL,
  `stateAbbr` varchar(6) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`state_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=53 ;

[David John]

Not sure why, Larry, but whenever I post in this forum, I end up solving my problem!  

I replaced "GROUP BY" with "ORDER BY" and now I think it works. 

 

Thanks for your books and resources; I truly have learned a lot from them.

[HartleySan]

Rubber duck debugging FTW!

[Larry]

Thanks, David! Kudos for figuring out and thanks for letting me know!