Counting record by date - Ex. Today's entry

[Johana Solendhal]

Hola hola!

I would like to know how to output the number of rows that have been added in the current day?

I mean, I would like to be able to display the number of entries in a dynamic way?

How my date is formated:

         
$timestamp = time()+date("P");
gmdate("YmdHis", $timestamp);
$ordertime = gmdate("YmdHis", $timestamp); 

How my date is added to the database:

$query = "INSERT INTO orders
 SET 
orderdate = '$ordertime'

"
; 

[foofoonet]

you would need to discover the timestamp for midnight yesterday, then count the number of rows since then (ie are greater than midnight).

[r937]

select count(*) as orders_today
  from orders 
 where orderdate >= current_date

[Johana Solendhal]

Gracias all.

But is this righ:

<?php 
                                  $result = mysql_query("select count(*) as orders_today from orders where orderdate >= current_date'"); 
$blah = mysql_query($result); 
echo "$blah"; 
                                  ?>

[trib4lmaniac]

You need

echo mysql_result($blah, 0); 

[Johana Solendhal]

Getting this:

Warning: mysql_result(): supplied argument is not a valid MySQL 

This is wha I'm using:

<?php 
                                  $result = mysql_query("select count(*) as orders_today from orders where orderdate >= current_date'"); 
$blah = mysql_query($result); 
echo mysql_result($blah, 0); 
                                  ?>

Can someone just explain to me the logic of the query? I mean, orders_today represents what? I mean, should it be one of the fields of my database?

Appologize for being a ZERO at this

[trib4lmaniac]

$result = mysql_query("select count(*) as orders_today from orders where orderdate >= current_date'"); 

An extra quote has crept in at the end of the string there.

orderstoday is a temporary "column" specific to that query. It is known as an alias.
It is created at "select count(*) as orders_today"
The where clause makes sure that only jobs from that day are selected. current_date is a function that returns the current date.

[Johana Solendhal]

Thanks for all your help.

I am still getting the same error.

Is the mysql_result echo right?

The problem seems to be coming from there.

 $result = mysql_query("select count(*) as orders_today from orders where orderdate >= current_date"); 
$blah = mysql_query($result); 
echo mysql_result($blah, 0); 

[trib4lmaniac]

Doh! Sorry, my fault for not paying attention

 $result = mysql_query("select count(*) as orders_today from orders where orderdate >= current_date");
echo mysql_result($result, 0); 

[Johana Solendhal]

hey thanks!! not getting any errors anymore.

My strangly, it's not outputing anything despite the fact that i added entries today.

[Johana Solendhal]

Not even outputing "0". :S

[trib4lmaniac]

What happens if you change the first line to

$result = mysql_query("select count(*) as orders_today from orders where orderdate >= current_date") or die(mysql_error()); 

[Johana Solendhal]

I get all of the rows in the table.

[trib4lmaniac]

Could you paste the output?

[Johana Solendhal]

<?php 
$result = mysql_query("select count(*) as orders_today from orders where orderdate >= current_date") or die(mysql_error());
echo mysql_result($result, 0); 
                                  ?>

And what I get is - 16 ( total number of rows )

ps. gracias for taking the time to help me out!

[trib4lmaniac]

Oh right I see. Well that particular piece of code seems to be functioning correctly.
If you are sure 16 is the wrong answer, perhaps it is a timezone issue.

In your first post you seem to be doing something strange to get the current date - are you trying to get it to GMT?
If so, you need to calculate it with:

$ordertime = gmdate("YmdHis", time()); 

Edit:

Or it could be the comparison in the where clause of the SQL. I don't think a DATE to DATETIME comparison would play out very well. Perhaps:

$result = mysql_query("select count(*) as orders_today from orders where orderdate >= timestamp(current_date)"); 

[Johana Solendhal]

Thanks for that.

I checked the database and the time seems to be getting stored correctly.

This is the format stored in the database orderdate field: 20060518002200

[Johana Solendhal]

Oh right I see. Well that particular piece of code seems to be functioning correctly.
If you are sure 16 is the wrong answer, perhaps it is a timezone issue.

In your first post you seem to be doing something strange to get the current date - are you trying to get it to GMT?
If so, you need to calculate it with:

$ordertime = gmdate("YmdHis", time()); 

Edit:

Or it could be the comparison in the where clause of the SQL. I don't think a DATE to DATETIME comparison would play out very well. Perhaps:

$result = mysql_query("select count(*) as orders_today from orders where orderdate >= timestamp(current_date)"); 

Tried it, but it still outputs the number of all of the rows.

I agree with you, it surely has something to do with the way I store the date in the database.

[trib4lmaniac]

Is your orderdate column type a string?

[Johana Solendhal]

Is your orderdate column type a string?

What does that mean?

[trib4lmaniac]

What column type does the orderdate column in your database have?

[Johana Solendhal]

This is it:

`orderdate` varchar(50) NOT NULL default '', 

[trib4lmaniac]

Okay, that's the problem then. The comparison will only work if the column is of type DATETIME.

It should be safe to convert it straight to DATETIME and have all the rows update themselves from the current value.
I'd recommend doing a backup first though if there's any information in there you need to keep.

[r937]

it surely has something to do with the way I store the date in the database.

well, i wouldn't store it as a string, i'd use a DATETIME column, for one thing because it then requires no conversion in order to do date arithmetic or date comparisons, and secondly because it's a smaller size column, but actually there's no problem using that format, because, after the conversion, mysql can recognize it

for confirmation, you can do this --

select date_add('20060518002200', interval 15 minute) as d

[Johana Solendhal]

ah I see.

So can I create the column like so:

`orderdate` DATETIME(50) NOT NULL default '',