CODEWITHSUNDEEP
phpmysql
CIB
CIB

Reputation: 785

Multiple MySQL Statements

I'm having some issues with a MySQL query any help would be appreciated. I basically have a table that contains job information and my query is determined from multiple selects in a form. So there is user_id, date_from, date_to and client_name.

I have got the query working when user selects all fields user_id, date_from, date_to and client_name the result as displayed but I can't seem to get it working when only a date_from and date_to are selected or client_name and date etc.

The query need to work if just one field is selected or all field are selected.

Hop that make sense! Here is my code that has the results working when all fields all filled in. 

SELECT * FROM `jobs` 
WHERE (`job_date` BETWEEN '" . date('Y-m-d', strtotime($from_date)) . "' AND '" . date('Y-m-d', strtotime($to_date)) . "'    
AND `client_name`='" . $client_name . "'
AND `staff_id`= '" . $staff_id . "')

Upvotes: 0

Views: 58

Answers (3)

xNeyte
xNeyte

Reputation: 622

Doing your statement with this trick should work :

SELECT *
FROM jobs
WHERE (Client_name = :clientName OR :clientName is null) 
AND ...

If your user didn't fill client_name, the condition will be true because input will be null, and if he filled it, it will search what he wrote !

It allows you to make only one statement.

Another example here : https://stackoverflow.com/a/30030510/4734085

Upvotes: 0

Sujeet Sinha
Sujeet Sinha

Reputation: 2433

You need to use OR condition instead of AND. Try as follows:

SELECT * FROM `jobs` 
WHERE (`job_date` BETWEEN '" . date('Y-m-d', strtotime($from_date)) . "' AND '" . date('Y-m-d', strtotime($to_date)) . "'    
OR `client_name`='" . $client_name . "'
OR `staff_id`= '" . $staff_id . "')

Upvotes: 0

Gerald Schneider
Gerald Schneider

Reputation: 17797

Build the conditions dynamically, depending on which fields are set:

$query = "SELECT * FROM `jobs`";
$conditions = array();
if ( !empty($from_date) && !empty($to_date) ) {
    $conditions[] = "`job_date` BETWEEN '" . date('Y-m-d', strtotime($from_date)) . "' AND '" . date('Y-m-d', strtotime($to_date)) . "'";
}
else {
    if ( !empty($from_date) )
        $conditions[] = "`job_date` > '" . date('Y-m-d', strtotime($from_date)) . "'";
    if ( !empty($to_date) )
        $conditions[] = "`job_date` < '" . date('Y-m-d', strtotime($to_date)) . "'";
}
if ( !empty($client_name ) )
    $conditions[] = "`client_name`='" . $client_name . "'";
if ( !empty($staff_id ) )
    $conditions[] = "`staff_id`='" . $staff_id . "'";

if ( sizeof( $conditions ) > 0 ) {
    $query .= " WHERE " . implode( " AND ", $conditions );
}

This will result in a query only containing fields that actually have been set. empty fields will not be in the condition.

For example. if $from_date, $to_date and $staff_id is set, it would result into this:

SELECT * FROM `jobs` WHERE `job_date` BETWEEN '2016-02-23' AND '2016-06-15' AND `staff_id`='foo'

Upvotes: 1

Related Questions