How to query MySQL based on user selections

[lbrown]

I am trying to create a php script that will create a MySQL query based on criteria selected by the user from a form. The form has 9 different critereon the user can select from and I cannot figure out how to query the DB and return only the criteria the user has selected.

The user should be allowed to query any, all or any combination of the database fields. I am fairly new to PHP and I would greatly appreciate any help. ThanX

lbrown

[laserlight]

Use some way to obtain the criterion from the user, e.g. a drop down menu.

Then SELECT fieldname FROM tablename, where the fieldname corresponds to the criterion chosen.

[lbrown]

Originally posted by laserlight
Use some way to obtain the criterion from the user, e.g. a drop down menu.

Then SELECT fieldname FROM tablename, where the fieldname corresponds to the criterion chosen.

Thank you. I understand how the query works, but I cant understand quite how to plugin the variables without generating an error. For Example:

There are fields 1,2,3,4,5,6,7,8,9 and the user wants to select records matching only fields 1,3,5. I would use SELECT * FROM database WHERE .... = 1 AND ...=3 AND ...=5.

But if the user chooses to query only 1,5 am I not then left with an extraneous 'AND' statement? It is difficult to explain but I am being as clear as I possible can. It is sort of like an advanced search on Yahoo or Google where one may select multiple search criteria.

ThanX

[laserlight]

There are fields 1,2,3,4,5,6,7,8,9 and the user wants to select records matching only fields 1,3,5. I would use SELECT * FROM database WHERE .... = 1 AND ...=3 AND ...=5.

I think you're confusing field names and their contents here.

But if the user chooses to query only 1,5 am I not then left with an extraneous 'AND' statement?

Consider this:

$where = " WHERE"; //construct the WHERE clause
//array of field names as search criteria
$fieldnames = array("name", "surname", "gender", "logins");
//loop through the criteria
foreach ($fieldnames as $fieldname) {
	if (!empty($_GET[$fieldname]))
		$where .= " $fieldname='{$_GET[$fieldname]}'";
}
//construct the query
$query = "SELECT * FROM tablename WHERE $where";

[lbrown]

Thank you very much. This seems to work well. 🙂

Originally posted by laserlight
I think you're confusing field names and their contents here.


Consider this:

$where = " WHERE"; //construct the WHERE clause
//array of field names as search criteria
$fieldnames = array("name", "surname", "gender", "logins");
//loop through the criteria
foreach ($fieldnames as $fieldname) {
	if (!empty($_GET[$fieldname]))
		$where .= " $fieldname='{$_GET[$fieldname]}'";
}
//construct the query
$query = "SELECT * FROM tablename WHERE $where";

[/B]