PDO bindParam – SELFHTML-Forum Forum als Ergänzung zum SELFHTML-Wiki und zur Dokumentation SELFHTML https://forum.selfhtml.org/self PDO bindParam Fri, 16 Aug 19 06:13:20 Z https://forum.selfhtml.org/self/2019/aug/16/pdo-bindparam/1755813#m1755813 https://forum.selfhtml.org/self/2019/aug/16/pdo-bindparam/1755813#m1755813 <p>Hallo alle,</p> <p>siehe auch: <a href="https://forum.selfhtml.org/self/2019/aug/11/prepared-statement-mit-or-bei-zunachst-unbekannter-anzahl-von-bedingungen/1755284#m1755284" rel="noopener noreferrer">T1755284</a></p> <p>ich habe folgende vereinfacht dargestellte Situation:</p> <pre><code class="block language-php"><span class="token variable">$foo</span> <span class="token operator">=</span> <span class="token string double-quoted-string">"1,2,3,4"</span><span class="token punctuation">;</span> <span class="token variable">$stmt</span> <span class="token operator">=</span> <span class="token variable">$db</span> <span class="token operator">-></span> <span class="token function">prepare</span><span class="token punctuation">(</span><span class="token string double-quoted-string">"SELECT `col` FROM `table` WHERE `ID` IN (:foo)"</span><span class="token punctuation">)</span><span class="token punctuation">;</span> <span class="token variable">$stmt</span> <span class="token operator">-></span> <span class="token function">bindValue</span><span class="token punctuation">(</span><span class="token string single-quoted-string">':foo'</span><span class="token punctuation">,</span><span class="token variable">$foo</span><span class="token punctuation">)</span><span class="token punctuation">;</span> </code></pre> <p>Nach einem execute erhalte ich jedoch keine Datensätze. Ich vermute, das liegt daran, dass die Query</p> <pre><code class="block bad language-sql"><span class="token keyword">SELECT</span> <span class="token identifier"><span class="token punctuation">`</span>col<span class="token punctuation">`</span></span> <span class="token keyword">FROM</span> <span class="token identifier"><span class="token punctuation">`</span>table<span class="token punctuation">`</span></span> <span class="token keyword">WHERE</span> <span class="token identifier"><span class="token punctuation">`</span>ID<span class="token punctuation">`</span></span> <span class="token operator">IN</span> <span class="token punctuation">(</span><span class="token string">'1,2,3,4'</span><span class="token punctuation">)</span> </code></pre> <p>statt</p> <pre><code class="block good language-sql"><span class="token keyword">SELECT</span> <span class="token identifier"><span class="token punctuation">`</span>col<span class="token punctuation">`</span></span> <span class="token keyword">FROM</span> <span class="token identifier"><span class="token punctuation">`</span>table<span class="token punctuation">`</span></span> <span class="token keyword">WHERE</span> <span class="token identifier"><span class="token punctuation">`</span>ID<span class="token punctuation">`</span></span> <span class="token operator">IN</span> <span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">,</span><span class="token number">2</span><span class="token punctuation">,</span><span class="token number">3</span><span class="token punctuation">,</span><span class="token number">4</span><span class="token punctuation">)</span> </code></pre> <p>erzeugt wird.</p> <p>Gibt es dafür eine Lösung.</p> <p>Das beschriebene Verhalten kann ich mit php-myadmin nachstellen. Heißt auch, dass die zweite Query die gewünschten Datensätze liefert.</p> <p>Bis demnächst<br> Matthias</p> <div class="signature">-- <br> Pantoffeltierchen haben keine Hobbys.<br> ¯\_(ツ)_/¯ </div> PDO bindParam Fri, 16 Aug 19 06:19:29 Z https://forum.selfhtml.org/self/2019/aug/16/pdo-bindparam/1755814#m1755814 https://forum.selfhtml.org/self/2019/aug/16/pdo-bindparam/1755814#m1755814 <p>Tach!</p> <blockquote> <p>Nach einem execute erhalte ich jedoch keine Datensätze. Ich vermute, das liegt daran, dass die Query</p> <pre><code class="block bad language-sql"><span class="token keyword">SELECT</span> <span class="token identifier"><span class="token punctuation">`</span>col<span class="token punctuation">`</span></span> <span class="token keyword">FROM</span> <span class="token identifier"><span class="token punctuation">`</span>table<span class="token punctuation">`</span></span> <span class="token keyword">WHERE</span> <span class="token identifier"><span class="token punctuation">`</span>ID<span class="token punctuation">`</span></span> <span class="token operator">IN</span> <span class="token punctuation">(</span><span class="token string">'1,2,3,4'</span><span class="token punctuation">)</span> </code></pre> <p>statt</p> <pre><code class="block good language-sql"><span class="token keyword">SELECT</span> <span class="token identifier"><span class="token punctuation">`</span>col<span class="token punctuation">`</span></span> <span class="token keyword">FROM</span> <span class="token identifier"><span class="token punctuation">`</span>table<span class="token punctuation">`</span></span> <span class="token keyword">WHERE</span> <span class="token identifier"><span class="token punctuation">`</span>ID<span class="token punctuation">`</span></span> <span class="token operator">IN</span> <span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">,</span><span class="token number">2</span><span class="token punctuation">,</span><span class="token number">3</span><span class="token punctuation">,</span><span class="token number">4</span><span class="token punctuation">)</span> </code></pre> <p>erzeugt wird.</p> </blockquote> <p>Ja, es wird immer nur ein Wert gebunden. Und $foo ist ein String.</p> <blockquote> <p>Gibt es dafür eine Lösung.</p> </blockquote> <p>Für jeden Wert einen eigenen Platzhalter verwenden.</p> <p>Beim <a href="https://www.php.net/manual/en/pdostatement.execute.php" rel="nofollow noopener noreferrer">PDOStatement::execute()</a> ist das auch bestätigt: Multiple values cannot be bound to a single parameter; for example, it is not allowed to bind two values to a single named parameter in an IN() clause.</p> <p>dedlfix.</p> PDO bindParam Fri, 16 Aug 19 07:35:41 Z https://forum.selfhtml.org/self/2019/aug/16/pdo-bindparam/1755824#m1755824 https://forum.selfhtml.org/self/2019/aug/16/pdo-bindparam/1755824#m1755824 <p>Hallo Matthias Apsel,</p> <p>die Lösung findet sich unter <a href="https://stackoverflow.com/questions/14767530/php-using-pdo-with-in-clause-array" rel="noopener noreferrer">https://stackoverflow.com/questions/14767530/php-using-pdo-with-in-clause-array</a></p> <pre><code class="block language-php"><span class="token variable">$arr</span> <span class="token operator">=</span> <span class="token punctuation">[</span><span class="token number">1</span><span class="token punctuation">,</span><span class="token number">2</span><span class="token punctuation">,</span><span class="token number">3</span><span class="token punctuation">]</span><span class="token punctuation">;</span> <span class="token variable">$in</span> <span class="token operator">=</span> <span class="token function">implode</span><span class="token punctuation">(</span><span class="token string single-quoted-string">','</span><span class="token punctuation">,</span> <span class="token function">array_fill</span><span class="token punctuation">(</span><span class="token number">0</span><span class="token punctuation">,</span> <span class="token function">count</span><span class="token punctuation">(</span><span class="token variable">$arr</span><span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token string single-quoted-string">'?'</span><span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token punctuation">;</span> <span class="token variable">$sql</span> <span class="token operator">=</span> <span class="token string double-quoted-string">"SELECT * FROM tab WHERE foo=? AND col IN (<span class="token interpolation"><span class="token variable">$in</span></span>) AND bar=? AND baz=?"</span><span class="token punctuation">;</span> <span class="token variable">$stmt</span> <span class="token operator">=</span> <span class="token variable">$db</span> <span class="token operator">-></span> <span class="token function">prepare</span><span class="token punctuation">(</span><span class="token variable">$sql</span><span class="token punctuation">)</span><span class="token punctuation">;</span> <span class="token variable">$params</span> <span class="token operator">=</span> <span class="token function">array_merge</span><span class="token punctuation">(</span><span class="token punctuation">[</span><span class="token variable">$foo</span><span class="token punctuation">]</span><span class="token punctuation">,</span> <span class="token variable">$arr</span><span class="token punctuation">,</span> <span class="token punctuation">[</span><span class="token variable">$bar</span><span class="token punctuation">,</span> <span class="token variable">$baz</span><span class="token punctuation">]</span><span class="token punctuation">)</span><span class="token punctuation">;</span> <span class="token variable">$stmt</span> <span class="token operator">-></span> <span class="token function">execute</span><span class="token punctuation">(</span><span class="token variable">$params</span><span class="token punctuation">)</span><span class="token punctuation">;</span> <span class="token variable">$data</span> <span class="token operator">=</span> <span class="token variable">$stmt</span> <span class="token operator">-></span> <span class="token function">fetchAll</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span> </code></pre> <p>Danke auch noch mal an <a href="/users/27" class="mention registered-user" rel="noopener noreferrer">@dedlfix</a>.</p> <p>Bis demnächst<br> Matthias</p> <div class="signature">-- <br> Pantoffeltierchen haben keine Hobbys.<br> ¯\_(ツ)_/¯ </div> PDO bindParam Fri, 16 Aug 19 06:25:13 Z https://forum.selfhtml.org/self/2019/aug/16/pdo-bindparam/1755815#m1755815 https://forum.selfhtml.org/self/2019/aug/16/pdo-bindparam/1755815#m1755815 <p>Hallo dedlfix,</p> <p>Ich hab mir jetzt so geholfen:</p> <pre><code class="block language-php"><span class="token variable">$stmt</span> <span class="token operator">=</span> <span class="token variable">$db</span> <span class="token operator">-></span> <span class="token function">query</span><span class="token punctuation">(</span><span class="token string double-quoted-string">"SELECT `col` FROM `table` WHERE `ID` IN (<span class="token interpolation"><span class="token variable">$foo</span></span>)"</span><span class="token punctuation">)</span><span class="token punctuation">;</span> </code></pre> <p>Aber schön ist das irgendwie nicht. Interessant, dass es an anderer Stelle genau so funktioniert hat. Ich muss gleich mal testen, ob das nur aufgrund meiner Testdaten so war.</p> <p>Bis demnächst<br> Matthias</p> <div class="signature">-- <br> Pantoffeltierchen haben keine Hobbys.<br> ¯\_(ツ)_/¯ </div> PDO bindParam Fri, 16 Aug 19 06:33:22 Z https://forum.selfhtml.org/self/2019/aug/16/pdo-bindparam/1755816#m1755816 https://forum.selfhtml.org/self/2019/aug/16/pdo-bindparam/1755816#m1755816 <p>Tach!</p> <blockquote> <p>Aber schön ist das irgendwie nicht.</p> </blockquote> <p>Anders geht es nicht, weil dann nicht klar wird, ob der Wert als Skalar behandelt werden soll oder auf irgendeine Weise zerpflückt werden muss.</p> <p>Nun könnte man argumentieren, dass bei Übergabe eines Arrays das PDO die Aufteilung vornimmt und das Statement im Nachhinein ändert. Das wäre dann aber eine Ausnahme und Abweichung vom Üblichen. Und das würde auch nur bei simulierten Prepared Statements gehen, weil ansonsten das Statement ja bereits im DBMS ist.</p> <p>dedlfix.</p> PDO bindParam Fri, 16 Aug 19 06:55:16 Z https://forum.selfhtml.org/self/2019/aug/16/pdo-bindparam/1755817#m1755817 https://forum.selfhtml.org/self/2019/aug/16/pdo-bindparam/1755817#m1755817 <p>Hallo Matthias Apsel,</p> <blockquote> <p>Interessant, dass es an anderer Stelle genau so funktioniert hat. Ich muss gleich mal testen, ob das nur aufgrund meiner Testdaten so war.</p> </blockquote> <p>Es war nur aufgrund meiner Testdaten.</p> <p>Bis demnächst<br> Matthias</p> <div class="signature">-- <br> Pantoffeltierchen haben keine Hobbys.<br> ¯\_(ツ)_/¯ </div>