[Doug Lochert]

I've been stuck on a problem in displaying SQL data in a desired format for a few days now and was hoping someone might be able to point me in the right direction. I hope I can explain this properly...

I am developing a WEBSITE for my wife's catering business. What I have is a shopping cart where clients can browse the site and select products which are passed to their cart using $_SESSION. After the client makes selections and confirms desired quantities etc they can SUBMIT their order, and the data is passed to the DB(database).

Each of the selected products are posted to the DB singularily, meaning each item creates another row in the DB, but each has the exact same date/time in the "orderDate" field. (This is my novice way of grouping orders by "date" rather than perhaps a better method of grouping them by "orderID").

This system seems to work ok for most intents and purposes. What I would like to improve upon is how the client (or site admins) view the posted orders. Currently, the client (or admins) can go to their Profile and view their posted orders which are kept on record for their convenience which they can print etc for their own files.

Here's the problem...
I cannot figure out how to group the orders by the "date/time" so they display in same table more resembling a "purchase order" with totals etc for each date, and not a list of every "product" ordered. I have no problem in producing the tables and totals etc. I believe the problem resides in fetching the data properly in perhaps arrays or as I've seen before, [$i] statements?

Below is a snapshot showing how it displays now, to give you an idea:

If image not loading: IMAGE HERE

This is the PHP code I'm using to fetch the data, and then display it:

<?php
$connection = @mysql_connect($server, $dbusername, $dbpassword) or die(mysql_error());
$db = @mysql_select_db($db_name,$connection) or die(mysql_error());
$sql ="SELECT * FROM orders ORDER BY orderDate DESC";
$result = @mysql_query($sql,$connection) or die(mysql_error());
$num = mysql_num_rows($result);

while ($sql = mysql_fetch_object($result)) {
	$uname	 =	$sql -> username;
	$company   =	$sql -> company;
	$order        = 	$sql -> orderNumber;
	$date	        =	$sql -> orderDate;
	$total	        =	$sql -> orderTotal;
	$pqty         =	$sql -> productQty;
	$pname	 =	$sql -> productName;
	$pprice	 =	$sql -> productPrice;
	$comment   =	$sql -> comment;

        echo'
	<table class=orders>
		<tr>
			<td>
				'.$date.'
			</td>
			<td>
				'.$uname.' 
			</td>
			<td>
				('.$company.')
			</td>
		</tr>
		<tr class=items>
			<td>
				'.$pname.'
			</td>
			<td colspan="2">
				'.$pqty.' @ '.$pprice.' = $ '.$total.'
			</td>
		</tr>
                <tr>
                        <td colspan="3">
                             '.$comment.'
                        </td>
                </tr>
	</table>
        ';
        }

I hope this makes some sense, and look forward to any useful direction.

[grace1004]

How about using 'group by' as shown below:

$sql ="SELECT * FROM orders group by orderDate DESC";

For output code, why don't you add a headline as shown in the following example code:

PHP Code:

echo "<table border='1'>     
<tr>     
<td>Roll</td>     
<td>Stream</td>     
<td>Session</td>     
<td>Name</td>     
<td>Mother Name</td>     
<td>Father Name</td>     
<td>Total Mark's</td>     
<td>Grade</td>     
<td>Result</td>     
</tr>";     
   
while($row = mysql_fetch_array($result)) {  

  echo "<tr>";     
  echo "<td>" . $row['Roll'] . "</td>";     
  echo "<td>" . $row['Stream'] . "</td>";     
  echo "<td>" . $row['Session'] . "</td>";     
  echo "<td>" . $row['Name'] . "</td>";     
  echo "<td>" . $row['Mother_Name'] . "</td>";     
  echo "<td>" . $row['Father_Name'] . "</td>";     
  echo "<td>" . $row['Total_Marks'] . "</td>";     
  echo "<td>" . $row['Grade'] . "</td>";     
  echo "<td>" . $row['Result'] . "</td>";     
  echo "</tr>";     

} // end of while loop    
} // end of else statement    
 echo "</table>";   

?> 


[Doug Lochert]

I did try using GROUP BY, as you suggested...

But the output only lists ONE of the items posted, and not the entire order.
In the image above, (as example) 2 items were posted in an SINGLE order and both items are listed, however they are listed individually. Looking like 2 separate orders when they are actually one order.

As for headings for each column displayed, that is simple enough and also good idea, one I had planned on implementing... once I figure out how to list the orders properly.

[grace1004]

I tested 'group by' with my table having different time by second; so it looks like working. Your are right; keep using 'order by'.

[Doug Lochert]

I do believe "GROUP BY" is what we require... but now there is a way to "parse or explode" the grouped data retrieved instead of just displaying one of the rows (or objects in my case), perhaps by use of something like foreach($date as [i]-> i++) somehow? Me being the novice that I am can't quite figure this out, and have been stuck on it awhile. Google queries haven't turned up a lot, as it's difficult to pose the question in any short terms.

[grace1004]

I could not find a way to add sub_total by orderDate. How about displaying another table resulting from the following query?

PHP Code:

$sql2 = "select orderDate, sum(orderTotal) as sub_total from orders group by orderDate order by orderDate desc";
$result2 = mysql_query($sql2);

echo "<table border='1'>      
<tr>      
<td>Date</td>      
<td>Sub_Total</td>  
</tr>"; 

while($row=mysql_fetch_array($result2)) {
echo "<tr>";      
  echo "<td>" . $row['orderDate'] . "</td>";      
  echo "<td>" . $row['sub_total'] . "</td>";      
echo "</tr>"    
}
echo "</table>"; 


[Doug Lochert]

I'm not exactly sure I understand your suggestion... I do however appreciate the feedback, keep it coming!

Calculating totals is not the issue. The field orderTotal is already the sum of the order, which I calculated in PHP using $orderTotal = round($pprice * $pqty) before posting it to DB. But of coarse, once I get the other order items to actually list within the same table, and not in a seperate table, we will perform another calculation to sum all of the item quantities in the order, and that is easy to perform, I have no issue there.

The issue is with getting all of the items that were posted within a single order to display within that same table(paragraph), instead of creating a new table for each item. As you may see from the image provided above, the page is showing ALL orders placed on the site... the first two items (Chicken/Sausage) are one order but are displayed in two tables(paragraphs), but they should be displayed within same table(paragraph). One table, listing each of the items ordered by that person/dateOrdered, not two tables with one item in each.

[grace1004]

Have you tried the posted code with a heading? That might be nearly what you want if lines are added between different date.

The total I suggested is a sub_total for all products ordered on the same date, i.e. date_total.
I thought you want to add date_total to the table format that I suggested. Sorry for my misunderstanding!

[Doug Lochert]

I had to do a little bit of reconstruction... but after a few tweaks I was FINALLY able to get it to work and I'm happy to say your suggestion was right on the money. At first I just couldn't grasp what you were suggesting, or that it could be something so simple as table placement. Secondly, I wasn't aware that it was possible to NEST one SQL call within another, never mind 3 of them... which is basically what I ended up doing.



Grace, thank you so much for taking the time to help out, I truly do appreciate it!