SitePoint Sponsor

User Tag List

Results 1 to 12 of 12
  1. #1
    SitePoint Evangelist sp0om's Avatar
    Join Date
    Feb 2004
    Location
    MN
    Posts
    408
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    date > last_login?

    $sql_get_num_new_messages = "SELECT * FROM messeges_info WHERE messege_to = '$_SESSION[username]' AND messege_date > '$last_login'";

    I can't get the above to get working. Yes, I do realize that message is spelled incorrectly. I will fix that up later, but that has nothing to do with the code not working.

    Each time I run this, although there are new messages, it doesn't count them. Am I using messege_date > '$last_login' incorrectly?

    Is the > operator correct for this purpose?

  2. #2
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    that will only work is message_date is a column of type date and that $last_login is of the format yyyy-mm-dd
    Quote Originally Posted by sp0om
    I can't get the above to get working. Yes, I do realize that message is spelled incorrectly. I will fix that up later, but that has nothing to do with the code not working.
    define "not working".

  3. #3
    SitePoint Guru
    Join Date
    Aug 2003
    Location
    CT
    Posts
    643
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I would go as far as to make them a unix timestamp. Doing math functions on dates can be tricky, especially if you get 1 bad date in a table.

  4. #4
    SitePoint Evangelist sp0om's Avatar
    Join Date
    Feb 2004
    Location
    MN
    Posts
    408
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Would it just be easier to have a field in messeges_info which indicates whether a message has been read or not, and just count up how many rows in my table are affected to get the number of unread messages? I think that's what happens on some other sites...

  5. #5
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    well, now that you're ralking about efficiency, your first query is wrong. if you just want to count the number of records, you should be using count(*), not just *. as written, your query pulls ALL of those rows in to memory, even if you don't actually use them.

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by briansol
    I would go as far as to make them a unix timestamp. Doing math functions on dates can be tricky, especially if you get 1 bad date in a table.
    ahem!

    doing date functions on unix timestamps is even messier, since you have to convert them to dates first

    and much more difficult if you get 1 bad integer in the table

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    *hands rudy a sprite to clear his throat*

  8. #8
    SitePoint Guru
    Join Date
    Aug 2003
    Location
    CT
    Posts
    643
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937
    ahem!

    doing date functions on unix timestamps is even messier, since you have to convert them to dates first
    why?

    say we have the date right now:

    now: Tuesday, January 31st 2006, 18:33:21 (GMT) translates to 1138732401


    if we store the time stamp in the DB (which i do on my of my programs instead of a datetime) it's simple math on the server.

    SELECT * FROM table WHERE date_stored < $now;

    If you want to format it, a simple getdate() in php will give you the formatting in any way you want.

    I don't see where we had to convert to a date first???


    and much more difficult if you get 1 bad integer in the table
    granted-- but the odds of a timestamp getting inserted wrong is pretty slim. it would most likely be an all or nothing kind of error.

    where as, you can have dates going in as 2006-01-31 or 01/31/2006 or any other combination.

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by briansol
    I don't see where we had to convert to a date first???
    okay, here's an example -- extract all orders placed between 5pm friday and 9am monday

    with unixtimes, you have a choice: convert them right there in the sql (which is unnecessary overhead when you coulda used datetimes in the first place), or pull all orders across the database-application interface and do the conversion in php, so that you throw away most orders (very inefficient)


    Quote Originally Posted by briansol
    where as, you can have dates going in as 2006-01-31 or 01/31/2006 or any other combination.
    actually, no you can't, mysql accepts dates only in year-month-day sequence, so there's never any ambiguity

    granted, mysql used to accept sh1t dates, like 2005-00-00 or 2005-02-30, but i believe this has been fixed in version 5

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    SitePoint Guru
    Join Date
    Aug 2003
    Location
    CT
    Posts
    643
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I understand what you're saying, but i would attack this 5pm friday to 9am monday problem differently.

    I would first process my data with php, converting the startdate and enddate to a valid date using the strtotime() function.
    then passing that into a query with 'between' parameters.

    I'm more of a programmer than a DBA, so i think that's why i'm attacking this problem with this idea set, vs you, a sql guy, attacking it as much as you can on the sql side.

  11. #11
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by briansol
    I would first process my data with php, converting the startdate and enddate to a valid date using the strtotime() function.
    then passing that into a query with 'between' parameters.
    but there isn't a startdate and an enddate

    consider a table of customer orders, with only one date column (date_ordered or something)

    i'm not after orders placed between a specific friday 5pm and monday 9am, i'm after orders placed between any friday 5pm and monday 9am

    see the difference?

    there's no way you can do that with just unix times, and you sure as shootin better not push all orders across the database-application interface before deciding whether they fit within the desired specifications

    that would be the same as getting customers in oregon by extracting all customers from the database and then using php to throw away the ones that aren't in oregon
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  12. #12
    SitePoint Guru
    Join Date
    Aug 2003
    Location
    CT
    Posts
    643
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937

    i'm not after orders placed between a specific friday 5pm and monday 9am, i'm after orders placed between any friday 5pm and monday 9am

    see the difference?
    Ahh yes.

    <- stands corrected, again


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
  •