Random but ordered

A first shoot at solving the problem, with some memory of Don Knuth's "the art of computer programming":
1 - put all the indexes into a single array (say record_id), that will be n+1 values with index ranging from 0 to n.
2 - using the rand() function http://www.php.net/manual/function.rand.php, do the following

for ($i=1; $i <=n; i++) { // explore n exchanges between the n+1 values
     $j=rand(0,$i); // random value between 0 and $i included
     if ($j<$i) { // else record_id[$i] is not exchanged
          $temp=$record_id[$i];
          $record_id[$i]=$record_id[$j];
          $record_id[$j]=$temp;
     } // end exchange 2 IDs
;} // end exchange loop

You can now display the records with index key $record_id[0], [1] etc
Note that this is NOT the same that having $j=rand(0,$n) which would move more the high values.

Hi mag1c1an,

"cno" is $row[44], right?
Try this:

<?php
    // First you get X random companies (cno-s)
    $limit = X;
    $sql = 'SELECT DISTINCT cno FROM webplans_shared ORDER BY RAND() LIMIT '.$limit;
    $result = mysql_query($sql);
    while ($row = mysql_fetch_row($result)) {
        $random_cno[] = $row[0];
    }
    $random_cno = implode(', ', $random_cno);
   
    // now select all records you need
    $sql = 'SELECT * FROM webplans_shared
                    WHERE (price <= 200) AND (space >=2) AND (cno IN ('.$random_cno.'))
                    ORDER BY cno, price LIMIT 0, 30';
    // ... continue your code.
?>

BTW, there is a problem with you current code:
Since each company can have any number of "webplans" you are losing some of the plans by cutting the list with the limit.
Say, for example, you have 2 companies, each has 20 "webplans". Your query returns only 30 plans - you will loose half of the plans of the second company.

I would recommend following code:
<?php
    // decide how many companies you want to show on one page:
    $companies = 4;
    // if you want to use pagination, you should specify some number to use with RAND() and pass it between pages
    // so RAND() will always return the same random sequence
    $sql = 'SELECT DISTINCT cno FROM webplans_shared ORDER BY RAND()';
    //    ... same code as above
    $sql = 'SELECT * FROM webplans_shared
                    WHERE (price <= 200) AND (space >=2) AND (cno IN ('.$random_cno.'))
                    ORDER BY cno, price'; // No LIMIT
    // ... continue your code
---
German Rumm.

But if you break this up in pagenation, the next page of results could show previously shown ones as they're a randomized set, correct?

This is what I was thinking when I looked at this last night.

Perhaps randomize the sort...

$sorts = array('cno','price','name','etc');
$thissort = $sorts[mt_rand(0,count($thissort)-1)];

In this way, you could pass the sort from page to page and not see duplicate entries. And you also have the advantage of allowing the customer to sort manually; as a side benefit.

Hi Guys,
Thanks for replying, you have helped me get some ideas of my own.

The problem is, when i try most of your ideas I get a crapload of errors, can you give me a little more code so I can work from there? I guess i am just tired of this project so my brain is not working :-(

****
"cno" is $row[44], right?
****
Nope, $row[44] is "company"


Thanks,
Mag

Perhaps you need to indicate whose solution you prefer so only that person can try to help you with coding.

Hi,
My apoligies for my slightly delayed response, I have been working non stop on another clients project and have not had time to work on my own...I am sure a lot of you have the same problems ;-)


"Perhaps you need to indicate whose solution you prefer so only that person can try to help you with coding."

Presently I think your solution "levelg" suits me best right now, but I am keeping an open mind thats why I didnt name anybody.

Levelg, please post come code where i can copy and test it out.

Thanks in advance,
Mag

Hi Mag,
I have 3 deadlines to meet within next 2 weeks and I'm afraid I won't be able to provide you with working code even though it should take no more than 1-2 hours to have it ready.

No problem...I'll try to figure out exactly what you meant as soon as i get time,
I'm sure to run into problems and will post again, if I do not, feel free to post any advise or code in the meantime.

Thanks go out to everyone who has replied on this list, everyones suggestions were good but I just feel levelg's solution suits my needs best right now.

Cheers,
Mag

levelg,

Would it be an option to add an additional key to the tables which stores the random ordering.
When inserting a new company you could then run an additional query:

$query = 'UPDATE `companies` SET `rnd_order` = RAND()'; //rnd_order being a FLOAT(12) column or something

Another option could be to have this additional field and have it reordered once a day using a cronjob.

My 2cts

-r-

Roonaan,
What you're suggesting will give some companies permanent or daily advantage over other companies.
My solution would give every company a chance to be on the top every time a user submits a new search query.

No comment has been added to this question in more than 21 days, so it is now classified as abandoned..
I will leave the following recommendation for this question in the Cleanup topic area:
Accept: Roonaan {http:#14006450} & levelg {http:#13952369}

Any objections should be posted here in the next 4 days. After that time, the question will be closed.

Huji
EE Cleanup Volunteer