CODEWITHSUNDEEP
phpmysqlsql
Boris
Boris

Reputation: 52

Select only 5 random rows in the last 50 entries

I'm just starting with MySQL so I would like to know how can I select only 5 random rows in the last 50 entries of my database? I hope you understand my question.

I'm using PDO and what I have now is this: 

$otherChoiseRig = $bdd->query("SELECT * FROM articulos WHERE cat = '$ArtCat' ORDER BY RAND()  ");
 $otherChoiseRig2 =  $otherChoiseRig->fetchAll(PDO::FETCH_ASSOC);

Then I use a PHP foreach loop...

Thank you

Upvotes: 2

Views: 213

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269973

The challenge is determining the last 50 entries. Assuming you have an auto-incremented id, you can do:

SELECT a.*
FROM (SELECT a.*
      FROM articulos a
      WHERE cat = '$ArtCat'
      ORDER BY id DESC
      LIMIT 50
     ) a
ORDER BY RAND() 
LIMIT 5;

The key idea is the subquery to get the last 50 entries, and then the final query to get the 5 random rows. The subquery needs to specify how you identify the last 50.

Upvotes: 3

Mahesh Madushanka
Mahesh Madushanka

Reputation: 2998

just add limit

i assume in your table you have some date column so we can get last 50

SELECT * FROM (SELECT * FROM articulos WHERE cat = '$ArtCat' ORDER BY created_tiem desc limit 50 ) t order by RAND() limit 5;

Upvotes: 0

Pradeepan M
Pradeepan M

Reputation: 86

$otherChoiseRig = $bdd->query("SELECT * FROM articulos WHERE cat = '$ArtCat' ORDER BY RAND() LIMIT 5 ");
$otherChoiseRig2 =  $otherChoiseRig->fetchAll(PDO::FETCH_ASSOC);

Upvotes: 0

Related Questions