PHP: Coding "advanced search" the best way

Question

Alright, I would like some advice on how to code an advanced search for the profiles the right way.

The "advanced" search contains:

Gender   female/both/male
Search in    All/latest profiles 1/2/7/14/32 days ago
Online: Yes(checked)
Age (xx) to (xx) years (xx means you can write e.g 12 and 18)
Cities: all/city1/city2/city3

There's a lot of criteria that you can pick and choose.

I would like to know how should I code this the most effective way, with the least code repetition. Should I build a query from what the user chooses? Example:

$query = "SELECT * FROM users WHERE"
if(!empty($gender){ // if its empty, then the user chose both..
    $query .= "gender = $gender";
}
....

Or are there any better solutions? I think doing this will result in problems, because if an user doesn't choose any gender, and another if $online (example) starts with AND , it will result WHERE AND ..

score 0 · Answer 1 · answered Oct 02 '10 at 13:48

I think your solution is a good start. Continue checking your advanced search fields and built out your SQL where clause as necessary.

The problem will come if you need to do fuzzy logic - Males OR City1. But you haven't indicated that in your OP.

To avoid the WHERE AND just start your where clause with a true expression:

$query = "SELECT * FROM users WHERE 1"

This will always evaluate to true and when you add more conditions they will append correctly - SELECT * FROM users WHERE 1 AND city = 'city1'

score 0 · Answer 2 · edited Oct 02 '10 at 13:53

0

The "and" problem is pretty easy to solve:

$andArr[] = "gender = $gender";
$andArr[] = "age between $from and $to";
...

$adsString = implode(' AND ', $andArr);

For the gender i would use a dropdown with "both" as preselected.

edited Oct 02 '10 at 13:53

phihag

278,196
72
453
469

answered Oct 02 '10 at 13:50

ITroubs

11,094
4
27
25

Please don't use uninitialized variables like andArr. Additionally, I'm pretty there shouldn't be any braces after $andArr in the final line. Edited. – phihag Oct 02 '10 at 13:54
it is just a code snippet. normaly i initialize all my variablse. $andArr = array(); would be before my $andArr[] = ...; – ITroubs Oct 02 '10 at 14:04
What does implode do? if i echo the string, would it be gender = $gender AND age between $from and $to ? – Johnson Oct 02 '10 at 14:20
it takes an array and glues it with the glue string. so it puts the " AND " between every array field and puts it together to one string. – ITroubs Oct 02 '10 at 14:26

score -1 · Answer 3 · edited May 23 '17 at 12:20

-1

Please do not write code that allows SQL injections. Also, make sure your php code runs with register_globals set to off. Apart from that, I don't think there's any reason why it wouldn't work that way.

So here is a simple example, using PDO:

$query = 'SELECT * FROM users WHERE 1';
$params = array();
if (!empty($_POST['gender'])) {
    $query .= ' AND gender=:gender'
    $params[':gender'] = $_POST['gender'];
}
...
$st = $pdo->prepare($query);
$result = $st>execute($params);

edited May 23 '17 at 12:20

Community

1
1

answered Oct 02 '10 at 13:50

phihag

278,196
72
453
469

1

if i just add "WHERE 1" at the start $query in the example i got above, does it allow sql injections? – Johnson Oct 02 '10 at 13:56
@user457827 I just assumed that $gender would be the direct user input (and that requires register_globals). If you do some preprocessing anyways, it is safe IF that preprocessing is correct. Basically the only way to make sure it is that is to use a safe function such as intval or check it vs a hardcoded list of possible values. Since it's so easy to screw up there, I'd use prepared statemtents all the time. – phihag Oct 02 '10 at 14:01
prepared statements, how does that work? Im new to this. And about example above looks advanced, you use PDO, and so, does the $result then work just like normal (so you could do $mysql_num_rows($result) and fetch_array($result) ? ) – Johnson Oct 02 '10 at 14:06
prepared statements and/or an input validation and/or an inputtransofmation method like mysql_escape_string – ITroubs Oct 02 '10 at 14:06
So wouldnt I be alright if I use my example above with WHERE 1 and make all the inputs mysql_escape_string. What could possible make it vuln. to sql injection – Johnson Oct 02 '10 at 14:13
@user457827 I suggest your read up on prepared statements at http://php.net/manual/en/pdo.prepared-statements.php . PDO is way nicer than the old database-specific functions. The analogon to mysql_num_rows($result) (without a dollar, since it's not a variable) is $result->rowCount(), and you fetch using $result->fetch() (one row at a time) or $result->fetchAll() (all the rows at once). You can find more about PDO at http://www.php.net/manual/en/book.pdo.php . – phihag Oct 02 '10 at 14:14
@zser457827 mysql_escape_string does not do what you would expect (safely escape a string). See the big fat warning at http://php.net/manual/en/function.mysql-escape-string.php . You are looking for mysql_real_escape_string which does protect you from SQL injections. – phihag Oct 02 '10 at 14:17
I ment the real_escape_string, i know theres a big difference. I will look and read about PDO, but doing it the "old database-specific functions" way, will i be alright with mysql_real_escape_string the user inputs? – Johnson Oct 02 '10 at 14:23
@user457827 Sorry, didn't activate notifications for this question. Yes, that will be fine. – phihag Oct 03 '10 at 17:23

PHP: Coding "advanced search" the best way

3 Answers3