Query help needed. Count number of days.

[ztimer]

I have a database lets say punch_log

 

The data in the table looks like:

---------------------------------------------------------------------------

|punch_log_id |  user_id | punch_id | punch_time          |

---------------------------------------------------------------------------

|  10010        |  21        | 1      | 2010-11-10 15:04:59|

|  10011        |  21        |      2      | 2010-11-10 15:50:05|

|  10010        |  21        | 1      | 2010-11-11 15:04:59|

|  10011        |  21        |      2      | 2010-11-11 15:50:05|

|  10010        |  21        | 1      | 2010-11-12 15:04:59|

|  10011        |  21        |      2      | 2010-11-12 15:50:05|

|  10010        |  21        | 1      | 2010-11-13 15:04:59|

|  10011        |  21        |      2      | 2010-11-13 15:50:05|

|  10010        |  21        | 1      | 2010-11-14 15:04:59|

|  10011        |  21        |      2      | 2010-11-14 15:50:05|

|  10010        |  21        | 1      | 2010-11-14 15:50:59|  <-- this is why i need this.

|  10011        |  21        |      2      | 2010-11-14 15:55:05|  <-- this is why i need this.

----------------------------------------------------------------------------

 

Im currently using :

 

$kust  = $_POST['AKuu'];

$kuni3 = $_POST['LKuu'];

$valitudtootaja = $_POST['TNimi'];

 

 

mysql_select_db($database, $con);

$query2 = "SELECT *, SUM(punch_id) FROM punch_log WHERE user_id = '".$valitudtootaja."' AND punch_time BETWEEN '$kust' AND '$kuni3' AND punch_id ='1' ";

$result2 = mysql_query($query2) or die(mysql_error());

while($row = mysql_fetch_array($result2)){

$X = $row['SUM(punch_id)'];

 

When using the current query im getting

 

Workers days at work = 6

it should be 5 but thats why i need some solution to group dates and then sum them.

 

Does anybody have any ideas?

[litebearer]

Perhaps using GROUP BY, ie (just a rough Psuedo)

SELECT * FROM yourtable GROUP BY DAY(datefield) WHERE user_id = some_id
then count the number of rows returned

[ztimer]

Thank you for the idea to group by DAY(datefield)  this really helped me out.

 

Perhaps using GROUP BY, ie (just a rough Psuedo)

SELECT * FROM yourtable GROUP BY DAY(datefield) WHERE user_id = some_id
then count the number of rows returned