SQL multiple where子句使用PHP进行过滤

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

Answer 1

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.