DISTINCT Not Working

[JeffWalden]

I need to make a drop menu to select a date from the table.

This is what I have:

$soups = mysql_query("SELECT DISTINCT Day, SoupID FROM DailySoups");
?>
<a href="newdate.php">Add New Date</a>
<form action="newdatesql.php" method="get">

<select name="calendar" size="1">
<?php
while ($list = mysql_fetch_array($soups)) {
$date = $list["Day"];
$sname = htmlspecialchars($list["SoupID"]);
echo("<option value='$date'>$date</option>");
}
?>
</select><p>

The drop menu works fine, except for the fact that I am seeing the same date in there twice. I tried using the DISTINCT feature, but it doesn't seem to work.

The table is setup to accompany a new row for every unique combination of dates and SoupID. This means that there may be 3 rows that have 2003-04-27 with a SoupID that is different each time. In short, this is a schedule to view the "SoupID"(s) for that selected day.

Anyway, I have probably confused most of you, but I can't get the DISTINCT thing to work... I think. If you would like me to post the structure and data of the table, let me know.

You can view the semi-working version here: http://www.soupladle.com/admin/dailysoups.php

Thanks.

[Dr Livingston]

Where is your PK in there ? What ? You don't have a PK ? LoL

[JeffWalden]

[img]images/smilies/confused.gif[/img] What are you talking about? A Primary Key? Both columns (Day and SoupID) are primary keys. Here is the structure and data:

#
# Table structure for table `DailySoups`
#

CREATE TABLE DailySoups (
Day date NOT NULL default '0000-00-00',
SoupID smallint(6) NOT NULL default '0',
PRIMARY KEY (Day,SoupID)
) TYPE=MyISAM;

#
# Dumping data for table `DailySoups`
#

INSERT INTO DailySoups VALUES ('2003-04-27', 1);
INSERT INTO DailySoups VALUES ('2003-04-27', 2);
INSERT INTO DailySoups VALUES ('2003-04-28', 3);
INSERT INTO DailySoups VALUES ('2003-04-28', 10);

[JeffWalden]

Anybody know why the DISTINCT won't work? Or maybe it is, but I am just setting it up wrong?

[website]

well, I belive that you can (or it is recommended) to have only 1 primary key in a table, also primary key is unique so you may not have have more than one line of the same soupID in this table and you may never have the same day. So your table is basicly... scrued!

but distinct should work else I think, you might try to use GROUP BY instead.

[r937]

website, hyperbolik's primary key is a composite key, perfectly okay (except for using a reserved word as a column name), and furthermore, it is the only primary key -- you were right that a table can have only one, but it is allowed to be composite

hyperbolik, DISTINCT does work, the problem is, it doesn't work the way you think it works, it works the way it's supposed to

when you say SELECT DISTINCT Day, SoupID you will get exactly that -- all the distinct Day/SoupID combinations


rudy

[JeffWalden]

I think I figured it out. I setup the query to select DISTINCT days, and put the query for soupid into another part.

Thanks again!

[r937]

if you're thinking of using a subquery, you need version 4.1

[JeffWalden]

No, I used two completely seperate queries. It works fine.

That and I have 4.2

THANKS AGAIN!

[website]

website, hyperbolik's primary key is a composite key, perfectly okay (except for using a reserved word as a column name)

ok, little mistake, but I thought since primarky key should be unique eg if the same date can come twice it isn't unique.

and what does hyperbolik mean?

[redemption]

Yes a primary key should be unique throughout a table.

The primary key for hyperbolik's table is (Day,SoupID), meaning that we only have unique combinations of Day and SoupID.

So this:

tue, chicken
tue, mushroom
wed, rock

is OK even though there are 2 of the same days, because the entire primary key (across the 2 fields) is still unique.

[website]

ah I see, nice!

[JeffWalden]

Thanks Guys!