[SOLVED] Most efficient way to display group(s) of data, sorted by date

[stealthify]

Hi there,

Hopeless amateur SQL/php romantic here, hoping you can help me with a conundrum I've stumped myself with.

 

To set things up, I've created a table, with data similar to this:

 

Table Name: orders_products

 

id

linked_client_id

shipment_status

shipment_date

tracking_number

product_name

1

1

complete

2009-08-01 00:00:00

0303 0000 1566 4566

Black Hat

2

1

complete

2009-08-01 00:00:00

0303 0000 1566 4566

White Shoes

3

1

open

null

null

Purple Tie (backordered)

4

1

complete

2009-08-20 00:00:00

Z1LA453453

Green Shirt

5

2

complete

2009-08-20 00:00:00

1243 4234 7777 7544

White Hat

6

2

complete

2009-08-20 00:00:00

1243 4234 7777 7544

Blue Jeans

7

3

open

null

null

Purple Tie (backordered)

 

Everything's a piece of cake for me so far. I can SELECT all of the rows for, say, linked_client_id #1, and spit them out into a display table. I'm able to achieve the following simple results:

 

Product Name

Date Shipped

Tracking Number

Black Hat

8/1/2009

0303 0000 1566 4566

White Shoes

8/1/2009

0303 0000 1566 4566

Purple Tie

n/a

not yet shipped

Green Shirt

8/20/2009

Z1LA453453

 

SELECT * FROM orders_products WHERE linked_client_id = '1'

<table>
<tr>
<td>Product Name</td>
<td>Date Shipped</td>
<td>Tracking Number</td>
</tr>
<?php
$results = mysql_query("SELECT * FROM orders_products WHERE linked_client_id = '1'");

while($row = mysql_fetch_array($result)) {
  echo '<tr><td>' . $row['product_name'] . '</td><td>' . $row['shipment_date'] . '</td><td>' . $row['tracking_number'] . '</td></tr>';
}
?>
</table>

 

But I'd like to take it a step further. Only recently have I started to look into GROUPing in SQL, and it gave me an idea. I just don't know what the proper way to go about it is when it comes to the mixture of php code and SQL to get it to display correctly.

 

Here's what I'm hoping to display the data above as:

 

---

Shipment #1 (8/1/2009) | Tracking: 0303 0000 1566 4566

------------------------------------------------------

• Black Hat
  
• White Shoes
  

 

Shipment #2 (8/20/2009) | Tracking: Z1LA453453

------------------------------------------------------

• Green Shirt
  

 

Waiting to be Shipped

------------------------------------------------------

• Purple Tie
  

---

 

Much cleaner looking for sure. I figured grouping them by date or tracking number made the most sense, and then anything with an empty shipment_date gets displayed at the end.

 

It seems so simple, yet I'm over-analyzing it and pulling my hair out on how to correctly attack this. Anyone able to help set me in the right direction?

[kickstart]

Hi

 

Not really a grouping issue. Grouping normally applies to adding up results, etc.

 

What I would normally do is store the current shipment date and only put it out when it changes.

 

Possible things you could do using GROUP_CONCAT (which adds up grouped text), but probably not useful in this case.

 

All the best

 

Keith

[stealthify]

Hi Keith,

That's certainly a start, I figured I might have been influenced with the wrong approach. I've never used GROUP_CONCAT before.

 

If this was all static data, I feel I could do this very easily. The part that's throwing me off is that all of this data is dynamic, especially the column I want to sort by (date). Ugh!

[kickstart]

Hi

 

Minor change to your previous code to maybe give you some ideas. Using unordered lists rather than tables.

 

<?php
$results = mysql_query("SELECT * FROM orders_products WHERE linked_client_id = '1' ORDER BY shipment_date");
$prevShipDate = "";
$ShipNo = 0;
while($row = mysql_fetch_array($result)) {
if ($row['shipment_date'] != $prevShipDate)
{
	if ($ShipNo != 0)
	{
		echo '</ul>';
	}
	$ShipNo++;
	echo '<b>Shipment #'.$ShipNo.'</b> '.$row['shipment_date'] . ' | Tracking ' . $row['tracking_number'];		
	$prevShipDate = $row['shipment_date'];
	echo '<ul>';
}
echo '<li>' . $row['product_name'] . '</li>';		
}
if ($ShipNo != 0)
{
echo '</ul>';
}
?>

 

You would probably want to code around those with a null shipment date to put "not yet shipped".

 

All the best

 

Keith

[stealthify]

Problem solved. I have no idea why I thought this was an SQL trick.

 

Thanks for your time, you are king.