php – Maintaining Array Count When Values ​​Are Empty

Using a form submit and a foreach loop, I am creating an array of names and values. These names and values ​​will be used in a prepared statement so maintaining the count is important and it works fine when all fields are filled on the test form. When one or more are empty, though, the prepared statement fails due to a count mismatch. This was expected but now that it’s working, I need to solve the issue but have been unsuccessful so far.

Here is the foreach loop and the variable in question is $Values where it is building the values ​​for the bind

$Types = "";
$Values = "";
foreach ($_POST as $val=>$formVal) :
    global $$val; // Subdues "undefined variable" errors
    $Types .=  (is_string($val)) ? "s," : "i,"; // needs work
    if (empty($formVal)) $formVal = NULL;
    $$val .= $formVal;
    $Values .= "$formVal,";
endforeach;
    
$Values = array_filter(explode(",",$Values));
// remove trailing comma, create array
// $Values = explode(",",rtrim($Values));
$Types = str_replace(',','',$Types);

The $Values output isn’t directly usable as it is but this removes the extra comma at the end and creates the array:

$Values = explode(",",rtrim($Values));

The output is as below with an extra empty array value at the end as [4]=>

Array ( [0] => MyName [1] => MyLastName [2] => MyCity [3] => MyState [4] => )

Using array_filter() sorts it out.

$Values = array_filter(explode(",",$Values));

Array ( [0] => MyName [1] => MyLastName [2] => MyCity [3] => MyState )

However, if any form field is empty when submitted, array_filter() by its nature and design removes it too and the array count is off so how can I maintain the number of elements in the array? I tried assigning them as NULL in the foreach loop but to no avail.

One thought is to use the schema to get a finite number of names, then append the post values ​​to it but I am not sure how to do that. The beginnings of it are in the code below as $sqlSchema.

Here is the entire prepared statement insert test script for anyone who might want to play with it. I’m sure it is overly complicated and that much can be streamlined but I’m afraid I’m quite rusty after having not programmed much in decades:

<?php

error_reporting(E_ALL);
ini_set('display_errors', '1');

// Establish connection to specified database
function dbConn($DBname) {
    $DBhost = "localhost";
    $DBusername = "db_username";
    $DBpass = "db_password";
    $dbconn = new mysqli($DBhost, $DBusername, $DBpass, $DBname);
    $dbconn->set_charset("utf8mb4");
    return $dbconn;
}

$mysqli = dbConn("my_database");

$queryText = "CREATE TABLE IF NOT EXISTS `xmembers` (
            `ID` tinyint unsigned NOT NULL AUTO_INCREMENT,
             `FirstName` varchar(80) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,
             `LastName` varchar(80) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,
             `City` varchar(80) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,
             `State` varchar(80) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,
              PRIMARY KEY (`ID`)
            ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4";

$mysqli->query( $queryText );

if (!empty($_POST)) :
    // Dynamically create placeholder values for prepare statement
    $Placeholder = "";
    foreach ($_POST as $val=>$formVal) :
        $Placeholder .=  "?,";
    endforeach;

    // Trim extra comma from end
    $Placeholder = rtrim($Placeholder,',');

    // Build the prepared statement
    $queryText = "INSERT INTO xmembers (";
    $queryText .=  implode(',', array_keys($_POST)); 
    $queryText .=  ") VALUES ($Placeholder)";

    $stmt = $mysqli->prepare( $queryText );
    
    // Not in use - try to determine data types from database
    $sqlSchema = "SELECT DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS 
                                    WHERE TABLE_NAME = 'xmembers' AND COLUMN_NAME <> 'ID'";
    $mysqli->query($sqlSchema);

    $Types = "";
    $Values = "";
    foreach ($_POST as $val=>$formVal) :
        global $$val; // Subdues "undefined variable" errors
        $Types .=  (is_string($val)) ? "s," : "i,"; // needs work
        if ($formVal === "") $formVal = NULL;
        $$val .= $formVal;
        $Values .= "$formVal,";
    endforeach;
    
    $Values = array_filter(explode(",",$Values));
    // remove trailing comma, create array
    //$Values = explode(",",rtrim($Values));
    $Types = str_replace(',','',$Types);

    if( $stmt ) :
        $stmt->bind_param($Types, ...$Values);
        $stmt->execute();
    else :
        echo "<pre>";
            print_r($mysqli->error);
        echo "</pre>";
    endif;
endif;

$mysqli->close();
?>
<!DOCTYPE HTML>
<html>
<head>
<title>Prepared Statement Raw Test Page</title>
</head>

<body>

<style>
html,body {
    height: 100%;
    width: 100%;
    margin: 0;
}

body {
    font-family: Arial, Helvetica, sanserif;
    font-size: 16px;
    color: #f00000;
    display: flex;
    background-color: #fafafa;
}

form {
    margin: auto;
}

input[type=text] {
    padding:10px;
    border:0;
    box-shadow:0 0 15px 4px rgba(0,0,0,0.06);
}

* {
    box-sizing:border-box;
}

textarea {
    width:100%;
    resize: vertical;
    padding:15px;
    border-radius:15px;
    border:0;
    box-shadow:4px 4px 10px rgba(0,0,0,0.06);
    height:150px;
}
</style>

 <form  action = "test3.php" method = "post">
    <label for="FirstName">First Name</label><br>
    <input type="text" id="FirstName" name="FirstName"><br>
    <label for="LastName">Last Name</label><br>
    <input type="text" id="LastName" name="LastName"><br>
    <label for="City">City</label><br>
    <input type="text" id="City" name="City"><br>
    <label for="State">State</label><br>
    <input type="text" id="State" name="State"><br>
    <input type="submit" value="Post">
</form> 

</body>
</html>

Leave a Comment