Hello all. Here is my problem.
I have a form with a text box on it. The User inputs a City and then the results are returned. I am not getting the correct results. Here is my code, please help.
<HEAD>
<TITLE>Member Firms: Search Member Firms: Search Results</TITLE>
<SCRIPT LANGUAGE="javaScript" SRC="../scripts.js">
window.status="Error! Cannot find scripts.js!";
return true;
</SCRIPT>
</HEAD>
<BODY BGCOLOR="#FFFFFF" LINK="#0000FF" VLINK="#800080" MARGINHEIGHT="0" MARGINWIDTH="0" LEFTMARGIN="0">
<TABLE BORDER="0" WIDTH="100%" HEIGHT="100%" CELLPADDING="" CELLSPACING="0">
<TR>
<TD WIDTH="37" HEIGHT="451" VALIGN="top" ROWSPAN="4">
<IMG SRC="images/member_tab.gif" WIDTH="37" HEIGHT="451" BORDER="0">
</TD>
<TD WIDTH="631" HEIGHT="30" COLSPAN="2" BGCOLOR="#E0E0C6">
</TD>
</TR>
<TR>
<TD WIDTH="30" HEIGHT="421" BGCOLOR="#E0E0C6" ROWSPAN="2" VALIGN="top">
</TD>
<TD WIDTH="601" HEIGHT="17" BGCOLOR="#E0E0C6" VALIGN="top">
<!--Business Sector Search-->
<A HREF="form_main.php3" ONMOUSEOVER="mOver(bus_search)" ONMOUSEOUT="mOut(bus_search)"><IMG SRC="images/bus_search_w.gif" WIDTH="146" HEIGHT="17" NAME="bus_search" BORDER="0"></A>
<!--Geographic Search-->
<A HREF="map_main.php3" ONMOUSEOVER="mOver(geo_search)" ONMOUSEOUT="mOut(geo_search)"><IMG SRC="images/geo_search_w.gif" WIDTH="119" HEIGHT="17" NAME="geo_search" BORDER="0"></A>
</TD>
</TR>
<TR>
<TD WIDTH="601" HEIGHT="421" BGCOLOR="#E0E0C6" VALIGN="top">
<?
$limit=20;
if (!$start)
{
$start=0;
}
mysql_connect("terminator.trytel.com", "acec_mapping", "mapps");
mysql_select_db("acec_webmap");
$query="select tblCompany.CompanyID,tblCompany.CompanyName,tblCompany.City ";
$queryfrom="from tblCompany";
if (($CountryID!="All") && ($CountryID!=""))
{
$queryfrom.=",tblCountry";
if (!$querywhere)
{
$querywhere="where";
}
else
{
$querywhere.=" AND";
}
$querywhere.=" tblCompany.CompanyID=tblCountry.CompanyID and tblCountry.CountryID='".$CountryID ."' " ;
}
elseif ($CountryID=="All")
{
if ($RegionID>0)
{
$queryfrom.=",tblCountry";
if (!$querywhere)
{
$querywhere="where";
}
else
{
$querywhere.=" AND";
}
$regionquery= "select * from tlkpCountry where RegionID='".$RegionID ."'";
$regionresult=mysql_query($regionquery);
$regionum=mysql_num_rows($regionresult);
if($regionum)
{
$querywhere.=" tblCompany.CompanyID=tblCountry.CompanyID and ( ";
while ($regionrow=mysql_fetch_array($regionresult))
{
$querywhere.=" tblCountry.CountryID='".$regionrow["CountryID"] ."' or" ;
}
$querywhere= substr($querywhere,0,-2);
$querywhere.=" ) ";
}
}
}
if ($DisciplineID!="")
{
if (($DisciplineID !="1")||($DisciplineID !="2")||($DisciplineID !="3")||($DisciplineID !="4")||($DisciplineID !="15")||($DisciplineID !="19"))
{
$queryfrom.=",tblEmp";
if (!$querywhere)
{
$querywhere="where";
}
$querywhere.=" tblCompany.CompanyID=tblEmp.CompanyID and tblEmp.DisciplineID like ".$DisciplineID;
}
}
if (($ServiceID!="") && ($ServiceID!="All"))
{
$queryfrom.=",tblSpecializations ";
if (!$querywhere)
{
$querywhere="where";
}
else
{
$querywhere.=" AND";
}
if ($service_perindustry!="All")
{
$querywhere.=" tblCompany.CompanyID=tblSpecializations.CompanyID and tblSpecializations.SpecializationID ='".$service_perindustry."' ";
}
else
{
if ($ServiceID[1]==".")
{
$servicevalue=$ServiceID[0] .".";
}
else
{
$servicevalue=$ServiceID[0] .$ServiceID[1] .".";
}
$querywhere.=" tblCompany.CompanyID=tblSpecializations.CompanyID and tblSpecializations.SpecializationID like '".$servicevalue."%' ";
}
}
if ($CompanyName!="")
{
$temp=split(" ",$CompanyName);
for ($i=0; $i<count($temp); $i++)
{
if (!$querywhere)
{
$querywhere="where";
}
else
{
$querywhere.=" AND";
}
$querywhere.=" tblCompany.CompanyName like '%".$temp[$i]."%'";
}
}
if ($province!="")
{
$queryfrom.="";
if (!$querywhere)
{
$querywhere="where";
}
else
{
$querywhere.=" AND";
}
$querywhere.=" tblCompany.Province='".$province."'";
}
if ($City!="")
{
$queryfrom.="";
if (!$querywhere)
{
$querywhere="where";
}
else
{
$querywhere.=" AND";
}
$querywhere.=" tblCompany.City like '%".$City."%'";
}
$finalquery=$query." ".$queryfrom;
if ($querywhere!="")
{
$finalquery .= " ".$querywhere;
}
$finalquery .= " group by tblCompany.CompanyID order by CompanyName";
//echo $finalquery."<BR>".mysql_error()."<BR>";
$total=mysql_query($finalquery);
$rows=mysql_num_rows($total);
$finalquery .= " limit ".$start.",".$limit;
// echo "<h5>".$finalquery."</h5>\n";
if ($province)
{
echo "<P ALIGN=\"center\"><IMG SRC=\"images/search/$province.gif\" WIDTH=407 HEIGHT=333 BORDER=\"0\" ALT=$province>";
}
else
{
echo "<P ALIGN=\"center\"><IMG SRC=\"images/search/canada.gif\" WIDTH=407 HEIGHT=333 BORDER=\"0\" ALT=CANADA>";
}
$result=mysql_query($finalquery);
if ($rows>0)
{
if (($start+$limit) > $rows)
{
$VIEWTO=$rows;
}
else
{
$VIEWTO=$start+$limit;
}
$VIEWFROM=$start+1;
echo "<CENTER><font face=tahoma color=#48399B><h4>Viewing ".$VIEWFROM." to ".$VIEWTO." of ".$rows." matches found</h4></CENTER>\n";
}
echo "<table border=0 cellpadding=2 cellspacing=0 width=100%>";
if ($rows>0)
{
$counter=$start;
while ($row=mysql_fetch_array($result))
{
echo "<tr>";
$counter++;
echo "<td valign=top><font face=tahoma color=#48399B size=1>".$counter.".</td>";
//echo "<td valign=top><span class=small><font color=\"#48399B\" size=1>".substr($row[0],0,5)."</span></td>";
echo "<td valign=top><font face=tahoma color=#48399B size=1><a href=\"company.php3?CompanyID=".$row[CompanyID]."\">".$row[CompanyName]."</a></td>";
echo "<td valign=top><font face=tahoma color=#48399B size=1><span class=small>(".$row[City].")</span></td>";
if ($counter<$rows)
{
$row=mysql_fetch_array($result);
$counter++;
echo "<td valign=top><font face=tahoma color=#48399B size=1>".$counter.".</td>";
//echo "<td valign=top><font color=\"#48399B\" size=1>".substr($row[0],0,5)."</span></td>";
echo "<td valign=top><font face=tahoma size=1><a href=\"company.php3?CompanyID=".$row[CompanyID]."\">".$row[CompanyName]."</a></td>";
echo "<td valign=top><font face=tahoma color=#48399B size=1>(".$row[City].")</span></td>";
}
echo "</tr>\n";
}
}
else
{
echo "<tr><td colspan=9 align=center><font face=tahoma color=\"#48399B\">No Matched Results</font></td></tr>\n";
}
echo "</table>\n";
echo "<hr>";
if ( ($start-$limit) >= 0 )
{
echo "<CENTER><a href=\"results.php3?start=".($start-$limit)."&DisciplineID=".$DisciplineID."&ServiceID=".$ServiceID."&service_perindustry=".$service_perindustry."&province=".$province."&CompanyName=".$CompanyName."&CountryID=".$CountryID."&RegionID=".$RegionID."\">Last $limit Matches</a> | \n";
}
echo "<a href=\"form_main.php3\">New Search Criteria</a>\n";
if ( $rows > ($start+$limit) )
{
echo " | <a href=\"results.php3?start=".($start+$limit)."&DisciplineID=".$DisciplineID."&ServiceID=".$ServiceID."&service_perindustry=".$service_perindustry."&province=".$province."&CompanyName=".$CompanyName."&CountryID=".$CountryID."&RegionID=".$RegionID."\">Next $limit Matches</a></CENTER>\n";
}
?>
</TD>
</TR>
</TABLE>
</BODY>
TIA
Stormin
