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 `parent.id` ...

<?php

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

// WAS A RESULT POSTED FROM THE FORM?
if( isset($_POST["$master_keycol"]) ) {
  
$keyval mysqli_real_escape_string$conn$_POST["$master_keycol"] );
}
else {
  
$keyval='';
}

// PUT UP THE FORM
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>
</form>
"
;

// DISPLAY RESULTS IF ANY
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" );
}

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

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

?>

Return to the Artful MySQL Tips page