Adding extra conditions to a PDO query

Hello, thank you in advance for any advice.

I have a query (within a function) where I would like to pass an extra condition into the query and supply both field and BIND the value.
Note: This is part of an old mysql site and using mysql it was rather simple where I would pass either a ZERO or the extra condition to the function, for example:

"AND id != '$Blogid'"

I would then pickup this condition as $extra in the function and using…

$extracondition = ($extra!="0" ? " $extra" : '');

…I would set the variable $extracondition, which is placed in the query.

NOW that I’m switching this to PDO I’m not sure how I can change my query and bind the value parameter while supplying the field and condition as before.


	$extracondition = ($extra!="0" ? " $extra" : '');	
	$query = $db->prepare("SELECT id,BlogName FROM `blogs`
	WHERE online = :online
	AND inn_id = :inn_id
	$extracondition
	ORDER BY id DESC LIMIT :LIMIT");
	$online=1;
	$query->bindParam(":online", $online);
	$query->bindParam(":inn_id", $inn_id);
	$query->bindParam(":LIMIT", $limit);
	
	$query->execute();  

Note This function could be called from a number of pages and so my hope would be to fix it here rather than changing how it’s called from these other pages.

It certainly will work without binding any perimeters but hoping someone with a little more PDO knowledge can offer a suggestion.

	$extracondition = ($extra!="0" ? " $extra" : '');	
	$query = $db->prepare("SELECT id,BlogName FROM `blogs`
	WHERE online = $online
	AND inn_id = $inn_id
	$extracondition
	ORDER BY id DESC LIMIT $limit");
	$query->execute(); 
    

    $query = $db->prepare("SELECT id, BlogName FROM `blogs` WHERE online = :online AND inn_id = :inn_id AND extra = :extra ORDER BY id DESC LIMIT :LIMIT");


    $online=1;
    $query->bindParam(":online", $online);
    $query->bindParam(":inn_id", $inn_id);
    $query->bindParam(":extra",$extracondition);
    $query->bindParam(":LIMIT", $limit); 
    $query->execute();


Hi Drummin,

What about doing something like this?


$values = array(
    ":online" => $online,
    ":inn_id" => $inn_id,
    ":LIMIT"  => $limit
);

$sql = "SELECT id,BlogName FROM `blogs`
WHERE online = :online
AND inn_id = :inn_id";

if ($optional_cond) {
    $sql .= $optional_cond['field'];
    $values[$optional_cond['param']] = $optional_cond['value'];
}

$sql .= "ORDER BY id DESC LIMIT :LIMIT";

$query = $db->prepare($sql);
$query->execute($values);

Then you would pass an array into your function as an optional argument, that looked something like this:


$optional_cond = array(
    'field' => 'AND id != :blog_id',
    'param' => ':blog_id',
    'value' => 5
);

That would give you flexibility to pass in whatever optional parameter you need, and the value still gets bound as part of the prepared statement.

Thank you both for the replies.

ColinHughes thanks but that will escape the whole string and work in this case.

Fretburner, I like how you broke it down into an array which identifies the field and condition, parameter and value. That will work, though I’m going to need to change every page that calls this function. It’s doable.

RELATED QUESTION:
Seems :LIMIT is being quoted causing query to fail. Any ideas?

Hmm, yeah… I’ve just been browsing the manual and it appears that when you pass params into execute() as an array, they get treated as string types. To make it work, my example would have to change like this:


$sql = "SELECT id,BlogName FROM `blogs`  
WHERE online = :online  
AND inn_id = :inn_id";

if ($optional_cond) {
    $sql .= $optional_cond['field'];
}

$sql .= "ORDER BY id DESC LIMIT :LIMIT";    

$query = $db->prepare($sql);

$query->bindParam(":online", $online); 
$query->bindParam(":inn_id", $inn_id); 
$query->bindParam(":LIMIT", $limit, PDO::PARAM_INT);

if ($option_cond) {
    $query->bindParam($optional_cond['param'], $optional_cond['value']); 
}

$query->execute();

See how with the line $query->bindParam(":LIMIT", $limit, PDO::PARAM_INT); we’re explicitly passing the type of variable to be bound as an integer, so it should work in the LIMIT clause.

What arguments are normally passed into the function when there’s an optional condition? It might be possible to work with them inside the function so you don’t have to change all your calls to the function throughout the app.

This is as much an mental exercise to “do it right”, while making this PDO conversion as it is to find out what can be done. It works without Binding any parameters.

I needed to add a space after the parameter :inn_id to space things out (no biggie) and found when Binding the optional condition I needed to wrap the parameter in quotes so I made it into a variable first. I’ve not been able to get query results while Binding the Limit variable.

$optional_cond = array(
    'field' => 'AND id != :blog_id',
    'param' => ':blog_id',
    'value' => 5
);	
	$sql = "SELECT id,BlogName FROM `blogs`
WHERE online = :online
AND inn_id = :inn_id ";

if ($optional_cond) {
    $sql .= $optional_cond['field'];
}

//$sql .= " ORDER BY id DESC LIMIT :LIMIT";

$query = $db->prepare($sql);

$query->bindParam(":online", $online);
$query->bindParam(":inn_id", $inn_id);
//$query->bindParam(":LIMIT", $limit, PDO::PARAM_INT);

if ($optional_cond) {
	$param = "{$optional_cond['param']}";
	$value = "{$optional_cond['value']}";
    $query->bindParam("$param", $value);
}

$query->execute();

It will work without Binding the limit but that’s not fulfilling the goal of this exercise to Bind all values passed to query.

$sql .= " ORDER BY id DESC LIMIT $limit";

This function (called from any blog pages) just builds a link list of blogs other than the one being viewed. It is located within a common “includes” file. So really in this case the Optional Condition will always be the same. There are other functions in this includes file where the optional condition is not set in stone so using this technique will work. Just need to either not Bind the limit or find a solution for it. Thanks for your help.

GOT IT!

$query->bindValue(":LIMIT", (int) $limit, PDO::PARAM_INT);