Tag Archives: MYSQL

How to remove all catalog products in Magento?

SET FOREIGN_KEY_CHECKS = 0;
TRUNCATE TABLE `catalog_product_bundle_option`;
TRUNCATE TABLE `catalog_product_bundle_option_value`;
TRUNCATE TABLE `catalog_product_bundle_selection`;
TRUNCATE TABLE `catalog_product_entity_datetime`;
TRUNCATE TABLE `catalog_product_entity_decimal`;
TRUNCATE TABLE `catalog_product_entity_gallery`;
TRUNCATE TABLE `catalog_product_entity_int`;
TRUNCATE TABLE `catalog_product_entity_media_gallery`;
TRUNCATE TABLE `catalog_product_entity_media_gallery_value`;
TRUNCATE TABLE `catalog_product_entity_text`;
TRUNCATE TABLE `catalog_product_entity_tier_price`;
TRUNCATE TABLE `catalog_product_entity_varchar`;
TRUNCATE TABLE `catalog_product_link`;
TRUNCATE TABLE `catalog_product_link_attribute`;
TRUNCATE TABLE `catalog_product_link_attribute_decimal`;
TRUNCATE TABLE `catalog_product_link_attribute_int`;
TRUNCATE TABLE `catalog_product_link_attribute_varchar`;
TRUNCATE TABLE `catalog_product_link_type`;
TRUNCATE TABLE `catalog_product_option`;
TRUNCATE TABLE `catalog_product_option_price`;
TRUNCATE TABLE `catalog_product_option_title`;
TRUNCATE TABLE `catalog_product_option_type_price`;
TRUNCATE TABLE `catalog_product_option_type_title`;
TRUNCATE TABLE `catalog_product_option_type_value`;
TRUNCATE TABLE `catalog_product_super_attribute`;
TRUNCATE TABLE `catalog_product_super_attribute_label`;
TRUNCATE TABLE `catalog_product_super_attribute_pricing`;
TRUNCATE TABLE `catalog_product_super_link`;
TRUNCATE TABLE `catalog_product_enabled_index`;
TRUNCATE TABLE `catalog_product_website`;
TRUNCATE TABLE `catalog_product_entity`;

TRUNCATE TABLE `cataloginventory_stock`;
TRUNCATE TABLE `cataloginventory_stock_item`;
TRUNCATE TABLE `cataloginventory_stock_status`;
SET FOREIGN_KEY_CHECKS = 1;
INSERT  INTO `catalog_product_link_type`(`link_type_id`,`code`) VALUES (1,'relation'),(2,'bundle'),(3,'super'),(4,'up_sell'),(5,'cross_sell');
INSERT  INTO `catalog_product_link_attribute`(`product_link_attribute_id`,`link_type_id`,`product_link_attribute_code`,`data_type`) VALUES (1,2,'qty','decimal'),(2,1,'position','int'),(3,4,'position','int'),(4,5,'position','int'),(6,1,'qty','decimal'),(7,3,'position','int'),(8,3,'qty','decimal');
INSERT  INTO `cataloginventory_stock`(`stock_id`,`stock_name`) VALUES (1,'Default');

Then you may require re-indexing all your indexes after running above query.

for that go to System > Index Management > Reindex all.

Advertisements
PDO-Database-connections-and-abstraction

How to connect to the MYSQL using PDO?

Today I explain you how to connect with the MYSQL database using PDO.

In PDO(PHP Data Object) it’s very easy to connect with MYSQL.  As per below example you can easily  understand.

<?php
	$user='root'; // Enter your DB User Name.
	$pass=''; // Enter your DB Password.
	$hostName='localhost'; // Enter your host name.
	$dataBaseName='webexpertdeveloper'; // Enter your Database Name.
	$dbh = new PDO('mysql:host='.$hostName.';dbname='.$dataBaseName, $user, $pass);
	echo "Connection Successful";
?>

If there are any connection errors, a PDOException object will be thrown. You may catch the exception if you want to handle the error condition. For that you have to use below example you have to handle exception using try-catch .

<?php
	try
	{
		$user='root'; // Enter your DB User Name.
		$pass=''; // Enter your DB Password.
		$hostName='localhost'; // Enter your host name.
		$dataBaseName='webexpertdeveloper'; // Enter your Database Name.
		$dbh = new PDO('mysql:host='.$hostName.';dbname='.$dataBaseName, $user, $pass);
		echo "Connection Successful";
	}
	catch (PDOException $e)
	{
		print "Error!: " . $e->getMessage() . "
";
		die();
	}
?>

If any error occur then PDOException handle this and give you proper error message.

If you want to Persistent connections using PDO then follow below example.

Many web applications will benefit from making persistent connections to database servers. Persistent connections are not closed at the end of the script, but are cached and re-used when another script requests a connection using the same credentials. The persistent connection cache allows you to avoid the overhead of establishing a new connection every time a script needs to talk to a database, resulting in a faster web application.

<?php
	try
	{
		$user='root'; // Enter your DB User Name.
		$pass=''; // Enter your DB Password.
		$hostName='localhost'; // Enter your host name.
		$dataBaseName='webexpertdeveloper'; // Enter your Database Name.
		$dbh = new PDO('mysql:host='.$hostName.';dbname='.$dataBaseName, $user, $pass,array(PDO::ATTR_PERSISTENT => true));
		echo "Connection Successful";
	}
	catch (PDOException $e)
	{
		print "Error!: " . $e->getMessage() . "
";
		die();
	}
?>

Closing a connection

<?php
	try
	{
		$user='root'; // Enter your DB User Name.
		$pass=''; // Enter your DB Password.
		$hostName='localhost'; // Enter your host name.
		$dataBaseName='webexpertdeveloper'; // Enter your Database Name.
		$dbh = new PDO('mysql:host='.$hostName.';dbname='.$dataBaseName, $user, $pass,);
		echo "Connection Successful";
                $dbh = null; // It Will Close the connection
	}
	catch (PDOException $e)
	{
		print "Error!: " . $e->getMessage() . "
";
		die();
	}
?>