[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 210
  • Last Modified:

Random but ordered

Hey,

I have been screwing around with this code for around a day and am hitting a
wall, any
help would be appreciated.


Basically, I am working on a site that queries the DB for companies and the
plans the company
is offering, the DB is quite big (a few thousand records in each of the 20
tables)

The parameters are taken from the client in the form of a search box and
usually gives around 1000 results per query, they results are then displayed
like this:


company 1
[] plan
[] plan
[] plan
[] plan
company 2
[] plan
[] plan
[] plan
company 3
[] plan
company 4
[] plan
[] plan

So far, no problems (even though the code and the design are mixed together
in the page)
but so far the code is getting the results grouping via $cno, which results
in the lowest being displayed first which gives the first companies who
joined an edge as they are always getting displayed.


Heres the kicker, he wants to have the same display as above....but randomly
from the DB *while still* having pagination.. so new joined companies too
get displayed and not shoved at the end of the results.


To see the code used presently (stripped down to a few lines) :
http://jappz.com/testing.phpp

To see the output from the above :
http://jappz.com/testing.html


Any suggestions, classes, code, links or general help would be appreciated.

Thanks,
Mag
0
mag1c1an
Asked:
mag1c1an
  • 4
  • 3
  • 2
  • +4
2 Solutions
 
fiboCommented:
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.
0
 
German_RummCommented:
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.
0
 
PromethylCommented:
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.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
PromethylCommented:
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.
0
 
levelgCommented:
Here's what I would do

When the user sends the search query:

1. Get plan ids for all plans matching the search criteria from the database
2. Store the result in an array
3. Randomize plan ids in that array and save the final array in a session variable or in db table created to store this result
4. Pick the first N plan ids and send a query to DB to get neccessary information (company & plan details)
5. Output the plans and pagination links if neccessary. If in the Step 3 above you choose to store the randomized array in DB table make sure you include the key field value in the links to other pages

When the user clicks on Page 2, 3 and so on

1. Initialize the session variable or query the temp DB table using the key field value found in the query to get the randomized array
2. Pick the next N plan ids from the randomized array based on the page number requested
3. Query the DB to get the plans information and output the result on the screen

If you choose to store the randomized array in a temporary DB table here's a table structure I would suggest

searchid - key, autoincrement
searchtime - timestamp (to clear old entries later)
planids - text (you can use serialize and unserialize PHP functions to easily store and restore the randomized array value)

Regards,

  Garik
0
 
mag1c1anAuthor Commented:
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
0
 
levelgCommented:
Perhaps you need to indicate whose solution you prefer so only that person can try to help you with coding.
0
 
mag1c1anAuthor Commented:
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
0
 
levelgCommented:
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.
0
 
mag1c1anAuthor Commented:
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
0
 
RoonaanCommented:
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-
0
 
levelgCommented:
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.
0
 
RoonaanCommented:
levelg, that is offcourse correct.

In that case, I think the best way to go would be to use a seeded randomize function in which you store the seed into a session. The usage of such an seed should cause a random, but consistent behaviour when you reuse the same seed.

For example a sample code would/could be:

<?php
session_start();

if(!isset($_SESSION['companyseed'])) $_SESSION['companyseed'] = make_seed();

function make_seed()
{
   list($usec, $sec) = explode(' ', microtime());
   return (float) $sec + ((float) $usec * 100000);
}
?>

Then in your queries you would/could use:

$query = 'SELECT .... FROM table WHERE ..... ORDER BY company_id * RAND('.$_SESSION['companyseed'].')';

-r-
0
 
hujiCommented:
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
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 4
  • 3
  • 2
  • +4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now