Posting in the Magento forums has been disabled pending the implementation of a new and improved forum solution which should better serve the community.

For new questions please post at magento.stackexchange.com, the community-run support site for the Magento community. We will be providing updates on the new forum solution soon. For questions or concerns please email community@magento.com.

Magento Forum

Simple SQL based Price and Quantity Update - Any problem with the codes
 
pixelslave
Jr. Member
 
Avatar
Total Posts:  3
Joined:  2010-05-16
 

I created a simple PHP page to update prices and quantities directly thru SQL (the admin tool’s import is WAY TOO slow. I have over 10,000 products and it takes hours to update.) I am new to Magento/MySQL/PHP, so I am not sure if there are any potential problems with my codes. Can someone please check it out and see if there’s any problems? I would really appreciate it.

My codes expect a csv file at the site root’s /var/import/inventory.csv. The format of the file is ‘sku,qty,price’, without a header row. There’s only 1 website/store. FYI, I have a remote app to update the pricing, and the output of the codes are constructed in according to the remote app’s requirement—you can just ignore them.

<?
$my_user 
"... my sql user name (I actually put it in a separate php outside of the site root) ...";
$my_pass "... my sql pass (I actually put it in a separate php outside of the site root) ...";

$filename $_SERVER['DOCUMENT_ROOT''/var/import/inventory.csv';

if (!
file_exists($filename)) {
    
die("<error>Could not locate the inventory file.</error>");
}

$link 
mysql_connect("localhost"$my_user$my_pass)
or die(
"<error>Could not connect : " mysql_error() . "</error>");
echo 
"Connected successfully<br/>";
mysql_select_db("toysacom_magento") or die("<error>Could not select database</error>");


$load="
CREATE TEMPORARY TABLE TempInventory (
sku VARCHAR(64) NOT NULL, 
qty DECIMAL(12,4) NOT NULL DEFAULT 0.0000,
price DECIMAL(12,4) NOT NULL DEFAULT 0.0000
) ENGINE=MEMORY;
"
;

mysql_query($load);
if (!
mysql_error()) {
    $successMsg 
"";
    
    echo 
"table created successfully!<br/>";
    
    
$load="LOAD DATA LOCAL INFILE '" $filename "' INTO TABLE TempInventory FIELDS TERMINATED BY ',';";
    
mysql_query($load);
    if (!
mysql_error()) {
        
echo "data loaded successfully!<br/>";
        
        
$load="
            UPDATE cataloginventory_stock_item s, cataloginventory_stock_status ss, catalog_product_entity p, TempInventory t
            SET s.qty = t.qty, s.is_in_stock = IF(t.qty > 0, 1, 0), ss.qty = t.qty, ss.stock_status = IF(t.qty > 0, 1, 0)
            WHERE s.product_id = p.entity_id AND s.product_id = ss.product_id AND p.sku = t.sku; 
        "
;
        
mysql_query($load);
        if (!
mysql_error()) {
            $successMsg 
.= "QUANTITY = " mysql_info() . "\r\n";
            echo 
"Quantity Update: " mysql_info() . "<br/>";
        
}
        
else {
            
echo "<error>Data base error. MySQL:"mysql_error(), '</error><br/>';
        
}
        
        
        $load
="
            UPDATE catalog_product_entity_decimal e, catalog_product_entity p, TempInventory t
            SET e.value = t.price
            WHERE e.entity_type_id = 10 AND e.attribute_id = 99 AND e.entity_id = p.entity_id AND p.sku = t.sku AND t.price > 0.0000;
        "
;
        
mysql_query($load);
        if (!
mysql_error()) {
            $successMsg 
.= "PRICE = " mysql_info() . "\r\n";
            echo 
"Price Update: " mysql_info() . "<br/>";
        
}
        
else {
            
echo "<error>Data base error. MySQL:"mysql_error(), '</error><br/>';
        
}
        
    }
    
else {
        
echo "<error>Data base error. MySQL:"mysql_error(), '</error><br/>';
    
}
    
    $load
="DROP TABLE TempInventory;";
    
mysql_query($load);
    if (!
mysql_error()) {
        
echo "table dropped successfully!<br/>";
    
}
    
else {
        
echo "<error>Data base error. MySQL:"mysql_error(), '</error><br/>';
    
}
    
    $output 
shell_exec('php -f /home/toysacom/www/shell/indexer.php -- --reindex "catalog_product_price"');
    echo 
"<pre>$output</pre>";
    echo 
"<success>$successMsg$output</success>";
}
else {
    
echo "<error>Data base error. MySQL:"mysql_error(), '</error><br/>';
}


mysql_close
($link);
unlink($filename);
?>

I am seeing big improvement in terms of import speed. Updating ~13,000 products took less than a minute (down from a couple hours.)

One thing that I am particularly unsure is whether there’s a better way to trigger the indexer. I simply called the shell script. It works, though.

 
Magento Community Magento Community
Magento Community
Magento Community
 
Brent W Peterson
Moderator
 
Avatar
Total Posts:  3217
Joined:  2009-02-26
Minneapolis MN
 

The script looks great, wouldn’t it be easier to run a job directly through MySQL using a program like Data Loader Professional? It would be fast and less over head, You could run the indexer in your cron.

I am just curious because I do the same thing, just is a different way. My update (be it only 6800 products) takes about 35 seconds.

 
Magento Community Magento Community
Magento Community
Magento Community
 
pixelslave
Jr. Member
 
Avatar
Total Posts:  3
Joined:  2010-05-16
 

The script looks great, wouldn’t it be easier to run a job directly through MySQL using a program like Data Loader Professional? It would be fast and less over head, You could run the indexer in your cron.

I could very well do that, but there are existing programs on my end that handles synchronizing multiple storefronts, and writes out the inventory file. It\’s much easier for me to use that file to FTP to the server, then hit that php with one HTTP get.

 
Magento Community Magento Community
Magento Community
Magento Community
 
Brent W Peterson
Moderator
 
Avatar
Total Posts:  3217
Joined:  2009-02-26
Minneapolis MN
 

How are you securing your get?

 
Magento Community Magento Community
Magento Community
Magento Community
 
pixelslave
Jr. Member
 
Avatar
Total Posts:  3
Joined:  2010-05-16
 

I am only using a HTTPS + htaccess.

 
Magento Community Magento Community
Magento Community
Magento Community
 
junomedia
Sr. Member
 
Avatar
Total Posts:  142
Joined:  2008-09-10
Nottingham, England
 

A nice way of getting the DB connection:

// ---- Get DB connection info.
 if (file_exists('app/etc/local.xml')):
     
$xml simplexml_load_file('app/etc/local.xml');
    
    
$tblprefix $xml->global->resources->db->table_prefix;
    
$dbhost $xml->global->resources->default_setup->connection->host;
    
$dbuser $xml->global->resources->default_setup->connection->username;
    
$dbpass $xml->global->resources->default_setup->connection->password;
    
$dbname $xml->global->resources->default_setup->connection->dbname;
 else: 
     
// ---- Cant get the connection info.
    
exit('Failed to open app/etc/local.xml');
 endif;

You can then account for table prefixes etc…

Andy.

 
Magento Community Magento Community
Magento Community
Magento Community
 
Brent W Peterson
Moderator
 
Avatar
Total Posts:  3217
Joined:  2009-02-26
Minneapolis MN
 

Thanks Andy, that was very helpful! But wouldn’t you get the same thing by called the main Mage App, you would get all of the other functions by default.

 
Magento Community Magento Community
Magento Community
Magento Community
 
davew123
Jr. Member
 
Total Posts:  10
Joined:  2010-09-03
 

So I’ve set up various filenamehere.php files to help monitor cron jobs.
All located at the root level.

Trouble is I must have something configured wrong because I run into the “else” statement at the top of these files and then get an error that “Failed to open ../app/etc/local.xml”.

Have looked at this every which way.

Here’s the beginnings of the code:

/***********************
 * Scan Magento local.xml file for connection information
 ***********************/

if (file_exists('./app/etc/local.xml')) {

$xml 
simplexml_load_file('./app/etc/local.xml');

$tblprefix $xml->global->resources->db->table_prefix;
$dbhost $xml->global->resources->default_setup->connection->host;
$dbuser $xml->global->resources->default_setup->connection->username;
$dbpass $xml->global->resources->default_setup->connection->password;
$dbname $xml->global->resources->default_setup->connection->dbname;

$tables = array(
   
'dataflow_batch_export',
   
'dataflow_batch_import',
   
'log_customer',
   
'log_quote',
   
'log_summary',
   
'log_summary_type',
   
'log_url',
   
'log_url_info',
   
'log_visitor',
   
'log_visitor_info',
   
'log_visitor_online',
   
'report_event'
);



else {
    
exit('Failed to open ./app/etc/local.xml');
}
 
Magento Community Magento Community
Magento Community
Magento Community
 
davew123
Jr. Member
 
Total Posts:  10
Joined:  2010-09-03
 

So I’ve set up various filenamehere.php files to help monitor cron jobs.
All located at the root level.

Trouble is I must have something configured wrong because I run into the “else” statement at the top of these files and then get an error that “Failed to open ../app/etc/local.xml”.

Have looked at this every which way.

Here’s the beginnings of the code:

/***********************
 * Scan Magento local.xml file for connection information
 ***********************/

if (file_exists('./app/etc/local.xml')) {

$xml 
simplexml_load_file('./app/etc/local.xml');

$tblprefix $xml->global->resources->db->table_prefix;
$dbhost $xml->global->resources->default_setup->connection->host;
$dbuser $xml->global->resources->default_setup->connection->username;
$dbpass $xml->global->resources->default_setup->connection->password;
$dbname $xml->global->resources->default_setup->connection->dbname;

$tables = array(
   
'dataflow_batch_export',
   
'dataflow_batch_import',
   
'log_customer',
   
'log_quote',
   
'log_summary',
   
'log_summary_type',
   
'log_url',
   
'log_url_info',
   
'log_visitor',
   
'log_visitor_info',
   
'log_visitor_online',
   
'report_event'
);



else {
    
exit('Failed to open ./app/etc/local.xml');
}

... the php file continues.

 
Magento Community Magento Community
Magento Community
Magento Community
Magento Community
Magento Community
Back to top