I am searching my database of projects. Each project has a many 2 many relationship with categories as one project can have many categories and one category can have many projects.
I can search my database by category($cat), however this many to many relationship is causing me a problem as when a project has more than one category, the project details are displayed multiple time, e.g. if the project has 3 categories, all the project details are displayed 3 times.
I want the project details to be displayed once.
Can anyone help?
I have 4 query loops:
$result4 searches by category ($cat)
$result1 searches by $search and $region and displays project information
£result2 displays a list of categories relevant to each project
$result 3 displays practice examples relevant to each project
My php code follows:
mysql_connect ("localhost", "root", "purple1");
mysql_select_db ('good_practice');
$cat=$_POST["cat"];
$region=$_POST["region"];
$search=$_POST["search"];
$result4 = mysql_query("SELECT catdesc, g.catid, p.id FROM def_cats AS d, cats AS g, project AS p
WHERE g.catid = d.catid AND g.id = p.id AND '$cat'='0'
OR g.catid = d.catid AND g.id = p.id AND g.catid LIKE '$cat'
")
or die ("Error - ".mysql_error()."");
if(mysql_num_rows($result4)>0)
{
while($r4=mysql_fetch_array($result4))
{
$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' AND id = ".$r4["id"]."
OR p.region = r.region AND org LIKE '%$search%' AND $region=0 AND id = ".$r4["id"]."
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>";
$result2 = mysql_query("SELECT catdesc, g.catid FROM def_cats AS d, cats AS g
WHERE g.catid = d.catid AND g.id = ".$r["id"]."
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>";
echo " <td width=25><td valign=top width=400>";
$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 "<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>";
}
}
}
echo " </table>";
}
}
else {echo "Sorry, no results found ";
}
