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, Oct 13 '17 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, Oct 13 '17 at 20:29
`isset` will return true if the variable is `1` OR `0` also your session variables are not matching — cmorrissey, Oct 13 '17 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, Oct 13 '17 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, Oct 13 '17 at 20:34
When you combine `AND` and `OR` in the same query, you often need to group them with parentheses to make sure you get the logic you want. — Barmar, Oct 13 '17 at 20:35
See https://stackoverflow.com/questions/27663976/sql-statement-is-ignoring-where-parameter — Barmar, Oct 13 '17 at 20:36

score 3 · Accepted Answer · edited Oct 13 '17 at 20:36

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.

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. — Joseph Gregory, Oct 13 '17 at 20:40
The array isn't being used in SQL. PHP converts the array to a string with `implode()`. — Barmar, Oct 13 '17 at 20:42
It's just a convenient way to build up a string containing a dynamic list of things. — Barmar, Oct 13 '17 at 20:42

SQL multiple where clause using PHP to filter

1 Answers1