I am trying to perform an SQL query kind of thing where I need to check if my database table contains a particular record or not. If it does, do nothing else add the record which isn't present.
My table sample contains fields like sample_id (auto-incremented), order_id, order_email_id, review_request, coupon_sent . It has got 4 records having order_ids (71, 74, 126, 165)
To obtain $ids, I have this query,
$to_date = date('Y-m-d H:i:s',strtotime('-3 days'));
$orders = Mage::getModel('sales/order')->getCollection()->addFieldToFilter('status', 'complete')->addFieldToFilter('updated_at',array('to' => $to_date ))->addAttributeToSelect('customer_email')->addAttributeToSelect('entity_id');
foreach($orders as $order)
{
$email = $order->getCustomerEmail();
$id = $order->getEntityId();
$sample = Mage::getModel('sample/sample')->getCollection()->addFieldToFilter('order_id', $id)->addFieldToSelect('order_id');
if($sample != null)
{echo "Record already exists";}
else
{
echo "Insert this record";
$sample->setOrderId($id);
}
}
My $id contains (71, 74, 126, 165, 166, 167)
So ideally the below query should check against existing records (71, 74, 126, 165) and insert new ones (166, 167) . I echoed the query and it returned right but it still echoed 'Record already exists' for ids 166 and 167 which ideally it shouldn't
Is there any other way to check the value of an existing field in the table in Magento?
Any help is appreciated. Thanks in advance
Connect magento database :
ReplyDelete$conn = Mage::getSingleton('core/resource')->getConnection('core_read');
// perform sql queries
$result = $conn->fetchAll("SELECT * FROM sample;");
foreach($result as $rows) {
echo $rows['order_ids'] . "\n";
}
So, you can use a simple SQL expression with this way.
It can be done in a few steps using collection filtering.
ReplyDelete$wantedIds = $orders->getAllIds();
$sample = Mage::getModel('sample/sample')->getCollection()
->addFieldToFilter('order_id', array('in' => $wantedIds)
->addFieldToSelect('order_id');
$presentIds = $sample->getColumnValues('order_id');
$newIds = array_diff($wantedIds, $presentIds);
The final step gives you the IDs that still need to be created. It is easy to use them since they are an array.
foreach ($newIds as $id) {
Mage::getModel('sample/sample')
->setOrderId($id)
->save();
}