ansaurus

Question

PHP: Coding "advanced search" the best way

Answer 1

A:

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'

Jason McCreary 2010-10-02 13:48:30

Answer 2

A:

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.

ITroubs 2010-10-02 13:50:07

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 2010-10-02 13:54:13

it is just a code snippet. normaly i initialize all my variablse. $andArr = array(); would be before my $andArr[] = ...;

ITroubs 2010-10-02 14:04:15

What does implode do? if i echo the string, would it be gender = $gender AND age between $from and $to ?

Johnson 2010-10-02 14:20:05

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 2010-10-02 14:26:19

Answer 3

A:

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);

phihag 2010-10-02 13:50:50

if i just add "WHERE 1" at the start $query in the example i got above, does it allow sql injections?

Johnson 2010-10-02 13:56:46

my registerglobal is off

Johnson 2010-10-02 13:57:45

@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 2010-10-02 14:01:08

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 2010-10-02 14:06:21

prepared statements and/or an input validation and/or an inputtransofmation method like mysql_escape_string

ITroubs 2010-10-02 14:06:39

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 2010-10-02 14:13:32

@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 2010-10-02 14:14:33

@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 2010-10-02 14:17:55

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 2010-10-02 14:23:06

last question before accept?

Johnson 2010-10-02 17:42:44

@user457827 Sorry, didn't activate notifications for this question. Yes, that will be fine.

phihag 2010-10-03 17:23:23

Answer 4

A:

you should definately check out lucene and solr

Joe Hopfgartner 2010-10-02 19:43:28

ansaurus

tags:

views:

answers:

PHP: Coding "advanced search" the best way

related questions