View Full Version : Selectively building an SQL query

01-11-2006, 11:32 AM
I have a form that has 4 select boxes out of which the first one is mandatory and other 3 may or may not be selected by the user. If one or more of them is selected the searching (or rather viewing) gets more specific according to that criterea.

My problem is how you build the SQL for this kind of thing ? My problem is further complicated by the fact that if the user selects one or more selectives at most two tables have to be joined to get the results. So I have to get these tables to the from clause as well as in the where clause and for joining.

Any pointers or ideas would be very much appreciated,

Thanks all.

01-11-2006, 11:44 AM
some php, the selects give you values for your variables

use some logic to supply you with whatever building blocks you need and then construct the query

01-11-2006, 02:00 PM
I'm doing something like this on a database search page (here (http://www.napathon.net/MusicDBSearch.php)). Depending on the number of elements a user searches for, I use any one of about four or five queries that are all pre-built except for the where clause. I build the where clause using some fairly extensive logic in a PHP script.

There's really no easy way to do this. Just brute force and a lot of judiciously applied code.

01-11-2006, 02:46 PM
You can do it with if statements or Select Case

Here is a basic example

$sql = "SELECT * FROM table WHERE Gender = '" . $_POST['Gender'] . "'";

if($_POST['Status'] != 'Choose') {
$sql .= " AND Status = '" . $_POST['Status'] . "'";
if($_POST['Location'] != 'Choose'){
$sql .= " AND Location = '" . $_POST['Location'] . "'";
$sql .= " AND Photo=1";
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<title>Untitled Document</title>

<form name="form1" method="post" action="">
<select name="Gender" id="Gender">
<option value="Male">Male</option>
<option value="Female">Female</option>
<select name="Status" id="Status">
<option value="Choose">Choose Status</option>
<option value="Single">Single</option>
<option value="Married">Married</option>
<select name="Location" id="Location">
<option value="Choose">Choose Location</option>
<option value="UK">UK</option>
<option value="US">US</option>
Has Photo
<input name="Photo" type="checkbox" id="Photo" value="1">
<input type="submit" name="Submit" value="Submit">
<p><?php if(isset($sql)){echo $sql;} ?></p>