conditional search fields

[estrellas]

another question ....
i'm having the user input certain search fields from a typical html form .. passing them in with $_POST (<--php stuff) values .. and then i'm stuck at writing my query ...

what i have now is..
$query = "SELECT * FROM networks WHERE name='$name'
and latitude='$latitude'
and longitude='$longitude'
and elevation='$elevation'";

when i input the name to search for it doesn't return any answers becasue the other fields are null from the input field ...

can someone help me with writing the query for optional conditions to search for (if that is even a thing) ... or just clarify if what i'm doing is totally dumb...

thanks much in advance

If programming were as fun as cycling, then we'd need more bike mechanics.

 

[cLFlaVA]

estrellas -

This should have probably been in the php forum, but this is what you'd do:

$sql = "SELECT * FROM networks WHERE ";
if (isset ($names))
    $sql .= " name = '$name' AND ";
if (isset ($latitude))
    $sql .= " latitude = '$latitude' AND ";
if (isset ($longitude))
    $sql .= " longitude = '$longitude' AND ";
if (isset ($elevation))
    $sql .= " elevation = '$elevation' AND ";

// Then you'll have to strip the last and

$sql = left($sql, strlen($sql)-4);

*cLFlaVA
----------------------------
A polar bear walks into a bar and says, "Can I have a ... beer?"
The bartender asks, "What's with the big pause?

 

[Bastien]

You need to build the sql dynamically. Validate the data so that it has appropriate values (unset them if not valid entries, then they won't be included in the query)

$query = "SELECT * FROM networks WHERE name='$name'";
if (isset($latitude)){
   $query .= " and latitude='$latitude' ";
}
if (isset($longitude)){
   $query .= " and longitude='$longitude' ";
}
if (isset($elevation)){
   $query .= " and elevation='$elevation'";
}

mysql_query($query);

Bastien

Cat, the other other white meat

 

[estrellas]

awesome thank you so much guys ... (sorry about it being in the wrong forum, i wasn't sure which one it should be in)

that makes more sense about what i'm trying to do .. one question though .. what does this do exactly? .. strip off the null characters?

$sql = left($sql, strlen($sql)-4);

If programming were as fun as cycling, then we'd need more bike mechanics.

 

[cLFlaVA]

If you utilize Bastien's way, you shouldn't need it. This is a line of code that removes the final " AND " from the SQL the way my code generated it.

If you're utilizing Bastien's code though, watch out for the hairballs.

*cLFlaVA
----------------------------
A polar bear walks into a bar and says, "Can I have a ... beer?"
The bartender asks, "What's with the big pause?

 

[estrellas]

$sql = left($sql, strlen($sql)-$strlen('hairballs');

got that taken care of

-------

http://www.letour.fr

 

[estrellas]

it seems that my code isn't reading the isset() function .. maybei have something set up wrong ...
here's the html form

	<form method="post" action="select.php">
			<table>
			<tr><td>Name: </td><td><input type="text" name="name"></td></tr>
			<tr><td>Latitude: </td><td>&le;&nbsp;&nbsp;<input type="radio" value=" < " name="la_size">&nbsp;&nbsp;&nbsp;&nbsp;&ge;&nbsp;&nbsp;<input type="radio" value=" > " name="la_size"></td><td><input type="text" name="latitude"></td></tr>
			<tr><td>Longitude: </td><td>&le;&nbsp;&nbsp;<input type="radio" value=" < " name="lo_size">&nbsp;&nbsp;&nbsp;&nbsp;&ge;&nbsp;&nbsp;<input type="radio" value=" > " name="lo_size"></td><td><input type="text" name="longitude"></td></tr>
			<tr><td>Elevation: </td><td>&le;&nbsp;&nbsp;<input type="radio" value=" < " name="e_size">&nbsp;&nbsp;&nbsp;&nbsp;&ge;&nbsp;&nbsp;<input type="radio" value=" > " name="e_size"></td><td><input type="text" name="elevation"></td></tr>
<input type="submit" value="Submit Query">
</form>

and select.php
[code]
	$name=$_POST['name'];
	$la_size=$_POST['la_size'];
	$latitude=$_POST['latitude'];
	$lo_size=$_POST['lo_size'];
	$longitude=$_POST['longitude'];
	$e_size=$_POST['e_size'];
	$elevation=$_POST['elevation'];

	$query = "SELECT * FROM networks WHERE ";
	if(isset($name)){
		$query .= "name=%$name% and ";
	}
	if(isset($la_size) && isset($latitude)){
		$query .= "latitude $la_size= '$latitude' and ";
	}
	if(isset($lo_size) && isset($longitude)){
			$query .= "longitude $lo_size= '$longitude' and ";
	}
	if(isset($e_size) && isset($elevation)){
			$query .= "elevation $e_size= '$elevation' and ";
	}

//next line doesn't work but not worried about it yet
//$query = left($query, strlen($query) -4);

cool thanks

-------

http://www.letour.fr

 

[estrellas]

actually i figured it out ... i can't change the isset because i'm setting all the values when i create variable names in them from the $_POST values ... so instead of checking isset() i did

if(!empty($name)){do something}

thanks again for your help guys you're great!

-------

http://www.letour.fr