MySQL search for anything in a few columns

[trymbill]

Hi every one!

I've got a little bit of a problem. I have a page where users can search for women in a directory I have. They should be able to type in a firstname, lastname, country, jobtitle, jobtype and some other things. Each item (firstname, lastname etc.) has it's own input field. As for now I have a LARGE if-elseif-else script that checks if there was something written for each and every column and then checks mysql with a mysql_query.

Example:


if($_POST[firstname] != '' and $_POST[lastname] == '' and $_POST[country] == '' and $_POST[jobtitle] == '' and $_POST[jobtype] == '') {
$query = mysql_query("select * from users where firstname LIKE '%$_POST[firstname]%' and active='1' and email_verified='1' order by firstname");
} elseif($_POST[firstname] == '' and $_POST[lastname] != '' and $_POST[country] == '' and $_POST[jobtitle] == '' and $_POST[jobtype] == '') {
$query = mysql_query("select * from users where lastname LIKE '%$_POST[lastname]%' and active='1' and email_verified='1' order by firstname");
} elseif($_POST[firstname] != '' and $_POST[lastname] != '' and $_POST[country] == '' and $_POST[jobtitle] == '' and $_POST[jobtype] == '') {
$query = mysql_query("select * from users where firstname LIKE '%$_POST[firstname]%' and lastname LIKE '%$_POST[lastname]%' and active='1' and email_verified='1' order by firstname");
}


My question is: How can I put this all into a small function or a small mysql_query? I can't just search for each item / column with 'LIKE' because then it displays results that have nothing in their jobtitle or lastname and it messes with every thing :confused:

If some one could help me, it would be much appreciated!

Sincerely,
Magnus

[trymbill]

Do I need to explain the problem a bit more or does no one know how to accomplish this in a neat way?

[Mr Initial Man]

Hmmmm...

Try this:

$where = '';
$where_vals = Array();
$where_slot = 0;
if($_POST['firstname'] != ''){
$where_vals[$where_slot] = 'firstname LIKE %' . $_POST['firstname'] . '%';
$where_slot++;
}
if($_POST['lastname'] != ''){
$where_vals[$where_slot] = 'lastname LIKE %' . $_POST['lastname'] . '%';
$where_slot++;
}
if($_POST['country'] != ''){
$where_vals[$where_slot] = 'country LIKE %' . $_POST['country'] . '%';
$where_slot++;
}
if($_POST['jobtitle'] != ''){
$where_vals[$where_slot] = 'jobtitle LIKE %' . $_POST['jobtitle'] . '%';
$where_slot++;
}
if($_POST['jobtype'] != ''){
$where_vals[$where_slot] = 'jobtype LIKE %' . $_POST['jobtype'] . '%';
$where_slot++;
}

$where .= $where_vals[0];
for($i = 1; $i < count(where_vals); $i++){
$where .= ' AND ' . $where_vals[$i];
}

$query = mysql_query("select * from users where $where and email_verified='1' order by firstname")

I'm just using PHP to build a where clause; I've done it before.

You still get several if(){} statements, but if what you're saying is true, it should be less than what you're using, and they're easier to read to boot.

What this code does is say "If this field has something in it, add its value to the where clause, otherwise ignore it and check the next field."

You might even be able to get away with shortening the if conditions to something like if($_POST['firstname']){}, as I believe an empty field tests as false.

[bokeh]

$where = '';
$where_vals = Array();
$where_slot = 0;
if($_POST['firstname'] != ''){
$where_vals[$where_slot] = 'firstname LIKE %' . $_POST['firstname'] . '%';
$where_slot++;
}
if($_POST['lastname'] != ''){
$where_vals[$where_slot] = 'lastname LIKE %' . $_POST['lastname'] . '%';
$where_slot++;
}
if($_POST['country'] != ''){
$where_vals[$where_slot] = 'country LIKE %' . $_POST['country'] . '%';
$where_slot++;
}
if($_POST['jobtitle'] != ''){
$where_vals[$where_slot] = 'jobtitle LIKE %' . $_POST['jobtitle'] . '%';
$where_slot++;
}
if($_POST['jobtype'] != ''){
$where_vals[$where_slot] = 'jobtype LIKE %' . $_POST['jobtype'] . '%';
$where_slot++;
}

$where .= $where_vals[0];
for($i = 1; $i < count(where_vals); $i++){
$where .= ' AND ' . $where_vals[$i];
}

$query = mysql_query("select * from users where $where and email_verified='1' order by firstname")

I'm just using PHP to build a where clause; I've done it before.

You still get several if(){} statements, but if what you're saying is true, it should be less than what you're using, and they're easier to read to boot.

What this code does is say "If this field has something in it, add its value to the where clause, otherwise ignore it and check the next field."

You might even be able to get away with shortening the if conditions to something like if($_POST['firstname']){}, as I believe an empty field tests as false.Looks complicated! How about this:$where = '';

$fields = array('firstname', 'lastname', 'country', 'jobtitle', 'jobtype');

foreach($fields as $field)
{
if(!empty($_POST[$field]))
{
$where .= "AND {$field} LIKE '%{$_POST[$field]}%' ";
}
}

$query = mysql_query("select * from users where email_verified='1' {$where} order by firstname");By the way you also need to clean the inputs with mysql_real_escape_string to prevent injection attacks.

[trymbill]

Thanks for the answers guys!

Regarding mysql_real_escape_string() ... isn't there any better / neater way to prevent SQL injection attacks?

Sincerely,
Magnus

[Mr Initial Man]

Looks complicated! How about this:
You're right. I tend to overcomplicate things. But, hey, I was suffering from insomnia, so my mind wasn't entirely clear. :-/

[bokeh]

Thanks for the answers guys!

Regarding mysql_real_escape_string() ... isn't there any better / neater way to prevent SQL injection attacks?

Sincerely,
MagnusIf you are using MySQL then you should use mysql_real_escape_string. No other function is written specifically for MySQL.