...

View Full Version : Selectively building an SQL query



cen
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.

Tynan
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

vinyl-junkie
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.

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

Here is a basic example



<?php
if(isset($_POST['Submit'])){
$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'] . "'";
}
if(isset($_POST['Photo'])){
$sql .= " AND Photo=1";
}
}
?>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<title>Untitled Document</title>
</head>

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



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum