New to SQL, PHP, have big problem....
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?
PHP Code:
$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>";
}
--------------------------------------------------------------------------------
Help greatly appreciated.
