Sort fields within a WHILE loop?

[jeff5656]

$query = "SELECT painting, date1, date2, date3 FROM paintings WHERE artist_id = '$ar' ";
$result = mysql_query ($query) or die ("Invalid query: " . mysql_error ());
while ($row = mysql_fetch_assoc ($result)) {
   echo "The 3 sale dates for ".$row['painting']." are"$row['date......."
   echo "<br>";
}

 

Now within this while loop for each painting I want to echo out the 3 dates in order (sometimes date3 may be earlier than date1 or vice-versa).  How do I sort those three dates when I am already in the while loop?  Also how this work if one of the dates was blank?

Thanks.

[jdavidbakr]

The easiest might be to put the three dates into an array as timestamps and sort the array. Then you can just implode that array for your output.

[DavidAM]

First, don't design your tables with multiple columns containing the same information. This should really be two separate tables, then you could sort the results in the query.

 

To use what you have, you could do a very convoluted IF inside the query, or collect the data in PHP and sort it:

 

$query = "SELECT painting, date1, date2, date3 FROM paintings WHERE artist_id = '$ar' ";
$result = mysql_query ($query) or die ("Invalid query: " . mysql_error ());
// collect the data
$hold = array();
while ($row = mysql_fetch_assoc ($result)) {
   if (!empty($row['date1'])) $hold[$row['painting']][] = $row['date1']
   if (!empty($row['date2'])) $hold[$row['painting']][] = $row['date2']
   if (!empty($row['date3'])) $hold[$row['painting']][] = $row['date3']
}
// now sort and output
foreach ($hold as $painting => $dates) {
   sort($dates);
   echo "The " . count($dates) . " sale dates for ". $painting . " are " . implode(', ', $dates);
   echo "<br>";
}

* This code is not tested and carries no warranty whatsoever.

[laffin]

wow that is a convoluted if.

I woulda opted for a simpler design, like

<?php
date_default_timezone_set('PST8PDT');
$date1=date('Y-m-d H:i:s',$ct=time());
$date2=date('Y-m-d H:i:s',$ct+=rand(1,20000)-10000);
$date3=date('Y-m-d H:i:s',$ct+=rand(1,20000)-10000);
for($i=1;$i<=3;$i++)
{
	$di="date{$i}";
	$d[$i]=$$di;
}
foreach($d as $dt)
	echo $dt.PHP_EOL;
sort($d);
echo PHP_EOL;
foreach($d as $dt)
	echo $dt.PHP_EOL;

 

Even tho the data is convoluted, php does has some features which make things like this possible