Another PHP/MYSql Question

Discussion in 'OT Technology' started by venger240, Oct 31, 2005.

  1. venger240

    venger240 Hoemoe

    Joined:
    Oct 20, 2003
    Messages:
    2,938
    Likes Received:
    0
    Location:
    Nashville, TN
    Regarding dates again... sigh.

    So I have almost everything setup and working great. I now need to have a page setup so people can search through the datebase by different date ranges. Sooooo in order for the proper format for date to be setup(YYYY/MM/DD) I am trying to create drop down selections for month day and year. I am not sure how to join the three drop down menus, to make one date. For example:

    Year: Drop Down
    Month: Drop Down
    Day: Drop Down

    3 Selection boxes that I need to be posted as one date like this: 2005/10/31

    Thanks again for any help.
     
  2. kingtoad

    kingtoad OT Supporter

    Joined:
    Sep 2, 2003
    Messages:
    55,919
    Likes Received:
    10
    Location:
    Los Angeles
    If I understand your question correctly, you have three different dropdown menus--Year, Month, and Day. The best way to do this is to catinate the variables into one variable. Assuming "Year" passes the variable of year, "Month" passes the variable of month, and Day passes the variable of day, you would do something similair to this:

    PHP:
    $date "{$year}//{$day}//{$month}";
    Then you'll execute the query using $date instead of the previously used variables, which will now be unused.
     
  3. venger240

    venger240 Hoemoe

    Joined:
    Oct 20, 2003
    Messages:
    2,938
    Likes Received:
    0
    Location:
    Nashville, TN

    That is exactly what I was looking for, thanks! Can you tell me why this isnt working?

    PHP:
    $year1 $_POST['year1'];
    $day1 $_POST['day1'];
    $month1 $_POST['month1'];

    $year2 $_POST['year2'];
    $day2 $_POST['day2'];
    $month2 $_POST['month2'];

    $start "{$year1}//{$day1}//{$month1}"
    $end "{$year2}//{$day2}//{$month2}"
    $sql "SELECT * FROM af WHERE date BETWEEN \"$start\" AND \"$end\" ";
     
  4. CyberBullets

    CyberBullets I reach to the sky, and call out your name. If I c

    Joined:
    Nov 13, 2001
    Messages:
    11,865
    Likes Received:
    0
    Location:
    BC, Canada/Stockholm, Sweden
  5. kingtoad

    kingtoad OT Supporter

    Joined:
    Sep 2, 2003
    Messages:
    55,919
    Likes Received:
    10
    Location:
    Los Angeles
    The code you provided looks accurate. Debug the problem by testing the $_POST[] variables after you store them into the new variable. For example:

    PHP:
    $year $_POST['year'];
    $day $_POST['day'];
    $month $_POST['month'];
    $date "{$year}//{$day}//{$month}";

    echo 
    $date;
    If it displays correctly, then that isn't the problem, if it doesn't, then the problem probably occurs when the variable is being passed. Check your form and see if the variable names are correct and matching up to your script.

    Also, go into PHPMyAdmin and execute the query directly into the database to see if the database is being queried correctly. Instead of using the variable names, test it using dates.
    PHP:
    SELECT FROM af WHERE date BETWEEN `1999/20/4` AND `1999/25/4`;
    These are common debugging practices.
     
  6. kingtoad

    kingtoad OT Supporter

    Joined:
    Sep 2, 2003
    Messages:
    55,919
    Likes Received:
    10
    Location:
    Los Angeles
    While this is true, more flexible, and better for coding, he may have set his date format as 'YYYY//DD//MM' using PHP, which is why he's requesting it the way it is. Which is a common mistake for new PHP coders. They set the field type as a 'varchar' instead of 'date'. I've made the mistake in the past.
     
  7. venger240

    venger240 Hoemoe

    Joined:
    Oct 20, 2003
    Messages:
    2,938
    Likes Received:
    0
    Location:
    Nashville, TN
    Ok, everything checks out. "echo $start;" that works fine. Comes out correctly.

    Then testing using only the dates and not the variables works out correctly was well. I think its the way Im using the variables. Is the bolded correct?

    WHERE date BETWEEN \"$start\" AND \"$end\"
     
  8. venger240

    venger240 Hoemoe

    Joined:
    Oct 20, 2003
    Messages:
    2,938
    Likes Received:
    0
    Location:
    Nashville, TN

    No, thanks to prior recommendations its set as date. I am a newer php coder, however this isnt for a very complex project at all. I just need someway to hold some scoring and a way of retrieving the score via dates.
     
  9. kingtoad

    kingtoad OT Supporter

    Joined:
    Sep 2, 2003
    Messages:
    55,919
    Likes Received:
    10
    Location:
    Los Angeles
    Ah...
    PHP:
    $sql "SELECT * FROM af WHERE date BETWEEN '".$start."' AND '".$end."' ";
    $query mysql_query($sql);
     
  10. venger240

    venger240 Hoemoe

    Joined:
    Oct 20, 2003
    Messages:
    2,938
    Likes Received:
    0
    Location:
    Nashville, TN
    That didnt work either. :(
     
  11. kingtoad

    kingtoad OT Supporter

    Joined:
    Sep 2, 2003
    Messages:
    55,919
    Likes Received:
    10
    Location:
    Los Angeles
    Cyber was right, you'll need to query the database like this:

    PHP:
    $date "{$year}-{$month}-{$day}";
    That's the proper date format MySQL uses.
     
  12. venger240

    venger240 Hoemoe

    Joined:
    Oct 20, 2003
    Messages:
    2,938
    Likes Received:
    0
    Location:
    Nashville, TN
    yeah, I changed that already. I figured that out earlier...

    PHP:

    $year1 
    $_POST['year1']; 
    $day1 $_POST['day1']; 
    $month1 $_POST['month1']; 

    $year2 $_POST['year2']; 
    $day2 $_POST['day2']; 
    $month2 $_POST['month2']; 

    $start "{$year1}-{$day1}-{$month1}"
    $end "{$year2}-{$day2}-{$month2}"

    $sql "SELECT * FROM af WHERE date BETWEEN '".$start."' AND '".$end."' ";

    $ret mysql_query($sql) or die(mysql_error());

    Do you see anything else wrong?
     
  13. kingtoad

    kingtoad OT Supporter

    Joined:
    Sep 2, 2003
    Messages:
    55,919
    Likes Received:
    10
    Location:
    Los Angeles
    Are you getting an error? If so, what is it?
     
  14. kingtoad

    kingtoad OT Supporter

    Joined:
    Sep 2, 2003
    Messages:
    55,919
    Likes Received:
    10
    Location:
    Los Angeles
    Try switching $start and $end in the query. You might be mixing them up during variable passing.
     
  15. venger240

    venger240 Hoemoe

    Joined:
    Oct 20, 2003
    Messages:
    2,938
    Likes Received:
    0
    Location:
    Nashville, TN
    No errors. Just a blank page.

    If I change " '".$start."' AND '".$end."' " to " '2005-10-28' AND '2005-10-31' " it displays my results.

    If I do "echo $start" it displays the date that I have selected.
     
  16. venger240

    venger240 Hoemoe

    Joined:
    Oct 20, 2003
    Messages:
    2,938
    Likes Received:
    0
    Location:
    Nashville, TN

    Nope. Nothing.
     
  17. kingtoad

    kingtoad OT Supporter

    Joined:
    Sep 2, 2003
    Messages:
    55,919
    Likes Received:
    10
    Location:
    Los Angeles
    Oh, duh. Yes, that makes sense.

    Yes, you're not listing the data correctly. The best way to do this is the mysql_fetch_array() function. It will split your data into an array and you could access each element by calling the variable. Here's an example working with your code.

    PHP:

    $year1 
    $_POST['year1']; 
    $day1 $_POST['day1']; 
    $month1 $_POST['month1']; 

    $year2 $_POST['year2']; 
    $day2 $_POST['day2']; 
    $month2 $_POST['month2']; 

    $start "{$year1}-{$day1}-{$month1}"
    $end "{$year2}-{$day2}-{$month2}"

    $sql "SELECT * FROM af WHERE date BETWEEN '".$start."' AND '".$end."' ";

    $ret mysql_query($sql) or die(mysql_error());

    while (
    $score mysql_fetch_array($ret)) {
             echo 
    $score['fieldname_score'];
             echo 
    $score['fieldname_date'];
             echo 
    $score['fieldname_id'];
    }
    where ['fieldname_'] is would be the name of the actual field. So if there is a field called "score", it would be: $score['score'].
     
  18. venger240

    venger240 Hoemoe

    Joined:
    Oct 20, 2003
    Messages:
    2,938
    Likes Received:
    0
    Location:
    Nashville, TN
    Well this is how I currently have it setup...

    PHP:
    <table border="0" width="100%" cellpadding="0" cellspacing="0">
        <tr valign="top">
            <td width="80%">

                <?php while($row mysql_fetch_assoc($ret)) { ?>
                    * <?php echo $row['id']; ?><br>
                    * <?php echo $row['name']; ?><br>
                    * <?php echo $row['date']; ?><br>
                    * <?php echo $row['type']; ?><br>
                    * <?php echo $row['manager']; ?><br>
                    * <?php echo $row['score']; ?><br>
            <?php ?>
                </td>

        </tr>
    </table>
     
  19. kingtoad

    kingtoad OT Supporter

    Joined:
    Sep 2, 2003
    Messages:
    55,919
    Likes Received:
    10
    Location:
    Los Angeles
    I skimmed through your post, you said it worked when you manually punched in the dates.

    You need to swap month and day in the $date variable.

    PHP:
    $start "{$year1}-{$month1}-{$day1}"
    $end "{$year2}-{$month2}-{$day2}"
     
  20. venger240

    venger240 Hoemoe

    Joined:
    Oct 20, 2003
    Messages:
    2,938
    Likes Received:
    0
    Location:
    Nashville, TN
    OMFG! I am a fucking tool. A complete idiot. Thats what it was...

    God. Thank you so much for all of your help. I really appreciate it.
     
  21. kingtoad

    kingtoad OT Supporter

    Joined:
    Sep 2, 2003
    Messages:
    55,919
    Likes Received:
    10
    Location:
    Los Angeles
    :wavey:
     

Share This Page