Selecting the most popular entry from the last ten values entered

[delray]

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

<?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.

[r937]

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)

[Jeff Mott]

I think this is a case where you'll have to use WHERE IN.

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

[delray]

Thanks, I tried WHERE In and it doesn't seem to work. It won't echo any result.

$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?

[Jeff Mott]

Well I only guessed that you have a column named "popularity". You'll have to adjust the query to match your actual schema.

[delray]

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

$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']."]       "; 
         
        } 

[r937]

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?

[Jeff Mott]

I have 2 columns - one called id and the other called data.

So how were you expecting to judge the popularity of something?

[Dr John]

How does this table get populated?

Is there a voting script adding rows?

How exactly do YOU think popularity is being measured?