Need query help

grumpy

I 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 matches has a matching category in the cat_relations table which is selected by the site visitor and whose zip matches the zip entered by the site visitor. But I can't figure out to do it. I tried a join, and I tried two queries. When I echo the $relation_usr_id in the first query I get an unbroken list of all the usr_id's who match the id's of the selected $cat_name. My latest attempt is below. What am I doing wrong? Thanks


$query = mysql_query("select relation_usr_id from cat_relations where cat_relations.relation_cat ='$cat_name'");
$result = $query;
while ($row = mysql_fetch_array($result)) 
{
$relation_usr_id = $row["relation_usr_id"];
echo $relation_usr_id;
}

$query = mysql_query("select * from contractors where usr_id = '$relation_usr_id' and co_workzip1 like '%$zip%'");
$result = $query;

if(mysql_num_rows($result) == 0) 
{
echo ("<b>No Results Found</b>");
exit;
}
while ($row = mysql_fetch_array($result)) 
{


echo ("<table width=50% align=center>");
echo ("<tr><td colspan=2>" . $row["co_name"] . "</td></tr></table>");

}



?>

chasiv

Your first query is doing exactly what you tell it to.. it is listing all relation_usr_id info that matches your '$cat_name' value on one row. There is no line break in your loop:

while ($row = mysql_fetch_array($result))
{
$relation_usr_id = $row["relation_usr_id"];
echo $relation_usr_id;
}

if you want them on separate lines... use: echo $relation_user_id . "<br>";

Your 2nd query will also only display 1 result as $row is overwritten each time it cycles a record. So only the last record will be in the $row array.

echo ("<table width=50% align=center>");
while ($row = mysql_fetch_array($result))
{
echo ("<tr><td colspan=2>" . $row["co_name"] . "</td></tr>");
}
echo "</table>";

will display all the Contractor names on individual rows of a table...

Hope that helps... Without your form code, it's hard to tell exactly what you're looking at or for.