9

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?

17

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.

  • Thanks @Kay do you know what the consequences would be of me querying the db outside of Magento? – Will Wright Mar 24 '15 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 – Kay Int Veen Mar 24 '15 at 16:39
  • Where to find all of these queries ? – partho Jun 27 '17 at 7:22
  • 1
    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. – bassplayer7 Feb 12 at 20:44
15

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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