user3470987 user3470987 - 6 months ago 10
SQL Question

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

I have database table named

locations
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
copy.php
:

<?php
// create connection
mysql_connect("localhost","root",NULL);
@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);";


mysql_query($sql);
mysql_close();
echo "all good";
?>


When I refresh my
localhost/copy.php
I get the "all good" message, but again no changes in my
selected
table.

Answer

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)
Comments