user3470987 user3470987 - 1 year ago 74
SQL Question

Copying random rows from one MySQL table to another MySQL table with a different schema

I have database table named

that has many locations following this schema:
lid(PID), name, totalVotes, timeSince
and I want to randomly select 4 rows from this table and copy them into another table with a schema of
sid(PID), lid(Foreign), votes
. I want to set up a cron job to do this every day so I am first writing a PHP script to then be set up with the cron job later.

I am having problems getting the script to work though as no changes are being reflected in my selected database. Here is my attempt so far in

// create connection
@mysql_select_db("Locations") or die("Unable to select db");

// this SQL statement moves 4 random rows from locations to selected table
$sql = "INSERT INTO selected (lid) SELECT lid FROM locations WHERE lid IN (SELECT lid FROM locations ORDER BY RAND() LIMIT 4);";

echo "all good";

When I refresh my
I get the "all good" message, but again no changes in my

Answer Source

Could you try this? The first select is not needed and y think you have a Bad ; on thge end of the query.

INSERT INTO selected (lid) (SELECT lid FROM locations ORDER BY RAND() LIMIT 4)
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download