Databases
StackOverflow2013
Postsdbo
POBuilding WHERE clauses from multiple $_GET's
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POBuilding WHERE clauses from multiple $_GET's
    text
    copied!<p>I am currently trying to write complex MySQL WHERE clauses that are generated from $_GET variables (which themselves come from select dropdowns). First, a bit of code so you know what I am talking about:</p> <pre><code> if(!isset($_GET['order'])){ $order= 'start asc'; } elseif ($_GET['order'] == "dateasc") { $order= 'start asc'; } elseif ($_GET['order'] == "titleasc") { $order= 'title asc'; } elseif ($_GET['order'] == "titledesc") { $order= 'title desc'; }; if(!isset($_GET['cat'])){ $cat= '0'; } else { $cat = $_GET['cat']; }; if(!isset($_GET['loc'])){ $loc= '0'; } else { $loc = $_GET['loc']; }; if (isset($_GET['sd']) || isset($_GET['ed']) || isset($_GET['cat']) || isset($_GET['loc']) || isset($_GET['order']) ) { $where = 'WHERE '; if (isset($_GET['sd'])) { $where .= "start = " . $_GET['sd']; }; if (isset($_GET['ed'])) { $where .= "AND end = " . $_GET['ed']; }; if (isset($_GET['cat'])) { $where .= "AND category = " . $_GET['cat']; }; if (isset($_GET['loc'])) { $where .= "AND location = " . $_GET['loc']; }; }; $result = mysql_query("SELECT * FROM " . TABLE . $where . " ORDER BY " . $order); </code></pre> <p>Obviously this isn't working, otherwise I wouldn't be here. :) Basically, I have 4 variables that I want to <em>conditionally</em> use for sorting in my query: start date, and end date, a category, and a location. My problem is that all 4 of these may not always be used.. so given the above example, there might be a case where someone selects a category ($cat) but NOT a start date ($sd)... which means my WHERE clause would start off with 'AND', which is obviously invalid. So how do I build a query based off variables that may or may not be used?</p> <p>I really feel like I am overthinking this, and I am afraid of writing 9000 lines of isset tests to account for every combination of $_GET variable usage. Surely there a simple way to build a WHERE clause from multiple $_GETs that may or may not be used every time..? I've tried Googling but can only find solutions that suggest using a framework for building complex queries and that just seems overly... clunky... for such a simple problem.</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload