Problem in SQL query

Databases
1

I am making an online shopping site in PHP & My SQL where I need to fetch records of orders placed during last 5 days. The table name is “orders” with fields (Order_ID, Customer_ID, Total_Amount, Shippinig_Status, Order_Date)

Can anybody help me that what should be my SQL query to fetch records of orders placed in last 5 days ?

Any help will be highly appreciated.

Thanks & regards.

2

This is basic enough to where I might be able to help - but how are you storing your Order_Date? What’s the format?

3

in DATETIME format, which is being shown like this “2015-01-01 03:52:21”

4 
$date = date('Y-m-d H:i:s',time()-(5*86400));

$sql = "SELECT * FROM orders WHERE Order_Date<='$date' ";

Try that out - no promises it works. Partially made from me searching google and me trying to read this SQL Sitepoint book I got recently…100% sure that there is a better method that someone will correct me…

5

[quote=“sanjay_tank1975, post:1, topic:108723, full:true”]
I am making an online shopping site in PHP & My SQL [/quote]

no

you are doing homework assignments and you are only hurting yourself by trying to get answers off teh interwebs

8

dude, why assume php? also, your solution is time-specific, and will not return results on the morning of 5 days ago

do a search for sql date calculations using CURRENT_DATE and INTERVAL

9

Sorry - still learning!

10

Dear r937, pls help me in getting the right SQL for this.

Thanks & regard

11

do a search for sql date calculations using CURRENT_DATE and INTERVAL

12

Is it correct ?

SELECT * FROM orders WHERE Order_Date >= DATE_SUB(CURRENT_DATE, INTERVAL 5 DAY)

13

[quote=“sanjay_tank1975, post:12, topic:108723, full:true”]
Is it correct ?[/quote]

what happened when you tested it? ™

1 Like
14

Its working fine…But One doubt. Say today is 1st Jan 2015. So from which date should it display the results ?

15

well, let’s see…
1 day ago is dec 31 2014
2 days ago is dec 30 2014
3 days ago is dec 29 2014
4 days ago is dec 28 2014
5 days ago is dec 27 2014

so does this agree with your test results?

16

yes exactly…its showing today’s results also…So over all showing 6 days results

17

well, now

how do you think you could adjust your WHERE condition to get what you really want?

18

Have put INTERVAL 4 DAY…

19

and you tested it, right?

also, with INTERVAL 5 DAY, you would change it to “greater than” but not equal

1 Like
20

Dear, I have struck up in one more problem

I want to Generate serial number in mysql query with auto-increment .

My query is as follows -

set @a:=0;select @a:=@a+1 serial_number, b.Prod_ID, b.Product_Name, sum(t.Quantity) as transactionamount, sum(t.Quantity)*t.Price
from products b
left join order_details t on t.Prod_ID = b.Prod_ID
group by b.Prod_ID, b.Product_Name order by serial_number ASC

When I am running it directly in phpMyAdmin, its displaying the results properly but when I am using it in PHP code than its giving error as below -

Warning: mysql_fetch_assoc() expects parameter 1 to be resource, boolean given in C:\xampp\htdocs\shopping\try.php on line 9

Please help me in coming out of this problem

Thanks & regards

21

i can’t help you, someone else will have to step in

22

That error means that the query has failed. Echo the query and check what you’re sending to mysql. btw, are you aware that the old mysql_* extension has been deprecated as of version 5.5 of PHP?