Search Relational Database

wendystewart80

I am trying to search a relational database to display linked fields from different tables. I can search and display results from one table, but I don't know how to link the tables in PHP and display results from more than one table.
Here is my php for the search so far:
<?php

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

mysql_select_db ('good_practice');

$search=$_POST["search"];

$result = mysql_query("SELECT * FROM practice");
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"];

echo "Project ID:$id Age Range: $min_age to $max_age Good Practice Example:
 $gptext Updated: $verified <hr> ";

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

There is another table 'categories' which contains several categories for each practice example. I want to know how to display each category for each practice example.
Any suggestions much appreciated.
Many thanks. :eek:

RossC0

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

If you want to return results from more than one SQL table - you need to write a sql query that joins the tables.

i.e

SELECT * FROM practise LEFT JOIN categories ON practise.categoryid = categories.categoryid

I just googled and found this tutorial : http://www.keithjbrown.co.uk/vworks/mysql/mysql_p5.shtml

wendystewart80

Thanks for the help - I managed to return results from multiple tables.
I have now come across a new problem.
There is a one to many relationship - One practice example has more than one category. When the results are displayed, all the fields for the practice example is displayed with each 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');

$region=$POST["region"];
$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 ";
}
?>