[SOLVED] Inserting records into DB only if the record does not exist?????

[CaseyC1]

I populate one of the tables in my database using the following code:

 

$sql = "INSERT INTO CubeCart_inventory (productId, productCode, quantity, description, image, noImages, price, name, cat_id,popularity, sale_price, stock_level, useStockLevel, digital, digitalDir, prodWeight, taxType, showFeatured, prod_metatitle, prod_metadesc, prod_metakeywords, prod_sefurl, img_folder, my_price, catalog_price)" . "VALUES ('', '$productCode', '$quantity', '$description', '$image', '$noImages', '$price', '$name', '$cat_id', '$popularity', '$sale_price', '$stock_level', '$useStockLevel', '$digital', '$digitalDir', '$prodWeight', '$taxType', '$showFeatured', '$prod_metatitle', '$prod_metadesc', '$prod_metakeywords', '$prod_sefurl', '$img_folder', '$my_price', '$catalog_price')" ;

 

mysql_query($sql);

 

This works just fine when I initially write all of my products into an empty table. What I need to do now is to add to the table. The possibility exists that there might be items in the "additions" that are already in the table. I don't want to duplicate these. Is there anyway I can put a condition on the $sql that will not enter the record into the table if the VALUE $productCode already exists in the db? I read something about a "Subquery", but it didn't really register in my old brain........

 

Thanks,

Klaus Cook

Houston, Texas

[PHP Monkeh]

What are you wanting to do if the value already exists?  Not do anything at all or update that record?

 

To find out if a record already exists you can do:

 

<?php

$query = "SELECT id FROM CubeCart_inventory WHERE productCode = '$productCode'";
$result = mysql_query($query) or die(mysql_error());

if(mysql_num_rows($result) > 0) {

// $productCode already exists in the table!

} else {

// Do your insert code here

}

?>

 

Hope you can understand that

[CaseyC1]

Thank you for the reply. I just want to disregard the record if it is already in the db. I read online something about a subquery.....I think it goes something like the underlined portion of the sql, but I'm not sure. Maybe you, or someone else, can critique the code:

 

  $sql = "INSERT INTO CubeCart_inventory (productId, productCode, quantity, description, image, noImages, price, name, cat_id,popularity, sale_price, stock_level, useStockLevel, digital, digitalDir, prodWeight, taxType, showFeatured, prod_metatitle, prod_metadesc, prod_metakeywords, prod_sefurl, img_folder, my_price, catalog_price)" . "VALUES ('', '$productCode', '$quantity', '$description', '$image', '$noImages', '$price', '$name', '$cat_id', '$popularity', '$sale_price', '$stock_level', '$useStockLevel', '$digital', '$digitalDir', '$prodWeight', '$taxType', '$showFeatured', '$prod_metatitle', '$prod_metadesc', '$prod_metakeywords', '$prod_sefurl', '$img_folder', '$my_price', '$catalog_price') WHERE EXISTS (SELECT * FROM CubeCart_inventory WHERE productCode=$productCode)" ;

 

  mysql_query($sql);

[toplay]

You can't have a "where" condition after "values" like that in the "insert". You can specify a "select" to retrieve values from another table to be used to "insert".

 

If wanted to update you can use "ON DUPLICATE KEY UPDATE" option in the "insert" or better yet, use "replace" instead.

 

But since you don't want to do anything if row exists, then just do an "insert" and just ignore the duplicate key error. When row doesn't exist MySQL will add it, and when it does exist it won't add it and return error # 1022.

 

Insert syntax:

http://dev.mysql.com/doc/refman/5.0/en/insert.html

 

Error codes:

http://dev.mysql.com/doc/refman/5.0/en/error-messages-server.html

 

Replace syntax:

http://dev.mysql.com/doc/refman/5.0/en/replace.html