Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Page 1 of 2 12 LastLast
Results 1 to 15 of 23
  1. #1
    Regular Coder
    Join Date
    Jan 2007
    Posts
    142
    Thanks
    9
    Thanked 1 Time in 1 Post

    Looking for some assistance on this query...

    Can anyone tell me why this isn't working....

    PHP Code:
    $todays_date date("m/d");

    $query="SELECT * FROM bb_schedule WHERE bb_date >= " $todays_date " ORDER BY bb_date LIMIT 5"
    What's it's giving me is the very first date which is less than todays date.

    The dates are in the database like this..... 03/21

    Does the column have to be an INT? I used varchar so I could use the / or - symbols.

    Any assistance would be appreciated.

  • #2
    Senior Coder CFMaBiSmAd's Avatar
    Join Date
    Oct 2006
    Location
    Denver, Colorado USA
    Posts
    3,151
    Thanks
    2
    Thanked 335 Times in 327 Posts
    If your column is a varchar, there should be quotes around the value you are comparing so that it is treated as a string. It is probably converting this to a number and doing the division. Try this -
    PHP Code:
    $query="SELECT * FROM bb_schedule WHERE bb_date >= '$todays_date' ORDER BY bb_date LIMIT 5"
    If you are learning PHP, developing PHP code, or debugging PHP code, do yourself a favor and check your web server log for errors and/or turn on full PHP error reporting in php.ini or in a .htaccess file to get PHP to help you.

  • #3
    Regular Coder
    Join Date
    Jan 2007
    Posts
    142
    Thanks
    9
    Thanked 1 Time in 1 Post
    Quote Originally Posted by CFMaBiSmAd View Post
    If your column is a varchar, there should be quotes around the value you are comparing so that it is treated as a string. It is probably converting this to a number and doing the division. Try this -
    PHP Code:
    $query="SELECT * FROM bb_schedule WHERE bb_date >= '$todays_date' ORDER BY bb_date LIMIT 5"
    Thanks, but that gets the same result. I had tried that initially. Also, it doesn't matter if I use >= or <= I still get the first 5 dates on the schedule which begin 02/02.

  • #4
    Regular Coder
    Join Date
    Jan 2007
    Posts
    142
    Thanks
    9
    Thanked 1 Time in 1 Post
    How embarrassing. I forgot to upload the file to my live server.

    It worked this time. Thanks.

  • #5
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,634
    Thanks
    4
    Thanked 148 Times in 139 Posts
    If your dates are stored as:
    The dates are in the database like this..... 03/21
    then they are either stored as varchar/char format in which case you lose all DATE/TIME functions in mysql OR you are not using MySQL.

    If you aren't using MySQL please specify so I can move the thread to the general database forum and title your thread with the database you are using.

    If you are using mysql you might want to consider entering your dates in yyyymmdd format so you can use DATE functions that are available to you in mysql.

  • #6
    Regular Coder
    Join Date
    Jan 2007
    Posts
    142
    Thanks
    9
    Thanked 1 Time in 1 Post
    Quote Originally Posted by guelphdad View Post
    If your dates are stored as:


    then they are either stored as varchar/char format in which case you lose all DATE/TIME functions in mysql OR you are not using MySQL.

    If you aren't using MySQL please specify so I can move the thread to the general database forum and title your thread with the database you are using.

    If you are using mysql you might want to consider entering your dates in yyyymmdd format so you can use DATE functions that are available to you in mysql.
    I am using MySQL. I tried to use the INT format but it wouldn't accept the extra characters I wanted to use. I suppose there is a way to output with the character but I don't know how to do all of that. I read a little on the timestamp function but I needed to enter the dates in myself and didn't want to have to convert each one.

    If it isn't obvious I am very green to databases and am learning on the fly. I have grasped a lot of it but there is much that I still have to learn.

  • #7
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,634
    Thanks
    4
    Thanked 148 Times in 139 Posts
    if you are using dates then use a DATE type and insert in the format yyyy-mm-dd if you are using DATE and TIME then use DATETIME and insert in format yyyy-mm-dd hh:mm:ss

    you can also use current_date() to insert a current date or now() to insert date and time.

    Using TIMESTAMP, will automatically insert the time or update the time depending if you are doing an insert or an update to your table.

    If you use char/varchar then you can't use any DATE or TIME functions like DATE_ADD or DATE_SUB used to calculate whether your records are in the next 15 days for example.

    You can use DATE_FORMAT to pull your dates out in any number of ways so don't worry about how your dates look when they are inserted.

  • #8
    Regular Coder
    Join Date
    Jan 2007
    Posts
    142
    Thanks
    9
    Thanked 1 Time in 1 Post
    Thanks, that is very informative and I went ahead and saved it to a text file. I won't be needing much of any of that for this project. All I am using this for is a permanent baseball schedule. I input it (which I already have) and it doesn't change. I just need it to display on the schedule page and I also need it to display the next 5 games for the main page. There's really not much adding and modifying necessary for this one but in the future I can use several of those functions you mentioned.

    Thanks again.

  • #9
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    I'm glad you are open to using DATE and/or DATETIME data types. You gain SO MUCH functionality when you do so-- even in a simple application where you just want to store a date to display it here or there, you find yourself wanting to perform a comparison against that date and it breaks down because you're storing it as a char or varchar data type.

    OK... I'm off my soapbox...

  • #10
    Regular Coder
    Join Date
    Jan 2007
    Posts
    142
    Thanks
    9
    Thanked 1 Time in 1 Post
    OK, I converted all my dates to timestamp in an INT column.

    Everything I try is failing. I can't seem to craft a statement that works now.

    I need it to select the dates in the table that are later than the current date.

    $bsk_date is a timestamp.

    Here is what I have at the moment....

    Code:
    $todays_date = date("m/d/y");
    
    $query="SELECT * FROM bsk_schedule WHERE date('%m/%d/%y', bsk_date) >= " . $todays_date . " ORDER BY bsk_date LIMIT 5";

  • #11
    Super Moderator Inigoesdr's Avatar
    Join Date
    Mar 2007
    Location
    Florida, USA
    Posts
    3,647
    Thanks
    2
    Thanked 406 Times in 398 Posts
    Code:
    SELECT * FROM `bsk_schedule` WHERE `bsk_date` > UNIX_TIMESTAMP() ORDER BY `bsk_date` ASC
    I think that's what you're trying to do.

  • #12
    Regular Coder
    Join Date
    Jan 2007
    Posts
    142
    Thanks
    9
    Thanked 1 Time in 1 Post
    Quote Originally Posted by Inigoesdr View Post
    Code:
    SELECT * FROM `bsk_schedule` WHERE `bsk_date` > UNIX_TIMESTAMP() ORDER BY `bsk_date` ASC
    I think that's what you're trying to do.
    Bingo.

    I didn't know about the UNIX_TIMESTAMP().

    Another question....why did you add the single quotes?

  • #13
    Super Moderator Inigoesdr's Avatar
    Join Date
    Mar 2007
    Location
    Florida, USA
    Posts
    3,647
    Thanks
    2
    Thanked 406 Times in 398 Posts
    They're not single-quotes, they're backticks(under the escape key), and I added them because it's proper syntax.

  • #14
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,634
    Thanks
    4
    Thanked 148 Times in 139 Posts
    actually no, backticks are not proper syntax, they don't conform to sql standards.
    they are proprietary to mysql and would cause an error if you transferred over to any other database. they should basically never be used.

    they can be used to escape a column, table name etc. that uses a reserved word. better practice of course would be to rename that item that is using a reserved word.

  • #15
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,634
    Thanks
    4
    Thanked 148 Times in 139 Posts
    Quote Originally Posted by unrelenting View Post
    OK, I converted all my dates to timestamp in an INT column.
    Why did you convert to an INT?

    why didn't you use a TIMESTAMP column which will automatically insert/update any time a row is affected and you don't need to worry about inserting it from php. you also get to avoid the need for conversion to/from unixtime.


  •  
    Page 1 of 2 12 LastLast

    Posting Permissions

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