[RESOLVED] random pair from an array

nickf1

so here is my code:

$query = "SELECT `filename`, `url` FROM (featured_articles) ORDER BY `KEY` ASC LIMIT 5";
$result = @mysql_query($query);

while ($row = mysql_fetch_array
($result, MYSQL_ASSOC)){
printf (" ID: %s  FileName: %s", $row['url'], $row["filename"]);
}

echo "Recent featured articles<br /><a href='./articles/?p=$featured_article_url'><img src='./images/featured_articles/$featured_article_img' alt='Featured article'></a>";

So I am pulling the filename and url fields from the 5 bottom rows of a table. Now I want to select a random pair of matching filename and urls from the 5 rows i have pulled.

the trouble is that if I use array_rand() it returns the array elements in a completely random order...I want to keep element 0 paired with element 1 and 2 with 3 and 4 with 5, etc. So I want a random 'pair' from the array, how might I do this?

sid

have a look at the following functions:

[man]array_rand/man, [man]current/man, [man]next/man and [man]array[/man] in general.

hope that helps!

nickf1

sid wrote:
have a look at the following functions:

[man]array_rand/man, [man]current/man, [man]next/man and [man]array[/man] in general.

hope that helps!

the trouble is that array_rand mixes up the array so if i use [man]next/man to get the next value in the array it is not going to be the right value.

here is a simplified version:

array(eggs, bacon, football, baseball, ford, toyota)

so i want to get a random pair so that only these 3 outcomes are possible:

outcome1: eggs, bacon
outcome 2: football, baseball
outcome 3: ford, toyota

so I get a random "paired outcome"

any thoughts on the best way do do this?

sid

i'd make a copy of the original array; apply the [man]array_rand/man to the copy. then find the returned element in the original array with [man]array_search/man and get the succeeding element with [man]next/man; finally you just need to compensate for the possibility that the rand item is at the end of the array.

nickf1

sid wrote:
i'd make a copy of the original array; apply the [man]array_rand/man to the copy. then find the returned element in the original array with [man]array_search/man and get the succeeding element with [man]next/man; finally you just need to compensate for the possibility that the rand item is at the end of the array.

that has gone pretty far over my head...an example of the syntax would be really appreciated :o

laserlight

Assuming that you have a numerically indexed array of an even number of elements, n, this problem is equivalent to generating an even random number in the range [0, n).

$array = array('eggs', 'bacon', 'football', 'baseball', 'ford', 'toyota');

$rand_index = mt_rand(0, count($array) / 2 - 1) * 2;

echo $array[$rand_index] . " " . $array[$rand_index + 1];

In this case, the random number generated is in the range [0, 2]. By multiplying by 2, we get the even random numbers. To get the next element, we just add 1.

I note that in your original example, you say you selected the 5 bottom rows of the table, but you list 6 array elements.

nickf1

that works great thank you...I seem to be having trouble using it with my fetched array:

while ($row = mysql_fetch_array
($result, MYSQL_ASSOC)){

but maybe this is a different issue.

laserlight

You have to select an even number of rows (and actually place them into an array). If you only need those two rows, then you should compute the offset and then run the query, thus retrieving only two rows.

NogDog

If you are using MySQL 4.1 or later:

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

laserlight

Oh dear, I think I may have been misled by the "keep element 0 paired with element 1 and 2 with 3 and 4 with 5" and the subsequent simplified example. If you actually want to select a random row from a table, then NogDog's idea is correct. With only 5 rows in the intermediate result set, ORDER BY RAND() should remain scalable.

NogDog

laserlight wrote:
Oh dear, I think I may have been misled by the "keep element 0 paired with element 1 and 2 with 3 and 4 with 5" and the subsequent simplified example. If you actually want to select a random row from a table, then NogDog's idea is correct. With only 5 rows in the intermediate result set, ORDER BY RAND() should remain scalable.

That assumes I have correctly interpreted the requirement, which may not be a sure thing. 😉

nickf1

hehe...sorry to confuse you guys.

So by the look of it NogDog's solution is the easiest way to go...I would have attempted it that way if I had a better knowledge of mysql queries.

I am however having trouble running mysql_fetch_array with this:
while ($result = mysql_fetch_array
($query, MYSQL_ASSOC)){

it is the second line that it gets stuck on with this error:

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in C:\Program Files\xampp\ht...

laserlight

We have to see the query you tried to see why. Most likely the query is incorrect, so $result is false, not a valid result resource.

nickf1

oh it's the exact same query as NogDog posted...i checked it through and he got all my table names correct and everything.

bradgrafelman

Just a personal preference here, but I've always used [man]mysql_fetch_assoc/man rather than the _array() function with a constant as the second parameter; just seemed like there was less clutter (and less typing :p) to me.

If you're getting that message, and $query is the result of a [man]mysql_query/man call, then the SQL query has failed and you need to get some debug information. Normally, catching the errors in failing SQL queries is done like so:

$query = 'SELECT `foo` FROM `bar` WHERE "syntax_error == TRUE';
$exec = mysql_query($query) or die('MySQL error: ' . mysql_error() . '<hr>Query: ' . $query);

Let us know what error messages MySQL is giving you (as well as showing us the outputted query).

nickf1

MySQL error: This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

my version is PHP: 4.4.5

and MySQL 5.0.33

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.