[RESOLVED] random pair from an array

bradgrafelman

Never had that error message myself, but it sounds like you need to pester your sysadmin to upgrade the SQL server (or migrate you to a newer one, if one exists).

EDIT: Nevermind, you posted the versions. I'm no DB guru, so someone else may have a better solution.

nickf1

i just updated my post with my versions ... I am running xampp so I am my own system admin

laserlight

Oh, NogDog did specify MySQL 4.1 or greater. If you cannot use a subquery, you can generate a random offset, though you no longer need the even random number method I described. If I remember my LIMIT syntax correctly, it may be something along the lines of:

$offset = mt_rand(0, 4);

$query = "SELECT `filename`, `url` FROM (featured_articles) ORDER BY `KEY` ASC LIMIT $offset, 1";

bradgrafelman

If you visit the MySQL manual and read this user comment, someone posted an example workaround for this type of query.

nickf1

i am so confused now...give me a minute to read through everything and try and understand.

Thanks for all your help

nickf1

laserlight wrote:
Oh, NogDog did specify MySQL 4.1 or greater. If you cannot use a subquery, you can generate a random offset, though you no longer need the even random number method I described. If I remember my LIMIT syntax correctly, it may be something along the lines of:
$offset = mt_rand(0, 4);

$query = "SELECT `filename`, `url` FROM (featured_articles) ORDER BY `KEY` ASC LIMIT $offset, 1";

I have tried this but am getting this error:

MySQL 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 '$offset, 1 "WHERE syntax_error == TRUE' at line 1

NogDog

nickf1 wrote:
i am so confused now...give me a minute to read through everything and try and understand.

Thanks for all your help

Don't worry, I'm confused too. I thought it was a fairly straight-forward solution, and had never run into that particular limitation before.

If the SQL solution is not tenable, then you could do something like:

$query = "SELECT `filename`, `url` FROM (featured_articles) ORDER BY `KEY` ASC LIMIT 5";
$result = @mysql_query($query);
$data = array();
while ($row = mysql_fetch_assoc($result)){
   $data[] = $row;
}
$ix = array_rand($data);
printf (" ID: %s  FileName: %s", $data[$ix]['url'], $data[$ix]['filename']);

laserlight

I have tried this but am getting this error:

hmm... that's strange, it looks correct me.

I think NogDog's example could be simplified to:

$query = "SELECT `filename`, `url` FROM (featured_articles) ORDER BY `KEY` ASC LIMIT 5";
$result = @mysql_query($query);
mysql_data_seek($result, mt_rand(0, mysql_num_rows($result) - 1));
$data = mysql_fetch_assoc($result);
printf(" ID: %s  FileName: %s", $data['url'], $data['filename']);

nickf1

hallelujah

thanks NogDog that works great

thanks to everyone else too...couldn't have done it without your help

it is for this site if you are interested:

http://www.amateurillustrator.com/

🙂

nickf1

laserlight wrote:

hmm... that's strange, it looks correct me.

I think NogDog's example could be simplified to:

$query = "SELECT `filename`, `url` FROM (featured_articles) ORDER BY `KEY` ASC LIMIT 5";
$result = @mysql_query($query);
mysql_data_seek($result, mt_rand(0, mysql_num_rows($result) - 1));
$data = mysql_fetch_assoc($result);
printf(" ID: %s  FileName: %s", $data['url'], $data['filename']);

yep still works as before and is cleaner code...thank you

laserlight

Oh wait, I just realised. MySQL was spitting out an error that mentioned $offset... this is impossible, since $offset should have been replaced by a number. Did you perhaps use single quoted strings for your SQL?

nickf1

laserlight wrote:
Oh wait, I just realised. MySQL was spitting out an error that mentioned $offset... this is impossible, since $offset should have been replaced by a number. Did you perhaps use single quoted strings for your SQL?

i actually just copied and pasted your code

laserlight

i actually just copied and pasted your code

That's really strange. Oh well.

nickf1

yeah it's working now so I might leave it as it is only a small part of a larger thing and will take me a long time 🙂

thanks for all your help, i'll mark as resolved