[drayarms]

I'm trying to create a search form where members of a site can search other members whose user information is stored in a mysql database. One of the search parameters is gender which searches for records from a column in the database called gender where members can either have the value "man" or "woman". The user can choose between 3 options from a pull down menu named gender (man, woman, both). Then I'm thinking about using a select query to search the database upon submission of the form, which goes something like this:

[syntax=php]
$sql= SELECT* FROM members WHERE gender ='{$_POST['gender']}' ;
[/syntax]

Now I can easily assign the value "man" for the option man in the pull down menu and "woman for the option woman, to match their corresponding names in the database. The problem lies with the "both" option which has to be either man or woman. I'm thinking about assigning it the value "man OR woman" so that when the form is submitted, the query would read: SELECT*FROM members WHERE gender ='{$_POST[man OR woman']};
I just don't know if this would be a right usage of the OR keyword and if such a query would work. Before trying it out, I'd like to know if this makes any sense and if not, what's an alternative way to work around this?

[Atli]

Hey.

If there are only two options, and the user chooses to select both, then there is not need to specify that column in the where clause. I mean:

/* This: */
SELECT * FROM members WHERE gender IN('man', 'woman');

/* Returns the same thing as this: */
SELECT * FROM members;

So the trick is not to have PHP add a WHERE clause to select both options, but to omit the where clause altogether.

Consider something like this:

<?php
$sql = "SELECT * FROM members";
if (isset($_POST['gender']) && $_POST['gender'] != 'both') {
    $sql .= sprintf(" WHERE gender = '%s'",
                mysql_real_escape_string($_POST['gender']));
}

P.S.
Note the use of the mysql_real_escape_string function there. You should never put user input into a SQL query without verifying it and running it through that function. Otherwise you risk opening yourself up to a SQL Injection attack.

This post has been edited by Atli: 24 May 2011 - 03:48 PM

[drayarms]

@Atli. Well I forgot to mention that there are other search parameters besides gender. Take for example lets say I had to search for gender AND interest. Then I must specify a WHERE clause for both columns right? What do I do in such a case?

[calebjonasson]

If there are more then one parameter you can always just break apart the sql string into multiple parts and include them where needed similar to how Atli did it above. Another way to do this is to find out what is needed in the where clause and throw each piece of code into an array. Once you know what is going to be added to the where clause you can then add in the appropriate sql operators and spaces where needed.

[baavgai]

Build your where. If you have multiple conditions, this allows you to check to see the state of the where.

e.g.

$sql = 'SELECT * FROM members';
$where = '';
if (isset($_POST['gender']) && $_POST['gender'] != 'both') {
	if ($where == '') { $where = ' WHERE '; } else { $where .= ' AND '; }
	$where .= sprintf("gender='%s'", mysql_real_escape_string($_POST['gender']));
}

if (isset($_POST['interest']) && $_POST['interest'] != 'all') {
	if ($where == '') { $where = ' WHERE '; } else { $where .= ' AND '; }
	$where .= sprintf("interest=%s", mysql_real_escape_string($_POST['interest']));
}

// ...
$sql .= $where . $order_by;

You can probably work up a nice function for this to avoid a lot of cut and paste code.

[drayarms]

@baavgai

Your code seems pretty complex. Would you be so kind to do a step by step explanation? That is basically convert it from PHP to English? I'm not familiar with some of the functions like sprintf and variables lik %s and the PHP manual is not pretty clear in explaining their usage.

[baavgai]

It's pretty straight forward. You're just checking if a post value exists, if it isn't the value for both, and adding to a current where clause.

Perhaps if we used a function...

function addWhere($where, $postName, $allValueName, $dbName) {
	// if the value isn't in post, return without change
	if (!isset($_POST[$postName]) { return $where; }
	
	$value = $_POST[$postName]; // grab the value from post

	// if the value is an everything value, return without change
	if ($value == $allValueName) { return $where; }

	// if we got this far, we know we're going to add something
	if ($where == '') { 
		// if $where is empty
		// then we start with the first statement
		// which is, well, where
		$where = ' WHERE '; 
	} else { 
		// if conditions already exist,
		// use and to tack on another condition
		$where .= ' AND ';
	}
	
	// add the field name
	$where .= $dbName;
	// add value
	$where .= "='" . mysql_real_escape_string($value);
	
	// return new and improved where
	return $where; 
}

// look at wheres
$where = addWhere('', 'gender', 'both', 'gender');
$where = addWhere($where, 'interest', 'all', 'interest');

// add to base sql
$sql = 'SELECT * FROM members' . $where;

Hope this helps.