Differences

This shows you the differences between two versions of the page.

--- v5:reference:connection:param [2018/07/21 02:08] – [Usage] mnewnham
+++ v5:reference:connection:param [2021/02/26 01:35] (current) – Call with falsy values: param(false) vs param(0) - see #682 dregad
@@ Line 13: / Line 13: @@
 This method is used with [[v5:userguide:learn_bind:bind_vars|bind variable SQL statement execution]], to produce injection resistant code.
+On RDBMS with positional (numbered) query parameters such as PostgreSQL, calling ''param()'' with a "falsy" value((equivalent to ''false''; read the //Converting to boolean// section in the [[https://www.php.net/manual/en/language.types.boolean.php|PHP manual]])) will reset the parameter counter, allowing to start building a new query without first executing the previous one.
+<WRAP tip>
+Note that calling ''param(false)'' **only** resets the counter, //without returning a placeholder//. This allows forcing a reset operation independently from building a query; the next invocation will return the first placeholder.
+Compare that with ''param(0)'' or ''param(%%''%%)'', which both resets **and** returns the first placeholder.
+This is illustrated in the examples below.
+</WRAP>
 ------------------------------
 ===== Usage =====
+This is an example of how to use param() to build SQL queries:
 <code php>
-/*
+$sql1 = 'SELECT * FROM accounts WHERE '
-* Connection to Oracle database
+    . 'name = '  . $db->param('account') . ' AND '
-*/
+    . 'total = ' . $db->param('amount');
-print $db->param('account');
-/*
+// Reset param count as a standalone operation then build the query
-* prints ':account'
+$db->param(false);
-*/
+$sql2 = 'SELECT * FROM accounts WHERE id = ' . $db->param('id');
+// Reset param count with a "falsy" value while building the query
+$sql3 = 'SELECT * FROM accounts WHERE '
+    . 'name = ' . $db->param(0) . ' AND '
+    . 'status = ' . $db->param(1);
 </code>
-<code php>
-/*
-* Connection to IBM DB2 database
-*/
+See below for the code's output with various database drivers.
-print $db->param('account');
-/*
+==== MySQL, IBM DB2 ====
-* prints ?
+<code>
-*/
+$sql1: SELECT * FROM accounts WHERE name = ? AND total = ?
+$sql2: SELECT * FROM accounts WHERE id = ?
+$sql3: SELECT * FROM accounts WHERE name = ? AND status = ?
 </code>
-<code php>
-/*
+==== Oracle (oci8) ====
-* Connection to Postgres database
+<code>
-*/
+$sql1: SELECT * FROM accounts WHERE name = :account AND total = :amount
-print $db->param('account');
+$sql2: SELECT * FROM accounts WHERE id = :id
-/*
+$sql3: SELECT * FROM accounts WHERE name = :0 AND status = :1
-* prints $1
+</code>
-*/
+==== PostgreSQL ====
+<code>
+$sql1: SELECT * FROM accounts WHERE name = $1 AND total = $2
+$sql2: SELECT * FROM accounts WHERE id = $1
+$sql3: SELECT * FROM accounts WHERE name = $1 AND status = $2
 </code>
+Without the parameter reset, //$sql2// would be ''... id = $3'' and //$sql3// ''... name = $4 AND status = $5'', which would result in errors when calling [[v5:reference:connection:execute|]].