PDO: Queries and Data

(Page 1 of 2 )

In this fourth part of a five-part series on PDO, you'll learn how to execute a prepared query, retrieve data, and more. This article is excerpted from chapter 23 of the book Beginning PHP and Oracle: From Novice to Professional, written by W. Jason Gilmore and Bob Bryla (Apress; ISBN: 1590597702).

Executing a Prepared Query

Theexecute()method is responsible for executing a prepared query. Its prototype follows:

boolean PDOStatement::execute([array input_parameters])

This method requires the input parameters that should be substituted with each iterative execution. This is accomplished in one of two ways: either pass the values into the method as an array, or bind the values to their respective variable name or positional offset in the query using thebindParam()method. The first option is covered next, and the second option is covered in the upcoming introduction tobindParam().

The following example shows how a statement is prepared and repeatedly executed byexecute(), each time with different parameters:

<?php

    // Connect to the database
    $dbh = new PDO("oci:dbname=xe", "chapter23", "secret");

    // Create the query
    $query = "INSERT INTO products (product_id, sku,title)
              VALUES (:productid, :sku,:title)";

    // Prepare the query
    $stmt = $dbh->prepare($query);

    // Execute the query
    $stmt->execute(array(':productid' => 6, ':sku' => 'MN873213',
                          ':title' => 'Minty Mouthwash'));

    // Execute again
    $stmt->execute(array(':productid' => 7, ':sku' => 'AB223234',
                          ':title' => 'Lovable Lipstick'));
?>

This example is revisited next, where you’ll learn an alternative means for passing along query parameters using thebindParam()method.

Binding Parameters

You might have noted in the earlier introduction to theexecute()method that theinput_parametersparameter 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? ThebindParam()method:

boolean PDOStatement::bindParam(mixed parameter, mixed &variable [, int datatype 
                                [, int length [, mixed driver_options]]])

When using named parameters,parameteris the name of the column value placeholder specified in the prepared statement using the syntax:name. When using question mark parameters,parameteris the index offset of the column value placeholder as located in the query. Thevariable 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 optionaldatatype parameter explicitly sets the parameter datatype, and can be any of the following values:

1. PDO_PARAM_BOOL: SQLBOOLEANdatatype
   
2. PDO_PARAM_INPUT_OUTPUT: Used when the parameter is passed into a stored procedure
   and therefore could be changed after the procedure executes 
    
3. PDO_PARAM_INT: SQLINTEGERdatatype 
    
4. PDO_PARAM_NULL: SQLNULLdatatype 
    
5. PDO_PARAM_LOB: SQL large object datatype 
    
6. PDO_PARAM_STMT:PDOStatementobject type; presently not operational 
    
7. PDO_PARAM_STR: SQL string datatypes

The optionallengthparameter specifies the datatype’s length. It’s only required when assigning it thePDO_PARAM_INPUT_OUTPUTdatatype. Finally, thedriver_optionsparameter is used to pass along any driver–specific options.

Let’s revisit the previous example, this time usingbindParam()to assign the column values:

<?php

    // Connect to the database server
    $dbh = new PDO("oci:dbname=xe", "chapter23", "secret");

    // Create and prepare the query
    $query = "INSERT INTO products (product_id, sku,title)
              VALUES (:productid, :sku, :title)";
    $stmt = $dbh->prepare($query);

    // Assign two new variables
    $productid = 8;
    $sku = 'PO998323';
    $title = 'Pretty Perfume';

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

    // Execute the query
    $stmt->execute();

    // Assign new variables
    $productid = 9;
    $sku = 'TP938221';
    $title = 'Groovy Gel';

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

    // Execute again
    $stmt->execute();

?>

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

$query = "INSERT INTO products SET productid = ?, sku = ?, title = ?";

Therefore the correspondingbindParam()calls would look like this:

$stmt->bindParam(1, 9);
$stmt->bindParam(2, 'PO998323');
$stmt->bindParam(3, 'Pretty Perfume');
. . .
$stmt->bindParam(1, 9);
$stmt->bindParam(2, 'TP938221');
$stmt->bindParam(3, 'Groovy Gel');

Next: Retrieving Data >>
 

More Database Articles Articles
More By Apress Publishing

blog comments powered by Disqus