Wednesday, 29 February 2012

Using insert and update query in one go

Developer in his/her logic need to check whether this record exists or not then if exist perform a update query else go for the insert query.
For example a typical sql query written in php in traditional way:
$query="SELECT * FROM `catalog_product_entity_varchar` WHERE `entity_id` =$entityId AND entity_type_id = $entityTypeId AND attribute_id = $attributeId";
$rows=mysql_query($query);
if(mysql_num_rows($rows) > 0)
{  //Update the row 
 $query="Update catalog_product_entity_varchar set value='".$securityRef."' where value_id=".mysql_fetch_object($rows)->value_id;
 if(mysql_query($query)) echo "\n
$sku===Udpated==".date("H:i:s");
}
else
{  //insert a rwo

 $query="insert into catalog_product_entity_varchar set entity_type_id = $entityTypeId, attribute_id = $attributeId, store_id = 0, entity_id = $entityId, value = '".$securityRef."'";
 if(mysql_query($query)) echo "\n
$sku===Added====".date("H:i:s");
}



Now we can use the innovated way which is 60% efficient:
$query="insert into catalog_product_entity_varchar set entity_type_id = $entityTypeId, attribute_id = $attributeId, store_id = 0, entity_id = $entityId, value = '".$securityRef."'".

   " ON DUPLICATE KEY UPDATE value = '".$securityRef."'";

if(mysql_query($query)) echo "\n
$sku===Added====".date("H:i:s");

3 comments :

  1. I wanted to thank you yet again for this amazing web-site you have designed here. It's full of ideas for those who are definitely interested in this subject, especially this very post. You're really all absolutely sweet and thoughtful of others plus reading your blog posts is a superb delight if you ask me. And what a generous gift! Ben and I are going to have fun making use of your points in what we should instead do next week. Our list is a mile long and tips might be put to good use.
    My Bolg : DepressionSymptomsMedication.com

    ReplyDelete
  2. Excellent post. You must continue to offer excellent resources and content like you have been offering. I will most likely stop by again in the future.

    ReplyDelete
  3. exactly what i was looking for, thank you

    ReplyDelete