Master-detail data

from the Artful MySQL Tips List

More weeks than not, someone in a MySQL forum asks how to display data from a master table row along with matching data from a detail table.

It's a 4-step:
1. Connect to MySQL.
2. Put up a form that collects input and posts the result back to the script.
3. Retrieve the key value and associated data from the master table.
4. Retrieve matching data from the detail table.

This example assumes a master table named `parent`, primary key `ID`, and a detail table named `child` with a column `parent_id` that references `` ...


$script "test.php";  // NAME OF THIS SCRIPT
$db "test";          // DATABASE
$host "localhost";   // CHANGE AS REQUIRED
$master_tbl "parent";
$detail_tbl "child";
$master_keycol "id";
$detail_keycol "parent_id";
$conn dbconnect$host$user$pswd$db );

if( isset($_POST["$master_keycol"]) ) {
$keyval mysqli_real_escape_string$conn$_POST["$master_keycol"] );
else {

echo "
<form action='
$script' method='post'>
  ID: <input type='text' name='
$master_keycol' value='$keyval' size='4'>
  <button id='save' type='submit'>Save</button>
  <button id='cancel' type='button'>Cancel</button>

if( !empty($keyval) ) {
$resmaster mysqli_query$conn"select * from $master_tbl where $master_keycol=$keyval
               or exit( 
mysqli_error($conn) );
r2html$resmaster"Master" );
$resdetail mysqli_query$conn"select * from $detail_tbl where $detail_keycol=$keyval
               or exit( 
mysqli_error($conn) ); ;
r2html$resdetail"Detail" );

function dbconnect$host "LOCALHOST",
$user "USR",
$pswd "PSWD",
) {
$conn mysqli_connect$host$user$pswd$db ) or exit( mysqli_connect_error() );
"<p>Connected to $db DB</p>n";

function r2html$res$hdr="Results"$tblformat="border='1'" ) { 
is_bool$res )) return $res "True" "False";
  if( !empty(
$hdr) ) echo "<br /><b>$hdr</b><br />";
mysqli_num_rows($res)== ) {
"No rows found.";
"<table $tblformat>PHP_EOL<tr>";
$f mysqli_fetch_field$res )) echo "<td><b>"$f->name"</b></td>PHP_EOL";
$row mysqli_fetch_row$res )) {
$row as $c ) echo "<td>", empty($c)?"&nbsp;":$c"</td>";


Return to the Artful MySQL Tips page