I've got a database with 2 tables:

zipcodes
zipcode - has a zipcode
hxagent - holds an agent ID
sfagent - holds an agent ID
pwagent - holds an agent ID
distrib - holds an agent ID

agents
agentcode - - holds an agent ID

Customer enters in an zipcode
zipcode table is searched for a matching zipcode.

When a match is made, the agent codes are returned for that zip.

The agent table is then searched for the agent codes and agents info is displayed.

hxagent, sfagent and distrib may or maynot have different info, yet all matches need to be returned together.


This is what I got so far, but it doesn't quite cut it. I cant do a nested Select as VO's version of MYSQL doesn't support that feature yet. Any ideas?



$query = "select DISTINCT zipcodes.hxagent, zipcodes.sfagent, zipcodes.distributor from zipcodes, agents where zipcodes.zipcode ='$searchterm'";
$result = mysql_query($query);

$num_results = mysql_num_rows($result);

echo "<p>Number of Agents found: ".$num_results."</p>";
echo stripslashes($searchterm);

for ($i=0; $i <$num_results; $i++)
{
$row = mysql_fetch_array($result);
$query2 = "select * from agents where zipcodes.hxagent ='stripslashes($row["hxagent"])'";
$result2 = mysql_query($query2);
echo "<p><strong>".($i+1).". HXAgent:<BR>";
echo stripslashes($row["hxagent"]).", ";
echo stripslashes($row["distributor"])."<BR> ";
echo stripslashes($row["agentname"])."<BR>";
echo stripslashes($row["repadd"])."<BR>";
echo stripslashes($row["repcity"]).", ";
echo stripslashes($row["repstate"])." ";
echo stripslashes($row["repzip"]);
echo "</strong>";
if ($row["zipcodes.hxagent"] != $row["zipcodes.sfagent"])
{
echo "<p><strong>".($i+1).". SFAgent:<BR>";
echo stripslashes($row["sfagent"]).", ";
echo stripslashes($row["distributor"])."<BR> ";
echo stripslashes($row["agentname"])."<BR>";
echo stripslashes($row["repadd"])."<BR>";
echo stripslashes($row["repcity"]).", ";
echo stripslashes($row["repstate"])." ";
echo stripslashes($row["repzip"]);
echo "</strong>";
}
echo "</p>";
}

--------------------------------------------------------------------------------


I'm in over my head at this point (the dummies book just don't cover this, ya know?)

Help greatly appreciated.

change host:p

Well, as I believe MYSQL as a whole doesn't support subquiries yet, thet wont help much, now will it?

You are trying to do a join with two seperate sql statements, you need to either do the full join on the first sql statement, or do the comparison in the second sql statement against the PHP variables (which you kinda did, you are comparing the PHP variable to zipcode.hxagent but selecting against the table agent. In the first sql statement you are selecting against zipcode and agent, but not using agent)

My $0.02 cents :)

First Query:

$query = "select DISTINCT hxagent, sfagent, distributor from zipcodes where zipcode ='$searchterm'";

Second Query:

$query2 = "select * from agents where agentcode ='stripslashes($row["hxagent"])'";

That should work the way you want.