webweever Posted May 16, 2009 Share Posted May 16, 2009 I've been looking over this forum and Google trying to figure this out for hours. MY DB looks like this CREATE TABLE IF NOT EXISTS `category` ( `cat_id` int(11) NOT NULL AUTO_INCREMENT, `cat_name` varchar(100) NOT NULL, PRIMARY KEY (`cat_id`), UNIQUE KEY `cat_id` (`cat_id`,`cat_name`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ; CREATE TABLE IF NOT EXISTS `markers` ( `marker_id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(200) NOT NULL, `street` varchar(200) NOT NULL, `city` varchar(100) NOT NULL, `state` varchar(100) NOT NULL, `zip` int(11) NOT NULL, `url` varchar(200) NOT NULL, `lat` float(10,6) NOT NULL, `lng` float(10,6) NOT NULL, `cat_id` int(11) NOT NULL, PRIMARY KEY (`marker_id`), UNIQUE KEY `marker_id` (`marker_id`,`url`), KEY `cat_id` (`cat_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ; This is the code I'm using: <?php require("../includes/db.php"); ?> <html> <body> <?php $query="SELECT m.name, m.street, m.city, m.state, m.zip, m.url, m.lat, m.lng, c.cat_name FROM `markers` m LEFT JOIN `markers` m ON `category`.`cat_id` = m.`cat_id`"; $result=mysql_query($query); $num=mysql_numrows($result); mysql_close(); ?> <table border="1" cellspacing="0" cellpadding="2"> <tr> <th><font face="verdana" size="2">Name</font></th> <th><font face="verdana" size="2">Street</font></th> <th><font face="verdana" size="2">City</font></th> <th><font face="verdana" size="2">State</font></th> <th><font face="verdana" size="2">Zip</font></th> <th><font face="verdana" size="2">Category</font></th> <th><font face="verdana" size="2">URL</font></th> <th><font face="verdana" size="2">Latitude</font></th> <th><font face="verdana" size="2">Longitude</font></th> </tr> <?php $i=0; while ($i < $num) { $name=mysql_result($result,$i,"name"); $street=mysql_result($result,$i,"street"); $city=mysql_result($result,$i,"city"); $state=mysql_result($result,$i,"state"); $zip=mysql_result($result,$i,"zip"); $cat_name=mysql_result($result,$i,"cat_name"); $url=mysql_result($result,$i,"url"); $lat=mysql_result($result,$i,"lat"); $lng=mysql_result($result,$i,"lng"); ?> <tr> <td><font face="verdana" size="2"><?php echo $name; ?></font></td> <td><font face="verdana" size="2"><?php echo $street; ?></font></td> <td><font face="verdana" size="2"><?php echo $city; ?></font></td> <td><font face="verdana" size="2"><?php echo $state; ?></font></td> <td><font face="verdana" size="2"><?php echo $zip; ?></font></td> <td><font face="verdana" size="2"><?php echo $cat_name; ?></font></td> <td><font face="verdana" size="2"><a href="<?php echo $url; ?>"><?php echo $url; ?></a></font></td> <td><font face="verdana" size="2"><?php echo $lat; ?></font></td> <td><font face="verdana" size="2"><?php echo $lng; ?></font></td> </tr> <?php $i++; } ?> </body> </html> [code] Ultimately I want to SELECT the name, street, city, state, zip, url, lat, lng from the markers table as well as the cat_name from the category table. I know nothing about JOIN statements, this is my crash course I guess. Any ideas anyone? Link to comment https://forums.phpfreaks.com/topic/158366-selecting-with-join-across-multiple-tables/ Share on other sites More sharing options...
webweever Posted May 16, 2009 Author Share Posted May 16, 2009 This is my DB ERD although I'm not sure I've created correctly in MySQL. Link to comment https://forums.phpfreaks.com/topic/158366-selecting-with-join-across-multiple-tables/#findComment-835198 Share on other sites More sharing options...
webweever Posted May 16, 2009 Author Share Posted May 16, 2009 Just in case someone else is watching this post I got it to work. <?php $query= "SELECT * FROM markers JOIN category ON category.cat_id = markers.cat_id"; $result=mysql_query($query) or die(mysql_error()); $num=mysql_numrows($result); mysql_close(); ?> That code works but I have to select * from the db for it to work, otherwise I get a syntax error. Not sure why that is. Link to comment https://forums.phpfreaks.com/topic/158366-selecting-with-join-across-multiple-tables/#findComment-835239 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.