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?

Why don't you echo your query to see what is wrong when dropdown is empty? — Mostafa Shahverdy
– Mostafa Shahverdy, Commented Apr 3, 2013 at 17:37
Don't use mysql_query, It's deprecated. Also, you're vonurable to sql injections. — Jonast92
– Jonast92, Commented Apr 3, 2013 at 17:38
Right now I'm just concerned with getting it operational. This is just specifically for intranet and to get it working. It will never see the external world. Thank you for the concern, it's something I am aware of but want to see if I can just get this working. — Hanny
– Hanny, Commented Apr 3, 2013 at 17:42

Your Common Sense · Accepted Answer · 2022-03-05 06:09:54Z

38

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);

edited Mar 5, 2022 at 6:09

Your Common Sense

158k42 gold badges226 silver badges373 bronze badges

answered Apr 3, 2013 at 17:39

SeanWM

17.1k10 gold badges54 silver badges85 bronze badges

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

2 Comments

Hanny Over a year ago

I had considered something like that - but what about the WHERE ? Meaning the statement has to include WHERE for the first one - but AND for all subsequent ones. Best way to do that?

SeanWM Over a year ago

Just check if $sql contains anything. If it does, use it but add the word WHERE before using $sql

CHORTik · Accepted Answer · 2013-04-03 18:01:38Z

$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 better if in GET you pushed name of tables rows; $_GET['STATION_NETWORK'] --- some 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);

Collectives™ on Stack Overflow

Create a dynamic mysql query using php variables

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related