[RESOLVED] How Should I Construct This Query?

rsmith

I have a form that allows users to set criteria through a combination of drop-down menus and checkboxes. Each item is stored into the array titled criteria[] and passed to my searchResults script. My question is, how would I construct the query that will be sent to MySQL so that the options selected are the columns that are looked at while those that aren't specified, do not get added to the query. Meaning if an option isn't selected to be included into criteria[], I don't want the absence of the option to halt my query, leave out qualified results, or include unqualified records.

rsmith

Anyone?

bradgrafelman

Can you show us an example of the data submitted? I understand the concept you're talking of, but I don't understand how the criteria is being POST'ed and whatnot.

rsmith

I haven't written the form yet. I always use POST when submitting form data so the variables will be retrieved that way. My big concern is lets say someone sets a specific number of beds as 2, but doesn't specify the number of baths, etc. How would the query look so that it doesn't return results that shouldn't be there and ones that should. To me the following would not produce the right records:

<?
      $beds = $_POST['beds']; //for this post assume 2.
      $baths = $_POST['baths']; //for this post assume value was not set.

  $query = "SELECT * FROM Properties WHERE beds = '".$beds."' AND baths = '".$baths."'";
  $result = mysql_query($query);
  $info = mysql_fetch_assoc($query);
?>

In my mind this will return 0 results because no properties will have 0 baths and any # of beds. I hope this helps in aiding you guys to help me!

TIA!

Piranha

Something like below should work:

<?
      $beds = $_POST['beds']; //for this post assume 2.
      $baths = $_POST['baths']; //for this post assume value was not set.

  $query = "SELECT * FROM Properties WHERE 1";
  if (empty($beds)) $query = $query . " and beds = '".$beds."'";
  if (empty($baths)) $query = $query . " AND baths = '".$baths."'";
  $result = mysql_query($query);
  $info = mysql_fetch_assoc($query);
?>

I hope that the code above was only an example. If not you should be sure to protect the code from sql injection.

rsmith

Yes, it is only an example. The real form is much larger and I don't have access to it while I'm traveling. This goes back to my original question though, won't such a query return 0 records because it's looking for 2 criteria, only 1 of them being matched? Meaning it finds properties with 2 beds, but it won't find any with 2 beds and 0 baths, and therefore returning a result of 0 records?

bradgrafelman

I would suggest changing if(empty($baths)) to if($baths != '').

Then, the answer to your question would be no, it would return the correct results. I believe Piranha made a typo and meant to say if(!empty($baths)).

If you did something such as:

      $query = "SELECT * FROM Properties WHERE 1";
      if ($beds != '') $query = $query . " and beds = '".$beds."'";
      if ($baths != '') $query = $query . " AND baths = '".$baths."'";

then the query would only be appended with the WHERE conditions if they specified a number.

As Piranha noted, you should also be concerned with SQL injection and use a function such as [man]mysql_real_escape_string/man on all user-supplied data in your SQL queries. In this case, if the variables are going to be numbers, you could use [man]intval/man (or cast them to an integer) instead (and in which case you would get rid of the quotes in your SQL query - numeric values shouldn't be quoted, only strings).

rsmith

I got it working the way I want. Here's my solution for anyone that may have a similar problem:

<?
	//Get the type and determine the propery queries.
		$type = $_GET['type'];

	if($type == "criteria"){
		$city = $_GET['city'];
		$beds = $_GET['beds'];
		$baths = $_GET['baths'];
		$sleeps = $_GET['sleeps'];
		$garage = $_GET['garage'];
		$view = $_GET['view'];

		$query = "SELECT id, title, description, listingPhoto, mapReference, propertyCode, city, state, rating FROM Properties WHERE active = 'Yes'";

			if(!empty($city)){
				$query .= " AND city = '".$city."'";
			}
			if(!empty($beds)){
				$query .= " AND beds = '".$beds."'";
			}
			if(!empty($baths)){
				$query .= " AND baths = '".$baths."'";
			}
			if(!empty($sleeps)){
				$query .= " AND sleeps = '".$sleeps."'";
			}
			if(!empty($garage)){
				$query .= " AND garage = '".$garage."'";
			}
			if(!empty($view)){
				$query .= " AND view = '".$view."'";
			}

		$query .= " ORDER BY rating";
		$result = mysql_query($query);
	}
?>

Thanks to all who helped!

laserlight

More like:

<?php
//Get the type and determine the propery queries.
if (isset($_GET['type']) && $_GET['type'] == 'criteria') {
	$query = "SELECT id, title, description, listingPhoto, mapReference, propertyCode, city, state, rating
	          FROM Properties WHERE active = 'Yes'";

if (!empty($_GET['city'])){
	$query .= " AND city = '" . mysql_real_escape_string($_GET['city']) . "'";
}

if (!empty($_GET['beds'])){
	$query .= " AND beds = '" . mysql_real_escape_string($_GET['beds']) . "'";
}

if (!empty($_GET['baths'])){
	$query .= " AND baths = '" . mysql_real_escape_string($_GET['baths']) . "'";
}

if (!empty($_GET['sleeps'])){
	$query .= " AND sleeps = '" . mysql_real_escape_string($_GET['sleeps']) . "'";
}

if (!empty($_GET['garage'])){
	$query .= " AND garage = '" . mysql_real_escape_string($_GET['garage']) . "'";
}

if (!empty($_GET['view'])){
	$query .= " AND view = '" . mysql_real_escape_string($_GET['view']) . "'";
}

$query .= " ORDER BY rating";
$result = mysql_query($query);
}
?>

Try to spot the differences. Notable among them are the use of <?php instead of <?, the fixing of the PHP strings to be "'" instead of "', the proper use of isset and empty, the use of mysql_real_escape_string(), and the better indentation.

rsmith

Thanks. I went back and added the mysql_real_escape_string() to all my variables!