Hi there,
Can you help?
I am using php to search a mysql database. I can search by table columns where the relationship is one to one, e.g. where keywords in a search are to match certain fields in a table.
I want to search by the category type of a project. Each project has more than one category so I have a table for projects and a separate table for categories.
To display results from this one to many relationship I have 2 queries. I have tried to search by category in the second query($result2), where the categories are called, but if I select a category (e.g cat1) from the drop down list (cats) on my form, the error message is returned:
Error - Unknown column 'cat1' in 'where clause'
Any suggestions are to what I am doing wrong would be greatly appreciated.
<?php
mysql_connect ("localhost", "root", "******");
mysql_select_db ('good_practice');
$cat=$POST["cat"];
$region=$POST["region"];
$search=$_POST["search"];
$result1 = mysql_query("SELECT * FROM project AS p, regions AS r
WHERE p.region = r.region AND org LIKE '%$search%' AND p.region LIKE '$region'
OR p.region = r.region AND org LIKE '%$search%' AND $region=0
ORDER BY org
")
or die ("Error - ".mysql_error()."");
if(mysql_num_rows($result1)>0)
{
while($r=mysql_fetch_array($result1))
{
$id=$r["id"];
$org=$r["org"];
$address1=$r["address1"];
$address2=$r["address2"];
$address3=$r["address3"];
$city=$r["city"];
$pcode=$r["pcode"];
$tel=$r["tel"];
$fax=$r["fax"];
$email=$r["email"];
$web=$r["web"];
$name=$r["name"];
$surname=$r["surname"];
$image=$r["image"];
$updated=$r["updated"];
$regdesc=$r["regdesc"];
echo "<table width=800><tr><td><hr></table><table><tr bgcolor=#ffffff><td width=225 valign=top><font face=Arial, Helvetica, sans-serif size=3><b>$org</b> <br> <font face=Arial, Helvetica, sans-serif size=2>
$address1";
if($address2>0)print "<br>$address2";
if($address3>0)print "<br>$address3";
echo "<br> <font face=Arial, Helvetica, sans-serif size=2>
$city<br>$pcode<br><i>Region:</i> $regdesc";
if($tel>0)print "<br><i>Tel:</i> $tel<br>";
if($fax>0)print "<i>Fax:</i> $fax<br>";
print "<i>Email:</i> $email<br>";
print "<i>Contact Name: </i>$name $surname<br>";
print "<i>Web: </i>$web<br>";
if($updated>0)print "<i>Updated:</i> $updated<br>";
echo"<font face=Arial, Helvetica, sans-serif size=2><b>Categories:</b><br>";
$cat=$_POST["cat"];
$result2 = mysql_query("SELECT catdesc, g.catid FROM def_cats AS d, cats AS g, project AS p
WHERE g.catid = d.catid AND p.id = g.id AND p.id = ".$r["id"]." AND $cat=0
OR g.catid = d.catid AND p.id = g.id AND p.id = ".$r["id"]." AND g.catid LIKE '$cat'
ORDER BY catdesc
")
or die ("Error - ".mysql_error()."");
while($r2=mysql_fetch_array($result2))
{ $catdesc=$r2["catdesc"];
echo "<font face=Arial, Helvetica, sans-serif size=2>$catdesc <br>";
}
echo"<td valign=top><font face=Arial, Helvetica, sans-serif size=2><img src='$image' width='100'></td>";
$result3 = mysql_query("SELECT * FROM practice AS p, project AS t, regions AS r
WHERE p.id = t.id AND t.region = r.region AND p.id = ".$r["id"]."
ORDER BY verified
")
or die ("Error - ".mysql_error()."");
while($r3=mysql_fetch_array($result3))
{ $gptext=$r3["gptext"];
$gptitle=$r3["gptitle"];
$min_age=$r3["min_age"];
$max_age=$r3["max_age"];
$verified=$r3["verified"];
echo "<td width=25><td valign=top width=400><font face=Arial, Helvetica, sans-serif size=2><i>Practice Example Title: </i><b>$gptitle</b><br>
<i>Age range:</i> $min_age to $max_age <br><i>Updated:</i> $verified<br><br><hr></table>";
}
}
}
else {echo "Sorry, no results found ";
}
?>
