How to work with PDO
How to connect to a database
$dsn = "mysql:host=bscacad3.buffalostate.edu;dbname=your-bsc-userid";
$username = "your-bsc-userid";
$password = "your-Banner-id";
$db = new PDO($dsn, $username, $password); // creates PDO object
Two methods of the PDO class for selecting data
| Method |
Description |
| query($select_statement) |
Executes the specified SQL SELECT statement and returns a PDOStatement object that contains the result set. If no result set is returned, this method returns a FALSE value. |
| query($input) |
Places quotes around the input and escapes special characters. |
A query() method with the SELECT statement coded in a variable
$query = 'SELECT * FROM products WHERE categoryID = 1 ORDER BY productID';
$products = $db->query($query); // $products contains the result set
A query() method with the SELECT statement coded as an argument
$products = $db->query('SELECT * FROM products');
An unquoted parameter (not secure!)
$query = "SELECT productCode, productName, listPrice FROM products
WHERE productID = $product_id";
$products = $db->query($query);
A quoted parameter (more secure)
$product_id_q = $db->quote($product_id);
$query = "SELECT productCode, productName, listPrice FROM products
WHERE productID = $product_id_q";
$products = $db->query($query);
Description
- To execute a dynamic
SELECT statement, you use the query() method of the PDO object. It requires just one argument, which is the SELECT statement to be executed. This argument can be a variable that contains the SELECT statement or the statement itself.
- If the
SELECT statement returns a result set, the query() method returns the result set in a PDOStatement object. Then, you can get the data from the result set by using the techniques described in chapter 2.
- Data that’s input by users can be malicious. To protect against this, you can use the
quote() method or prepared statements.
Back