PHP MySQL使用where子句INSERT来自另一个表的数据，并将唯一值插入到相同的行中

问题描述：

I want to insert data from one table into another where one field equals another in both tables. So far this works. The problem is, I also need to insert additional data into those same rows that is not included on the first table.

//enter rows into database 
foreach($_POST['sku'] as $row=>$sku)
{ 
//this is the data that needs to be added to the table 
$item_sku=$sku;
$image="/$item_sku.jpg";
$small_image="/$item_sku.jpg"; 
$thumbnail="/$item_sku.jpg";

//currently this is what is working to import data from one table to the other
$sql= "INSERT INTO magento_import (sku, description, price) 
       SELECT PR_SKU, PR_Description, PR_UnitPrice
       FROM products 
       WHERE PR_SKU = '$sku'";

//I need something here to add the above variables to the same row where PR_SKU = '$sku'

if (!mysql_query($sql))
{
die('Error: '.mysql_error()); 
}
echo "$row record added";
}

The columns for the missing data on magento_table are called 'image', 'small_image', and 'thumbnail'. This is simple a hack to put data from an old product table into a new product table, export as a CSV, and run a profile in Magento. I don't need to worry about SQL injections. It's something I'm running off of a local machine. I'm trying to avoid as much manual data entry as possible while switching products over to a new ecommerce system. Thanks for any help you can give.

我想将一个表中的数据插入到另一个表中，其中一个字段等于两个表中的另一个字段。到目前为止这个工作。问题是，我还需要将其他数据插入到第一个表中未包含的相同行中。 p>

  //将行输入数据库
foreach（$ _ POST ['sku']为$ row =＆gt; $ sku）
 {\  n //这是需要添加到表中的数据
 $ item_sku = $ sku; 
 $ image =“/ $ item_sku.jpg”; 
 $ small_image =“/ $ item_sku.jpg”;  
 $ thumbnail =“/ $ item_sku.jpg”; 
 
 //当前这是将数据从一个表导入另一个表的工作
 $ sql =“INSERT INTO magento_import（sku，description，price）  
 SELECT PR_SKU，PR_Description，PR_UnitPrice 
 FROM products 
 WHERE PR_SKU ='$ sku'“; 
 
 //我需要一些东西将上面的变量添加到PR_SKU ='$ sku'的同一行 n 
if（！mysql_query（$ sql））
 {
die（'Error：'。mysql_error（））;  
} 
echo“$ row record added”; 
} 
  code>  pre> 
 
  magento_table上缺少数据的列称为“image”，“small_image”， 和'缩略图'。 将旧产品表中的数据放入新产品表，导出为CSV并在Magento中运行配置文件，这很简单。 我不需要担心SQL注入。 这是我在本地机器上运行的东西。 在将产品切换到新的电子商务系统时，我试图避免尽可能多的手动数据输入。 感谢您提供任何帮助。 p> 
  div>

答

Selecting literal values should do what you intend:

$sql= "INSERT INTO magento_import (sku, description, price, image, small_image, thumbnail) 
       SELECT PR_SKU, PR_Description, PR_UnitPrice, \"$image\", \"$small_image\", \"$thumbnail\"
       FROM products 
       WHERE PR_SKU = '$sku'";

答

You can use another update statement to do this. I doubt if you can do this with one single query

foreach($_POST['sku'] as $row=>$sku)
{ 
//this is the data that needs to be added to the table 
$item_sku=$sku;
$image="/$item_sku.jpg";
$small_image="/$item_sku.jpg"; 
$thumbnail="/$item_sku.jpg";

//currently this is what is working to import data from one table to the other
$sql= "INSERT INTO magento_import (sku, description, price) 
SELECT PR_SKU, PR_Description, PR_UnitPrice
FROM products 
WHERE PR_SKU = '$sku'";

//I need something here to add the above variables to the same row where PR_SKU = '$sku'

if (!mysql_query($sql))
{
die('Error: '.mysql_error()); 
}else {

$sql = "UPDATE magento_import SET item='$item',image='$image',small_image='$small_image',thumbnail='$thumbnail' WHERE PR_SKU = '$sku'";
echo "$row record added";
}

}

答

You can just add the variables directly to the query. As long as they are quoted they will work as simple values.

$sql= "INSERT INTO magento_import (sku, description, price, x, y, z) 
   SELECT PR_SKU, PR_Description, PR_UnitPrice, '$x' AS x, '$y' AS y, '$z' AS z
   FROM products 
   WHERE PR_SKU = '$sku'";

PHP MySQL使用where子句INSERT来自另一个表的数据，并将唯一值插入到相同的行中

相关推荐