SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    Web Enthusiast
    Join Date
    Jul 2000
    Location
    Western Massachusetts, USA
    Posts
    1,389
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    How sort on "count."

    I have built a report on a query that "counts" the number of search phrases, and displays them grouped by search phrase with the number of instances of each phrase. Works fine at:
    http://www.kripalu.org/search_report_v2.php
    if you plug in date range of 3/29 - 3/30/05

    Now I have created a version to sort by "count."
    However, as "count" is not a real field, the script produces an error.
    http://www.kripalu.org/search_report_v3.php
    Again, to test plug in date range of 3/29 - 3/30/05

    Error retrieving search data from database!
    Error: Unknown column 'count' in 'order clause'
    PHP Code:
     $sql "select search_phrase, COUNT(*) AS num_search_phrase, 
    found, 
    found_date 
    from 
    search 
    where 
    found_date >= 
    $startdate and found_date <= $enddate 
    group by search_phrase 
    order by 
    count desc"

    How can I revise the script to sort on "count?"
    Paul C.
    ClickBasics
    http://www.clickbasics.com

  2. #2
    Chessplayer kleineme's Avatar
    Join Date
    Apr 2004
    Location
    Germany
    Posts
    608
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    depending on the DB you may try

    ORDER BY COUNT(*) DESC

    or

    ORDER BY num_search_phrase DESC
    Never ascribe to malice,
    that which can be explained by incompetence.
    Your code should not look unmaintainable, just be that way.

  3. #3
    Web Enthusiast
    Join Date
    Jul 2000
    Location
    Western Massachusetts, USA
    Posts
    1,389
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks kleineme.

    PHP Code:
    ORDER BY num_search_phrase DESC 
    works fine.
    Paul C.
    ClickBasics
    http://www.clickbasics.com


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
  •