Selecting Random Rows in MySQL
At the time of this writing I have implemented a tile based home page. I populate a number of these tiles by pulling random blog posts. I was a little shocked when it occurred to me I have never had a reason to retrieve random data. I hashed out a few solutions in my head, but they seemed a little less than elegant; a quick search of the MySQL’s documentation gave my the answer I was looking for. RAND()
, more specifically, ORDER BY RAND()
. In my case, I want six random blog posts so the query would look like this.
SELECT
title,
slug
FROM
contents
WHERE
content_status = 'published'
AND
content_type = 'posts'
ORDER BY RAND()
LIMIT 6;
Since jasonsnider.com runs on top of CakePHP my final implementation is as follows. Note: if you’re using CakePHP but not MySQL, this may not work. Check the documentation of your DBMS for some alternative syntax.
$this->Post->find(
'all',
array(
'fields'=>array(
'title',
'slug'
),
'conditions' => array(
'Post.content_status' => 'published'
),
'order' => 'rand()',
'limit' => 6,
)
);