Need to get all orders in a date range by sql

Hi

From phpadmin i try to get all orders (only some columns) from the table orders.

I need to get a list for march 2013

This is what i have tried (this is just an example i tried for 2011):

SELECT 'orders_id',`customers_name`,`order_total`,`payment_method` FROM 'orders' WHERE 'date_purchased' BETWEEN '2011-02-01' and '2011-02-01';

But i get en error.

Can anyone show me the code i have to use?

Thanks

Quote:

---

Originally Posted by navido

Hi

From phpadmin i try to get all orders (only some columns) from the table orders.

I need to get a list for march 2013

This is what i have tried (this is just an example i tried for 2011):

SELECT 'orders_id',`customers_name`,`order_total`,`payment_method` FROM 'orders' WHERE 'date_purchased' BETWEEN '2011-02-01' and '2011-02-01';

But i get en error.

Can anyone show me the code i have to use?

Thanks

---

Well it would be helpful to know what the error is......

your dates not dates but text from the why I see it....look at this example.

Code:

---

SELECT *
FROM orders
WHERE order_date >= to_date('2003/01/01', 'yyyy/mm/dd')
AND order_date <= to_date('2003/12/31','yyyy/mm/dd');

---

try this:

Code:

---

SELECT 'orders_id',`customers_name`,`order_total`,`payment_method`, `date_purchased` FROM orders WHERE date_purchased BETWEEN "2013-03-01" AND "2013-03-30"

---

ps - looks like you just had regular single-quotes (') around the table name instead of back-quotes (`) which was throwing the error

Thanks for each one of you.

jc8125:

It worked, however two things

I exported to "CSV for MS excel" and the "orders_id" doesnt show up with the ordernumber, but the text "orders_id". See the picture below

Attachment 12539

Also when try to use the Autosum in excel, it does not summarize the orders total column. It just shows 0. This is because it doesnt recognize is as integer or number. How to solve this?

doh! I copied your original code, which had the incorrect quotes around the orders_id field, try this:

Code:

---

SELECT `orders_id`,`customers_name`,`order_total`,`payment_method`, `date_purchased` FROM orders WHERE date_purchased BETWEEN "2013-03-01" AND "2013-03-30"

---

not sure about the autosum issue.. I don't export as CSV for Excel - so can't provide much help. I just tested using the "export as csv" and it works fine, and autosums things correctly (in excel). might want to use that method

Thanks

How about the Autosum. Any idea?

you might not have seen the edited part of my post, seems to work when exporting as csv (as opposed to csv for excel). not sure what exactly the difference is, probably adds some goofy microsoft formatting. i would just use the plain csv unless you have a reason otherwise.

Yes i managed to get it work with CSV by following this step:
http://www.brighthub.com/computing/w...82.aspx#imgn_0

However the column which contains the total "order total" does not get summarized. It just shows 0 when i use the autosum.

not sure.. when i export as "csv for excel", i have to go through the text-to-columns steps you referenced. however, when i export as plain old csv, it opens up fine in excel, already split into columns correctly, and the total/sum works fine on the order totals. not sure what to tell ya.

you could always try to re-format the cells in excel (right click, format cells, then under the "number" tab, select number or currency). might work, but I feel like these are hoops you shouldn't need to jump through. at least I've never had to....

No you misunderstand, the problem is when i export it to CSV, not CSV for MS Excel

Instead of using phpMyAdmin, why not try Sales Report (in the Plugins at the top of the page). It does date based reporting and should give you what you need.