SitePoint Sponsor |
|
User Tag List
Results 1 to 13 of 13
Thread: DISTINCT Not Working
-
Apr 27, 2003, 12:55 #1
- Join Date
- Sep 2002
- Location
- Los Angeles
- Posts
- 1,709
- Mentioned
- 5 Post(s)
- Tagged
- 0 Thread(s)
DISTINCT Not Working
I need to make a drop menu to select a date from the table.
This is what I have:
PHP Code:$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 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.TAKE A WALK OUTSIDE YOUR MIND.
-
Apr 27, 2003, 13:36 #2
- Join Date
- Jan 2003
- Posts
- 5,748
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Where is your PK in there ? What ? You don't have a PK ? LoL
-
Apr 27, 2003, 13:41 #3
- Join Date
- Sep 2002
- Location
- Los Angeles
- Posts
- 1,709
- Mentioned
- 5 Post(s)
- Tagged
- 0 Thread(s)
[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);TAKE A WALK OUTSIDE YOUR MIND.
-
Apr 27, 2003, 15:43 #4
- Join Date
- Sep 2002
- Location
- Los Angeles
- Posts
- 1,709
- Mentioned
- 5 Post(s)
- Tagged
- 0 Thread(s)
Anybody know why the DISTINCT won't work? Or maybe it is, but I am just setting it up wrong?
TAKE A WALK OUTSIDE YOUR MIND.
-
Apr 27, 2003, 15:50 #5
- Join Date
- Oct 2002
- Location
- Iceland
- Posts
- 1,238
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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.- website
-
Apr 27, 2003, 16:26 #6
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
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
-
Apr 27, 2003, 16:29 #7
- Join Date
- Sep 2002
- Location
- Los Angeles
- Posts
- 1,709
- Mentioned
- 5 Post(s)
- Tagged
- 0 Thread(s)
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!TAKE A WALK OUTSIDE YOUR MIND.
-
Apr 27, 2003, 17:44 #8
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
if you're thinking of using a subquery, you need version 4.1
-
Apr 27, 2003, 17:53 #9
- Join Date
- Sep 2002
- Location
- Los Angeles
- Posts
- 1,709
- Mentioned
- 5 Post(s)
- Tagged
- 0 Thread(s)
No, I used two completely seperate queries. It works fine.
That and I have 4.2
THANKS AGAIN!TAKE A WALK OUTSIDE YOUR MIND.
-
Apr 28, 2003, 10:35 #10
- Join Date
- Oct 2002
- Location
- Iceland
- Posts
- 1,238
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Originally Posted by r937
and what does hyperbolik mean?- website
-
Apr 28, 2003, 11:10 #11
- Join Date
- Sep 2001
- Location
- Singapore
- Posts
- 5,269
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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:
Code:tue, chicken tue, mushroom wed, rock
-
Apr 28, 2003, 11:14 #12
- Join Date
- Oct 2002
- Location
- Iceland
- Posts
- 1,238
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
ah I see, nice!
- website
-
Apr 28, 2003, 17:31 #13
- Join Date
- Sep 2002
- Location
- Los Angeles
- Posts
- 1,709
- Mentioned
- 5 Post(s)
- Tagged
- 0 Thread(s)
Thanks Guys!
TAKE A WALK OUTSIDE YOUR MIND.
Bookmarks