Differences

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

--- pdo:select_statements [2016/10/14 08:45] – peter
+++ pdo:select_statements [2020/07/15 09:30] (current) – external edit 127.0.0.1
@@ Line 26: / Line 26: @@
 $results = $stmt->fetchAll(PDO::FETCH_ASSOC);
 // Use $results...
+</code>
+If you have lots of parameters. This is how you can do it in PDO:
+<code php>
+<?php
+$stmt = $db->prepare("SELECT * FROM table WHERE id=? AND name=?");
+$stmt->execute(array($id, $name));
+$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
+<php>
+The **prepare** method sends the query to the server, and it's compiled with the '?' placeholders to be used as expected arguments.  The **execute** method sends the arguments to the server and runs the compiled statement.  Since the query and the dynamic parameters are sent separately, there is no way that any SQL that is in those parameters can be executed... so NO SQL INJECTION can occur!  This is a much better and safer solution than concatenating strings together.
+**NOTE**:  When you bind parameters, do NOT put quotes around the placeholders.  It will cause strange SQL syntax errors, and quotes aren't needed as the type of the parameters are sent during **execute** so they are not needed to be known at the time of **prepare**.
+There's a few other ways you can bind parameters as well. Instead of passing them as an array, which binds each parameter as a String type, you can use **bindValue** and specify the type for each parameter:
+<code php>
+<?php
+$stmt = $db->prepare("SELECT * FROM table WHERE id=? AND name=?");
+$stmt->bindValue(1, $id, PDO::PARAM_INT);
+$stmt->bindValue(2, $name, PDO::PARAM_STR);
+$stmt->execute();
+$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
+</code>
+===== Named Placeholders =====
+Now if you have lots of parameters to bind, doesn't all those '?' characters make you dizzy and are hard to count? Well, in PDO you can use named placeholders instead of the '?':
+<code php>
+<?php
+$stmt = $db->prepare("SELECT * FROM table WHERE id=:id AND name=:name");
+$stmt->bindValue(':id', $id, PDO::PARAM_INT);
+$stmt->bindValue(':name', $name, PDO::PARAM_STR);
+$stmt->execute();
+$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
+</code>
+You can bind using execute with an array as well:
+<code php>
+<?php
+$stmt = $db->prepare("SELECT * FROM table WHERE id=:id AND name=:name");
+$stmt->execute(array(':name' => $name, ':id' => $id));
+$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
 </code>