PHP named parameter

Question

Hi I am new to php and I am facing difficulties with named parameter. I am learning form "Murach's php and sql book" Here is the code in my question,

$query = 'SELECT productCode, productName, listPrice
          FROM products
          WHERE productID = :product_id'; 
$statement = $db->prepare($query); 
$statement->bindValue(':product_id',    $product_id); 
$statement->execute(); 
$product = $statement->fetch();    
$statement->closeCursor();

So all my confusion is around "WHERE productID = :product_id';" What does this means ? I dont understand this. Usually we make condition there like WHERE productID < 100 or something like that. So it gives all the productIDs with value less than 100 right? Then how this works here ? i mean I did not see that this named parameter has been assigned with any values anywhere else. This is an example from the book. Or is that in real program we assign values to the named parameter variable so that it will provide appropriate result? Please help me and excuse me if it sounds so foolish because I am totally new to this.

It's searching for that exact product where the `product_id` **`=`** `$product_id`. You could do `WHERE productID < :product_id` if you so choose. — Darren, Jan 07 '16 at 01:59
Thanks for the reply but still i am confused can u please explain me little more ? what is the value of that named parameter ? is that something we assign later to the $product_id variable ? — Tom, Jan 07 '16 at 02:05
In this statement `$statement->bindValue(':product_id', $product_id); `. You assign the value of the `$product_id` to `:product_id`. — momouu, Jan 07 '16 at 02:06
The example doesn't have the step where `$product_id` is given a value. In a actual program, you would have assigned it a value. The necessity of that step was hopefully covered in earlier parts of the book. You may want to reread them or syst with something a little more basic. — , Jan 07 '16 at 02:11
Thank you very much you saved my day. So I assume that somewhere in the program the variable " $product_id " got assigned with a value and this is passed to that named parameter " :product_id " right ? — Tom, Jan 07 '16 at 02:13
Thank you very much for all the comments it clear all my doubts ! — Tom, Jan 07 '16 at 02:14

score 2 · Answer 1 · edited May 23 '17 at 12:23

You're overthinking named parameters. Think of it as a placeholder. It is essentially just a reference for the variable to point to once it's passed through to the server.

Using prepared statements essentially mitigates SQL Injections by passing the query and data (binds) separately.

This, once parsed to the server, will replace :product_id with the value present in $product_id.

You're best to read up on Prepared Statements, to gain a better understanding of what is actually going on.

You should also read up on: When should I use prepared statements?

score 1 · Accepted Answer · answered Jan 07 '16 at 02:06

The line $statement->bindValue(':product_id', $product_id); will replace :product_id in your SQL statement with the value that is in the php variable $product_id.

It's the same as saying WHERE productID = 'somevalue', it's just that PHP will fill in somevalue for you.

This is built into PHP for preparing statements, see here. Since PHP and databases are two different things, they built a way for PHP to interact with database objects called PDO (PHP Data Object) that is basically a bunch of methods for interacting with databases from inside of PHP. bindValue (linked above) is the MySQL PDO way to add variables into a MySQL statment.

score 0 · Answer 3 · answered Jan 07 '16 at 02:08

What is happening is that the database is able to cache and optimize the query for, it's also used to prevent SQL injection:

SELECT productCode, productName, listPrice FROM products WHERE productID = :product_id

If you have were to pass in a variable like this then that query has to be parsed and re-cached because of the $product_id (since it could be a variable number):

SELECT productCode, productName, listPrice FROM products WHERE productID = $product_id

If $product_id is not escaped properly someone could set $product_id to 123; DROP table products which would drop your table (bad).

So, the binding that happens is meant to make things more efficient and secure when running queries that require values coming from user input.

PHP named parameter

3 Answers3