Mysql

Sql query to search for a value in csv field

Posted on Updated on

Sql query to search for a value in csv field
SELECT * FROM `TABLE_NAME` WHERE `FIELD_NAME` REGEXP ‘(\,VALUE\,)|(^VALUE\,)|(\,VALUE$)’

Magento Version
$collection->addAttributeToFilter(‘FIELD_NAME’, array(‘regexp’=> ”(\,VALUE\,)|(^VALUE\,)|(\,VALUE$)’));

Example:

Table ‘test’
———————————
id |value                        |
1  |1,2,3,4,5,6                |
2  |2,4,11,12                  |
———————————-

Search for a row which has a value of 1 in the csv in value column

SQL Query
SELECT * FROM ‘test’ WHERE `value` REGEXP ‘(\,1\,)|(^1\,)|(\,1$)’

Log all Sql Queries in Magento

Posted on Updated on

Have you ever wondered what sql queries are executed when we open a new page in magento or when a product is added to cart or an order is placed?

Well, Magento has a very useful profiler tool that lets us view all the queries that are run during a page request.

One way to see all the queries in the browser itself when we run a code is :

Activate the Zend SQL Profiler with the following node in your app/etc/local.xml

<resources>
 <default_setup>
  <connection>
   <profiler>1</profiler>

Then you can access the profiler somewhere in your code and retrieve a lot of informations about all executed queries:

$profiler = Mage::getSingleton('core/resource')->getConnection('core_write')->getProfiler();

To simply output all queries:

echo("<pre>");
print_r($profiler->getQueryProfiles());
echo("</pre>");

This code will echo all the queries that are executed before this code is executed. So you might wanna place this code at the end of index.php file at the root for quick viewing. but this may break the ajax calls. So beware and use it as you feel right.

We can also log all the queries in a text file.
For that we can follow the following process.


In Varien_Db_Adapter_Pdo_Mysql

/lib/varien/Db/Adapter/Pdo/Mysql.php

set

protected $_debug               = true;

protected $_logAllQueries       = true;

and

protected $_debugFile           = ‘var/debug/pdo_mysql.log; // this is the file where the logs are stored. change the filename to your need. Also give read / write permission to the file

And its done. You can now view all the sql queries run. Beware Magento run loads and loads of query during a request. So you might have to forge your way through the log file to find the query you want 😀

Thank you for reading.
Hope this was helpful to you.
Cheers!!