Selecting 30 days or older entries using MySQL DateTime field

I’ve been doing quite a bit of Date and Time manipulations lately, and have grown to enjoy MySQL’s DateTime fields just as much as UNIX timestamps. Particularly its built-in functions for selecting any range of fields based on a time frame.

Needing to remove all entries from a table that are older than 30 days, at first I thought I would need to iterate over the very large table (90+k) rows of data and convert each MySQL DateTime field to a UNIX time stamp to do the calculations myself with PHP. However after a bit of research I’ve learned of a few things with MySQL DateTime that make this a lot easier.

SELECT fields FROM table WHERE DATE_SUB(CURDATE(),INTERVAL 30 DAY) >= timestamp;

The “timestamp” field is the MySQL DateTime field. Using the DATE_SUB, CURDATE and INTERVAL one can easily grab all records that are 30 days old without the headache of converting to UNIX timestamps in PHP and doing the grunt work yourself.

idea Also as a side note, if you wish to convert a MySQL DateTime field to a UNIX timestamp you can easily do this by:

$mysql_timestamp = gmdate("Y-m-d H:i:s",$mysql_timestamp_value);

If anyone has any better more efficient means to accomplish this please feel free to leave a comment. :D

Magento SEO

For those that are subscribed to the Magento RSS news feed, this may not be news to you, but for those that aren’t here is a good listing of SEO best practices for Magento. My only suggestion to Yoast was to add in Google Site map generation to their list which was added, along with a bit more about how to setup the Google site map’s thats built-in to Magento automagically generate daily or such with a cron job.

Here is the full list and I’d highly recommend alot of the tips and tricks mentioned.

arrow Magento SEO – Yoast – Tweaking Websites.

A couple other sites to perhaps check out:
arrow http://www.seothegame.com/
arrow http://www.johnon.com/

Exporting users/customers from X-Cart to Magento

title-images-import2

Following my previous posting of importing basic product data into Magento from X-Cart I stumbled across this code on the Magento Community forums with a nifty PHP script to prepare a CSV file of users to be imported into Magento.

:!: UPDATE: Finally got around to trying to utilize this script, and realized there are in fact some issues with using it. Some of the main items I noticed is:

  • It is not 3.5.x X-Cart compatible, and possibly lower versions.
  • As well as it was not very optimized for a very large number of customers, causing memory limit errors while attempting to export.

I have updated the code to handle the two items mentioned above and have successfully imported almost 6k customers from a 3.5.x version of X-Cart. Here is the updated script, the same instructions apply, copy the file into your admin directory of X-Cart and then proceed to login to the admin of X-Cart, after successfully logging in, simply change index.php in your url to migrate.php and wait for the script to prompt for you to download a .csv file of the users. Be patient especially if you have a large number of customers as the script can take awhile to generate this data without any prompts that it is working.

idea When using this code be sure and note the following comments as, depending on your X-Cart version. Anonymous customers are ignored.

Create the file $xcart_dir/admin/migrate.php and copy the following code into it:

<?php
/*
Migrate X-Cart customers to Magento
Original by Spydor at: http://www.magentocommerce.com/boards/viewthread/30894/
Modified by B00MER at: http://www.molotovbliss.com 3/15/2009 3:49:23 AM
Modifications include:
X-Cart 3.5 compatibility, possibly lower versions as well.
Optimization for large number of records to avoid memory limit errors
*/
@set_time_limit(2700);
# Include core functions
if (!require("../admin/auth.php"))
	require("./auth.php");
function resolveState($b_state,$b_country,$s_state,$s_country){
	global $states;
	$result = array();
	foreach($states as $key=>$value){
		// Billing
		if(($value['state_code']==$b_state) && ($value['country_code']==$b_country))
			 $result['billing'] = $value['state'];
		// Shipping
		if(($value['state_code']==$s_state) && ($value['country_code']==$s_country))
			 $result['shipping'] = $value['state'];
	}
	if(empty($result['billing']))
		$result['billing'] = $b_state;
	if(empty($result['shipping']))
		$result['shipping'] = $s_state;
	return $result;
}
function func_export_csv($data,$title) {
	$output = $data;
	$size_in_bytes = strlen($output);
	   header("Content-type: application/vnd.ms-excel");
	   header("Content-disposition: csv; filename=".$title . '_' . date("Y-m-d") . ".csv; size=$size_in_bytes");
	   return $output;
}
# Define new line
$newline = "\n";
# Define CSV fields
$output =  '"website","email","group_id","prefix","firstname","middlename","lastname","suffix","password_hash","taxvat","billing_prefix","billing_firstname","billing_middlename","billing_lastname","billing_suffix","billing_street_full","billing_city","billing_region","billing_country","billing_postcode","billing_telephone","billing_company","billing_fax","shipping_prefix","shipping_firstname","shipping_middlename","shipping_lastname","shipping_suffix","shipping_street_full","shipping_city","shipping_region","shipping_country","shipping_postcode","shipping_telephone","shipping_company","shipping_fax","created_in","is_subscribed"';
$output .= "$newline";
$states = func_query("SELECT $sql_tbl[states] .state, $sql_tbl[states] .code AS state_code, $sql_tbl[states] .country_code FROM $sql_tbl[states], $sql_tbl[countries] WHERE $sql_tbl[states] .country_code=$sql_tbl[countries] .code AND $sql_tbl[countries] .active='Y'");
# total customers
$sql = "SELECT COUNT(*) AS total_results FROM $sql_tbl[customers] where login NOT LIKE 'anonymous%'";
$results = func_query($sql);
$total_results=$results[0]["total_results"];
# number of records to return each loop
$num_of_records=150;
# start for loop to feed out data
for($i=0; $i<=$total_results; $i+=$num_of_records) {
  #$q = "SELECT * FROM xcart_customers where login NOT LIKE 'anonymous%' "; // Memory Hog with SELECT *
  $q = "SELECT email,title,firstname,lastname,password,title,b_address,b_city,b_country,b_zipcode,phone,company,fax,s_address,s_city,s_country,s_zipcode FROM xcart_customers where login NOT LIKE 'anonymous%' LIMIT $i,$num_of_records ";
  $result = func_query($q);
  foreach($result as $key=>$value){
       # Uncomment for version 4.0+ of X-Cart
  	   $result[$key]['password_hash'] = md5(text_decrypt($value['password']));
       # for version 3.5 and below of X-Cart
  	   #$result[$key]['password_hash'] = text_decrypt($value['password']);
  	   $realstates = resolveState($value['b_state'], $value['b_country'], $value['s_state'], $value['s_country']);
  	   $result[$key]['b_real_state'] = $realstates['billing'];
  	   $result[$key]['s_real_state'] = $realstates['shipping'];
  }
  foreach($result as $key => $value) {
    #func_print_r($result);exit;
  	$output .= '"base",';
  	$output .= '"' . $value['email'] . '",';
  	$output .= '"General",';
  	$output .= '"' . $value['title'] . '",';
  	$output .= '"' . $value['firstname'] . '",';
  	$output .= '"",';
  	$output .= '"' . $value['lastname'] . '",';
  	$output .= '"",';
  	$output .= '"' . $value['password_hash'] . '",';
  	$output .= '"",';
  	# Billing info
  	$output .= '"' . $value['title'] . '",';
  	$output .= '"' . $value['firstname'] . '",';
  	$output .= '"",';
  	$output .= '"' . $value['lastname'] . '",';
  	$output .= '"",';
  	$output .= '"' . $value['b_address'] . '",';
  	$output .= '"' . $value['b_city'] . '",';
  	$output .= '"' . $value['b_real_state'] . '",';
  	$output .= '"' . $value['b_country'] . '",';
  	$output .= '"' . $value['b_zipcode'] . '",';
  	$output .= '"' . $value['phone'] . '",';
  	$output .= '"' . $value['company'] . '",';
  	$output .= '"' . $value['fax'] . '",';
  	# Shipping Info
  	$output .= '"' . $value['title'] . '",';
  	$output .= '"' . $value['firstname'] . '",';
  	$output .= '"",';
  	$output .= '"' . $value['lastname'] . '",';
  	$output .= '"",';
  	$output .= '"' . $value['s_address'] . '",';
  	$output .= '"' . $value['s_city'] . '",';
  	$output .= '"' . $value['s_real_state'] . '",';
  	$output .= '"' . $value['s_country'] . '",';
  	$output .= '"' . $value['s_zipcode'] . '",';
  	$output .= '"' . $value['phone'] . '",';
  	$output .= '"' . $value['company'] . '",';
  	$output .= '"' . $value['fax'] . '",';
  	$output .= '"default",';
  	$output .= '"0"';
  	$output .= "$newline";
  }
}
#func_print_r($output); // uncomment to see output
print func_export_csv($output,"xcart_customers");
?>

Here is the original code by Spydor:

<?php
require "../auth.php";
$states = func_query("SELECT $sql_tbl[states] .state, $sql_tbl[states] .code AS state_code, $sql_tbl[states] .country_code FROM $sql_tbl[states], $sql_tbl[countries] WHERE $sql_tbl[states] .country_code=$sql_tbl[countries] .code AND $sql_tbl[countries] .active='Y'");
#print_r($states);
$q = "SELECT * FROM xcart_customers where login NOT LIKE 'anonymous%' ";
$result = func_query($q);
foreach($result as $key=>$value){
       $result[$key]['password_hash'] = md5(text_decrypt($value['password']));
       $realstates = resolveState($value['b_state'], $value['b_country'], $value['s_state'], $value['s_country']);
       $result[$key]['b_real_state'] = $realstates['billing'];
       $result[$key]['s_real_state'] = $realstates['shipping'];
}
#print_r($result);
$output =  '"website","email","group_id","prefix","firstname","middlename","lastname","suffix","password_hash","taxvat","billing_prefix","billing_firstname","billing_middlename","billing_lastname","billing_suffix","billing_street_full","billing_city","billing_region","billing_country","billing_postcode","billing_telephone","billing_company","billing_fax","shipping_prefix","shipping_firstname","shipping_middlename","shipping_lastname","shipping_suffix","shipping_street_full","shipping_city","shipping_region","shipping_country","shipping_postcode","shipping_telephone","shipping_company","shipping_fax","created_in","is_subscribed"';
$output .= "1512";
foreach($result as $key => $value){
    $output .= '"base",';
    $output .= '"' . $value['email'] . '",';
    $output .= '"General",';
    $output .= '"' . $value['title'] . '",';
    $output .= '"' . $value['firstname'] . '",';
    $output .= '"",';
    $output .= '"' . $value['lastname'] . '",';
    $output .= '"",';
    $output .= '"' . $value['password_hash'] . '",';
    $output .= '"",';
    # Billing info
    $output .= '"' . $value['title'] . '",';
    $output .= '"' . $value['firstname'] . '",';
    $output .= '"",';
    $output .= '"' . $value['lastname'] . '",';
    $output .= '"",';
    $output .= '"' . $value['b_address'] . '",';
    $output .= '"' . $value['b_city'] . '",';
    $output .= '"' . $value['b_real_state'] . '",';
    $output .= '"' . $value['b_country'] . '",';
    $output .= '"' . $value['b_zipcode'] . '",';
    $output .= '"' . $value['phone'] . '",';
    $output .= '"' . $value['company'] . '",';
    $output .= '"' . $value['fax'] . '",';
    # Shipping Info
    $output .= '"' . $value['title'] . '",';
    $output .= '"' . $value['firstname'] . '",';
    $output .= '"",';
    $output .= '"' . $value['lastname'] . '",';
    $output .= '"",';
    $output .= '"' . $value['s_address'] . '",';
    $output .= '"' . $value['s_city'] . '",';
    $output .= '"' . $value['s_real_state'] . '",';
    $output .= '"' . $value['s_country'] . '",';
    $output .= '"' . $value['s_zipcode'] . '",';
    $output .= '"' . $value['phone'] . '",';
    $output .= '"' . $value['company'] . '",';
    $output .= '"' . $value['fax'] . '",';
    $output .= '"default",';
    $output .= '"0"';
    $output .= "1512";
}
print func_export_csv($output,"xcart_customers") ;
function resolveState($b_state,$b_country,$s_state,$s_country){
    global $states;
    $result = array();
    foreach($states as $key=>$value){
        // Billing
        if(($value['state_code']==$b_state) && ($value['country_code']==$b_country))
             $result['billing'] = $value['state'];
        // Shipping
        if(($value['state_code']==$s_state) && ($value['country_code']==$s_country))
             $result['shipping'] = $value['state'];
    }
    if(empty($result['billing']))
        $result['billing'] = $b_state;
    if(empty($result['shipping']))
        $result['shipping'] = $s_state;
    return $result;
}
function func_export_csv($data,$title) {
    $output = $data;
    $size_in_bytes = strlen($output);
       header("Content-type: application/vnd.ms-excel");
       header("Content-disposition: csv; filename=".$title . '_' . date("Y-m-d") . ".csv; size=$size_in_bytes");
       return $output;
}

Here is the original posting by Spydor:
Magento – Migrating users from Xcart to Magento – General Forum – eCommerce Software for Growth.

Magento Commerce Tips

Magento Commerce Logo

Since I’ve started customizing MagentoCommerce about a year ago, here’s a few tricks I’ve learned to help further development and general operations.  I don’t claim to be a Magento Expert by any means but some of this information I had to dig long and hard before finally finding a resolution to the issues I was having.
  1. Can’t find where to edit email templates that are generated by Magento? There is a great article posted up on Magentos Wikipedia on how to Edit the E-mail templates.
  2. Wouldn’t it be nice if you could actually display what template files are making up the current Magento page you are viewing? Well you can!  Under the Admin area -> System -> Configuration.  Select the “Current Configuration Scope:” to the left.  <- This is an important step to get to this feature.  After you have selected the Store of your choice proceed to the “Developer” Subsection to the left you’ll notice a “Template Path Hints” field with a drop down to enable/disable it.  Enabling this will cause Magento to display what .phtml or template files make up any particular page.  This really helps speed up time when it comes to customizing Magento’s theme or adding functionality.
  3. When using MagentoConnect ALWAYS do a full site backup, including all files and database dumps, I know the docs mention this quite a bit but I have completely toasted Magento before by simply trying to update Magento to its latest release.  The biggest issue I have seen with using MagentoConnect is sometimes files and/or directories are not writable by the web server.  The best approach I have found for this is to download the latest version via Magento’s site and copying the files manually.  Particularly /app/code/core/Mage/
  4. Innon-db-fix.sql can be your best friend.  From some reason, I’m not sure why but Magento will have issues with tables in the database not being the proper type, Normally MyISAM instead of InnonDB.  Using this simple SQL file can help fix “some” issues that may not seem related to it.  I’m still hunting for a definitive list of what tables should be which, but it appears the majority are InnonDB.
  5. One error that plagued me for days after an upgrade was “SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails”, The innon-db-fix.sql did NOT fix this issue for me.  After reading over Magento’s community forums there was one posting in regards to residual products and or categories, ghosts basically.  Any attempt to save new products or categories yielded this error, Thankfully this was on a development site and I was able to simple clear out the table: “catalog_category_product_index“, which resolved the issue and I re-imported the products I had entered in before this error appeared.

Hopefully this has helped someone if so, leave a comment.