Disappearing orders - possible solution!

[Chris Dunning]

A client called me the other day and said that when he looked in his admin screen, he saw that he had 7 pending orders. However, when he clicked the link there were only 5. Why is that?

 

I searched the forums and found many people (including one very irate and annoying guy blaming the developers for his stupidity in breaking his store) with the same problem. Here's a quick summary of things to check, including my solution (which I haven't seen mentioned elsewhere).

 

First thing - are you using PayPal? That's the root cause of many people's problems, it seems. Try using one of the IPN contributions.

 

If that's not it, open up the orders.php file and find the sql query. Replace the variables with what they should be (ie, "TABLE_ORDERS" becomes simply "orders" in a stock store) then play with it until you find what's causing the problem. Here's a sample of the SQL query with the variables replaced with values:

 

select o.orders_id, o.customers_name, o.payment_method, o.date_purchased, o.last_modified, o.currency, o.currency_value, s.orders_status_name, ot.text as order_total from orders o left join orders_total ot on (o.orders_id = ot.orders_id), orders_status s where o.orders_status = s.orders_status_id and s.language_id = '1' and s.orders_status_id = '1' and ot.class = 'ot_total' order by o.orders_id DESC

 

Another thread here somewhere suggested taking out the "and ot.class=...." statement, so that was the first thing I tried. I hope you're reading this, because I think this is your solution! I found that when I took out that part of of the query, phpMyAdmin returned all of the pending orders just like it should. Since the order totals modules were obviously causing the problem, I checked them in the admin - two were set to the same sort order. I can't tell you exactly what this does except that it screws things up (I hope that's technical enough). Once I set all order total modules to different sort orders, all new orders showed up just fine. This doesn't fix the orders that are already missing - but any new orders show up fine. All of the other orders were still in the database, so it's easy enough to just edit the URL to pull up information on those orders.

 

So anyway...this was my solution to the problem. Your results may vary.