BindParam()

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

You might have noted in the previous introduction to execute() 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 offers a somewhat cleaner method for binding parameters to corresponding query placeholders.

When using named parameters, parameter is the name of the column value placeholder specified in the prepared statement using the syntax :name. 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 datatype parameter explicitly sets the parameter datatype, and can be any of the following values:

• PDO_PARAM_NULL: SQL NULL datatype

• PDO_PARAM_INT: SQL INTEGER datatype

• PDO_PARAM_STR: SQL CHAR, VARCHAR, and other string datatypes

• PDO_PARAM_LOB: SQL large object datatype

• PDO_PARAM_STMT: PDOStatement object type; presently not operational

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

The 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 database driver-specific options.

Let's revisit the previous example, this time using bindParam() to assign the column values:

// Connect to the database server

$dbh = new PDO("pgsql:host=localhost;dbname=corporate", "websiteuser", "secret"); // Create and prepare the query

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

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

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

If question mark parameters were used, the statement would look like this:

$query = "INSERT INTO product SET sku = ?, name = ?";

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');

Was this article helpful?

+2 -1

Post a comment