Limiting Random Results

[Tim_Silva]

Hi,

I am trying to echo 3 random articles from my articles table but I would like to echo only articles from within the top 20 most recent ones.

What I mean is. I know how to bring the most recent articles:

("SELECT * FROM $usertable ORDER BY DATE DESC LIMIT 20")

And I know how to bring articles randomly:

("SELECT * FROM $usertable ORDER BY RAND() LIMIT 3")

But I do not know how to do both. Meaning, bring me 3 random articles but limit them to the top 20.

Any help is appreciated.

Tim

[leatherback]

try:

$all = array();
//select 20
$result = mysql_query("select * from $usertable limit 20");
while($row = mysql_fetch_array($result))
  {
  $all[] = $row;
  }

shuffle($all)
$i = 0;
while($i<3)
  {
  echo $all[$i]['name'];
  $i++;
  }

[Piranha]

You can use a subquery:

SELECT * FROM 
(
     SELECT * FROM $usertable ORDER BY DATE DESC LIMIT 20
)
ORDER BY RAND() LIMIT 3")

The subquery returns the last 20 records, the "main" query then selects 3 random from these.

[Tim_Silva]

Piranha/Leatherback

The subquery seems very simple but it is not working. Obviously I must be doing something wrong. This is what I have:

mysql_connect($hostname,$username,$password) or die(mysql_error());
mysql_select_db("$dbname") or die(mysql_error());

$data = mysql_query("SELECT FROM(SELECT FROM $usertable ORDER BY DATE DESC LIMIT 20)ORDER BY RAND() LIMIT 3")
or die(mysql_error());
while($info = mysql_fetch_array( $data ))

{
echo $info['title'];
}

I tried all combinations of quotes (single and double) in the SELECT but it didn't work either

It is giving the error: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near "SELECT * FROM $usertable ORDER BY DATE DESC LIMIT 20)ORDER

Thanks

[leatherback]

subqueries were only recently introduced in mysql, I think. You might have a version issue

[Tim_Silva]

I tried in two different hosts (godaddy and 1and1 both run version 4.0.27) and both gave me the same error.

I am going to try Leatherback's first suggestion again (the one with the array) and see.

Thanks

[Tim_Silva]

I just checked and you're right, apparently subqueries were introduced in version 4.1. It reads, "MySQL 4.1 had three main focal areas with new features: subqueries and derived tables, character sets, and replication."

So subqueries is out for me 🙁

Tim

[Piranha]

Then change provider. The provider should make sure that they have what you need, if not the best way is to simply change provider.

[Roger_Ramjet]

Definitely change provider: no one should be providing hosting with less than 4.1 after all this time, it's not as if the upgrade would cost anything. Sheer laziness and stupidity and should be rewarded by losing all their customers.

When you use a subquery in the FROM clause then you have to alias it:

"SELECT * FROM
( SELECT * FROM $usertable ORDER BY DATE DESC LIMIT 20) AS t
ORDER BY RAND() LIMIT 3";