14

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?

2 Answers 2

27

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.

4
  • Thanks @Kay do you know what the consequences would be of me querying the db outside of Magento? Mar 24, 2015 at 16:38
  • 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 Mar 24, 2015 at 16:39
  • Where to find all of these queries ?
    – partho
    Jun 27, 2017 at 7:22
  • 3
    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. Feb 12, 2019 at 20:44
28

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
);
2
  • 2
    Nice. Taught me database query logic in Magento.
    – Anse
    Jul 4, 2017 at 14:41
  • 1
    Wow, I wish I knew this was possible when I started working on Magento years back. Great explanation! Jul 12, 2019 at 16:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.