Query Assistance

Started by Biology Forums, February 01, 2016, 04:18:03 PM

Previous topic - Next topic

Biology Forums

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?

margarett

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
Se forem conduzir, não bebam. Se forem beber... CHAMEM-ME!!!! :D

QuoteOver 90% of all computer problems can be traced back to the interface between the keyboard and the chair

Biology Forums

Thanks, margarett.

Could you actually write it out for me. Otherwise I'll just keep making hundreds of mistakes

margarett

:P

I can but not now as I'm on the phone (as I've been for quite some time, busy busy times...)
Se forem conduzir, não bebam. Se forem beber... CHAMEM-ME!!!! :D

QuoteOver 90% of all computer problems can be traced back to the interface between the keyboard and the chair

Biology Forums

Looking forward to a response... Others are welcome to help too

margarett

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
)
Se forem conduzir, não bebam. Se forem beber... CHAMEM-ME!!!! :D

QuoteOver 90% of all computer problems can be traced back to the interface between the keyboard and the chair

Biology Forums

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);

margarett

Se forem conduzir, não bebam. Se forem beber... CHAMEM-ME!!!! :D

QuoteOver 90% of all computer problems can be traced back to the interface between the keyboard and the chair

Advertisement: