Create a dynamic mysql query using php variables

Question

I have an html table that loads everything in a mySQL database table. I have dropdowns that relate to columns of that mySQL table - when the user selects one of the dropdowns it uses AJAX to query the database.

I need to figure out how to build the query dynamically because sometimes the dropdowns will be empty (i.e. they don't want to filter by that column).

What is the best way to do this?

Currently I have something like this:

    $stationFilter = $_GET['station'];
    $verticalFilter = $_GET['vertical'];
    $creativeFilter = $_GET['creative'];
    $weekFilter = $_GET['week'];    
    
    $result = mysql_query("SELECT * FROM tableName WHERE STATION_NETWORK = '$stationFilter' AND VERTICAL = '$verticalFilter' AND CREATIVE = '$creativeFilter'  AND WK = '$weekFilter'");   
    $data = array();
    while ($row = mysql_fetch_row($result) )
        {
        $data[] = $row;
        }   
    $finalarray['rowdata'] = $data;

Which you can imagine doesn't work because if any of those fields are empty - the query fails (or returns nothing, rather).

Obviously creating such a 'static' query like that really makes it difficult if certain variables are empty.

What is the best way to dynamically create that query so that it only enters the ones that are not empty get added to the query so it can successfully complete and display the appropriate data?

Answer 1

Just check if the variables contain a value and if they do, build the query like so:

$sql = [];
$parameters = [];
    
if ($stationFilter) {
    $sql[] = " STATION_NETWORK = ?";
    $parameters[] = $stationFilter;
}
if ($verticalFilter) {
    $sql[] = " VERTICAL = ?";
    $parameters[] = $verticalFilter;
}

$query = "SELECT * FROM tableName";

if ($sql) {
    $query .= ' WHERE ' . implode(' AND ', $sql);
}
$stmt = $mysqli->prepare($query);

if ($parameters) {
    $stmt->bind_param(str_repeat('s', count($array), ...$parameters);
}
$stmt->execute();
$result = $stmt->get_result();
$data = $result->fetch_all(MYSQLI_ASSOC);