darrin365 Posted January 4, 2007 Share Posted January 4, 2007 Greetings!I have a mySQL database and am trying to pull data for a Google Map with custom map icons. I need to pull and compare data from three tables (#_mt_links has the listing data, #_mt_cats has the categories and #_mt_cl is a table that joins the data from the other two). I need to pull the link_id from the links table, match it to the link_id in the cl table, then pull the corresponding cat_id from the table and compare it to the cat_id in the cats table. THEN compare the cat_id in the cats table back to the cat_id in the links table and pull the corresponding cat_name from the cats table. Then I can match a custom map icon with the category name. WHEW! Is that confusing, or what?Here is the code I've got so far:[code] //get category ids for listing $id = "25"; $result = mysql_query("SELECT cat_id FROM dtd_mt_cl WHERE link_id = $id AND main = '1'"); if (!$result) { $message = 'Invalid query: ' . mysql_error() . "\n"; $message .= 'Whole query: ' . $query; die($message); } while ($row = mysql_fetch_array($result)) { echo "(alert '$row[cat_id]');\n"; //get category name $cat_name = mysql_query("SELECT cat_name FROM dtd_mt_cats WHERE cat_id = $row[cat_id]"); echo $cat_name;}?>[/code]Right now $id just has the link_id of one of my listings so I can test the code. It works through the alert, then craps out. I'm wondering if the problem is that I've got a query nested in a WHILE loop. Any suggestions to help?If you want to see the data at work on my site, you can go to: [url=http://www.pastigo.com/index.php?option=com_mtree&Itemid=29]http://www.pastigo.com/index.php?option=com_mtree&Itemid=29[/url] and drill down into any state data. You'll see my maps within each state page. I'm just trying to create custom icons for the listing types, such as restaurants, stores, lodging, etc. Not that all restaurants do not have the same cat_id. It's different for each city and state listing.Thank you for your time.Darrin Quote Link to comment Share on other sites More sharing options...
Philip Posted January 4, 2007 Share Posted January 4, 2007 Try adding another echo right after it, to see if it is that echo or mysql statement.Also add an "or die(mysql_error())" after the mysql statement Quote Link to comment Share on other sites More sharing options...
Stooney Posted January 4, 2007 Share Posted January 4, 2007 What exactly do you mean by 'crap out'? Is there an error? does the query fail? Quote Link to comment Share on other sites More sharing options...
darrin365 Posted January 4, 2007 Author Share Posted January 4, 2007 Hi, chrisdburns,Before I added the "die" statement after the $result = mysql_query part, I was getting the correct response on the alert but after that I was getting "Resource ID#3" instead of the expected result for the final echo.BTW, isn't "crap out" an offical geek term? :) Quote Link to comment Share on other sites More sharing options...
SharkBait Posted January 4, 2007 Share Posted January 4, 2007 You fetch the result of the 2nd query, but you don't fetch it properly ;) You need another mysql_fetch_array($cat_name) in there.So:[code]<?phpwhile ($row = mysql_fetch_array($result)) { echo "(alert '$row[cat_id]');\n"; //get category name $nextQuery = mysql_query("SELECT cat_name FROM dtd_mt_cats WHERE cat_id = $row[cat_id]"); $myResult = mysql_fetch_array($nextQuery, MYSQL_ASSOC); echo $myResult['cat_name'];}?>[/code] Quote Link to comment Share on other sites More sharing options...
darrin365 Posted January 4, 2007 Author Share Posted January 4, 2007 Awesome! That did it. Thank you so much!! Quote Link to comment Share on other sites More sharing options...
SharkBait Posted January 4, 2007 Share Posted January 4, 2007 No prob, glad I could help Quote Link to comment Share on other sites More sharing options...
Stooney Posted January 5, 2007 Share Posted January 5, 2007 [quote author=darrin365 link=topic=120931.msg496631#msg496631 date=1167888990]BTW, isn't "crap out" an offical geek term? :)[/quote]yes, just undefined Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.