Is the Problem a Nested Query?

[darrin365]

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

[Philip]

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

[Stooney]

What exactly do you mean by 'crap out'?  Is there an error? does the query fail?

[darrin365]

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

[SharkBait]

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]
<?php
while ($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]

[darrin365]

Awesome! That did it. Thank you so much!!

[SharkBait]

No prob, glad I could help

[Stooney]

[quote author=darrin365 link=topic=120931.msg496631#msg496631 date=1167888990]

BTW, isn't "crap out" an offical geek term?  :)
[/quote]

yes, just undefined