从一个表导入数据并将其插入到另一个表中 - import data from one table and insert it into another

import data from one table and insert it into another

我有两个表product_myob和products。我要做的是在单击导入按钮以表products时，从product_myob导入产品。表products已经包含一些产品。

我尝试执行下面的查询，但表没有更新。请帮我查一下密码。

INSERT INTO products(myob_id, myob_displayID, Name)
  SELECT UID,displayID,itemName 
  FROM product_myob 
  WHERE UID NOT IN (SELECT myob_id from products);

您的产品表上似乎有一个唯一的检查，并且您正在插入一些重复的数据。如果是这种情况，您可以通过下面的查询忽略重复的值，即使您不需要检查产品表中的UID，因为此查询会自动忽略它。其他明智的做法是详细说明您的问题：

INSERT ignore INTO products(myob_id, myob_displayID, Name)
  (SELECT UID,displayID,itemName 
  FROM product_myob);

这是一个可以使用的示例代码。使用MySQLi，因为MySQL已经被弃用。

<?php
/* ESTABLISH CONNECTION AND CHANGE THE NECESSARY HOST, USERNAME, PASSWORD AND DATABASE */
$connection=mysqli_connect("YourHost","YourUsername","YourPassword","YourDatabase");
if(mysqli_connect_errno()){
echo "Error".mysqli_connect_error();
}
$result=mysqli_query($connection,"SELECT * FROM product_myob");
while($row=mysqli_fetch_array($result)){
$myobid=mysqli_real_escape_string($con,$row['UID']);
$myobdisplayid=mysqli_real_escape_string($con,$row['displayID']);
$name=mysqli_real_escape_string($con,$row['itemName']);
/* CHECK IF DATA IS ALREADY IN THE products TABLE */
   $result2=mysqli_query($connection,"SELECT * FROM products WHERE myob_id='$myobid' AND myob_displayID='$myobdisplayid' AND Name='$name'");
   if(mysqli_num_rows($result2)==0){
   mysqli_query($connection,"INSERT INTO products (myob_id, myob_displayID, Name) VALUES ('$myobid','$myobdisplayid','$name')");
   } /* END OF IF MYSQLI_NUM_ROWS($RESULT2) IS 0 */
   else {
   echo "Data has already been inserted before.<br>";
   }
} /* END OF WHILE LOOP RESULT */
?>