My query won't work when I add a date range to it. Using php and MySQL

Question

I currently have multiple search fields within one form which the user can pick and choose which one to filter, everything works perfectly until I add the date range to it. If I add the date range alone without any other fields to filter it works so I know it is not that I am writing the query wrong. Please help me figure this out. THANKS!

This is the query that works: (Also for some reason doing SELECT * won't work, I have to type the name of all the fields I want to show)

It includes the part of the form that includes the date range.

<li class="form-line form-line-column" id="id_29">
    <label class="form-label-top" id="label_29" for="input_30">
        From Date:
    </label>
    <div id="cid_29" class="form-input-wide">
        <input type="text" class="form-textbox validate" id="datepicker" name="from_date" size="10" />
    </div>
</li>
<li class="form-line form-line-column" id="id_29">
    <label class="form-label-top" id="label_29" for="input_8">
        To Date:
    </label>
    <div id="cid_29" class="form-input-wide">
        <input type="text" class="form-textbox validate" id="datepicker2" name="to_date" size="10" />
    </div>
</li> 

<?php
    $sso = $_GET['sso'];
    $assigned_to = $_GET['assigned_to'];
    $case_status = $_GET['case_status'];
    $startdate = $_GET['from_date'];
    $enddate = $_GET['to_date'];
    $subject = $_GET['subject'];

    $sql = ("SELECT 
                id, sso, full_name, case_status, assigned_to, resolution, 
                description, updated, created, additional_notes, subject
            FROM rmstable2
            WHERE 
                sso LIKE '%$sso%' AND
                case_status LIKE '%$case_status%' AND
                assigned_to LIKE '%$assigned_to%' AND
                subject LIKE '%$subject%'");
?>

This is the query I want:

<?php
    $sql = ("SELECT 
                id, sso, full_name, case_status, assigned_to, resolution, 
                description, updated, created, additional_notes, subject
             FROM rmstable2
             WHERE 
                sso LIKE '%$sso%' AND
                case_status LIKE '%$case_status%' AND
                assigned_to LIKE '%$assigned_to%' AND
                subject LIKE '%$subject%'
                AND created >= '$startdate'
                AND created <= '$enddate'");
?>

What does your generated query look like? Does it run properly directly in the database? — andrewsi
– andrewsi, Commented Aug 8, 2012 at 14:38
Can you show format of your to_date and from_date fields that come from PHP and also type of created field? — favoretti
– favoretti, Commented Aug 8, 2012 at 14:39
Use created BETWEEN '$startdate' and '$enddate' instead of created >= '$startdate' AND created <= '$enddate' — devasia2112
– devasia2112, Commented Aug 8, 2012 at 14:39
@andrewsi yes it runs perfectly without the date range added to the end of the query, it displays in a table, and also when I plug in actual dates it works. I know I could use BETWEEN, I just never worked with date ranges before. — Alexandra
– Alexandra, Commented Aug 8, 2012 at 14:40

John Woo · Accepted Answer · 2012-08-08 14:48:13Z

2

use BETWEEN for that and don't forget to wrap the dates with DATE( )

$sql = ("SELECT id, sso, full_name, case_status, assigned_to, 
                resolution, description, updated, created, 
                additional_notes, subject 
        FROM  rmstable2 
        WHERE sso LIKE '%$sso%' AND 
              case_status LIKE '%$case_status%' AND 
              assigned_to LIKE '%$assigned_to%' AND 
              subject LIKE '%$subject%' AND 
              DATE(created) BETWEEN DATE('$startdate') AND DATE('$enddate')");

UPDATE 1

how about changing AND to OR

$sql = ("SELECT id, sso, full_name, case_status, assigned_to, 
                resolution, description, updated, created, 
                additional_notes, subject 
        FROM  rmstable2 
        WHERE sso LIKE '%$sso%' OR 
              case_status LIKE '%$case_status%' OR
              assigned_to LIKE '%$assigned_to%' OR 
              subject LIKE '%$subject%' OR
              (DATE(created) BETWEEN DATE('$startdate') AND DATE('$enddate'))");

edited Aug 8, 2012 at 14:48

answered Aug 8, 2012 at 14:42

John Woo

265k70 gold badges509 silver badges500 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Alexandra Over a year ago

I tried it, but it only works if I fill ALL the search fields, not if I just fill one and leave the rest empty :/

Alexandra Over a year ago

yes it is, the format is not the problem, the problem is when filtering with multiple other fields.

John Woo Over a year ago

if that's the case, do not use AND but instead use OR

Alexandra Over a year ago

for all of them or just for date range?

John Woo Over a year ago

see my updated answer, all of them but for the date range use AND

|

JCSickmeyer · Accepted Answer · 2012-08-08 14:44:02Z

0

Assuming your values are formatted correctly, you could try using DATE(created) in your final query like:

AND DATE(created) >= '$startdate' AND DATE(created) <= '$enddate'

or even use BETWEEN such as:

AND DATE(created) BETWEEN '$startdate' AND '$enddate'

answered Aug 8, 2012 at 14:44

JCSickmeyer

1891 gold badge1 silver badge8 bronze badges

Comments

Martin · Accepted Answer · 2012-08-08 14:51:07Z

0

Best practice is to use echo $sql; and to paste the generated query in phpmyadmin(if u have such)! Other helpful command is echo mysql_error().

In your case most probably you have to check if the date format is right (it should be YYYY-MM-DD)

Also make this AND created >= '$startdate' AND created <= '$enddate'");

to this AND created >= '" . $startdate . "' AND created <= '" . $enddate . "'");

Because if...

$a=1;
echo '$a';

will output

$a

edited Aug 8, 2012 at 14:51

answered Aug 8, 2012 at 14:40

Martin

1,2534 gold badges13 silver badges24 bronze badges

1 Comment

favoretti Over a year ago

Not all people use phpmyadmin, you know ;) I never used anything other than mysql console for that matter and even find phpmyadmin annoyingly inconvenient :)

Mihai Iorga · Accepted Answer · 2012-08-08 15:06:00Z

0

// assuming date is like YYYY-MM-DD, or you can explode the time to create an timestamp
// assuming your date field in the database is date
// you can remove FROM_UNIXTIME if your field is not date

$startdate = strtotime($_GET['from_date']);
$enddate = strtotime($_GET['to_date']);


$addQuery = "";

if(isset($startdate)){
     $addQueryStart = "created >= FROM_UNIXTIME($startdate)";
}
if(isset($startdate)){
     $addQueryEnd = "created <= FROM_UNIXTIME($enddate)";
}

if(isset($startdate) && isset($enddate)){
    $addQuery .= "AND (".$addQueryStart." AND ".$addQueryEnd.")";
} elseif(isset($startdate) && !isset($enddate)){
    $addQuery .= "AND ".$addQueryStart;
} elseif(!isset($startdate) && isset($enddate)){
    $addQuery .= "AND ".$addQueryEnd;
}



$sql = "
    SELECT id, sso, full_name, case_status, assigned_to, resolution, description, updated, created, additional_notes, subject 
    FROM rmstable2 
    WHERE 
        sso LIKE '%$sso%' AND 
        case_status LIKE '%$case_status%' AND 
        assigned_to LIKE '%$assigned_to%' AND 
        subject LIKE '%$subject%' ".$addQuery;

Later edit: for different situations

edited Aug 8, 2012 at 15:06

answered Aug 8, 2012 at 14:44

Mihai Iorga

39.8k17 gold badges109 silver badges109 bronze badges

3 Comments

Alexandra Over a year ago

This only works if the date range is filled, if it is left empty nothing shows.

Mihai Iorga Over a year ago

Yes, it's for your example, if you need to leave some field you can make filters before the query.

Alexandra Over a year ago

you mean like create a condition?

Collectives™ on Stack Overflow

My query won't work when I add a date range to it. Using php and MySQL

4 Answers 4

7 Comments

Comments

1 Comment

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

7 Comments

Comments

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related