MAGENTO 1.X – MAKE BASIC AND DIRECT SQL QUERIES

Select from table

$resource = Mage::getSingleton('core/resource');

$readConnection = $resource->getConnection('core_read');

$query = "select * from table where field = '$fieldValue'";

$results1 = $readConnection->fetchAll($query); // Find all ocurrences - Returns a collection

$results2 = $readConnection->fetch($query); // Find first ocurrence - Returns an array

if(!empty($results1)){

echo "We have found ". count($results1). " values";

}else{

echo "We have not found any value;

}

Select from table (with left join)

$sql = "SELECT c.field1 AS field1, u.field2 AS field2

FROM table1 AS t1

LEFT JOIN table2 AS t2 ON (t1.id = t2.id)

LEFT JOIN table3 AS t3 ON (t2.id = t3.id)

$db = Mage::getModel('core/resource')->getConnection('core_read');

$result = $db->fetchAll($sql);

foreach($result as $row) {

echo $row['field1']." ".$row['field2'];

}

Insert

$write = Mage::getSingleton("core/resource")->getConnection("core_write");

$query = "INSERT INTO table_name (field1, field2, field3) values ('$field1Value', '".field2Value."', '".field3Value."')";

$write->query($query);

Update

$write = Mage::getSingleton("core/resource")->getConnection("core_write");

$query = "UPDATE table_name SET field_1 = 'field_1_value', field_2 = 'field_2_value' WHERE field_3 = 'field_3_value'";

$write->query($query);

Note: if you need to import any value with quotes ( eg: Children’s ), you will need to escape the field value. To do so, you can use:

1	$result = addslashes($fieldWithQuote);