Counting sql data with current date

[squishypentagon]

Ok, so far all I have been able to do is to query the database and return each value:

 

$result = mysql_query("SELECT date, username, timeid FROM totals");

while ($row = mysql_fetch_array($result)) {
   echo $row{'date'}."".$row{'username'}."
   ".$row{'timeid'}."<br>";
}

 

but i don't want to see each individual entry in the database.  All i need is for it to count how many times a specific piece of data shows up for the current date.  If anyone could help i would greatly appreciate it.

[DavidAM]

It kind of depends on how your date is stored. But you'll need to use something like this:

 

SELECT COUNT(*) FROM totals WHERE date = CURDATE()

[squishypentagon]

My date is stored as text in the format of m-d-yyyy

 

and i've tried that code and i can't figure out how to pull a specific piece of data, like a specific username in the database.  basically i have the user logged in and i want to know how many times their username pops up with the current date

[DavidAM]

Storing dates as varchar or text is a bad idea. It makes it very difficult to query based on the date or manipulate the date. If possible, you should consider using the DATE datatype for date columns.

 

However, with things the way they are, you can use DATE_FORMAT to convert the CURRENT_DATE() into a string.

DATE_FORMAT(CURRENT_DATE(), '%c-%e-%Y')

Unless you are using two-digits for the month and day, then it would be

DATE_FORMAT(CURRENT_DATE(), '%m-%d-%Y')

Those would give '1-8-2011' and '01-08-2011', respectively.

 

To get the number of times a particular user shows up, the query would be:

SELECT COUNT(*) FROM totals 
WHERE date = DATE_FORMAT(CURDATE(), '%c-%e-%Y') 
AND username = 'whatever'

 

See the mySql manual for DATE_FORMAT()

 

But, if at all possible, consider changing the datatype for the date column in your database. If you ever need to query for a range of dates, a string date is extremely difficult to use.

[squishypentagon]

Storing dates as varchar or text is a bad idea. It makes it very difficult to query based on the date or manipulate the date. If possible, you should consider using the DATE datatype for date columns.

 

However, with things the way they are, you can use DATE_FORMAT to convert the CURRENT_DATE() into a string.

DATE_FORMAT(CURRENT_DATE(), '%c-%e-%Y')

Unless you are using two-digits for the month and day, then it would be

DATE_FORMAT(CURRENT_DATE(), '%m-%d-%Y')

Those would give '1-8-2011' and '01-08-2011', respectively.

 

To get the number of times a particular user shows up, the query would be:

SELECT COUNT(*) FROM totals 
WHERE date = DATE_FORMAT(CURDATE(), '%c-%e-%Y') 
AND username = 'whatever'

 

See the mySql manual for DATE_FORMAT()

 

But, if at all possible, consider changing the datatype for the date column in your database. If you ever need to query for a range of dates, a string date is extremely difficult to use.

 

Thank you thank you thank you!  and there is only one instance that the date will be queried so i'm not worried about format because there is only one area it would be pulled.  but thank you!