Magento 1 SQL Queries
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.
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
);