DISTINCT - worked w/1 table but not 2?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
cdickson
Forum Contributor
Posts: 120
Joined: Mon Apr 05, 2004 1:30 pm
Location: Michigan, USA

DISTINCT - worked w/1 table but not 2?

Post 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);
}
?>
bling
Forum Commoner
Posts: 25
Joined: Mon Jul 12, 2004 12:44 pm

Post by bling »

Date may be a reserved word. Can you change the field name and try it again?
cdickson
Forum Contributor
Posts: 120
Joined: Mon Apr 05, 2004 1:30 pm
Location: Michigan, USA

Post by cdickson »

Thanks for the suggestion, but I know that isn't the problem, as it is working everywhere else.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

you are running the query over and over..
cdickson
Forum Contributor
Posts: 120
Joined: Mon Apr 05, 2004 1:30 pm
Location: Michigan, USA

Post 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.
Post Reply