SitePoint Sponsor

User Tag List

Results 1 to 13 of 13
  1. #1
    SitePoint Addict jamesbond's Avatar
    Join Date
    Feb 2001
    Location
    The Netherlands
    Posts
    256
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    mysql query problem

    I have this query:


    SELECT *,
    DATE_FORMAT(date, '%e-%c-%Y') as day,
    DATE_FORMAT(date, '%k:%i') as hour
    FROM stats
    ORDER BY date ASC
    LIMIT $last_referer_count
    ");


    And a variable $daydetails, which holds a value in the same format as the variable day, for example $daydetails=17-07-2001

    I would like mysql only to select the records WHERE day=$daydetails (I tried using WHERE in the query but doesn't seem to work with 'day')



    I got things working for now, by this inefficient method :
    if ($row[day]==$daydetails) { ... }

    But with this method (in combination with the query above) mysql selects all the records..

    I would like mysql to select only the records specific where day=$daydetails

  2. #2
    code addict Abstraction's Avatar
    Join Date
    Apr 2001
    Location
    Des Moines, IA
    Posts
    346
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You need a WHERE clause.


    SELECT
    DATE_FORMAT(date, '%e-%c-%Y') as day,
    DATE_FORMAT(date, '%k:%i') as hour
    FROM stats
    WHERE day=/"$daydetails/"
    ORDER BY date ASC
    LIMIT $last_referer_count
    ");


    Something along those lines. I might be off some on the syntax, but it's early. =)

  3. #3
    SitePoint Addict jamesbond's Avatar
    Join Date
    Feb 2001
    Location
    The Netherlands
    Posts
    256
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I tried that already but it results in an error.

    I think it is because 'day' is not an actual field in my mysql table.
    I have a field 'date' (type timestamp) in my table, but I haven't figured out yet how to check $daydetails against the date field.

    I expected it would be possible to check $daydetails against the value of day
    Last edited by jamesbond; Jul 19, 2001 at 07:03.

  4. #4
    code addict Abstraction's Avatar
    Join Date
    Apr 2001
    Location
    Des Moines, IA
    Posts
    346
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    is $daydetails a timestamp also?

  5. #5
    SitePoint Addict jamesbond's Avatar
    Join Date
    Feb 2001
    Location
    The Netherlands
    Posts
    256
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    No, it isn't

    $daydetails is actually a result of row[$day]

    On my stats page I have this:

    <a href=\"details.php?daydetails=$row[day]\">$row[day]</a>

    So a specific link would look like this :

    details.php?daydetails=18-7-2001

    When one clicks on this link I would like mysql to select only the records where day=18-7-2001 (which is the value of $daydetails)

    As I said I tried using WHERE day=$daydetails in the query but that doesn't work.
    Error:
    (Warning: Supplied argument is not a valid MySQL result resource in )

    Which I think means mysql is looking for 'day' in the mysql table and can't find it there.

  6. #6
    code addict Abstraction's Avatar
    Join Date
    Apr 2001
    Location
    Des Moines, IA
    Posts
    346
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    convert $daydetails into a timestamp.

    Details on how are here

  7. #7
    SitePoint Addict jamesbond's Avatar
    Join Date
    Feb 2001
    Location
    The Netherlands
    Posts
    256
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The problem with that is that my 'date' field in mysql is timestamp(14) (date including hour,min,seconds)

    If I would convert $daydetails to a timestamp it would never match my date timestamp(14) since $daydetails only contains day,month and year

    Or am i wrong?

  8. #8
    code addict Abstraction's Avatar
    Join Date
    Apr 2001
    Location
    Des Moines, IA
    Posts
    346
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    A timestamp is the number of seconds since the unix epoch. I believe that is january 1st 1970. I could be wrong. Anway, if you convert it to a timestamp you will have a number like 995466600. Which will easily be compared to the timestamp in your db since it to will be in the same format.

  9. #9
    SitePoint Addict jamesbond's Avatar
    Join Date
    Feb 2001
    Location
    The Netherlands
    Posts
    256
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    umm...strange

    timestamp(14) has this format in my database:

    20010717131800
    20010718145716
    20010719111859

    yyyymmddhhmmss

    $daydetails can never make a match with one of those values.

    A not so clean solution would be to add an extra column (timestamp(8) to the database and use that field for checking against, but if possible I would prefer to stick to one timestamp in my database.

    What do u think?

  10. #10
    code addict Abstraction's Avatar
    Join Date
    Apr 2001
    Location
    Des Moines, IA
    Posts
    346
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Well if your date is in the format of 18-7-2001 you can convert that to yyyymmddhhmmss.

    Use the explode function to spilt the numbers into an array, then just build the string so it is in the format you want.

  11. #11
    ********* wombat firepages's Avatar
    Join Date
    Jul 2000
    Location
    Perth Australia
    Posts
    1,717
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    SELECT DATE_FORMAT(date, '%e-%c-%Y') as day, DATE_FORMAT(date, '%k:%i') as hour FROM stats WHERE DATE_FORMAT(date, '%e-%c-%Y') ='$daydetails'
    ORDER BY date ASC
    LIMIT $last_referer_count
    ");



  12. #12
    SitePoint Addict jamesbond's Avatar
    Join Date
    Feb 2001
    Location
    The Netherlands
    Posts
    256
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    firepages, that looked so promising

    but it results in a :

    Warning: Supplied argument is not a valid MySQL result resource in ....

    The error disappears when I remove the WHERE line :

    WHERE DATE_FORMAT(date, '%e-%c-%Y') = '$daydetails'

  13. #13
    SitePoint Addict jamesbond's Avatar
    Join Date
    Feb 2001
    Location
    The Netherlands
    Posts
    256
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Firepages, ignore that previous post, I was in a rush (had to leave) and didn't notice an error on my page.

    Your solution works perfectly! thank you very much!


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
  •