MySQLi - When you have a query with more than 15 '?', and sisisissssisisiss, how do you keep track?

Question

When MySQLi statements are small, they are super easy to use. However, once you have more than a certain amount of bounded parameters, it gets extremely hard to track the s and i are synced up property. Like the following example:

$query = "INSERT INTO transaction_table (eventide, proxid, valid, fname, lame, email, studentid, status, affiliation, grade, error_statement, gender, netid, residence, school, department, major1, major2, dob, type, level) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";

if($stmt = $mysqli->prepare($query)){
    $grade_new = gradeChanger($grade);
    $stmt->bind_param('iiisssiisssssissssiss',$_POST['pid'],$_POST['proxid'],$valid,$fname,$lname,$email,$studentid,$status,$affiliation,$grade_new,$error_submit,$gender,$netid,$residence,$school,$department,$major1,$major2,$dob,$type,$level);
    $stmt->execute();
    $stmt->close();
}

Is there an easier way to separate the i and s? like this, I feel like if you would be able to break up the bind_param in groups, like so, it would be way easier to read. Is there anyway to do anything like this?

$stmt->bind_param('iiis', $_POST['pid'], $_POST['proxid'], $valid, $fname);
$stmt->bind_param('ssii', $lname, $email, $studentid, $status);
$stmt->bind_param('sass', $affiliation, $grade_new, $error_submit, $gender);
$stmt->bind_param('siss', $netid, $residence, $school, $department);
$stmt->bind_param('ssiss', $major1, $major2, $dob, $type, $level);

Is the only solution to switch to PDO?

Answer 1

One method is to dynamically call the bind_param method. You obviously know whether to use i or s with each variable, so let's put all of that into an array (in the correct order).

$params = array(
    array('i', $_POST['pid']),
    array('i', $_POST['proxid']),
    array('i', $valid),
    array('s', $fname),
    // etc.
);

Then you can loop over this to build the "format string" to pass to bind_param.

$bind_params = array('');
foreach($params as &$vals){
    $bind_params[0] .= $vals[0];
    $bind_params[] =& $vals[1];
}
call_user_func_array(array($stmt, 'bind_param'), $bind_params);

This should make it easier for you to add/remove values and to keep track of them.