SitePoint Sponsor

User Tag List

Results 1 to 15 of 15
  1. #1
    SitePoint Enthusiast
    Join Date
    May 2003
    Location
    Lincoln NE
    Posts
    58
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question drop down Menu question!

    I'm trying to pull some data from my database to insert in my <Select></select> drop down menu
    but some data in my MDATE column (date column) is repetitive (have the same date), and I'm trying to figuer out a way not to display the same date over and over if it appeared once in the drop down menu, but I also wants it to send the correct ID to the results page

    also each one is attached to a unique ID. is there anyway to resolve this issue,
    this page will be used for a search function, and this drop down menu is used to search records by date.
    but again, a lot of records have the same date, and I just want to display this date once in the <select>menu but have it show all the records that has this date results page.

    here is the code:

    <select name="dsearch" size="1">
    <option selected value="">***Any Date***</option>
    <?php
    $datequery = mysql_query('SELECT ID, MDATE FROM LBL ORDER BY MDATE');

    while ($drow = mysql_fetch_array($datequery)) {
    $did = $drow['ID'];
    $date = htmlspecialchars($drow['MDATE']);
    echo("<option value='$did'>$date</option>\n");
    }
    ?>
    </select>


    Your time and help are greatly appericated.
    chaffe Barbar
    Last edited by chafikb; May 18, 2003 at 09:25.

  2. #2
    Non-Member
    Join Date
    Jan 2003
    Posts
    5,748
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    PHP Code:
    [color=#483d8b]'SELECT DISTINCT ID, MDATE FROM LBL ORDER BY MDATE'[/color]
    [color=#483d8b][/color] 


    Should solve the problem of returning records with multiple dates though I'd say that you really should redesign your database design to remove this repeatitive data....


  3. #3
    SitePoint Enthusiast
    Join Date
    May 2003
    Location
    Lincoln NE
    Posts
    58
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Dr Livingston,
    thanks for your help.

    each one of my records has a unique ID, but sometimes in the MDATE COLUMN (date column) some rows have the same date, but again each row still have a unique ID.

    when I tried adding the DISTINCT to my 'SELECT DISTINCT ID, MDATE FROM LBL ORDER BY MDATE' to avoid having the same date showing over and over, it didn't work because of the ID field.

    I'm still new to PHP and MySQL, and any suggestion is appericated.

    Thanks again!

  4. #4
    Non-Member
    Join Date
    Jan 2003
    Posts
    5,748
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ummm....

    Tricky since DISTINCT should have selected only distinct ID and MDate rows....

    Try this:

    PHP Code:
    SELECT DISTINCT MDATEID FROM LBL ORDER BY MDATE 

  5. #5
    Can we go to a 48 hour day?
    Join Date
    May 2002
    Location
    MI
    Posts
    906
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    is the ID an auto filed? Do you have a bit more detail on how the table is set up?

    The really ugly way would be to add the date values to an array and then check them before listing them out.

    PHP Code:
     
    $dates 
    = array();
     
    while (
    getting rows of data)
    {
       if (!(
    in_array($row['MDATE'], $dates)))
       {
          echo(
    '<select>' $row['MDATE'] . '</select>');
          
    $dates[] = $row['MDATE'];
       }  

    mitechie.com
    "Techies just think a little differently
    ...at least that is what they keep telling me."

  6. #6
    Non-Member
    Join Date
    Jan 2003
    Posts
    5,748
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    This would work though after the PM I posted and going by your reply I would have to say that you should try the above first.

    Since you are new to database design and PHP; and at the moment I do not have the time to make changes to your database.

    Try the following from your script and the one posted by deuce868

    PHP Code:
    <select name="dsearch" size="1">
    <option selected value="">***Any Date***</option>
    <?php
    $dates 
    = array();
    $datequery mysql_query('SELECT ID, MDATE FROM LBL ORDER BY MDATE');
    while (
    $drow mysql_fetch_array($datequery)) {
    $did $drow['ID'];
     
    if (!(
    in_array($drow['MDATE'], $dates)))
       {
          echo(
    "<option value='$did'>"$drow['MDATE'] . "</option>");
    # current date is not in array yet so put it there
    # to stop a matching date being put to
    # select list
          
    $dates[] = $drow['MDATE'];
       } 
    # close off IF
    # close off WHILE
    .
    .
    This should solve the problem of having any duplicate dates now.

    deuce868 - Interesting solution I might add

  7. #7
    Can we go to a 48 hour day?
    Join Date
    May 2002
    Location
    MI
    Posts
    906
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    deuce868 - Interesting solution I might add
    See sig
    mitechie.com
    "Techies just think a little differently
    ...at least that is what they keep telling me."

  8. #8
    Non-Member
    Join Date
    Jan 2003
    Posts
    5,748
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Special Interest Group ? Got a Link I wonder ?

  9. #9
    Can we go to a 48 hour day?
    Join Date
    May 2002
    Location
    MI
    Posts
    906
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    no group...although maybe I could create one and use my sig quote as a motto. Kinda like the boy scouts only cooler. lol
    mitechie.com
    "Techies just think a little differently
    ...at least that is what they keep telling me."

  10. #10
    Non-Member
    Join Date
    Jan 2003
    Posts
    5,748
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Umm... In fact you were actually talking about your signature, right ?

    Got it all wrong again....

  11. #11
    Can we go to a 48 hour day?
    Join Date
    May 2002
    Location
    MI
    Posts
    906
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    yep
    $sig = "signature";
    mitechie.com
    "Techies just think a little differently
    ...at least that is what they keep telling me."

  12. #12
    SitePoint Enthusiast
    Join Date
    May 2003
    Location
    Lincoln NE
    Posts
    58
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks a lot guys, it's working just great, I would appericate any suggestions on a new layout for my table.

    the current table contains 5 columns.
    ID (primary key, to make it easy to pull records)
    COMPANY
    CODE (usually each company has a 4 digit code or I set it up to say N/A if non exists.
    so this column has a bunch of N/A records.)
    ADDRESS
    DATE (DATE when record was entered or modified)
    some of the DATE records are the same, and this is where my original problem started.

    Any suggestion is appericated. Thanks again guys!

  13. #13
    Can we go to a 48 hour day?
    Join Date
    May 2002
    Location
    MI
    Posts
    906
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I guess the first thing I see is why are you creating a drop down out of the dates...it seems that might be the problem.
    mitechie.com
    "Techies just think a little differently
    ...at least that is what they keep telling me."

  14. #14
    Non-Member
    Join Date
    Jan 2003
    Posts
    5,748
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ummm.... Good point. What I think you would be better off to do now is this:

    Have a dropdown box for these DATE elements:

    DAY / MONTH / YEAR

    And so you add/modify the date based on these selections. To get the complete date into your database you use PHPs date() and mktime() functions.

    I'll have a look at some script I have lying about on CD later and post some more later.

  15. #15
    Can we go to a 48 hour day?
    Join Date
    May 2002
    Location
    MI
    Posts
    906
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I guess what are you trying to do?

    I would just put in a textfield and mandate a format. I have two funtions I use for dates:
    date_Norm_Mysql and date_Mysql_Norm
    for changing the date format from the mysql (YYYY/MM/DD) to a user friendly format (DD/MM/YYYY)

    if it is for an update or something I would just parse the date and check the format.

    If, on the other hand, you are populating a drop down to say request a list of all records inserted on a certain day I might say it is benificial to do what you have now so that you limit the dates to known valids. The trouble there is that after a while you end up with a huge drop down listing.

    I really think it would be cleaner to just allow user input in a text box and validate it. Once you create the functions for it you can reuse them all over so it is not that great a waste of time.
    mitechie.com
    "Techies just think a little differently
    ...at least that is what they keep telling me."


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
  •