<html>
<head>
<title>Search 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</center></h1>
<h2>Search database with sort & update</h2>
<?php
error_reporting(E_ALL ^ E_WARNING ^ E_NOTICE); //all but warnings & notices
$host = 'localhost'; #local (either PC or NYU server)
// $host = 'oit.scps.nyu.edu'; #NYU server (explicit)
// $host = 'oit.scps.nyu.edu:3306'; #NYU server (explicit with a port)
$data = Array(); #define an array
display_form();
if ($_GET)
{
read_data();
display_result();
}
//===============================================================================
function display_form()
{
$search = $_GET[q]; #get the search argument
$sort = $_GET[s]; #get the sort order
print "<form method=get action=$_SERVER[PHP_SELF] >
Search for:
<input type=text name=q value='$search'>
<input type=submit value=Search>
<hr> \n";
}
//===============================================================================
function read_data()
{
global $host, $data;
$host = $host;
$DBname = 'demo2';
$DBuser = 'demo2';
$DBpswd = 'demo2';
if (!$_GET[q]) #if none provided
$search = "'%'"; #use % wildcard
else
$search = "'%". $_GET[q] ."%'";
if (!$_GET[s]) #if none provided
$_GET[s] = 'lastname_asc'; #use lastname asc
$sort = explode('_', $_GET[s]); #split on '_'
$field = $sort[0];
$seq = $sort[1];
$connect = mysqli_connect($host,$DBuser,$DBpswd,$DBname); #connect to db server
if (! $connect)
die('Could not connect: ' . mysqli_connect_error());
$query = "SELECT firstname,lastname,address,flavor,topping,creditCard,order_id
FROM cust_order
WHERE lower(firstname) LIKE $search
OR lower(lastname) LIKE $search
OR lower(address) LIKE $search
OR flavor LIKE $search
OR topping LIKE $search
OR creditCard LIKE $search
OR order_id LIKE $search
ORDER BY lower($field) $seq"; #not case sensitive
$cursor = mysqli_query($connect,$query); #execute the query
if (! $cursor)
die('Could not execute query: ' . mysqli_error($connect));
$i=0;
while ($row = mysqli_fetch_array($cursor)) #get each row as an array
$data[$i++] = $row; #store row in 2 dim array
mysqli_free_result($cursor); #free result buffer
mysqli_close($connect); #close connection
}
//===============================================================================
function display_result()
{
global $data;
$search = $_GET[q]; #get the search argument
$sort = $_GET[s]; #get the sort order
$name_seq = ($sort =='lastname_asc') ? 'lastname_desc' : 'lastname_asc';
$addr_seq = ($sort =='address_asc') ? 'address_desc' : 'address_asc';
$flav_seq = ($sort =='flavor_asc') ? 'flavor_desc' : 'flavor_asc';
$topg_seq = ($sort =='topping_asc') ? 'topping_desc' : 'topping_asc';
$card_seq = ($sort =='creditCard_asc')? 'creditCard_desc' : 'creditCard_asc';
print "<table>
<tr bgcolor=tan>
<th>Order#</th>
<th><a href=$_SERVER[PHP_SELF]?q=$search&s=$name_seq>Name</a>
<th><a href=$_SERVER[PHP_SELF]?q=$search&s=$addr_seq>Address</a>
<th><a href=$_SERVER[PHP_SELF]?q=$search&s=$flav_seq>Flavors</a>
<th><a href=$_SERVER[PHP_SELF]?q=$search&s=$topg_seq>Toppings</a>
<th><a href=$_SERVER[PHP_SELF]?q=$search&s=$card_seq>Credit Card</a>
<th><i>Action</i></th> \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); #put multiple values
$topping2 = str_replace("," , "<br>", $topping); #on separate lines
print "<tr>";
print "
<td>$order_id</td><td>$first $last</td><td>$address2</td>
<td>$flavor2</td><td>$topping2</td><td>$creditCard</td>
<th><a href=/~sultans/php/demo/4http/app/formToDBUpd.php?row=$order_id>
<img src=/~sultans/php/demo/4http/app/update.gif border=0></a>
<a href=/~sultans/php/demo/4http/app/formToDBUpd.php?row=$order_id>
<img src=/~sultans/php/demo/4http/app/delete.gif border=0></a></th> \n";
}
print "</table> <hr/> \n";
}
//===============================================================================
?>
<?php include "../include.php"; ?> <!-- hyperlink to see the code -->
<center>
<a href=/~sultans/php/demo/4http/app/formToDBAdd.php >add order</a> |
<a href=/~sultans/php/demo/4http/app/getFromDBList.php>list all orders</a> |
search |
<a href=/~sultans/php/demo/4http/app/formToDBUpd.php >update order</a>
</center>
</body>
</html>