使用不同的模式将随机行从一个MySQL表复制到另一个MySQL表

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.

我有数据库表名为位置,在此架构后面有许多位置:lid(PID),name,totalVotes,timeSince和我想从该表中随机选择4行并将它们复制到另一个具有sid(PID)模式的表中,盖子(外国),投票。我想设置一个cron作业来每天这样做,所以我先写一个PHP脚本,然后再用cron作业设置。

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:

我在让脚本工作时遇到问题,因为我选择的数据库中没有反映任何更改。这是我到目前为止在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.

当我刷新我的localhost / copy.php时,我收到了“all good”消息,但我所选择的表中没有任何更改。

1 个解决方案

#1

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)