SitePoint Sponsor

User Tag List

Results 1 to 13 of 13

Hybrid View

  1. #1
    Visible Ninja bronze trophy
    JeffWalden's Avatar
    Join Date
    Sep 2002
    Location
    Los Angeles
    Posts
    1,709
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    Post 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 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.
    TAKE A WALK OUTSIDE YOUR MIND.

  2. #2
    Non-Member
    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

  3. #3
    Visible Ninja bronze trophy
    JeffWalden's Avatar
    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.

  4. #4
    Visible Ninja bronze trophy
    JeffWalden's Avatar
    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.

  5. #5
    ********* Member website's Avatar
    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

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    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

  7. #7
    ********* Member website's Avatar
    Join Date
    Oct 2002
    Location
    Iceland
    Posts
    1,238
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937
    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?
    - website

  8. #8
    Visible Ninja bronze trophy
    JeffWalden's Avatar
    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.

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    if you're thinking of using a subquery, you need version 4.1

  10. #10
    Visible Ninja bronze trophy
    JeffWalden's Avatar
    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.

  11. #11
    SitePoint Wizard silver trophy redemption's Avatar
    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
    is OK even though there are 2 of the same days, because the entire primary key (across the 2 fields) is still unique.

  12. #12
    ********* Member website's Avatar
    Join Date
    Oct 2002
    Location
    Iceland
    Posts
    1,238
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ah I see, nice!
    - website

  13. #13
    Visible Ninja bronze trophy
    JeffWalden's Avatar
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •