Counting record by date - Ex. Today's entry

[Johana Solendhal]

This is what Phpmyadmin returns:
SQL query:
SELECT count( * ) AS date_add
FROM orders
WHERE orderdate >= date_add( current_date, INTERVAL -1
DAY )
[ Edit ] [ Explain SQL ] [ Create PHP Code ] [ Refresh ]




date_add
26

[r937]

excellent

so the query works

i would use a different column alias name besides "date_add" for a count which counts the rows for yesterday

okay, so this isn't the part that you said "didn't work"

[Johana Solendhal]

hehe sorry.

The problem is that it gives mw the total number of rows.

It's not giving me the rows that were added yesterday

[Johana Solendhal]

The problem is that it seems that is not subtracting 1 day to the current day.

SELECT count(*) as orders_yes FROM orders WHERE orderdate >= date_add(current_date, interval -1 day)

If I change -1 to -0 it will add me the orders that were added today, but if I change it back to -1 it will just give me the total rows in th table.

[r937]

if you only have 26 rows, please do this --

select orderdate from orders order by orderdate desc

and let's have a look at them

[Johana Solendhal]

Gracias for sticking with me!

Here's the result:

      2006-05-23 16:02:18 
      2006-05-23 16:02:01 
      2006-05-23 01:08:38 
      2006-05-23 01:08:29 
      2006-05-22 19:55:22 
      2006-05-22 19:52:45 
      2006-05-22 19:52:20 
      2006-05-22 19:48:59 
      2006-05-22 19:48:42 
      2006-05-22 19:44:32 
      2006-05-22 19:40:48 
      2006-05-22 19:38:12 
      2006-05-22 19:37:55 
      2006-05-22 19:36:51 
      2006-05-22 19:26:16 
      2006-05-22 19:22:15 
      2006-05-22 19:21:59 
      2006-05-22 19:19:44 
      2006-05-22 19:16:15 
      2006-05-22 19:16:04 
      2006-05-22 19:13:16 
      2006-05-22 19:11:00 
      2006-05-22 19:09:03 
      2006-05-22 19:08:45 
      2006-05-22 19:05:18 
      2006-05-22 19:02:15 

[r937]

good, and what time is it right now?

[Johana Solendhal]

Seems the records are one hour behind!!

Just added a product and the date of storage is 18:00 instead of the real time: 19:00

How is this affecting everything?

[r937]

how is it affecting everything? simple!

according to you, it's now 2006-05-23 18-something, or 2006-05-23 19-something

so yesterday is 2006-05-22, i.e. 2006-05-22 00:00:00

all your orderdates are greater than that

[Pedro Monteiro]

A tad messy, but this should get it working.

$result = mysql_query("select count(*) as orders_today from orders where orderdate > date_add(current_date, interval -1 day) AND orderdate < current_date"); 
echo mysql_result($result, 0);