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)== 0 ) {
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)?" ":$c, "</td>";
echo "</tr>PHP_EOL";
}
echo "</table>PHP_EOL";
}
?>
|
|