Magento Direct SQL Queries

Queries for fetching in an array


    /**
     * Get the resource model
     */
    $resource = Mage::getSingleton('core/resource');
     
    /**
     * Retrieve the read connection
     */
    $readConnection = $resource->getConnection('core_read');
     
    $query = 'SELECT * FROM ' . $resource->getTableName('catalog/product');
     
    /**
     * Execute the query and store the results in $results
     */
    $results = $readConnection->fetchAll($query);
     
    /**
     * Print out the results
     */
     var_dump($results);

Fetching one query

Therefore use the method fetchCol() instead of fetchAll, so like:

$sku = $readConnection->fetchCol('SELECT sku FROM ' . $table . ');

Read more at FishPig.

Queries for wrtiting

$resource = Mage::getSingleton('core/resource');
	$writeConnection = $resource->getConnection('core_write');
	
	$query = "INSERT INTO abc_hit SET
			  visitor_id = :visitor_id,
			  hit_date = :hit_date,
			  ";
	$binds = array(
    'visitor_id' => $_SESSION['visitor_id'],
    'hit_date' => date("Y-m-d H:i:s"),
	);
	
	$writeConnection->query($query, $binds);

Get Last Inserted ID

$write = Mage::getSingleton('core/resource')->getConnection('core_write');
$sql = "INSERT INTO .......";
$write->query($sql);
$lastInsertId = $write->lastInsertId();

Thanks to sbditto85.



Magento Product Flat Data – reindex error

When you get the error:

Product Flat Data index process unknown error:
exception 'PDOException' with message 'SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (`sprayfoa_store`.<result 2 when explaining filename '#sql-66c_ab044'>, CONSTRAINT `FK_CAT_PRD_FLAT_1_ENTT_ID_CAT_PRD_ENTT_ENTT_ID` FOREIGN KEY (`entity_id`) REFERENCES `catalog_product_entity`)' in ...
Stack trace:

Try to truncate all the category_flat_product_x tables in phpmyadmin and then reindex again.



Magento A/B testing or Multivariate testing – Php script

Split test A/B PHP Script

Magento is quite limited in tools like conversion optimalisation like A/B testing or multivariate testing. That is why I created a very simple php script that you can put directly into a phtml file. It’s a bit hacky, that it’s not via the core, but it works though and is fast. This script has its limitation, see the note on the bottom.

If you want to variate the text of a button on the product page and go to a file like /app/design/frontend/default/default/catalog/product/view.phtml and insert this into the header (in the php-code):

srand((double)microtime()*1000000);

$var = array();
$var[1]['name'] = 'direct';
$var[1]['value'] = 'Buy direct';
$var[2]['name'] = 'purchase';
$var[2]['value'] = 'Purchase product';
$var[3]['name'] = 'invest';
$var[3]['value'] = 'Invest in product';

$choice = cookieCheck($var);

function cookieCheck($var)
{
	$cookie = Mage::getSingleton('core/cookie');
	$cookievalue = $cookie->get('variation_test');
	if (isset($cookievalue) && ($cookievalue > 0))
	{
		$choice = $cookievalue;
	}
	else
	{
		$choice = rand(1,count($var));
		$cookie->set('variation_test', $choice ,time()+30*86400,'/');
	}
	return $choice;
}

Then create a piece of text like a link or a button. That is where the variation takes place:

<a class="varbutton" href="&lt;? echo $this-&gt;getUrl('bedankt'); ?&gt;">&lt;? echo $var[$choice]['value']; ?&gt;</a>

If you want to track the variable in Google Analytics, you can edit the template file /app/design/frontend/default/default/googleanalytics/ga.phtml , so it gets like:

 srand((double)microtime()*1000000);

$var = array();
$var[1]['name'] = 'direct';
$var[1]['value'] = 'Buy direct';
$var[2]['name'] = 'purchase';
$var[2]['value'] = 'Purchase product';
$var[3]['name'] = 'invest';
$var[3]['value'] = 'Invest in product';

$choice = cookieCheck();

function cookieCheck()
{
	$cookie = Mage::getSingleton('core/cookie');
	$cookievalue = $cookie->get('variation_test');
	if (isset($cookievalue) && ($cookievalue > 0))
	{
		$choice = $cookievalue;
	}
	else
	{
		$choice = rand(1,count($var));
		$cookie->set('variation_test', $choice ,time()+30*86400,'/');
	}
	return $choice;
}
 
 
?>
<?php if (!Mage::helper('core/cookie')->isUserNotAllowSaveCookie()): ?>
<?php $accountId = Mage::getStoreConfig(Mage_GoogleAnalytics_Helper_Data::XML_PATH_ACCOUNT) ?>
<!-- BEGIN GOOGLE ANALYTICS CODEs -->
<script type="text/javascript">
//<![CDATA[
    var _gaq = _gaq || [];
    _gaq.push(["_setCustomVar", 1, "variation_test", "<?php echo $choice; ?>"]);
    <?php echo $this->_getPageTrackingCode($accountId) ?>
    <?php echo $this->_getOrdersTrackingCode() ?>

    (function() {
        var ga = document.createElement('script'); ga.type = 'text/javascript'; ga.async = true;
        ga.src = ('https:' == document.location.protocol ? 'https://ssl' : 'http://www') + '.google-analytics.com/ga.js';
        var s = document.getElementsByTagName('script')[0]; s.parentNode.insertBefore(ga, s);
    })();

//]]>
</script>
<!-- END GOOGLE ANALYTICS CODE -->
<?php endif; ?>

Note: This script could not work properly if you’re using Full Page Cache. Also it could not work with the page block html cache. So, work in progress …



Lesti::Fpc and layered navigation (Vinagento and Amasty)

How to let Lesti::FPC Full page caching work with Vinagento Layered Navigation in Ajax mode?

I just tried some random settings and this seemed to work:

In System->Configuration->Sytem->Lesti FPC add the following to Session Params:
catalogsearch_advanced_result_ajax,
catalog_category_ajax_view,
catalog_category_layered_ajax,
catalogsearch_result_ajax

In System->Configuration->Sytem->Lesti FPC add the following to Uri Params:
id,
category,
page_id,
p,
limit,
dir,
order,
mode,
price,
cat,
manufacturer,
no_cache,
color,
ajax,
your_attribute_code1,
your_attribute_code2,

How to let Lesti::FPC Full page caching work with Amasty Layered Navigation in Ajax mode?

The parameters for Amasty will follow shortly.