Page 1 of 1

DISTINCT - worked w/1 table but not 2?

Posted: Thu Sep 30, 2004 12:02 pm
by cdickson
I am trying to retrieve info from a db by order date. On a previous project where the database consisted of a single table I was able to successfully use DISTINCT, but it seems that now I have 2 tables, I am doing something wrong.

I get a list, but instead of getting ALL dates on which orders have been placed, I am getting a repetitive list of only the LAST date on which an order was placed.

Can anyone tell me where I have erred?

Code: Select all

<?php
//View orders by order date
$query3 = "SELECT DISTINCT date FROM orders ORDER BY date DESC";
$result3 = mysql_query($query3);
$row3 = mysql_fetch_assoc($result3);
?>

//html

<?php
do {  
?>

<?php
//Run query
if ($result3 = mysql_query ($query3));
			  
//Convert order date format
$date = $row3['date'];
$date_arr = explode('-', trim($date));
$order_date = ($date_arr[1].'-'.$date_arr[2].'-'.$date_arr[0]);
?>

<!-- Show list of available dates -->
<option value="<?php echo $order_date; ?>"><?php echo $order_date; ?></option>

<?php
} while ($row3 = mysql_fetch_assoc($result3));
$rows = mysql_num_rows($result3);
if($rows > 0) {
mysql_data_seek($result3, 0);
$row3 = mysql_fetch_assoc($result3);
}
?>

Posted: Thu Sep 30, 2004 1:56 pm
by bling
Date may be a reserved word. Can you change the field name and try it again?

Posted: Thu Sep 30, 2004 2:24 pm
by cdickson
Thanks for the suggestion, but I know that isn't the problem, as it is working everywhere else.

Posted: Thu Sep 30, 2004 2:38 pm
by feyd
you are running the query over and over..

Posted: Sat Oct 02, 2004 9:31 am
by cdickson
:idea: Got it! Remove Line 15 and all is good.
Thank you once again, feyd.

Hopefully some day I'll know enough to actually help others in exchange for all of the help you all give me.