MySQL Forums
Forum List  »  PHP

Re: Returing multiple rows based on 2 criteria
Posted by: chris d
Date: December 09, 2012 10:17PM

else if($host == 'localhost/xampp/web/members/employee.php?report=uncompleted')
{
global $db;
//$reportType=$_GET['reporttype'];
$sqlOrder = "SELECT * FROM `order` WHERE orderStatus = 'uncompleted'";
$orderResult = $db->query($sqlOrder);
//$result = query($sql);
//$output[] = '<ul>';
$output[] = '<h2 class="report" style="text-align: center;">Uncompleted Orders</h2>';

// Report Column Headers
$output[] = '<table style="text-decoration: underline;" width="100%" cellspacing="0" cellpadding="0" border="0">';
$output[] = '<tbody>';
$output[] = '<td class="reportHeader" width="20%" align="center">Order ID</td>';
$output[] = '<td class="reportHeader" width="20%" align="center">Order Date</td>';
$output[] = '<td class="reportHeader" width="20%" align="center">Items</td>';
$output[] = '<td class="reportHeader" width="20%" align="center">Customer</td>';
$output[] = '<td class="reportHeader" width="20%" align="center">Status</td>';
$output[] = '</tbody>';
$output[] = '</table>';
$output[] = '<br />';

while ($row = $orderResult->fetch()) {
$output[] = '<table class="report" width="100%" cellspacing="0" cellpadding="0" border="0">';
$output[] = '<tbody>';
$output[] = '<tr style="border: 1px, thin, solid">';
$output[] = '<td width="20%" align="center">';
$output[] = $row['orderID'];
$output[] = '</td>';
$output[] = '<td width="20%" align="center">';
$output[] = $row['orderDateTime'];
$output[] = '</td>';
$output[] = '<td width="20%" align="center">';

// Retrieve Order Items
$sql = mysql_query("SELECT * FROM `order_line` WHERE `orderID` = ".$row['orderID']);
$orderItems = mysql_fetch_array($sql);

// MySQL Join Statement
//$sql2 = mysql_query("SELECT ");
//$sql2join = mysql_fetch_array($sql2);

//$output[] = $row['orderID'].' is equal to '.$orderItems[1];
if($row['orderID'] == $orderItems[1]) /* THIS IS WHAT RETRIEVES AND DISPLAYS THE SINGLE ITEM */
{
$getItem = mysql_query("SELECT * FROM `item` WHERE `itemID` = ".$orderItems[4]);
$getItemInfo = mysql_fetch_array($getItem);
$output[] = $getItemInfo[1].', ';

//$sql = mysql_query("SELECT * FROM `order_line` WHERE `orderID` = ".$row['orderID']);
//$orderItems = mysql_fetch_array($sql);

}

//$output[] = '<td width="20%" style="padding-left: 30px;">';
//$output[] = $orderItems[1];
$output[] = '</td>';
$output[] = '<td width="20%" align="center">';

// Fetch Customer Data for respective Order Number
$sqlCust = mysql_query ("SELECT * FROM `customer` WHERE `custID` =" .$row['custID']);
$custInfo = mysql_fetch_array($sqlCust);
$output[] = $custInfo['custFirstName']. ' ' .$custInfo['custLastName'];
$output[] = '</td>';

$output[] = '<td width="20%" align="center">';

$output[] = '<form method="post" action="./inc/updateStatus.php">';
$output[] = '<select name="status" onChange="changeStatus('.$row['orderID'].')">';
$output[] = '<option name="uncompleted" value="'.$row['orderStatus'].'">'.$row['orderStatus'].'</option>';
$output[] = '<option name="completed" value="completed">Completed</option>';
$output[] = '<option name="delete" value="delete">Delete</option>';
$output[] = '</select>';
$output[] = '<button type="submit" name="order" value="'.$row['orderID'].'">Go!</button>';
$output[] = '</form>';


//value="'.$row['orderStatus'].'">'.$row['orderStatus'].'</select>
$output[] = '</td>';
//$output[] = '<li style="padding: 0 20px 0 20px; font-family: calibri, sans-serif;"><b>'.$row['orderID']. ' '.$row['orderDateTime'].'</b>'.$row['orderPrice'].'<br /><br /></li>';
//$output[] = '<li style="padding: 0 20px 0 20px; font-family: calibri, sans-serif;"><b>'.$row['orderID']. ' '.$row['orderDateTime'].'</b>'.$row['orderPrice'].'<br /><br /></li>';
//$output[] = '<li>'.$row['itemName'].'<form class=add_item action=./inc/cart.php?action=add&id='.$row['itemID'].'"><input type=submit value="Add Item" /></form>'.$row['itemID'].'"><br /> '.$row['itemDesc'].': &dollar;'.$row['itemPrice'].'<br /><br /></li>';
//$output[] = '</td>';
$output[] = '</tr>';
$output[] = '</tbody>';
$output[] = '</table>';
}
$output[] = '<br />';

echo join('',$output);
}



This returns a page liek this:
http://oi45.tinypic.com/2n1de6g.jpg

and the order_items table looks like this:
http://oi46.tinypic.com/qrdsoy.jpg

So basically, i need to be able to retrieve the other items in a particular order, and then display them in a comma separated fashion. Thanks for the response!

Options: ReplyQuote




Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.