Setting up MySQL queries with multiple form elements?

[Agtronic]

Hey guys and gals,

 

Here's a question I have been searching an answer to for a long time. I just can't find any specific information.

 

What I have is a search form with 9 fields. The search form is setup so that the more information the user puts in, the more specific the search.

 

My problem is, I don't know of a way to properly structure the query so that it is always valid.

 

I currently have it set up this way :

 

<?php

			$query = "SELECT * FROM profiles WHERE " ;

			if ($_POST['firstname'])
			{
				$firstname = addslashes(trim($_POST['firstname'])) ;
				$query .= "FirstName='$firstname'" ;
			}

			if ($_POST['lastname'] && $_POST['firstname'])
			    $query .= " AND " ;

			if ($_POST['lastname'])
			{
				$lastname = addslashes(trim($_POST['lastname'])) ;
				$query .= "LastName='$lastname'" ;
			}

			if ($_POST['username'])
			{
				$username = addslashes(trim($_POST['username'])) ;
			  	$query .= "UserName='$username'" ;
			}

?>

 

It's obviously a very poor way setting up the query, and will take a long time for nothing. Is there a more elegant way of adding the "AND" statement ONLY if there is already a WHERE statement? It seems so simple and so obvious at first, then when I start to think of possible solutions I just get lost in my own thoughts.

 

Any help would be greatly appreciated!

 

Thanks for your time!

[Jeremysr]

Try this:

 

<?php
// trim and add slashes to all form fields
foreach ($_POST as $key => $value) {
$_POST[$key] = addslashes(trim($value));
}

if ($_POST['firstname']) { $firstname = "FirstName='".$_POST['firstname']."'"; } else { $firstname = "true"; }
if ($_POST['lastname']) { $lastname = "LastName='".$_POST['lastname']."'"; } else { $lastname = "true"; }
if ($_POST['username']) { $username = "UserName='".$_POST['username']."'"; } else { $username = "true"; }

$query = "SELECT * FROM profiles WHERE $firstname AND $lastname AND $username";
?>

[Agtronic]

Whoa. That part you use to filter all data is awesome! I didn't know how to do that!!

 

So, essentially, if the person doesn't put anything into "firstname" or "lastname" and only puts something into "username", all others will be "true"?

 

The query will therefor actually look like:

 

"SELECT * FROM profiles WHERE true AND true AND UserName='Agtronic'" ;

 

Is this right? And this is a valid mysql query?

 

Thank you so much for your time!!

 

[Agtronic]

Just wanted to say thanks, it worked perfectly! It's something I've been trying to get for a long time, so I'm very thankful for your information!

 

Take care!

[kenrbnsn]

Here is another method...

 

<?php
$qtmp = array();
foreach ($_POST as $k => $v) {
     switch ($k) {
            case 'firstname':
            case 'lastname':
            case 'username':
                   if (strlen(trim(stripslashes($v))) != 0)
                          $qtmp[] = ucwords($k) . " = '" . mysql_real_escape_string(stripslashes($v)) . "'";
                   break;
      }
}
$q = "SELECT * FROM profiles WHERE " . implode(' and ',$qtmp);
?>

 

This will create a query with just those fields which are not blank.

 

Ken