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 "Project ID:$id Age Range: $min_age to $max_age Good Practice Example:
 $gptext Updated: $verified Categories$gpcatdesc<hr> ";

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

saraadmin

A common way to solve this problem is to use multiple queries.

First select all the practice rows and loop through them (in PHP). Inside those loops, use the current practice ID to construct sub queries of the other tables.

wendystewart80

Can you give an example of how to do this?

saraadmin

OK, your table and field names are a little cryptic, but I'll give it a try:

mysql_connect("localhost", "root", "purple1"); 
mysql_select_db('good_practice'); 

$practice_query = mysql_query("SELECT * FROM practice");

while ($practice_row = mysql_fetch_assoc($practice_query))
{
    echo("<div>\n<h2>Practice Record:</h2>\n");

// echo practice details here

$gpcats_query = mysql_query("SELECT * FROM gpcats WHERE gpcats.gpid=" . $practice_row['gpid']);

while ($gpcats_row = mysql_fetch_assoc($gpcats_query))
{
    echo("<div>\n<h3>GPCATS Record:</h3>\n");
    // echo gpcats details here
    echo("</div>\n");
}

echo("</div>\n");
}

wendystewart80

Thanks so much for your help SarahAdmin

LoganK

BTW, next time, put PHP code within

tags, and mark the thread resolved once it is solved.

P.S. Don't put your DB password in plaintext like that - obscure with asterisks or something, otherwise it's a security risk, and who wants one of those? 😃