CHAPTER 6 – Binding Variables

There are two types of bound variables: input variables that are bound to the statement, and output variables that are bound to the result set. For input variables, you need to specify a question mark as a placeholder in your SQL statement, like this: SELECT Id, Country FROM City WHERE City=? INSERT INTO City (Id, Name) VALUES (?,?)

Output variables can be bound directly to the columns of the result set. The procedure for binding input and output variables is slightly different. Input variables must be bound before executing a prepared statement, while output variables must be bound after executing the prepared statement. The process for input variables is as follows: 1. Preparing (parsing) the statement 2. Binding input variables 3. Assigning values to bound variables 4. Executing the prepared statement The process for output variables is as follows: 1. Preparing (parsing) the statement 2. Executing prepared statement 3. Binding output variables 4. Fetching data into output variables Executing a prepared statement or fetching data from a prepared state- ment can be repeated multiple times until the statement will be closed or there are no more data to fetch (see Table 6.5).

Table 6.5 mysqli Prepared Statement Functions Function Name Description mysqli_prepare(...) Prepares a SQL statement for execution. $mysqli->prepare() Parameters: · 0 Connection object (function only) · 1 Statement mysqli_stmt_bind_result(...) Binds variables to a statement's result set. $stmt->bind_result(...) Parameters: · 0 Statement object (function only) · 1 Variables mysqli_stmt_bind_param(...) Binds variables to a statement. $stmt->bind_result(...) Parameters: · 2 Statement object (function only) · 3 String that specifies the type of variable (s=string, i=number, d=double, b=blob) · 4 Variables mysqli_stmt_execute(...) Executes a prepared statement. Parame- $stmt->execute ters include a statement object (function only). mysqli_stmt_fetch(...) Fetches data into output variables. The $stmt->fetch parameter includes the statement object (function only). mysqli_stmt_close(...) Closes a prepared statement. $stmt->close() Here is an example of a data manipulation query using bound input variables: <?php $conn = mysqli_connect("localhost", "test", "", "world"); $conn->query("CREATE TABLE alfas ". "(year INTEGER, model VARCHAR(50), accel REAL)"); $stmt = $conn->prepare("INSERT INTO alfas VALUES(?, ?)"); $stmt->bind_param("isd", $year, $model, $accel); $year = 2001; $model = '156 2.0 Selespeed'; $accel = 8.6; $stmt->execute(); $year = 2003; $model = '147 2.0 Selespeed'; $accel = 9.3; $stmt->execute(); $year = 2004; $model = '156 GTA Sportwagon'; $accel = 6.3; $stmt->execute(); Here is an example of using binding for retrieving data: <?php $conn = mysqli_connect("localhost", "test", "", "test"); $stmt = $conn->prepare("SELECT * FROM alfas ORDER BY year"); $stmt->execute(); $stmt->bind_result($year, $model, $accel); print "<table>n"; print "<tr><th>Model</th><th>0-100 km/h</th></tr>n"; while ($stmt->fetch()) { print "<tr><td>$year $model</td><td>{$accel} sec</td>n"; } print "</table>n"; Here, we bind $year, $model, and $accel to the columns of the "alfas" table. Each $stmt->fetch() call modifies these variables with data from the current row. The fetch() method returns TRUE until there is no more data, then it returns FALSE.