PHP MySQL INSERT data from another table using the where clause and insert unique values to the same rows

Question

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.

Do I get you right: The additional data comes from outside and doesn't reside somewhere in the old database? You probably have to do some interprocessing and split up SQL statements: Fetch data via sql, enrich data by adding values to a php array, store new data array via sql. — matthias
– matthias, Commented Oct 22, 2012 at 18:22
Please don't use mysql_query in new applications. It's very difficult to use correctly and can lead to hazardously bad code full of SQL injection points. Your example here is a gigantic liability that is asking to be exploited. Using PDO or mysqli is considerably safer, and easier to use correctly. — tadman
– tadman, Commented Oct 22, 2012 at 19:26

gregmac · Accepted Answer · 2012-10-22 18:39:02Z

2

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'";

edited Oct 22, 2012 at 18:39

gregmac

25.6k11 gold badges93 silver badges121 bronze badges

answered Oct 22, 2012 at 18:38

redlami

713 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

JRJP Over a year ago

Selecting literal values worked like a charm. Awesome. Thank you very much.

Abhishek Saha · Accepted Answer · 2012-10-22 18:36:25Z

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";
}

}

AlexP · Accepted Answer · 2012-10-22 18:40:05Z

0

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'";

answered Oct 22, 2012 at 18:40

AlexP

9,8571 gold badge28 silver badges44 bronze badges

Collectives™ on Stack Overflow

PHP MySQL INSERT data from another table using the where clause and insert unique values to the same rows

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related