stealthify Posted August 20, 2009 Share Posted August 20, 2009 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? Quote Link to comment https://forums.phpfreaks.com/topic/171143-solved-most-efficient-way-to-display-groups-of-data-sorted-by-date/ Share on other sites More sharing options...
kickstart Posted August 20, 2009 Share Posted August 20, 2009 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 Quote Link to comment https://forums.phpfreaks.com/topic/171143-solved-most-efficient-way-to-display-groups-of-data-sorted-by-date/#findComment-902570 Share on other sites More sharing options...
stealthify Posted August 20, 2009 Author Share Posted August 20, 2009 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! Quote Link to comment https://forums.phpfreaks.com/topic/171143-solved-most-efficient-way-to-display-groups-of-data-sorted-by-date/#findComment-902577 Share on other sites More sharing options...
kickstart Posted August 20, 2009 Share Posted August 20, 2009 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 Quote Link to comment https://forums.phpfreaks.com/topic/171143-solved-most-efficient-way-to-display-groups-of-data-sorted-by-date/#findComment-902605 Share on other sites More sharing options...
stealthify Posted August 21, 2009 Author Share Posted August 21, 2009 Problem solved. I have no idea why I thought this was an SQL trick. Thanks for your time, you are king. Quote Link to comment https://forums.phpfreaks.com/topic/171143-solved-most-efficient-way-to-display-groups-of-data-sorted-by-date/#findComment-903135 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.