Query between two dates with MySQL

Question

I am trying to get all rows in a database which have been created between two dates inclusively. When I search for meetings in 2013-05-01 and todays date, I get no results but when I search without the WHERE clause I see there are two records for today. I thought, since the dates are DATETIME, I would try casting them as dates but this doesn't seem to work.

My function is as follows:

function meeting_reports($connection, $to, $from)
{
    $status = array();
    $sql = 
      $connection->query (
        "SELECT `meeting_id`,`visibility`,`meeting_start` 
         FROM `details` 
         WHERE DATE(`meeting_start`) BETWEEN '{$from}' AND '{$to}'"
    );
    $status["total_meetings"] = 0;
    $status["cancelled_meetings"] = 0;
    if($sql->num_rows > 0)
    {
        while($results = $sql->fetch_assoc())
        {
            if($results["visibility"]==0)
            {
                $status["total_meetings"]++;                
            }
            elseif($results==1)
            {
                $status["total_meetings"]++;                
            }
            elseif($results["visibility"]==2)
            {
                $status["total_meetings"]++;                
            }
            elseif($results["visibility"]==3)
            {
                $status["cancelled_meetings"]++;                
            }
        }
    }
    return $status;
}

What am I doing wrong?

Is `DATE(\`meeting_start\`)`the name of column in your database? — Reporter, Dec 17 '15 at 16:07
Possible duplicate of [MySQL "between" clause not inclusive?](http://stackoverflow.com/questions/5080824/mysql-between-clause-not-inclusive) — devlin carnate, Dec 17 '15 at 16:07
meeting_start is the name of the column in the database, I just thought that was how you cast a date — wrichards0, Dec 17 '15 at 16:09
@wrichards0 I think you should rename the column name and the second one, you should cast the strings into MySQL's Datetime type. — Reporter, Dec 17 '15 at 16:12

score 1 · Answer 1 · answered Dec 17 '15 at 16:32

I see a couple issues here. you need to clarify if your data type is date, or datetime. going to assume datetime. also if you are looking for meetings that occurred on a single specific day, you cannot search for events between x and y if x=y. there is nothing between it. if you are using datetime date type, concat 00:00:00 to your start date and 23:59:59 to your end date, now you have a valid range that includes the the valid times for the date in question. or for single date searches, do between ? and ? + interval 1 day and pass date twice as '12-25-2015 00:00:00'

also, you are directly using strings in your query, this can open you up to sql injection attacks. do a google search on bound parameters and never use a variable in an sql query EVER again.

score 0 · Answer 2 · answered Dec 17 '15 at 16:24

0

Try this

$connection->query (
"SELECT meeting_id,visibility,meeting_start 
         FROM details 
         WHERE meeting_start BETWEEN '" . $from . "' AND '" . $to . "'"
);

answered Dec 17 '15 at 16:24

masood elsad

434
2
6
18

Query between two dates with MySQL

2 Answers2