PHP Archive

Binding Parameters 2

You might have noted in the earlier introduction to the execute() method that the input_parameters parameter was optional. This is convenient because if you need to pass along numerous variables, providing an array in this manner can quickly become unwieldy. So what's the alternative? The bindParam() method. Its prototype follows:

boolean PDOStatement::bindParam(mixed parameter, mixed &variable [, int datatype

When using named parameters, parameter is the name of the column value placeholder specified in the prepared statement using the syntax :title. When using question mark parameters, parameter is the index offset of the column value placeholder as located in the query. The variable parameter stores the value to be assigned to the placeholder. It's depicted as passed by reference because when using this method in conjunction with a prepared stored procedure, the value could be changed according to some action in the stored procedure. This feature won't be demonstrated in this section; however, after you read Chapter 32, the process should be fairly obvious. The optional datatype parameter explicitly sets the parameter datatype, and can be any of the following values:

• PDO_PARAM_BOOL: SQL BOOLEAN datatype

• PDO_PARAM_INPUT_OUTPUT: Used when the parameter is passed into a stored procedure and therefore could be changed after the procedure executes

• PDO_PARAM_INT: SQL INTEGER datatype

• PDO_PARAM_NULL: SQL NULL datatype

• PDO_PARAM_LOB: SQL large object datatype (not supported by MySQL)

• PDO_PARAM_STMT: PDOStatement object type; presently not operational

• PDO_PARAM_STR: SQL string datatypes

The optional length parameter specifies the datatype's length. It's only required when assigning it the PDO_PARAM_INPUT_OUTPUT datatype. Finally, the driver_options parameter is used to pass along any driver-specific options.

The following example revisits the previous example, this time using bindParam() to assign the column values:

// Connect to the database server

$dbh = new PDO("mysql:host=localhost;dbname=chp31", "webuser", "secret"); // Create and prepare the query

$query = "INSERT INTO products SET sku = :sku, title = :title"; $stmt = $dbh->prepare($query);

// Bind the parameters $stmt->bindParam(':sku', $sku); $stmt->bindParam(':title', $title);

// Bind the parameters $stmt->bindParam(':sku', 'AB223234'); $stmt->bindParam(':title', 'Lovable Lipstick');

If question mark parameters were used, the statement would look like this: $query = "INSERT INTO products SET sku = ?, title = ?";

Therefore, the corresponding bindParam() calls would look like this:

$stmt->bindParam(1, 'MN873213'); $stmt->bindParam(2, 'Minty Mouthwash');

$stmt->bindParam(1, 'AB223234'); $stmt->bindParam(2, 'Lovable Lipstick');

0 0