CODEWITHSUNDEEP
phpmysql
janicehoplin
janicehoplin

Reputation: 397

SQL selecting date range

I have a table with a date column, in the format mm/dd/yyyy.

So I'm trying to do the following:

<?php
    $start = '05/01/2016'; 
    $end = '05/31/2016';
    $stmt = $link->prepare('SELECT COUNT(*) date from dates WHERE user=:user AND date between '$start' AND '$end'');
    $stmt->bindParam(':user', $user, PDO::PARAM_STR);
    if($stmt->execute()) {
        $result = $stmt->fetchAll();
        foreach($result as $row) {
            if($row[0] > 0) {
                echo $row[0];
            }
        }
    }
?>

I think the problem might be the multiple AND operators.

Upvotes: 0

Views: 168

Answers (2)

fthiella
fthiella

Reputation: 49049

You are using a placeholder for user, which is good, but why you are not using placeholders for start and end date?

I would suggest you to use this:

$stmt = $link->prepare('SELECT COUNT(*) date from dates WHERE user=:user AND date between :start AND :end');
$stmt->bindParam(':user', $user, PDO::PARAM_STR);
$stmt->bindParam(':start', $start, PDO::PARAM_STR);
$stmt->bindParam(':end', $end, PDO::PARAM_STR);

anyway the date format should be YYYY-MM-DD, but reading your comment it seems that you are using a string to store date using the format MM/DD/YYYY.

While this query will return what you are expecting:

"SELECT COUNT(*) date
from dates
WHERE user=:user AND str_to_date(date, 'MM/DD/YYYY') between :start AND :end"

With str_to_date you can convert strings to date, and the comparison will succeed as expected, however I would strongly suggest you to store dates using a DATE column, you can always format the date at the application layer.

Upvotes: 2

Nelson Teixeira
Nelson Teixeira

Reputation: 6562

Your problem is most likely with this date format '05/01/2016'. You're using a date in portuguese/brazilian format that is not immediatelly recognized by mysql.

try the '2016-01-05' format and it will probably work

Upvotes: 1

Related Questions