My Web Training

Bind Parameters

Bind parameters are values that you pass into a prepared statement to replace placeholders in the SQL query. This improves security by preventing SQL injection and simplifies variable handling.

In PDO, bind parameters are passed as an array to execute():

$stmt = $pdo->prepare("SELECT * FROM users WHERE email = ?");
  $stmt->execute([$email]);

You can also bind values explicitly using bindParam() if needed — typically when reusing a statement inside a loop:

$stmt = $pdo->prepare("SELECT * FROM users WHERE email = :email");
  $stmt->bindParam(':email', $email);
  $stmt->execute();

💡 Pro Tip: The bindParam() method binds by reference, meaning the value is assigned at the time execute() is called. Use this when looping through inputs or dynamically changing the bound value.

Most of the time, passing an array to execute() is simpler and preferred for clarity and readability.

SELECT

Case 1 - Fetch Multiple Rows for Processing

Use this approach when you expect multiple records — for example, a list of members sharing the same last name.

Example

include_once 'db_connect.php';
  
  $query = "SELECT firstname, email FROM membership WHERE lastname = ?";
  $stmt = $pdo->prepare($query);
  $stmt->execute([$lastname]);
  $rows = $stmt->fetchAll();
  
  if (count($rows) > 0) {
      echo "<ul>\n";
      foreach ($rows as $member) {
          $email     = htmlspecialchars($member['email']);
          $firstname = htmlspecialchars($member['firstname']);
          echo "<li>Email: <a href=\"mailto:$email\">$email</a>, Name: $firstname</li>\n";
      }
      echo "</ul>\n";
  } else {
      echo "<p>No records found.</p>";
  }

This retrieves all matching rows into an array and loops through them using foreach, making it easy to build HTML output or arrays for further logic.

Why This Works

• prepare() sets up the SQL query with placeholders.
• execute() binds the variables securely and runs the query.
• fetch() retrieves a single row from the result set.
• fetchAll() retrieves all rows as an array of associative arrays.

Case 2 - Fetch a Single Record

This example retrieves a single record based on a unique identifier. It uses a prepared statement to structure the query and fetch the result immediately.

Example

include_once 'db_connect.php';
  
  $query = "SELECT firstname, lastname FROM membership WHERE memberID = ?";
  $stmt = $pdo->prepare($query);
  $stmt->execute([$memberID]);
  $row = $stmt->fetch();
  
  if ($row) {
      $firstname = $row['firstname'];
      $lastname  = $row['lastname'];
  } else {
      // Handle the case where no record is found
  }

Here, the placeholder (?) is replaced by the value in $memberID. The result is fetched into an associative array — ideal for quickly displaying or processing a single row.

Let’s break this down:

• prepare() sends the structure of the SQL command to the database with a placeholder.
• execute([$memberID]) provides the value to be inserted into the placeholder.

💡 Pro Tip: Prepared statements prevent SQL injection by ensuring the query and its data are handled separately — even when values come from user input.

Remember:

• The number of placeholders (?) must match the number of values in the array passed to execute().
• You should never insert user data directly into the SQL string — always use placeholders.
• Only values can be parameterized — table names or column names must be written directly into the query.

INSERT

The INSERT statement adds a new record to a database table. When using prepared statements, you define the fields to be populated and provide the values in a secure and organized way. If your table has an auto-incrementing primary key, like memberID, it will be created automatically — no need to include it in the query.

Example

include_once 'db_connect.php';
  
  $query = "INSERT INTO membership (firstname, lastname, username, email, password)
            VALUES (?, ?, ?, ?, ?)";
  $stmt = $pdo->prepare($query);
  $stmt->execute([$firstname, $lastname, $username, $email, $password]);

Each value in the array passed to execute() corresponds to a placeholder in the SQL statement. The values are inserted in the order they appear.

💡 Pro Tip: In a production app, passwords should always be hashed using password_hash(). For development and testing, plain text is sometimes used temporarily to simplify testing — but never store real passwords this way.

UPDATE

The UPDATE statement modifies one or more fields in a record. Like the DELETE statement, it includes a WHERE clause to specify which record should be updated. Prepared statements make this process secure and reusable.

Example

include_once 'db_connect.php';
  
  $query = "UPDATE membership
            SET firstname = ?, lastname = ?, username = ?, email = ?
            WHERE memberID = ?";
  $stmt = $pdo->prepare($query);
  $stmt->execute([$firstname, $lastname, $username, $email, $memberID]);

This script updates a single record in the membership table based on its memberID. Each ? in the query corresponds to a value passed in the execute() array, in the same order.

Let’s review what’s happening:

• prepare() creates the template for the SQL query with placeholders.
• execute() replaces each placeholder with a matching value from the array, in order.

Using prepared statements ensures the data is safely passed to the database without exposing your query to injection attacks.

DELETE

The DELETE statement removes a record from a database table. Most often, this is based on a condition — such as deleting a member by their unique ID. Prepared statements help ensure this operation is secure, especially when data comes from a form or URL parameter.

Example

include_once 'db_connect.php';
  
  $query = "DELETE FROM membership WHERE memberID = ?";
  $stmt = $pdo->prepare($query);
  $stmt->execute([$memberID]);

This example deletes a member from the membership table where the memberID matches the provided value. The ? is a placeholder that will be replaced by the value in the $memberID variable — safely escaped by the database engine.

Takeaways for Students

• Always use prepared statements when incorporating user input into queries.
• The number of placeholders must match the values passed to execute().
• Use fetch() when expecting one record, and fetchAll() for multiple records.
• Never interpolate user input directly into an SQL string — use prepare() and execute() to prevent SQL injection.

By following these patterns, you ensure your PHP application is secure, efficient, and easy to maintain. Prepared statements are a powerful tool in your PHP toolkit, especially when working with databases.