Jump to content
Larry Ullman's Book Forums

Sql And Json


Recommended Posts

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 :)

Link to comment
Share on other sites

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 ;

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
 Share

×
×
  • Create New...