CODEWITHSUNDEEP
phpmysqlsqldatabase
user1896446
user1896446

Reputation:

Count IDs from current month

I am trying to count the number of ID's for the current month in a table called bike_main with a column for ID and a column for date. The current command returns 541853 which is the total number of IDs

    $monthlyRiders= 'SELECT id FROM bike_main WHERE MONTH(date) = MONTH(CURDATE()) AND YEAR(date) = YEAR(CURDATE()) ORDER BY id DESC LIMIT 1';
    $monthlyRidersResult=mysql_query($monthlyRiders);
    $monthlyRidersRow = mysql_fetch_assoc($monthlyRidersResult);

Upvotes: 0

Views: 106

Answers (1)

Loïc
Loïc

Reputation: 11943

You are not counting the ids here, you are fetching all ids within the month. And with mysql_fetch_assoc you will get the last one (which can also represent the total number of ids).

To count the number of rows, use this :

$monthlyRiders= 'SELECT count(id) as number_of_rows FROM bike_main WHERE MONTH(date) = MONTH(CURDATE()) AND YEAR(date) = YEAR(CURDATE()) ORDER BY id DESC LIMIT 1';
$monthlyRidersResult=mysql_query($monthlyRiders);
$monthlyRidersRow = mysql_fetch_assoc($monthlyRidersResult);

Or alternatively select your rows, then use mysqli_num_rows() (this is slower).

Upvotes: 1

Related Questions