<html>
<!--
=====================================================================================================
This PHP script retrieves data from a database, using multiple tables (parent/child)
Using parent table (cust_order2), and child tables (cust_order2_flavor, cust_order2_topping)
Since the table relationships is 1:N:N, the output is undesirable (multiple lines per single order)
=====================================================================================================
-->
<head>
<title>Retrieve data from database</title>
<style>
a {text-decoration:none; color:brown}
table {font:10pt arial; border:solid 2px black; border-collapse:collapse; margin-left:70px; background-color:#eeeeee; }
th,td {border:solid 1px black; padding-left:5px; padding-right:5px; vertical-align:top;}
</style>
</head>
<body bgcolor=lightyellow>
<h1><center>The Ice Cream Shop 2</center></h1>
<h2>Retrieve data from Database (1:N:N relationship)</h2>
<?php
$host = 'localhost'; #local (either PC or NYU server)
$data = array(); #define an array
read_data();
display();
//===============================================================================
function read_data()
{
global $host, $data, $flavors, $toppings;
$host_port = $host;
$DBname = 'demo2';
$DBuser = 'demo2';
$DBpswd = 'demo2';
$connect = mysqli_connect($host_port,$DBuser,$DBpswd,$DBname); #connect to db server
if (! $connect)
die('Could not connect: ' . mysqli_connect_error());
//query the orders, sort by order_id ------------------------------------
$query = "SELECT firstname,lastname,address,flavor,topping,creditCard,order_id
FROM cust_order2 join cust_order2_flavor using (order_id)
join cust_order2_topping using (order_id)
ORDER BY order_id";
//print $query; #for debugging
$cursor = mysqli_query($connect,$query); #execute the query
if (! $cursor)
die('Could not execute query: ' . mysqli_error($connect));
while ($row = mysqli_fetch_assoc($cursor)) #get each row as an array
$data[] = $row; #store row in 2 dim data array
mysqli_free_result($cursor); #free result buffer
mysqli_close($connect); #close connection
}
//===============================================================================
function display()
{
global $data, $flavors, $toppings;
print "<table>
<tr bgcolor=tan>
<th>Order#<th>Name<th>Address<th>Credit Card<th>Flavors<th>Toppings \n";
foreach($data as $row)
{
$first = $row['firstname'];
$last = $row['lastname'];
$address = $row['address'];
$flavor = $row['flavor'];
$topping = $row['topping'];
$creditCard = $row['creditCard'];
$order_id = $row['order_id'];
$address2 = nl2br($address); #change all \n to <br>
$flavor2 = str_replace("," , "<br>", $flavor); #change commas to <br>
$topping2 = str_replace("," , "<br>", $topping);
print "<tr><td><tr>";
print "<th>$order_id</th><td>$first $last</td><td>$address2</td><td>$creditCard</td>
<td>$flavor2 </td><td>$topping2</td> \n";
}
print "</table> \n";
}
//===============================================================================
?>
<?php include "../include.php"; ?> <!-- hyperlink to see the code -->
</body>
</html>