Creating a dynamic mysql query using php variables

Question

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

I need to figure out how to build a query dynamically, because sometimes the drop-down lists will be empty (i.e. they don’t want to filter this column).

What is the best way to do this?

I currently 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; 

What you can imagine does not work, because if any of these fields is empty, the request fails (or returns nothing).

Obviously, creating a “static” query like this really makes it difficult to execute certain variables.

What is the best way to dynamically create this request so that it is included only in those that are not empty, is added to the request so that it can successfully complete and display the corresponding data?

Answer 1

Just check if the variables contain a value, and if they do, build the query as follows:

 unset($sql); if ($stationFilter) { $sql[] = " STATION_NETWORK = '$stationFilter' "; } if ($verticalFilter) { $sql[] = " VERTICAL = '$verticalFilter' "; } $query = "SELECT * FROM $tableName"; if (!empty($sql)) { $query .= ' WHERE ' . implode(' AND ', $sql); } echo $query; // mysql_query($query); 

Answer 2

 $filter = array(); if ($_GET['station'] != '') { $filter[] = 'STATION_NETWORK = '.$_GET['station'];} if ($_GET['vertical'] != '') { $filter[] = 'VERTICAL = '.$_GET['vertical'];} if ($_GET['creative'] != '') { $filter[] = 'CREATIVE = '.$_GET['creative'];} if ($_GET['week'] != '') { $filter[] = 'WK = '.$_GET['week'];} $query = 'SELECT * FROM $tableName WHERE '.implode(' AND ', $filter); $result = mysql_query($query); ... 

but it is better if in GET you clicked the name of the rows in the table; $ _GET ['STATION_NETWORK'] --- something like this:

 then you make foreach ($_GET as $key => $value) { if ($value != '') { $filter[] = $key.' = '.$value;} } 

or try

 $filter = array('STANTION_NETWORK' => $_GET['station'], 'VERTICAL' => $_GET['vertical'], 'CREATIVE' => $_GET['creative'], 'WK' => $_GET['week']); $query_array = array(); foreach ($filter as $key => $value) { if ($value != '') { $query_array[] = $key.' = '.$value;} } $query = 'SELECT * FROM $tableName WHERE '.implode(' AND ', $query_array); $result = mysql_query($query);