[SOLVED] php/Query help please

[grumpy]

I have two tables, one stores categories (cat_relations) relative to the contractor's usr_id, and another (contractors) stores user info, including zip codes. The zip codes are in comma-separated strings in a row. The idea is that a site visitor selects a category and then they enter a zip. Every contractor's info should be displayed who's usr_id has a matching category_id in the cat_relations table which is selected by the site visitor and whose zip matches the zip entered by the site visitor. In other words:  I need the query to say: get the category ID that the site user selects and the zip code that the site user entered, and then find all the contractors who meet two criteria: has a relation_cat that matches the category ID and whose zip code matches the zip entered, and display their company name (co_name). But I can't figure out to do it. My latest attempt is below. I get results but they are not correct. They seem to only go by the zip. What am I doing wrong? Thanks.

 

$query = mysql_query("select * from cat_relations,contractors where contractors.co_workzip1 like '%$zip%' and cat_relations.relation_cat = '$ID'"); 

$result = $query or die ("Error:" . mysql_error());

if(mysql_num_rows($result) == 0)

{

echo ("<b>No Results Found</b>");

exit;

}

while ($row = mysql_fetch_array($result))

{

 

 

echo $row["co_name"];

}

[kratsg]

$query = mysql_query("SELECT contractors.co_workzip1,cat_relations.relation_cat from cat_relations INNER JOIN contractors ON WHERE contractors.co_workzip1 LIKE '%$zip%' AND cat_relations.relation_cat = '$ID'") or die ("Error:" . mysql_error()); 
if(!mysql_num_rows($query))//0 = false, any other number = true
{
echo ("No Results Found");
exit;
} else {//add your else statement, to prevent errors while reading this, just in case php doesn't "die"
echo "<ol>";
while ($row = mysql_fetch_array($query))
{
echo "<li>".$row["co_name"]."</li>";
}
echo "</ol>";
}

 

Try that, I removed the $result variable. I fixed your if/else statement a little bit. I moved the or die() part into the end of the mysql_query [where it should belong]. Added a bit of html to display the results in an ordered list, easier to read.

 

EDIT: Didn't realize that you were doing two diff tables, remodified, added the INNER JOIN statement which reads two tables at once really The modified code should work perfectly though ;-)

[teng84]

you try to select two table cat_relations,contractors where is the join condition for that or are you serious about that kind of join (Cartesian join )

[grumpy]

Hello-

Thanks to everyone for their help. But I think I just figured it out. Only took me 6 hours I wasn't relating the usr_ids from the two tables.

Instead of:

$query = mysql_query("select * from cat_relations,contractors where contractors.co_workzip1 like '%$zip%' and cat_relations.relation_cat = '$ID'"); 

 

This seems to work:

$query = mysql_query("select * from cat_relations,contractors where contractors.co_workzip1 like '%$zip%' and cat_relations.relation_cat = '$ID' and cat_relations.relation_usr_id = contractors.usr_id"); 

 

 

 

[teng84]

yahh 

 

and better if your joining condition is first in your condition