algorithm howto,

Manat

Hello all,

Suppose I have a database full of form entries, and I want to radomly extract x amount of rows and put them in another table.

Say for example I have a contest, with 25 000 entries in MySQL, and I wanted to create a script that would radomly choose 400 entries from the table and put them all in a seperate table, how would I do so?

Thx
MK

bad76

Hi,
you can use rand function in your query to extract this field, and put it in a brand new table it's easy, not?

Else you can use any ID as key, integer autoinc. Then in a "for" you can query one to one each row WHERE ID=$rnd,
where $rnd=rand(min,max)

it's the same...

i hope this help you

drawmack

actually you'll want a function something like this:

define('NUM_FINALISTS',200);
$sql = "SELECT <id> FROM <table> ORDER BY rand()";
$query = mysql_query($sql);
$finalists = array();
$i=0;
while(NUM_FINALISTS > count($finalists)) {
    $id = mysql_result($query,$i,0);
    if(!in_array($id,$finalists)) {
        array_push($finalists,$id);
    } //end if
} //end while

$sql = "INSERT INTO <table_2> WHERE (SELECT * FROM <table_1> WHERE ID IN (" . implode(",",$finalists) . ")";
mysql_query($sql);

that will give you the first 200 unique random finalists inserted into table 2.

Manat

Hi Drawmack,

I used your code, but i'm getting an error on line 8, a HUGE error as well.  Just one or 3 requests caused my php log to increase to 210megs.

My error: Warning: Supplied argument is not a valid MySQL result resource in ./fileName.php on line 8

line 8:
while(NUM_FINALISTS > count($finalists)) {

Although this error is nothing more than a warning, it got appended to my log is a zillion times, and nothig got appended to table2, table2 is still empty.

Any Idea what's up?
Thx

drawmack

I didn't test the code so I'm not sure what's up, what is the warning you are getting?