You may notice a small buildup between articles, and that’s intentional. No reason to overwhelm or dilute the topic. In this article, we’ll answer one additional question related to inserting data with prepared statements: do you need to keep binding variables for each set of data that you’re inserting?

Let’s just spoil that response: you don’t. Setup the binding once, change the variables, and execute. It may look a little strange, but as long as you know the flow, it will work.

https://blog.devgenius.io/php-p86-mysql-insert-with-prepared-statements-57f37daeb109

Recap

<?php
namespace Authors;

require_once("DB.php");

use Database\DB;

class Author
{
    public function insert( array $data )
    {
        $sql = "INSERT INTO authors (first_name, last_name, email) 
                VALUES (?, ?, ?)";

        $db = new DB;
        $mysqli = $db->_connection;

        $statement = $mysqli->prepare( $sql );
        $statement->bind_param("sss", $data['first_name'], $data['last_name'], $data['email']);
        $statement->execute();

        $statement->close();
        $db->close();
    }
}

Our Author class has an insert method. It creates a $statement object and binds the parameters. Once bound, we call the execute method to process the insertion into the authors table.

use Authors\Author;

$data = [
    "first_name" => "Harrison",
    "last_name"  => "Cajic",
    "email"      => "harrison@example.com"
];

$author = new Author;
$author->insert($data);

Simple Single Insertions

<?php

$mysqli = new mysqli(
    "localhost",
    "admin",
    "E9bIyRDwzidT",
    "dino_test_db"
);

if ( $mysqli->connect_error ) {
    die( $mysqli->connect_error );
}

$sql = "INSERT INTO authors (first_name, last_name, email) 
                VALUES (?, ?, ?)";

$statement = $mysqli->prepare( $sql );

$first_name = "Dino";
$last_name  = "Cajic";
$email      = "dino@example.com";

$statement->bind_param("sss", $first_name, $last_name, $email);
$statement->execute();

$statement->close();
$mysqli->close();

It’s just one file to keep it simple. We’ll worry about modifying our Author and DB later. This is what it looks like to insert one time. We established the connection, prepared a statement, bound our variables, and executed the insertion. Let’s see what it looks like if we want to add another author.

Multiple Insertions

<?php

$mysqli = new mysqli(
    "localhost",
    "admin",
    "E9bIyRDwzidT",
    "dino_test_db"
);

if ( $mysqli->connect_error ) {
    die( $mysqli->connect_error );
}

$sql = "INSERT INTO authors (first_name, last_name, email) 
                VALUES (?, ?, ?)";

$statement = $mysqli->prepare( $sql );

$first_name = "Dino";
$last_name  = "Cajic";
$email      = "dino@example.com";

$statement->bind_param("sss", $first_name, $last_name, $email);
$statement->execute();

// New Code Start
$first_name = "John";
$last_name  = "Johnson";
$email      = "john@example.com";
$statement->execute();
// New Code End

$statement->close();
$mysqli->close();

Look at the new code.

$first_name = "John";
$last_name  = "Johnson";
$email      = "john@example.com";
$statement->execute();

That’s all there is to it. There’s one thing that it seems like it should be there, but its not, and that’s the following statement:

$statement->bind_param("sss", $first_name, $last_name, $email);

We already bound our variables. We don’t have to do it again. We just have to call our execute method.

Modifying Our Author Insert

Let’s take a look at a time when we’ll need to actually bind each time. What would happen if we passed an array of arrays? We’ll have to bind it each time since we’re technically pointing at different variables each time. There’s no way to bind $data[x][‘first_name’]. What is x? It’s an index value of a multidimensional array, and because it changes, the values have to be bound each time. Let me show you what I mean.

<?php
require_once("./Author.php");

use Authors\Author;

$data = [
    [
        "first_name" => "Dino",
        "last_name"  => "Cajic",
        "email"      => "dino@example.com"
    ],
    [
        "first_name" => "Frank",
        "last_name"  => "Josh",
        "email"      => "frank@example.com"
    ],
    [
        "first_name" => "Stewart",
        "last_name"  => "Steven",
        "email"      => "stewart@example.com"
    ],
];

$author = new Author;
$author->insert($data);

The $data that we pass is an array of arrays. Each array contains data for one author. In this case, there are three authors that we want to insert. All we have to do is tweak our insert statement in our Author class to get it to work.

<?php
namespace Authors;

require_once("DB.php");

use Database\DB;

class Author
{
    public function insert( array $data )
    {
        $sql = "INSERT INTO authors (first_name, last_name, email) 
                VALUES (?, ?, ?)";

        $db = new DB;
        $mysqli = $db->_connection;

        $statement = $mysqli->prepare( $sql );

        foreach( $data as $author ) {
            $statement->bind_param("sss", $author['first_name'], $author['last_name'], $author['email']);
            $statement->execute();
        }

        $statement->close();
        $db->close();
    }
}

Nothing really changed other than we wrapped our bind_param and execute within a foreach loop that loops through each of the $data array elements. We could be more specific and change $data to $authors.

<?php
namespace Authors;

require_once("DB.php");

use Database\DB;

class Author
{
    public function insert( array $authors )
    {
        $sql = "INSERT INTO authors (first_name, last_name, email) 
                VALUES (?, ?, ?)";

        $db = new DB;
        $mysqli = $db->_connection;

        $statement = $mysqli->prepare( $sql );

        foreach( $authors as $author ) {
            $statement->bind_param("sss", $author['first_name'], $author['last_name'], $author['email']);
            $statement->execute();
        }

        $statement->close();
        $db->close();
    }
}

Each of the $author elements is bound and the statement is executed.

foreach( $authors as $author ) {
    $statement->bind_param("sss", $author['first_name'], ...);
    $statement->execute();
}

What about the DB class? It stays the same, except I removed our legacy insert statement. We’re not going to need it anymore. The DB class just allows us to establish a connection to the database.

https://github.com/dinocajic/php-youtube-tutorials