MySQL random

Nickosssss

Hello i would like to ask if someone knows how to insert values randomly....

for example

i have this table

Create table students (stud_id, name, lastname....);

I want to randomize the select

select * from student order by rand();

and then insert the records to another table

how do i construct the INSERT statement?

thank you

Kudose

MySQL is always going to insert data into the next row.

It won't randomly skip a few rows (i.e. create 5 empty rows, then insert a new one).

planetsim

Originally posted by Nickosssss
Hello i would like to ask if someone knows how to insert values randomly....

for example

i have this table

Create table students (stud_id, name, lastname....);

I want to randomize the select

select * from student order by rand();

and then insert the records to another table

how do i construct the INSERT statement?

thank you

Like Kudose said you cant randomly add records. However reading further down what your after is read all records in a random order (not sure why you wish to do this) and insert into another table.

// no error checking so you may get errors since this is also not tested
$query = mysql_query("select * from student order by rand()");
while ($row = mysql_fetch_assoc($query))
{
    mysql_query("INSERT INTO mytable
                            (stud_id, name, lastname)
                            VALUES ('null', '".$row['name']."', '".$row['lastname']."')
                             "); // you may want the same stud_id so use $row['stud_id'] instead of null.
}

Nickosssss

Oh ok i got it now.....but the main problem is that i need to randomize the fields of TWO tables and insert their primary keys into one table for matching.

the first table has 18 unique fields and the second has 3

because i use mysqli_fetch_assoc it only displays 3 fields because the second table has 3.

I need to have 18 different matches with these 3....

i mean that each of the 3 primary keys needs to match with 6 different primary keys.

the 18 field table is for students
and the 3 fields table is for staff

$query = "select stud_id from student order by rand() limit 2";

$query2 = "select staff_id from staff order by rand() ";

$result = $db_conn->query($query);
$result2 = $db_conn->query($query2);

while ($row2 = mysqli_fetch_assoc($result2))
{

while( $row = mysqli_fetch_assoc($result)) 
{


$query3 ="INSERT INTO level_A VALUES ('".$row['stud_id']."' , '".$row2['staff_id']."')";                         
$result3 = $db_conn->query($query3);

and something else if u know....How do i ensure that one key is not inserted more than one time...for the student table....