I have a table (blog_articles) that has approximately 1200 rows.
Every time a new row is added to this table, the row is given an id.
What I would like to do is echo out a random row from blog_articles randomized from 5 of the latest ids recorded:
The following code almost does what I want, except it echos out a random id from all the rows in that table:
$request = db_query("
SELECT subject, article_id
FROM {$db_prefix}blog_articles
GROUP BY article_id
ORDER BY RAND()
LIMIT 1", __FILE__, __LINE__);
$row = mysql_fetch_assoc($request);
mysql_free_result($request);
What can I do to make this work?
You need to randomize the results after the query. First fetch the last 5 IDs and then make them random. Probably not in SQL but in php (in the resulting array)
http://php.net/manual/en/function.shuffle.php
Thanks, margarett.
Could you actually write it out for me. Otherwise I'll just keep making hundreds of mistakes
:P
I can but not now as I'm on the phone (as I've been for quite some time, busy busy times...)
Looking forward to a response... Others are welcome to help too
OK, here goes nothing :P I'm throwing some partially-untested code but I guess it should work.
So, this code here:
$request = db_query("
SELECT subject, article_id
FROM {$db_prefix}blog_articles
ORDER BY article_id DESC
LIMIT 5",
__FILE__, __LINE__);
$data = array();
while ($row = mysql_fetch_assoc($request))
$data[] = $row;
mysql_free_result($request);
Should give you an array like, eg:
Array
(
[0] => Array
(
[subject] => Subject of article ID 123
[article_id] => 123
)
[1] => Array
(
[subject] => Subject of article ID 122
[article_id] => 122
)
[2] => Array
(
[subject] => Subject of article ID 121
[article_id] => 121
)
[3] => Array
(
[subject] => Subject of article ID 120
[article_id] => 120
)
[4] => Array
(
[subject] => Subject of article ID 119
[article_id] => 119
)
)
If you shuffle it, eg:
shuffle($data);
Then the array key 0 will have a randomized item from your last 5 articles.
Array
(
[0] => Array
(
[subject] => Subject of article ID 119
[article_id] => 119
)
So putting it all together, this should work for you:
$request = db_query("
SELECT subject, article_id
FROM {$db_prefix}blog_articles
ORDER BY article_id DESC()
LIMIT 5",
__FILE__, __LINE__);
$data = array();
while ($row = mysql_fetch_assoc($request))
$data[] = $row;
mysql_free_result($request);
shuffle($data);
$random_last_article = $data[0];
unset($data);
An example for $random_last_article from my tests
Array
(
[subject] => Subject of article ID 120
[article_id] => 120
)
Thanks for the idea.
The following script works for me:
$dbresult = db_query("
SELECT subject, article_id
FROM {$db_prefix}blog_articles
ORDER BY article_id DESC
LIMIT 5", __FILE__, __LINE__);
//Create an empty array
$data = array();
while ($row = mysql_fetch_assoc($dbresult))
//Place database findings into the array
$data[] = $row;
mysql_free_result($dbresult);
shuffle($data);
$latest_blog_news = "<span style=\"color: #33587a;font-weight:bold;\">Read:</span> <a href=\"http://website.com/".$data[0]['article_id'].".html\"><i><b>".$data[0]['subject']."</b></i> <i style=\"font-size: 90%;\" class=\"fa fa-rss-square\"></i></a>\n";
unset($data);
Good :)
Solved, then? ;)