{"id":1649,"date":"2017-03-22T15:14:33","date_gmt":"2017-03-22T09:44:33","guid":{"rendered":"https:\/\/www.wikitechy.com\/technology\/?p=1649"},"modified":"2018-10-24T12:34:45","modified_gmt":"2018-10-24T07:04:45","slug":"difference-bindparam-bindvalue","status":"publish","type":"post","link":"https:\/\/www.wikitechy.com\/technology\/difference-bindparam-bindvalue\/","title":{"rendered":"What is the difference between bindParam and bindValue"},"content":{"rendered":"<h2 id=\"bindparam-and-bindvalue\"><span style=\"color: #000080;\"><strong>bindParam and bindValue<\/strong><\/span><\/h2>\n<h3 id=\"bindvalue\"><span style=\"color: #ff6600;\"><strong>bindValue(),<\/strong><\/span><\/h3>\n<ul>\n<li>Unlike PDOStatement::bindValue(), the variable is bound as a reference and will only be evaluated at the time that PDOStatement::execute() is called.<\/li>\n<\/ul>\n<h3 id=\"bindparam\"><span style=\"color: #800080;\"><strong>bindParam()<\/strong><\/span><\/h3>\n<ul>\n<li>call PDOStatement::bindParam() to bind <a href=\"https:\/\/www.wikitechy.com\/technology\/check-php-directory-exists\/\" target=\"_blank\" rel=\"noopener\">PHP<\/a> variables to the parameter markers: bound variables pass their value as input and receive the output value, if any, of their associated parameter markers<\/li>\n<\/ul>\n<h2 id=\"php-pdostatement-bindparam-vs-bindvalue\"><span style=\"color: #0000ff;\"><strong>PHP PDOStatement: bindParam vs bindValue<\/strong><\/span><\/h2>\n<ul>\n<li>It is an ordinary place to repeatedly execute a query, with each iteration using different parameters.<\/li>\n<li>However, using the conventional query() method and a <a href=\"https:\/\/www.wikitechy.com\/step-by-step-tutorials\/php\/php-for-loop\" target=\"_blank\" rel=\"noopener\">looping mechanism<\/a> comes at a cost of both overhead, because of the repeated parsing of more or less identical query for validity, and coding convenience, and the need to repeatedly reconfigure the query using the new values for each iteration.<\/li>\n<li>Using prepared statement eliminate this problem. It is easy to accomplish in PHP using the PDOStatement class.<\/li>\n<li>The bindValue and bindParam PDOStatement methods are both use in binding a variable to a parameter (name or question mark placeholder use in SQL statement).<\/li>\n<li>The binds a parameter exclusively to a specified variable name which is bindParam.<\/li>\n<li>Bound as a reference while the binds a value which could be a variable, bindValue.<\/li>\n<li>An integer or string to a parameter.<\/li>\n<\/ul>\n[ad type=&#8221;banner&#8221;]\n<h4 id=\"to-make-the-difference-clearer-we-can-see-the-code-examples\"><span style=\"color: #003366;\"><strong>To make the difference clearer, we can see the code examples:<\/strong><\/span><\/h4>\n<ul>\n<li>Both bind a variable to a placeholder\u2019s parameter.<\/li>\n<\/ul>\n<ul>\n<li>In Both <b>bindParam<\/b> and <b>bindValue<\/b><b>,<\/b> a variable can be binded to a parameter.<\/li>\n<\/ul>\n<div class=\"code-embed-wrapper\"> <div class=\"code-embed-infos\"> <span class=\"code-embed-name\">php code<\/span> <\/div> <pre class=\"language-php code-embed-pre line-numbers\"  data-start=\"1\" data-line-offset=\"0\"><code class=\"language-php code-embed-code\">&lt; ?php<br\/>$name = &#039;collins&#039;;<br\/>$sql = &#039;INSERT INTO feeds (name) VALUES (:name)&#039;;<br\/>$update = $conn -&gt; prepare($sql);<br\/>$update -&gt; bindParam(&#039;:name&#039;, $name);<br\/>$update -&gt; execute();<\/code><\/pre> <\/div>\n<div class=\"code-embed-wrapper\"> <div class=\"code-embed-infos\"> <span class=\"code-embed-name\">php code<\/span> <\/div> <pre class=\"language-php code-embed-pre line-numbers\"  data-start=\"1\" data-line-offset=\"0\"><code class=\"language-php code-embed-code\">&lt; ?php<br\/>$name = &#039;collins&#039;;<br\/>$sql = &#039;INSERT INTO feeds (name) VALUES (:name)&#039;;<br\/>$update = $conn -&gt; prepare($sql);<br\/>$update -&gt; bindValue(&#039;:name&#039;, $name);<br\/>$update -&gt; execute();<\/code><\/pre> <\/div>\n<ul>\n<li><strong>bindParam<\/strong> binds variable as a reference.<\/li>\n<li>For example, when a variable is assigned as a reference to another variable, a change in the value of the variable assigned as a reference also affect the parent variable.<\/li>\n<li>For creating <a href=\"https:\/\/www.wikitechy.com\/tutorials\/c++\/call-by-reference-in-c++\" target=\"_blank\" rel=\"noopener\">reference<\/a>, the <strong>&amp;<\/strong> is used<\/li>\n<\/ul>\n<h3 id=\"example\"><span style=\"color: #339966;\"><strong>Example<\/strong><\/span><\/h3>\n<div class=\"code-embed-wrapper\"> <div class=\"code-embed-infos\"> <span class=\"code-embed-name\">javascript code<\/span> <\/div> <pre class=\"language-javascript code-embed-pre line-numbers\"  data-start=\"1\" data-line-offset=\"0\"><code class=\"language-javascript code-embed-code\"><br\/>$a = &#039;good boy&#039;;<br\/>$b = &amp;$a;  \/\/ $a and $b both equal &quot;good boy&quot;<br\/>$b = &quot;bad boy&quot;; \/\/ $a and $b both equal &quot;bad boy&quot;<br\/>echo &quot;$a&quot;; \/\/ echos &quot;bad boy&quot;<\/code><\/pre> <\/div>\n<ul>\n<li>While checking <strong>bindParam<\/strong> description, you could see that the method\u2019s second argument accepts a variable that is pass as a reference and will only be evaluated at the time that the execute() is called.<\/li>\n<\/ul>\n<p>Thus, we can still change the value of a variable bonded to a parameter even after the bindParam() method had been called like so:<\/p>\n<div class=\"code-embed-wrapper\"> <div class=\"code-embed-infos\"> <span class=\"code-embed-name\">javascript code<\/span> <\/div> <pre class=\"language-javascript code-embed-pre line-numbers\"  data-start=\"1\" data-line-offset=\"0\"><code class=\"language-javascript code-embed-code\">$name = &#039;collins&#039;;<br\/>$sql = &#039;INSERT INTO feeds (name) VALUES (:name)&#039;;<br\/>$update = $conn -&gt; prepare($sql);<br\/>$update -&gt; bindParam(&#039;:name&#039;, $name);<br\/>$name = &#039;john&#039;;<br\/>$update -&gt; execute(); \/\/ execute with john inserted into the column &quot;name&quot;<\/code><\/pre> <\/div>\n[ad type=&#8221;banner&#8221;]\n<ul>\n<li>Above code will bind \u2018john\u2019 to the :name parameter because, as we mentioned, variable are binded by reference when using bindParam.<\/li>\n<li>\u201cbindValue\u201d accept different data types.<\/li>\n<li>Unlike the bindParam() that binds only a variable name to a parameter, with bindValue, you can bind a variable and also an <a href=\"https:\/\/www.wikitechy.com\/technology\/c-program-print-integer\/\" target=\"_blank\" rel=\"noopener\">integer<\/a>, float, and string.<\/li>\n<\/ul>\n<h3 id=\"binding-an-integer-to-a-parameter-via-bindvalue\"><span style=\"color: #008080;\"><b>Binding an integer to a parameter via \u201cbindValue\u201d.<\/b><\/span><\/h3>\n<div class=\"code-embed-wrapper\"> <div class=\"code-embed-infos\"> <span class=\"code-embed-name\">php code<\/span> <\/div> <pre class=\"language-php code-embed-pre line-numbers\"  data-start=\"1\" data-line-offset=\"0\"><code class=\"language-php code-embed-code\">&lt; ?php<br\/>$sql = &#039;INSERT INTO feeds (numbers) VALUES (:number)&#039;;<br\/>$update = $conn -&gt; prepare($sql);<br\/>$update -&gt; bindValue(&#039;:number&#039;, 100);<br\/>$update -&gt; execute();<\/code><\/pre> <\/div>\n<h3 id=\"binding-a-string-to-a-parameter-via-bindvalue\"><span style=\"color: #008080;\"><b>Binding a string to a parameter via \u201c<\/b><b>bindValue<\/b><b>\u201d.<\/b><\/span><\/h3>\n<div class=\"code-embed-wrapper\"> <div class=\"code-embed-infos\"> <span class=\"code-embed-name\">php code<\/span> <\/div> <pre class=\"language-php code-embed-pre line-numbers\"  data-start=\"1\" data-line-offset=\"0\"><code class=\"language-php code-embed-code\">&lt; ?php<br\/>$sql = &#039;INSERT INTO feeds (names) VALUES (:name)&#039;;<br\/>$update = $conn -&gt; prepare($sql);<br\/>$update -&gt; bindValue(&#039;:name&#039;, &#039;collins&#039;);<br\/>$update -&gt; execute();<\/code><\/pre> <\/div>\n<ul>\n<li>While you are trying to bind say, a <a href=\"https:\/\/www.wikitechy.com\/technology\/c-program-to-change-case-of-a-string\/\" target=\"_blank\" rel=\"noopener\">string<\/a> or number (float \/ integer) to a parameter using bindParam, you\u2019ll get this error:<\/li>\n<\/ul>\n<p style=\"text-align: center;\"><span style=\"color: #993300;\"><strong>&#8220;Fatal error: Cannot pass parameter 2 by reference&#8221;<\/strong><\/span><\/p>\n<p>&nbsp;<\/p>\n[ad type=&#8221;banner&#8221;]\n","protected":false},"excerpt":{"rendered":"<p>bindParam and bindValue bindValue(), Unlike PDOStatement::bindValue(), the variable is bound as a reference and will only be evaluated at the time that PDOStatement::execute() is called. bindParam() call PDOStatement::bindParam() to bind PHP variables to the parameter markers: bound variables pass their value as input and receive the output value, if any, of their associated parameter markers [&hellip;]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[25],"tags":[3467,3472,3478,3474,3475,3470,3468,3471,3465,3469,3464,3473,3476,3460,3461,3477,3463,3466,3462,3459],"class_list":["post-1649","post","type-post","status-publish","format-standard","hentry","category-php","tag-a-possible-bug-with-pdos-bindparam","tag-bindparam-in-php","tag-bindparam-multiple-values","tag-bindparam-mysqli","tag-bindparam-not-working","tag-bindvalue-bindparam-vs-array-performance","tag-bindvalue-and-foreach","tag-bindvalue-php","tag-confusion-between-bindvalue-and-bindparam","tag-foreaching-pdo-bindvalue-bindparam","tag-pdo-bindparam-and-bindvalue-which-is-the-diference","tag-pdo-bindparam-array","tag-pdo-bindparam-example","tag-pdo-bindparam","tag-pdo-bindvalue-and-pdo-closecursor","tag-pdoparam_int","tag-php-pdo-bindparambindvalue-double-quote-bug","tag-what-is-the-difference-between-bindvalue-and-having-executearray","tag-whats-the-difference-between-pdostatement-bindparam-and-pdostatement-bindvalue","tag-why-bindvalue-or-bindparam-doesnt-modify-the-prepared-statement"],"_links":{"self":[{"href":"https:\/\/www.wikitechy.com\/technology\/wp-json\/wp\/v2\/posts\/1649","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.wikitechy.com\/technology\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.wikitechy.com\/technology\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.wikitechy.com\/technology\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.wikitechy.com\/technology\/wp-json\/wp\/v2\/comments?post=1649"}],"version-history":[{"count":0,"href":"https:\/\/www.wikitechy.com\/technology\/wp-json\/wp\/v2\/posts\/1649\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.wikitechy.com\/technology\/wp-json\/wp\/v2\/media?parent=1649"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.wikitechy.com\/technology\/wp-json\/wp\/v2\/categories?post=1649"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.wikitechy.com\/technology\/wp-json\/wp\/v2\/tags?post=1649"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}