Differences

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

--- pdo:select_statements [2016/10/14 08:45] – created 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>
@@ Line 32: / Line 80: @@
 Note the use of **PDO::FETCH_ASSOC** in the **fetch()** and **fetchAll()** code above.  This tells PDO to return the rows as an associative array with the field names as keys. Other fetch modes like **PDO::FETCH_NUM** returns the row as a numerical array.  The default is to fetch with **PDO::FETCH_BOTH** which duplicates the data with both numerical and associative keys.  It's recommended you specify one or the other so you don't have arrays that are double the size! PDO can also fetch objects with **PDO::FETCH_OBJ**, and can take existing classes with **PDO::FETCH_CLASS**.  It can also bind into specific variables with **PDO::FETCH_BOUND** and using the **bindColumn** method.  There are even more choices! Read about them all here: [[http://www.php.net/manual/en/pdostatement.fetch.php|PDOStatement Fetch documentation]].