SQL multiple where clause using PHP to filter

Question

I am creating a simple table that shows the available training courses to the team members using a simple PHP query

$Get_Events_Widget_Query = "
                SELECT event_information.id AS info_id,
                event_type.name,
                event_type.prefix,
                event_information.start_date
                FROM event_information
                LEFT OUTER JOIN event_type
                ON event_information.type=event_type.prefix
                WHERE (event_information.live = '1' AND event_information.start_date > NOW()) AND event_type.prefix IN ('GPS','PET','FST','ICT','FSW','SAL')";

The above shows how the query working, but this is where I want to add the filter using PHP.

event_type.prefix is the category that all the training courses sit within, however each user has the ability to deselect which course type they wish to see through the session by assigning each one to either 0 or 1.

$_SESSION['filter_GPS']
$_SESSION['filter_PET']
$_SESSION['filter_FST']
$_SESSION['filter_ICT']
$_SESSION['filter_FSW']
$_SESSION['filter_SAL']

This is where my problem comes. I can't seem to work the problem within the PHP code if I want to filter out some of the options.

This is what I thought would work until I realised I can't call the same where clause more than once (in this format anyway);

if($_SESSION['filter_information_sessions'] == "1") {
    $Get_Events_Widget_Query .= "AND event_type.prefix = 'GPS' ";
}
if($_SESSION['filter_pet'] == "1") {
    $Get_Events_Widget_Query .= "AND event_type.prefix = 'PET'";
}
if($_SESSION['filter_fs_testing'] == "1") {
    $Get_Events_Widget_Query .= "AND event_type.prefix = 'FST'";
}
if($_SESSION['filter_ict_testing'] == "1") {
    $Get_Events_Widget_Query .= "AND event_type.prefix = 'ICT'";
}
if($_SESSION['filter_workshops'] == "1") {
    $Get_Events_Widget_Query .= "AND event_type.prefix = 'FSW'";
}
if(isset($_SESSION['filter_speaking'] == "1") {
    $Get_Events_Widget_Query .= "AND event_type.prefix = 'SAL'";
}

Any help would be much appreciated and apologies for the lack of correct terminology

If you use AND, it will only be true if the same field has two different values. How can that possibly be? — Barmar
– Barmar, Commented Oct 13, 2017 at 20:27
I think you want OR, not AND. Or use those filter variables to build the list of values in the IN() list. — Barmar
– Barmar, Commented Oct 13, 2017 at 20:29
isset will return true if the variable is 1 OR 0 also your session variables are not matching — cmorrissey
– cmorrissey, Commented Oct 13, 2017 at 20:32
You desperately need to put all that into an associative array that acts as a look-up table. This is getting completely out of control. — tadman
– tadman, Commented Oct 13, 2017 at 20:33
@Barmar regarding your first comment...Thats why I said it wasn't right in regards to your second comment if I include OR it counteracts the 'start_date' clause unless I'm structuring it wrong — Joseph Gregory
– Joseph Gregory, Commented Oct 13, 2017 at 20:34

cmorrissey · Accepted Answer · 2017-10-13 20:36:02Z

3

You should be using AND, not OR.

But a better way is to build the IN() list dynamically.

$event_types = array();
if($_SESSION['filter_information_sessions'] == "1") {
    $event_types[] = "'GPS'";
}
if(isset($_SESSION['filter_pet'])) {
    $event_types[] = "'PET'";
}
...
$events_string = implode(', ', $event_types);
$Get_Events_Widget_Query = "
                SELECT event_information.id AS info_id,
                event_type.name,
                event_type.prefix,
                event_information.start_date
                FROM event_information
                LEFT OUTER JOIN event_type
                    ON event_information.type=event_type.prefix
                WHERE (event_information.live = '1' AND event_information.start_date > NOW()) 
                    AND event_information.type IN ($events_string)";

I changed the last condition to test event_information.type rather than event_type.prefix, because when doing a LEFT JOIN you shouldn't test columns from the child table in the WHERE clause; if there's no matching row, LEFT JOIN returns NULL for those columns and the test will fail.

edited Oct 13, 2017 at 20:36

cmorrissey

8,5912 gold badges26 silver badges29 bronze badges

answered Oct 13, 2017 at 20:34

Barmar

789k57 gold badges555 silver badges669 bronze badges

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

3 Comments

Joseph Gregory Over a year ago

Brilliant thank you. I didn't realise an array could be used in this manner with SQL. Looks like I need to do some reading. It really means a lot.

Barmar Over a year ago

The array isn't being used in SQL. PHP converts the array to a string with implode().

Barmar Over a year ago

It's just a convenient way to build up a string containing a dynamic list of things.

Collectives™ on Stack Overflow

SQL multiple where clause using PHP to filter

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related