(PHP) MySQL select query with array from $_GET

Question

What I'm trying to do is go from a search URL such as this:

search.php?president=Roosevelt,+F.&congress=&nomination_received_by_senate=&state=CT

To a MySQL query like this:

SELECT `name` FROM `nominations` WHERE president=`Roosevelt, F.` AND state=`CT`

I have some code that strips any empty values from the URL, so I have an array as such:

Array ( [president] => Roosevelt, F. [state] => CT )

Going from this to the SQL query is what is giving me trouble. I was hoping there might be some simple means (either by some variation of PHP's join() or http_build_query()) to build the query, but nothing seems to work how it needs to and I'm pretty lost for ideas even after searching.

Not sure if it would require some messy loops, if there is a simple means, or if the way I'm going about trying to accomplish my goal is wrong, but I was hoping someone might be able to help out. Thanks in advance!

Edit: To clarify, sometimes the inputs could be empty (as in the case here, congress and nomination_received_by_senate), and I'm hoping to accommodate this in the solution. And yes, I intend to implement means to avoid SQL injection. I have only laid out the basics of my plan hoping for some insight on my methods.

To quote an unknown source: "Holy SQL injection, Batman!"

Ben
– Ben

2010-10-15 06:18:25 +00:00
Commented Oct 15, 2010 at 6:18 — Ben
– Ben, Commented Oct 15, 2010 at 6:18

Jacob Relkin · Accepted Answer · 2010-10-15 06:38:28Z

5

You could build up your query string like this if your GET params match your db fields:

$field_array = array('president', 'congress', 'nomination_received_by_senate', 'state');
$query = 'SELECT `name` FROM `nominations` WHERE ';
$conditions = array();
foreach($field_array as $field) {
   $value = $_GET[$field];
   if(empty($value)) continue;
   $condition = mysql_real_escape_string($field) . '` = ';
   $quote = '';
   if(!is_numeric($value)) $quote = '"';
   $condition .= $quote . mysql_real_escape_string($value) . $quote;
   $conditions[] = $condition; 
}

$query .= implode(' AND ', $conditions) . ';';

//perform query here...

edited Oct 15, 2010 at 6:38

answered Oct 15, 2010 at 6:23

Jacob Relkin

163k34 gold badges351 silver badges321 bronze badges

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

Comments

Nandit Tiku · Accepted Answer · 2010-10-15 06:25:45Z

0

To access the $_GET variables you could just do $_GET["president"] and $_GET["state"] and get the information. Make sure you sanitize the input.


$president = sanitize($_GET["president"]);
$state = sanitize($_GET["state"]);

$result = mysql_query("SELECT name FROM nominations WHERE president='".$president."' AND state='".$state"'");

Sanitize would be a function like mysql_real_escape_string() or your own function to clean up the input. Make sure you check if the $_GET variables are set using the isset() function.

answered Oct 15, 2010 at 6:25

Nandit Tiku

5933 silver badges5 bronze badges

Collectives™ on Stack Overflow

(PHP) MySQL select query with array from $_GET

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related