Magento 1 SQL Queries

Question

I am in the process of setting up some reporting functionality for my company, Im gathering data from the likes of Google, Moz and our Courier. As part of the reporting I also want to get data from Magento. As this will be hosted in a very secure folder on our server. What I would like to know is what is the safest way for me to run queries on the Magento Data?

I could run

• Direct SQL queries outside of Magento

• SQL queries inside Magento but then would have issues getting them out of Magento automatically

• Magento API

Which am I best doing from a safety and performance point of view for my website?

Answer 1

Yes you can run direct sql queries within Magento, the best way to do this is to use the read write resource. You can insatiate it with:

 $resource = Mage::getSingleton('core/resource');
 $readConnection = $resource->getConnection('core_read');
 $writeConnection = $resource->getConnection('core_write');

To run a select you can do something like this:

 $readConnection = $resource->getConnection('core_read');
 $query = 'SELECT * FROM ' . $resource->getTableName('catalog/product');
 $results = $readConnection->fetchAll($query);
 /* get the results */
 var_dump($results);

To write something to the database use:

 $resource = Mage::getSingleton('core/resource');
 $writeConnection = $resource->getConnection('core_write');
 $table = $resource->getTableName('catalog/product');
 $query = "UPDATE {$table} SET {item} = '{value}' WHERE entity_id = 'value'";
 $writeConnection->query($query);

Hope this helps out for you.

• Will Wright: Thanks @Kay do you know what the consequences would be of me querying the db outside of Magento?
• Kay Int Veen: Not that much, its just not best practice. and you can risk inconsistencies but normally no problem. but now you keep it all tight in one workflow
• partho: Where to find all of these queries ?
• bassplayer7: Please keep in mind that writing to the database in this way introduces a SQL injection vulnerability. Only do this if you are sure that your values are safe.

Answer 2

There is a more proper way to do this to avoid SQL Injections.

$resource = Mage::getSingleton('core/resource');
$write = $resource->getConnection('core_write');
$table = $resource->getTableName('your/model');

You can Create:

$write->insert(
 $table, 
 ['column_1' => 1, 'column_2' => 2]
);

Read:

$select = $write->select()
 ->from(['tbl' => $table], ['entity_id', 'company'])
 ->join(['tbl2' => $table2], 'tbl.entity_id = tbl2.product_id', ['stuff'])
 ->where('name LIKE ?', "%{$name}%")
 ->group('company');
$results = $write->fetchAll($select);

Update:

$write->update(
 $table,
 ['column_1' => 3, 'column_2' => 4],
 ['entity_id = ?' => 123]
);

Delete:

$write->delete(
 $table,
 ['entity_id IN (?)' => [123, 456]]
);

Insert Multiple:

$rows = [
 ['col_1'=>'value1', 'col_2'=>'value2', 'col_3'=>'value3'],
 ['col_1'=>'value3', 'col_2'=>'value4', 'col_3'=>'value5'],
];
$write->insertMultiple($table, $rows);

Insert Update On Duplicate:

$data = [];
$data[] = [
 'sku' => $sku,
 'name' => $name
];
$write->insertOnDuplicate(
 $table,
 $data, // Could also be an array of rows like insertMultiple
 ['name'] // this is the fields that will be updated in case of duplication
);

• Anse: Nice. Taught me database query logic in Magento.
• Eric Seastrand: Wow, I wish I knew this was possible when I started working on Magento years back. Great explanation!