Pull database rows according to multiple search criteria

mysql database

[kuper20]

Hello,

So I have a mysql database consisting of rows of people with information. I want to have some sort of form consisting of multiple search boxes where I can search for 1 or more of the fields pertaining to the people and get only the resulting rows. Pretty much what I want is exactly what phpmyadmin does, but I want it on my website instead of on the back end. I ran into a problem when I realized that for every combination of search criteria I specify the SELECT statement getting the results has to be different. I was thinking about having a bunch of if statements where I would concatenate another part of the sql code before I sent it to mysql, but that's messy and I'm wondering if there's another way. Here would be what the code would kind of look like:

$sql = "SELECT * FROM player_info";

if( isset($searchName) )
{
$sql .= " WHERE Name LIKE \"%$searchName%\"";
}

Of course the previous statement would also need more if statements to see which one to put the WHERE rather than the AND keywords.

Also, as a security concern, I think I may be going about searching in the wrong way. I have it like this:

$sql = "SELECT * FROM player_info
WHERE Name LIKE \"%$searchName%\"";

I have a feeling this isn't such a good way to do it since someone can write something in the search form of the page before and do something I don't want.

Any information would be greatly appreciated. Thanks guys.

[coopster]

You can preload your WHERE clause with a default value of true and then append to it if necessary. Also, you are correct, you should bare minimum escape any string that you intend to use in your query. You are better off edit-checking it first too.

$where = 'WHERE 1=1'; 
if (isset($_POST['searchName'])) { 
 $searchName = mysql_real_escape_string [php.net]($_POST['searchName']); 
 $where .= " AND Name LIKE \"%$searchName%\""; 
} 
$sql = "SELECT * FROM player_info $where";

[kuper20]

hmm, very tricky...I like it. Thanks a bunch