How do I convert this mysqli array statment into a mysqli prepared statement?

Question

How do I rewrite this mysqli statement into a prepared statement? I tried converting the code provided below into the prepared statement. But, only the first row of values get inserted not the rest. I am trying to integrate dynamic text fields into my form. So, user can easily add or remove input fields.

Original code: action.php

<?php
    
        include_once('config.php');
    
        $userData = count($_POST["name"]);
        
        if ($userData > 0) {
            for ($i=0; $i < $userData; $i++) { 
            if (trim($_POST['name'] != '') && trim($_POST['email'] != '')) {
                $name   = $_POST["name"][$i];
                $email  = $_POST["email"][$i];
                $query  = "INSERT INTO users (name,email) VALUES ('$name','$email')";
                $result = mysqli_query($mysqli, $query);
            }
            }
            echo "Data inserted successfully";
        }else{
            echo "Please Enter user name";
        }
    
    ?>

my try:

<?php

include_once('config.php');

$name = $email = "";
$name_err = $email_err = "";


    $userData = count($_POST["name"]);
    
    if ($userData > 0) {
        for ($i=0; $i < $userData; $i++) { 
        if (trim($_POST['name'] != '') && trim($_POST['email'] != '')) {
            
            
    //Validate Name
    $input_name = trim($_POST["name"][$i]);
    if(empty($input_name)){
        $name_err = "Please enter a name.";
    } elseif(!filter_var($input_name, FILTER_VALIDATE_REGEXP, array("options"=>array("regexp"=>"/^[a-zA-Z\s]+$/")))){
        $name_err = "Please enter a valid name.";
    } else{
        $name = $input_name;
    }
    
    
     // Validate Email
    $input_email = trim($_POST["email"][$i]);
    if(empty($input_email)){
        $email_err = "Please enter the email.";     
    }else{
        $email = $input_email;
    }
            
$query  = "INSERT INTO users (name,email) VALUES (?,?)";
            
if($stmt = $mysqli->prepare($query)){
            // Bind variables to the prepared statement as parameters
            $stmt->bind_param("ss", $param_name,$param_email);
            
            // Set parameters
            $param_name = $name;
            $param_email = $email;
            
            // Attempt to execute the prepared statement
            if($stmt->execute()){
                // Records created successfully. Redirect to landing page
                header("location: dynamic.php");
                exit();
            } else{
                echo "Something went wrong. Please try again later.";
            }
        }
         
        // Close statement
        $stmt->close();
    }
    
    // Close connection
    $mysqli->close();
}
    }
?>

Does this help? https://stackoverflow.com/a/66081450/1839439 — Dharman, Mar 16 '21 at 10:11

score 0 · Accepted Answer · answered Mar 16 '21 at 06:15

I'm having trouble following your code (tried re-indenting, think I have it).

<?php
include_once('config.php');

$name = $email = "";
$name_err = $email_err = "";


    $userData = count($_POST["name"]);
    
    if ($userData > 0) {
        for ($i=0; $i < $userData; $i++) { 
            if (trim($_POST['name'] != '') && trim($_POST['email'] != '')) {
                //Validate Name
                $input_name = trim($_POST["name"][$i]);
                    if(empty($input_name)){
                        $name_err = "Please enter a name.";
                    } elseif(!filter_var($input_name, FILTER_VALIDATE_REGEXP, array("options"=>array("regexp"=>"/^[a-zA-Z\s]+$/")))){
                        $name_err = "Please enter a valid name.";
                    } else {
                        $name = $input_name;
                    }
                // Validate Email
                $input_email = trim($_POST["email"][$i]);
                if(empty($input_email)){
                    $email_err = "Please enter the email.";     
                } else {
                    $email = $input_email;
                }
                $query  = "INSERT INTO users (name,email) VALUES (?,?)";
                if($stmt = $mysqli->prepare($query)){
                    // Bind variables to the prepared statement as parameters
                    $stmt->bind_param("ss", $param_name,$param_email);
                    // Set parameters
                    $param_name = $name;
                    $param_email = $email;
                    // Attempt to execute the prepared statement
                    if($stmt->execute()){
                    // Records created successfully. Redirect to landing page
                        header("location: dynamic.php");
                        exit();
                    } else {
                    echo "Something went wrong. Please try again later.";
                    }
                }
        // Close statement
            $stmt->close();
            }
    // Close connection
        $mysqli->close();
        }
    }

?>

I think you're getting all the way to this point:

                    if($stmt->execute()){
                    // Records created successfully. Redirect to landing page
                        header("location: dynamic.php");
                        exit();
                    } else {
                    echo "Something went wrong. Please try again later.";
                    }

You should first try removing the exit, that stops your script right there. Since your first row was added, I assume that your prepared statement does work, but the exit closes down your script after your first execute. You should also remove the header because that is going to repeat over and over until your for loop has finished. It's going to send repeated redirect responses to the browser. The header redirect could be used after you are successfully done with all of your executes. If you're going to use that I think it would be better located after you close. You could put the exit below the 'something went wrong' echo to stop your script if something goes wrong (though it would be better to handle that another way as well).

If I might suggest, you may want to validate your input before getting into the binding and inserting.

Also have a look at this (inserting multiple rows). You might want to prepare your statement outside the loop, you only need to prepare it once.

I'm not sure if you validate the email, but there is a pre-made filter for that.

Finally, please don't let the user input unlimited data to your database. Please consider rejecting the input if the `count' is too high, at the very least. I would recommend considerably more validation of user input than is present here.

How do I convert this mysqli array statment into a mysqli prepared statement?

1 Answers1