11532

Adjusting sql statement in a function based upon input

This is a bit urgent!

I'm trying to make a simple filter search where-by you can choose from a series of 3 drop downs and then based upon this the results are then displayed, How would I go about adjusting the sql query for each and if you were to only choose to search from aone of the 3 rather than all 3 etc... example there could be the url with input such as: url.com?location=gb&color=3&hair=4 and still form the correct sql query for something like this: url.com?location=gb&hair=1 and not encounter problems with WHERE and AND etc etc and empty variables in the statement

Would this not need to be a massive function to check using if to see how the data is set for all possibilities?

Thanks, Stefan

Answer1:

I answered a question the other day that I think is pretty similar to yours:

PHP: prepared statement, IF statement help needed

The idea is that you use conditional logic in your code to collect terms as needed corresponding to your application inputs. Then you join them together in such a way that produces the right SQL expression.

It does need some application function to build the SQL expression dynamically, and there are techniques to make it as concise as possible. If you really have many possible search terms, you might end up with a lengthy function. But guess what? If you have complex inputs, it should be no surprise that you need complex code to deal with them.

<hr>

Re your comment:

url.com?location=gb&color=3&hair=4

Okay, you have up to three inputs and you have to dynamically build up an SQL query from these. Let's start from the end and work backwards. Ultimately you want an SQL expression like this:

WHERE (location = 'gb') AND (color = 3) AND (hair = 4)

If you have an array of three terms, you can join them together in PHP using the implode() function. But you may also have fewer than three. You can handle any number of terms by putting however many terms you have into an array and imploding them with AND between each term:

$where_array = array( "(location = 'gb')", "(color = 3)", "(hair = 4)" ); $where_expr = "WHERE " . implode(" AND ", $where_array);

So how do you create the array with these terms? By writing code to append to the array conditionally for each input that is present in your app's current request:

$where_array = array(); if (array_key_exists("location", $_GET)) { $location = mysql_real_escape_string($_GET["location"]); $where_array[] = "(location = '$location')"; } if (array_key_exists("color", $_GET)) { $color = mysql_real_escape_string($_GET["color"]); $where_array[] = "(color = '$color')"; } if (array_key_exists("hair" $_GET)) { $hair = mysql_real_escape_string($_GET["hair"]); $where_array[] = "(hair = '$hair')"; }

After all that's done, your array has between zero and three elements. If it has one or more, you want to generate a WHERE clause as shown previously, otherwise skip it.

$where_expr = ''; if ($where_array) { $where_expr = "WHERE " . implode(" AND ", $where_array); }

Then append the $where_expr to your baseline SQL query.

$sql .= $where_expr

The stuff about $params is for query parameters, which is an alternative method of including dynamic values into an SQL expression, instead of mysql_real_escape_string(). It's not mandatory (and in fact PHP's old mysql extension doesn't support query parameters) but I recommend switching to PDO so you can use this feature. See example here: PDO::prepare().

Answer2:

Here is my go at it:

// discard empty values and unwanted keys $get = array_intersect_key(array_filter($_GET, 'strlen'), array_flip(array('location', 'color', 'hair'))); foreach ($get as $key => $value) { $get[$key] = $key . ' = ' . mysql_real_escape_string($value); } $sql .= ((count($get) == 0) ? null : ' WHERE ') . implode(' AND ', $get);

I haven't tested it but it should work well.

Recommend

  • double if conditioning in r language syntax [duplicate]
  • Building a dynamic query in C# (SQL Injection Attack)
  • Repackaging the .jar file
  • Need advice in designing tables in SQL-Server
  • JFreeChart BarChart - Category Markers
  • Regex: Match everything except backreference
  • Insert Path of a file with \\\\ in mysql using java
  • how to force the use of cmov in gcc and VS
  • Conditional serialization with protobuf-net
  • Writing a recursive function on lists in Haskell
  • PostgreSQL 9.1 timezones
  • ResponseBuilder is not working when used with entity object
  • SQL Server Nvarchar and Java prepared statement
  • Add custom field for WooCommerce CSV Export plugin - For customer first order [closed]
  • Escaping single quotes in JDBC with MySql
  • Generating anchors with PyYAML.dump()?
  • Are there any side effects from calling SQLAlchemy flush() within code?
  • .NET video play library which allows to change the playback rate?
  • How to plot large time series (thousands of administration times/doses of a medication)?
  • How to 'create temp table as select' in Slick?
  • How to make R's read_csv2() recognise the text characters properly
  • Android Activity.onWindowFocusChanged doesn't get called from within TabHost
  • RxJava debounce by arbitrary value
  • MySQL Order by column = x, column asc?
  • how to avoid repetitive constructor in children
  • C: Incompatible pointer type initializing
  • onBackPressed() not being executed
  • Meteor: Do Something On Email Verification Confirmation
  • Spark fat jar to run multiple versions on YARN
  • FB SDK and cURL: Unknown SSL protocol error in connection to graph.facebook.com:443
  • Java applet as stand-alone Windows application?
  • ActionScript 2 vs ActionScript 3 performance
  • Upload files with Ajax and Jquery
  • Weird JavaScript statement, what does it mean?
  • Do I've to free mysql result after storing it?
  • Compare two NSDates in iPhone
  • A cron job substitute?
  • json Serialization in asp
  • Error creating VM instance in Google Compute Engine
  • Why joiner is not used after Sequence generator or Update statergy