1

Hoping a seasoned Magento guy can help out here.

So for any new orders all of a sudden the table: sales_flat_order_payment and column: method is not being updated for ONLY the last 2 orders. It was working perfectly fine and then over the weekend something has gone a miss. We are using v1.9.2.2 with no changes having been done over. It was working perfectly fine with a custom file which we use to call the following:

("SELECT `method` FROM `sales_flat_order` LEFT JOIN `sales_flat_order_payment` ON `sales_flat_order`.`entity_id` = `sales_flat_order_payment`.`entity_id` WHERE `sales_flat_order`.`increment_id` = '" . $row['increment_id'] . "'");

Was working fine. Values were pulled in to our custom script with no isses. I can also manually go to phpmyadmin view: sales_flat_order_payment and column: method which does have the payment method used inserted. HOWEVER on doing a query:

SELECT  `method` , increment_id, remote_ip, updated_at, customer_email
FROM  `sales_flat_order` 
LEFT JOIN  `sales_flat_order_payment` ON  `sales_flat_order`.`entity_id` =  `sales_flat_order_payment`.`entity_id` 
WHERE sales_flat_order.updated_at >  '2016-02-23'
ORDER BY updated_at DESC

returns the last 2 orders on that sales_flat_order_payment and column: method as being NULL

Improve this question

1 Answer 1

Reset to default
3

Your problem is right here in the query:

 `sales_flat_order`.`entity_id` =  `sales_flat_order_payment`.`entity_id`

order.entity_id is not the same as order_payment.entity_id. They may have matched up in the past, but there is no guarantee that will always be the case. They are separate identifiers. You should link the tables on order_payment.parent_id instead, like so:

 `sales_flat_order`.`entity_id` =  `sales_flat_order_payment`.`parent_id`

Your query is returning NULL for some columns because it is not finding any record in sales_flat_order_payment to join at all.

Improve this answer
5
  • so i should be using this instead: "SELECT method FROM sales_flat_order LEFT JOIN sales_flat_order_payment ON sales_flat_order.entity_id = sales_flat_order_payment.parent_id WHERE sales_flat_order.increment_id = '" . $row['increment_id'] . "'");
    – Umbrella
    Commented Feb 24, 2016 at 14:47
  • You shouldn't be using direct queries at all, use Magento's models and collections... but yes, that should fix your problem. Better way: stackoverflow.com/a/26349245/2336164 giving you $order and $order->getPayment(). method would be $order->getPayment()->getMethod().
    – Ryan Hoerr
    Commented Feb 24, 2016 at 14:52
  • Thanks Ryan, yup seems to have fixed it. Its already an existing code that I'm having to work with that pushes data externally so having to live with it for the moment until it can be scrapped. What confused me was that its been working very well for quite a while and then all of a sudden its stopped working. Do you perhaps know if a sql upgrade on the server to a newer version would stop it from working? or is it normal that the column method called by the query in my post above would show the first 2 orders listed there with NULL values? and thus was just when not if it would fail!
    – Umbrella
    Commented Feb 24, 2016 at 15:05
  • Most likely some order failed during processing, in such a way that it reserved an entity_id from the order table but not from the order_payment table. If you look at sales_flat_order, you'll probably find a spot where a couple entity_ids get skipped.
    – Ryan Hoerr
    Commented Feb 24, 2016 at 15:18
  • yup, i can see quite a few entity_ids missing! most likely cause when the server fell over on the weekend ;( thanks for your input Ryan. FAST and EFFICIENT answer to something i was looking to hard at. fresh pair of eyes always helps :)
    – Umbrella
    Commented Feb 24, 2016 at 15:26

Your Answer

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

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