Bind parameters

249 Posts

cyclissmo Reply #1, 15 years, 6 months ago

I’m having some trouble implementing (or understanding) how to bind parameters to prepared statements.

Assuming TableA, TableB, and TableC have schemas and models defined, but no relationships:

$q = $modx->newQuery('TableA');
$q->select('TableA.*, TableB.*, TableC.*');
$q->leftJoin('TableB', 'TableB', array(
	'TableC.someid:IN' => ':str1',
	'TableC.somekey:=' => $somekey
));
$q->prepare();
echo $q->toSQL();

My goal is to substitute :str1 with the string ’TableB.csvids’ (a comma-delimited list of IDs).

I’ve tried different kinds of bindings, including:

$q->leftJoin('TableB', 'TableB', array(
	'TableC.someid:IN' => ':str1',
	'TableC.somekey:=' => $somekey
), null, array(':str1', 'TableB.csvids'));

$q->bind(':str1', 'TableB.csvids');

$q->prepare(array(':str1', 'TableB.csvids'));

But the echo’ed SQL statement always shows [tt]:str1[/tt] being parsed as 0. Ideally I would like to apply this query to a [tt]getObject[/tt] or [tt]getCollection[/tt] rather than implicitly execute the statement.

What am I missing? huh

lo9on.com

MODx Evolution/Revolution | Remote Desktop Training | Development

MODX Staff
10,725 Posts

opengeek Reply #2, 15 years, 6 months ago

You are missing the fact that PDO cannot bind parameters to an IN clause. Pass an array of values to query and xPDO can handle it however.

Jason Coward
Chief Architect @ MODX
http://www.jasoncoward.com | http://twitter.com/drumshaman | https://github.com/opengeek

249 Posts

cyclissmo Reply #3, 15 years, 6 months ago

But I assumed that you could bind parameters to an IN clause, with limitations - per PHP documentation:
Quote from: PDO::prepare

You cannot bind multiple values to a single named parameter in, for example, the IN() clause of an SQL statement.

But then again I’m probably interpreting the docs incorrectly. PDO has been a new adventure for me and I’m learning as I go.

Thanks for the guidance, now off I go to correct my code laugh

lo9on.com

MODx Evolution/Revolution | Remote Desktop Training | Development