SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    SitePoint Member
    Join Date
    May 2012
    Posts
    7
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Selecting the most popular entry from the last ten values entered

    I have the selecting from the last ten entries working, but am unsure how to get the most popular from these ten entries?

    PHP Code:

    <?php

            $sql 
    "SELECT data FROM table_answers ORDER BY id DESC LIMIT 10";
            
            
    $result mysql_query ($sql$db);

            while (
    $row mysql_fetch_array ($result))
               {
                    echo 
    "        [".$row['data']."]       ";
            
               }
            
      
    ?>
    Please help if you can, thanks.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,328
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    how do you know which one is the most popular?

    presumably, with only one column in play here, you're going to simply (re)sort the 10 retrieved data values?

    can't you do that with an array sort or something?

    (sorry, i don't do php)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Wizard bronze trophy Jeff Mott's Avatar
    Join Date
    Jul 2009
    Posts
    1,312
    Mentioned
    19 Post(s)
    Tagged
    1 Thread(s)
    I think this is a case where you'll have to use WHERE IN.

    Code SQL:
    SELECT * FROM table_answers
    WHERE id IN (
        SELECT id FROM table_answers
        ORDER BY id DESC LIMIT 10
    )
    ORDER BY popularity DESC
    LIMIT 1

  4. #4
    SitePoint Member
    Join Date
    May 2012
    Posts
    7
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks, I tried WHERE In and it doesn't seem to work. It won't echo any result.

    PHP Code:

    $sql 
    "SELECT data FROM table_answers WHERE id IN (SELECT id FROM table_answers ORDER BY id DESC LIMIT 10) ORDER BY popularity DESC LIMIT 1";
              
     
              
             
              
       
    $result mysql_query ($sql$db);

            while (
    $row mysql_fetch_array ($result))
            {
            echo 
    "        [".$row['data']."]       ";
            
            } 
    Any idea what I may be doing wrong please?

  5. #5
    SitePoint Wizard bronze trophy Jeff Mott's Avatar
    Join Date
    Jul 2009
    Posts
    1,312
    Mentioned
    19 Post(s)
    Tagged
    1 Thread(s)
    Well I only guessed that you have a column named "popularity". You'll have to adjust the query to match your actual schema.

  6. #6
    SitePoint Member
    Join Date
    May 2012
    Posts
    7
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Sorry I am new to mysql. I have 2 columns - one called id and the other called data. The table is called table_answers. It should work but it doesn't. Thanks for your help

    PHP Code:
    $sql "SELECT * FROM table_answers WHERE id IN (SELECT id FROM table_answers ORDER BY id DESC LIMIT 10) ORDER BY data DESC LIMIT 1"
               
      
               
              
               
       
    $result mysql_query ($sql$db); 

            while (
    $row mysql_fetch_array ($result)) 
            { 
            echo 
    "        [".$row['data']."]       "
             
            } 

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,328
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by delray View Post
    It should work but it doesn't.
    sorry, i am not familiar with the "should work but doesn't" error message

    is there any more information?

    did you test the query outside of php first?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Wizard bronze trophy Jeff Mott's Avatar
    Join Date
    Jul 2009
    Posts
    1,312
    Mentioned
    19 Post(s)
    Tagged
    1 Thread(s)
    Quote Originally Posted by delray View Post
    I have 2 columns - one called id and the other called data.
    So how were you expecting to judge the popularity of something?

  9. #9
    SitePoint Wizard
    Join Date
    Jul 2003
    Location
    Kent
    Posts
    1,921
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    How does this table get populated?

    Is there a voting script adding rows?

    How exactly do YOU think popularity is being measured?


Tags for this Thread

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
  •