PDO

[elemelas]

I'm building a search module, and I want it to select from db relative fields, depending if user has filled them in:

$search = "id!=0";
if(!isset($country)) { $search .= " AND country=$country"; }
if(!isset($city)) { $search .= " AND city=$city"; }
if(!isset($sex)) { $search .= " AND sex=$sex"; }

So it passes to the sql appropriate search terms. It works no problem with mysql query:

$query = mysql_query("SELECT * FROM profiles WHERE $search");

However, when I'm trying with PDO, it doesn't work properly. Here's what I'm trying:

$query = $conn->prepare('SELECT * FROM profiles WHERE :search');
$query -> bindValue(':search', $search, PDO::PARAM_STR);
$query -> execute();

After looking into mysql log I see that PDO has addes quotes to the variable search:

SELECT * FROM profiles WHERE 'id!=0 AND sex=male';

..... which is i presume the problem. So, finally, real question: How to get rid of these quotes ? Or, has anyone got an alternative idea ?

[laserlight]

You should bind $country, $city and $sex individually.

[elemelas]

Yeah but every search will be different, some will have 1 parameter, some 3. I can't do one statement like that:

$query = $conn->prepare('SELECT * FROM profiles WHERE country=:country AND city=:city AND sex=:sex');

because of the case if someone didn't fill in one of these fields.

[laserlight]

elemelas wrote:

Yeah but every search will be different, some will have 1 parameter, some 3. I can't do one statement like that:

You could do something like this:

$search = 'id!=0';
$params = array('country', 'city', 'sex');
foreach ($params as $param) {
    if (isset($$param)) {
        $search .= " AND $param=:$param";
    }
}

$query = $conn->prepare("SELECT * FROM profiles WHERE $search");
foreach ($params as $param) {
    $query->bindValue(":$param", $$param, PDO::PARAM_STR);
}
$query->execute();

Though I suspect that you should be using $_POST[$param] instead of $$param.