Implement MYSQLI Bind Param with unlimited possibilities

Question

I'm familiar with the basics of how mysqli_stmt_bind_param works, but I'm still new to PHP and running into an issue I'm not sure how to tackle. How would I implement mysqli_stmt_bind_param method whenever I'm not sure how many ?'s will be in the SQL query? I've attached an example of a code below that illustrates what I'm talking about. I've also included a screenshot of the database structure, if that helps any.

In this example, the SQL statement is built depending on if arguments are present in the URL, other conditions, etc. There could potentially be anywhere from 1 ? to 10 ?'s, which I'm not sure how to navigate.

I want to accomplish this, because my understanding is that stmt_bind_param is safer than doing it the way I'm currently doing it.. right?

  if (isset($_GET['type'])) {
    if ($_GET['type'] == "forgiven") {$supplemental = "discipline.forgiven = 1 AND ";}
    else if ($_GET['type'] == "active") {$supplemental = "discipline.forgiven = 0 AND ";}
    else if ($_GET['type'] == "all") {$supplemental = "discipline.forgiven IS NOT NULL AND ";}
    else {$supplemental = "discipline.type = '" .$_GET['type']. "' AND ";}
  }

  if (isset($_GET['uuid'])) {$SQL = "SELECT users.firstName, users.lastName, users.homeroom, discipline.* FROM `discipline` INNER JOIN users ON users.uuid = discipline.recipient WHERE " .$supplemental. "discipline.recipient LIKE '" .$_GET['uuid']. "' ORDER BY discipline.timestamp DESC";}
  else {$SQL = "SELECT users.firstName, users.lastName, users.homeroom, discipline.* FROM `discipline` INNER JOIN users ON users.uuid = discipline.recipient WHERE " .$supplemental. "users.homeroom LIKE '" .$_GET['search']. "' OR discipline.recipient LIKE '" .$_GET['search']. "' OR discipline.reason LIKE '%" .$_GET['search']. "%' OR discipline.action LIKE '%" .$_GET['search']. "%' ORDER BY discipline.timestamp DESC";}


  $stmt = mysqli_stmt_init($connection);
  if (!mysqli_stmt_prepare($stmt, $SQL)) {echo "SQL database connection error!";exit();}
  else {
    mysqli_stmt_execute($stmt);
    $results = mysqli_stmt_get_result($stmt);

Table Structure:

Answer 1

Here's an example of how you can make the bound parameters "dynamic", or in other words make an SQL query with a variable number of parameter placeholders based on conditions. As you write the conditions, append values to an array of parameters.

$andTerms = [];
$types = "";
$params = [];
if (isset($_GET['type'])) { 
  if ($_GET['type'] == "forgiven") {
    $andTerms[] = "discipline.forgiven = 1";
  } else if ($_GET['type'] == "active") {
    $andTerms[] = "discipline.forgiven = 0";
  } else if ($_GET['type'] == "all") {
    $andTerms[] = "discipline.forgiven IS NOT NULL";
  } else {
    $andTerms[] = "discipline.type = ?";
    $types .= "s";
    $params[] = $_GET['type'];
  }
} 

if (isset($_GET['uuid'])) { 
  $andTerms[] = "discipline.recipient LIKE ?"
  $types .= "s";
  $params[] = $_GET['uuid'];
} else {
  $andTerms[] = "users.homeroom LIKE ? OR discipline.recipient LIKE ? OR discipline.reason LIKE ? OR discipline.action LIKE ?";
  $types .= "ssss";
  $params[] = $_GET['search']; // homeroom
  $params[] = $_GET['search']; // recipient
  $params[] = "%{$_GET['search']}%"; // reason
  $params[] = "%{$_GET['search']}%"; // action
}   

$andTermsImploded = implode(" AND ", $andTerms);
$SQL = " 
  SELECT users.firstName, users.lastName, users.homeroom, discipline.*
  FROM `discipline` INNER JOIN users ON users.uuid = discipline.recipient
  WHERE {$andTermsImploded}
  ORDER BY discipline.timestamp DESC";

By the time you finish this, you have added a variable number of terms to the WHERE clause of the query. Meanwhile, the values to bind are in $params which is also variable length, but if you wrote each conditional block of code correctly, it has the same number of elements as the number of placeholders in the SQL query. Also $types has the correct number of control characters.

Then it's time to do the call to bind_param. Use the ... syntax to pass your array of values as if it's a list of arguments to that function. See example #10 in https://www.php.net/manual/en/functions.arguments.php that describes variable-length argument lists. This feature was introduced in PHP 5.6 in 2014, so it should be available to any site that runs a version of PHP that is not past its end of life.

$stmt = mysqli_stmt_init($connection);
if (!mysqli_stmt_prepare($stmt, $SQL)) {
  echo "SQL error!";
  trigger_error($stmt->error);
  exit();
} 

mysqli_stmt_bind_param($stmt, $types, ...$params); // passing variable arguments

$ok = mysqli_stmt_execute($stmt);
if (!$ok) {
  echo "SQL error!";
  trigger_error($stmt->error);
  exit();    
}

$results = mysqli_stmt_get_result($stmt);

Remember to report the error to your error log every time you call prepare or execute.

P.S.: I reformatted the code a bit, to conform to PSR-2 style guide. Please follow that guide in the future. It makes it easier for other developers to read your code.