Jump to content
Larry Ullman's Book Forums
Sign in to follow this  
David John

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

Share this post


Link to post
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 ;

Share this post


Link to post
Share on other sites

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

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.

Share this post


Link to post
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.

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...
Sign in to follow this  

×
×
  • Create New...