SitePoint Sponsor

User Tag List

Results 1 to 7 of 7

Hybrid View

  1. #1
    SitePoint Enthusiast
    Join Date
    Dec 1999
    Posts
    85
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi, can anyone tell me if there's anything wrong with this query?

    $today = date("Ymd");

    $query = "SELECT products, productid, COUNT(productid) AS count, DATE_FORMAT(date, '%Y%m%d') AS verbaldate FROM clicks GROUP by productid WHERE verbaldate = $today ORDER BY count DESC LIMIT 25";

    I want to rank my products according to the number of times they appear in the database.

    Table variables include: products, productid, and date.

    Thanks in advance!
    Ian Chiu
    Editor in Chief
    http://www.usbworkshop.com/

  2. #2
    SitePoint Author Kevin Yank's Avatar
    Join Date
    Apr 2000
    Location
    Melbourne, Australia
    Posts
    2,571
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Looks okay at a glance. Are you having any trouble with it?
    Kevin Yank
    CTO, sitepoint.com
    I wrote: Simply JavaScript | BYO PHP/MySQL | Tech Times | Editize
    Baby’s got back—a hard back, that is: The Ultimate CSS Reference

  3. #3
    Dumb PHP codin' cat
    Join Date
    Aug 2000
    Location
    San Diego, CA
    Posts
    5,460
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I do believe the GROUP BY clause should go after the WHERE clause


    $query = "SELECT products, productid, COUNT(productid) AS count, DATE_FORMAT(date, '%Y%m%d') AS verbaldate FROM clicks WHERE verbaldate = $today GROUP by productid ORDER BY count DESC LIMIT 25";
    Please don't PM me with questions.
    Use the forums, that is what they are here for.

  4. #4
    SitePoint Enthusiast
    Join Date
    Dec 1999
    Posts
    85
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Still doesn't work.

    I tried everything.Can I use sub-query to do this? If so, how does it work?
    Ian Chiu
    Editor in Chief
    http://www.usbworkshop.com/

  5. #5
    ********* Callithumpian silver trophy freakysid's Avatar
    Join Date
    Jun 2000
    Location
    Sydney, Australia
    Posts
    3,798
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    This is just a guess - but does by formating the date usiing DATE_FOMRAT mean that you should be comparing the string verbaldate to $today and therefor you need to enclose $today in quotes?

  6. #6
    SitePoint Enthusiast
    Join Date
    Dec 1999
    Posts
    85
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks to all...I got that thing to work...I cannot use the DATE_FORMAT afterall. Don't know why. I always have to use LIKE to get things working in a mySQL query.

    hmm...
    Ian Chiu
    Editor in Chief
    http://www.usbworkshop.com/

  7. #7
    Dumb PHP codin' cat
    Join Date
    Aug 2000
    Location
    San Diego, CA
    Posts
    5,460
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Did you try it like this:

    $query = "SELECT products, productid, COUNT(productid) AS count, DATE_FORMAT(date, '%Y%m%d') AS verbaldate FROM clicks WHERE DATE_FORMAT(date, '%Y%m%d') = '$today' GROUP by productid ORDER BY count DESC LIMIT 25";
    Please don't PM me with questions.
    Use the forums, that is what they are here for.


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
  •