One to many database search results

[wendystewart80]

I am using PHP to search a MySQL database
I can search multiple tables successfully but I want to display one result from one table, with multiple results from another table:
In my database one practice example has more than one category. My current PHP displays all the fields for the practice example with every relevant category. I want to display the practice example details once, then list all the categories for that practice example.
Can anyone help with how to do this?
Here is my php so far:
<?php

mysql_connect ("localhost", "root", "purple1");

mysql_select_db ('good_practice');

$search=$_POST["search"];

$result = mysql_query("SELECT * FROM practice AS p, gpcats AS g, def_gpcats AS d WHERE p.gpid = g.gpid
AND g.gpcatid = d.gpcatid");
if(mysql_num_rows($result)>0)

while($r=mysql_fetch_array($result))
{

$id=$r["id"];
$min_age=$r["min_age"];
$max_age=$r["max_age"];
$gptext=$r["gptext"];
$verified=$r["verified"];
$gpcatdesc=$r["gpcatdesc"];

echo "<b>Project ID:</B>$id<br><b>Age Range:</b> $min_age to $max_age<br> <b>Good Practice Example:</b>
<br>$gptext<br> <b>Updated:</b> $verified<br><b>Categories</b>$gpcatdesc<hr><br>";

}
else {echo "Sorry, no results found ";
}
?>

[jaski]

Welcome to the webmasterworld wendystewart80.

If you want to display multiple records based on one record .. you usually write a query and a while loop INSIDE the outer while loop ie nested loops.

Outer while loop is what you currently have. You can construct a query inside that based on variables you are getting by fetching the row, then have another while loop in there to fetch records for the inner query and process those.

Hope that helps.

[wendystewart80]

I tried to add the inner loop and second query but now it returns all the results from table 1 (practice) , then all the results from table 2 (gpcats). The 2 queries are not linked and the results from table 2 are not linked to the results from table 1. How do I link the two queries?

<?php

mysql_connect ("localhost", "root", "purple1");

mysql_select_db ('good_practice');

$region=$_POST["region"];
$search=$_POST["search"];

$result1 = mysql_query("SELECT * FROM practice AS p, project AS t
WHERE p.id = t.id");
$result2 = mysql_query("SELECT gpcatdesc FROM def_gpcats AS d, gpcats AS g, practice AS p
WHERE p.gpid = g.gpid AND g.gpcatid = d.gpcatid");
if(mysql_num_rows($result1)>0)

while($r=mysql_fetch_array($result1))
{
$id=$r["id"];
$min_age=$r["min_age"];
$max_age=$r["max_age"];
$gptext=$r["gptext"];
$verified=$r["verified"];

$org=$r["org"];

echo "<br><hr><br><i>Project: </i><b>$org</b><br><i>Age Range:</i> $min_age to $max_age<br> <i>Good Practice Example:</i>
<br>$gptext<br> <i>Updated:</i> $verified<br><i>Categories: <br>";

while($r=mysql_fetch_array($result2))
{ $gpcatdesc=$r["gpcatdesc"];
echo "$gpcatdesc <br>";
}

}

else {echo "Sorry, no results found ";
}
?>

[wendystewart80]

Managed to solve this on another forum.
The working code is:

<?php

mysql_connect ("localhost", "root", "******");

mysql_select_db ('good_practice');

$region=$_POST["region"];
$search=$_POST["search"];

$result1 = mysql_query("SELECT * FROM practice AS p, project AS t
WHERE p.id = t.id")
or die ("Error - ".mysql_error()."");

if(mysql_num_rows($result1)>0)
{
while($r=mysql_fetch_array($result1))
{
$id=$r["id"];
$min_age=$r["min_age"];
$max_age=$r["max_age"];
$gptext=$r["gptext"];
$verified=$r["verified"];

$org=$r["org"];

echo "<br><hr><br><i>Project: </i><b>$org</b><br><i>Age Range:</i> $min_age to $max_age<br> <i>Good Practice Example:</i>
<br>$gptext<br> <i>Updated:</i> $verified<br><i>Categories: <br>";

$result2 = mysql_query("SELECT gpcatdesc FROM def_gpcats AS d, gpcats AS g, practice AS p
WHERE p.gpid = g.gpid AND g.gpcatid = d.gpcatid AND p.gpid = ".$r["gpid"]."")
or die ("Error - ".mysql_error()."");

while($r2=mysql_fetch_array($result2))
{ $gpcatdesc=$r2["gpcatdesc"];
echo "$gpcatdesc <br>";
}

}

}

else {echo "Sorry, no results found ";
}

?>