Mysql Question posted in
The official MySQL documentation can be found here.

I am trying to get the number of rows between two dates in php/mysql

I have a mysql database with over a million records and need to see how many records exist between specific dates.
This is my code and always get one row.
I have validated that over the period in my code there are over 500 records.
What am I doing wrong?

$start_date = "1990-01-01";
$end_date = "1990-02-31";
echo "Start date is : " . $start_date . "<br>";
echo "End date is : " . $end_date . "<br>";
$sql = "SELECT COUNT(*) FROM " . $rgdtable . " WHERE `DATE-REGISTERED` BETWEEN " . $start_date . " AND " . $end_date;
echo "Sql string is : " . $sql . "<br>";

if ($result=mysqli_query($conn,$sql)) {
    $rowcount=mysqli_num_rows($result);
    echo "The total number of rows are: ".$rowcount; 
}
Tags:

2

Answers


  1. 0

    In your code – $end_date = "1990-02-31"; Is this even valid? February doesn’t have this? Shouldn’t it be $end_date = "1990-02-28" or "1990-03-01". If needed you can always do end_date minus 1 given that the datatype is Date

    Login or Signup to reply.
  2. 0

    you can also set the end_date 1 day higher und subcract 1 day in SQL.
    So you everytime this last day of the month before
    like this:

    $start_date = "1990-01-01";
$end_date = "1990-03-01";
echo "Start date is : " . $start_date . "<br>";
echo "End date is : " . $end_date . "<br>";
$sql = "SELECT COUNT(*) FROM " . $rgdtable . " WHERE `DATE-REGISTERED` BETWEEN " . $start_date . " AND " . $end_date . " - INTERVAL 1 DAY";
echo "Sql string is : " . $sql . "<br>";

if ($result=mysqli_query($conn,$sql)) {
    $rowcount=mysqli_num_rows($result);
    echo "The total number of rows are: ".$rowcount; 
}
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search