Duplicate sets of data inserted into table

Question

Why does this code insert duplicate sets of data?

Here's the $franchises array (2 = count($franchises)) passed to the function:

(
    [0] => Array
        (
            [franchise_id] => 3
            [franchisor_id] => 3
            [franchise_name] => Fitness Freaks
        )

    [1] => Array
        (
            [franchise_id] => 4
            [franchisor_id] => 3
            [franchise_name] => Gyms Galore
        )

)

Here's the function using a for loop to insert the data into the table:

public static function setLeadData($franchises)
{
   try
   {
        $db = static::getDB();

        $sql = "INSERT INTO leads_franchises SET
               franchise_id    = :franchise_id,
               franchisor_id   = :franchisor_id,
               franchise_name  = :franchise_name";
        $stmt = $db->prepare($sql);

        for($i = 0; $i < count($franchises); $i++)
        {
            $result = $stmt->execute([
                ':franchise_id'   => $franchises[$i]['franchise_id'],
                ':franchisor_id'  => $franchises[$i]['franchisor_id'],
                ':franchise_name' => $franchises[$i]['franchise_name']
            ]);
        }

        return $result;
    }
    catch(PDOException $e)
    {
        echo $e->getMessage();
        exit();
    }
}

Here's the same function using a foreach loop. It also inserts duplicate sets of data (array with count of 1 inserts 2 identical rows, with count of 2 inserts 2 sets of identical rows, with 3 inserts 6, etc.):

public static function setLeadData($franchises)
{
   try
   {
        $db = static::getDB();

        $sql = "INSERT INTO leads_franchises SET
               franchise_id    = :franchise_id,
               franchisor_id   = :franchisor_id,
               franchise_name  = :franchise_name";
        $stmt = $db->prepare($sql);

        foreach($franchises as $franchise)
        {
            $result = $stmt->execute([
                ':franchise_id'   => $franchise['franchise_id'],
                ':franchisor_id'  => $franchise['franchisor_id'],
                ':franchise_name' => $franchise['franchise_name']
            ]);
        }

        return $result;
    }
    catch(PDOException $e)
    {
        echo $e->getMessage();
        exit();
    }
}

Here's the function with the MySQL components inside the foreach loop:

public static function setLeadData($franchises)
{
   try
   {
        $db = static::getDB();

        foreach($franchises as $franchise)
        {
            $sql = "INSERT INTO leads_franchises SET
                   franchise_id    = :franchise_id,
                   franchisor_id   = :franchisor_id,
                   franchise_name  = :franchise_name";
            $stmt = $db->prepare($sql);
            $result = $stmt->execute([
                ':franchise_id'   => $franchise['franchise_id'],
                ':franchisor_id'  => $franchise['franchisor_id'],
                ':franchise_name' => $franchise['franchise_name']
            ]);
        }

        return $result;
    }
    catch(PDOException $e)
    {
        echo $e->getMessage();
        exit();
    }
}

Here's the function with stmt = $db->prepare($sql) inside the foreach loop:

public static function setLeadData($franchises)
{
   try
   {
        $db = static::getDB();

        $sql = "INSERT INTO leads_franchises SET
               franchise_id    = :franchise_id,
               franchisor_id   = :franchisor_id,
               franchise_name  = :franchise_name";

        foreach($franchises as $franchise)
        {                
            $stmt = $db->prepare($sql);
            $result = $stmt->execute([
                ':franchise_id'   => $franchise['franchise_id'],
                ':franchisor_id'  => $franchise['franchisor_id'],
                ':franchise_name' => $franchise['franchise_name']
            ]);
        }

        return $result;
    }
    catch(PDOException $e)
    {
        echo $e->getMessage();
        exit();
    }
}

(Edit addition): Here's the function using bindParam inside a foreach loop:

public static function setLeadData($franchises)
{

    try
    {
        $db = static::getDB();

        $sql = "INSERT INTO leads_franchises (franchise_id, franchisor_id, franchise_name)
                VALUES (:franchise_id, :franchisor_id, :franchise_name)";

        $stmt = $db->prepare($sql);

        foreach($franchises as $franchise)
        {
            $stmt->bindParam(':franchise_id', $franchise['franchise_id']);
            $stmt->bindParam(':franchisor_id', $franchise['franchisor_id']);
            $stmt->bindParam(':franchise_name', $franchise['franchise_name']);

            $result = $stmt->execute();
        }

        return $result;
    }
    catch(PDOException $e)
    {
        echo $e->getMessage();
        exit();
    }
}

Every version produces the same duplication.

Obviously, I'm pretty frustrated. I would really appreciate understanding why and how this code is resulting in the duplication.

Any help is greatly appreciated!

Table after function executes:

UPDATE

To help anyone who might encounter this issue, I wanted to post the cause of the duplication. As proven by Ermat Kiyomov, the function code was correct.

The error was in the Ajax code. The form was submitting twice. The solution, provided by Chris Sercombe is here.

In addition to e.preventDefault(), I needed to add e.stopImmediatePropagation. More info here.

The submit portion of the jQuery Ajax looks like this:

$("#form").submit(function(e) {
    e.preventDefault();
    e.stopImmediatePropagation();

    ....

I'm getting confused, which set has problem ? You have added 4 sets — Ravi, Jan 28 '18 at 16:20
@Ravi. Each variation of the function produces the same unwanted results, viz. duplication of the records I am trying to store. — JimB814, Jan 28 '18 at 16:23
Put `debug_print_backtrace()` at the beginning of your function, and see why it is executed twice. — Paul Spiegel, Jan 28 '18 at 16:37
@PaulSpiegel Where will the function display its results? My errors are displaying in the console.log because the code begins as an jQuery Ajax request. — JimB814, Jan 28 '18 at 17:06
@PaulSpiegel. I added (edit) another version using `bindParam()`. It works, but with the same duplication. — JimB814, Jan 28 '18 at 17:18

score 2 · Accepted Answer · answered Jan 28 '18 at 17:59

I've tested following sample and no duplicates occured:

class Franchises
{
    protected static function getDB()
    {
        $conn = NULL;

        try
        {
            $conn = new PDO("mysql:host=127.0.0.1;dbname=franchises_db", "dbuser", "dbpassword");
            $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        }
        catch(PDOException $e)
        {
            echo $e->getMessage();
            exit();
        }
        return $conn;
    } 
    public static function setLeadData($franchises)
    {
       try
       {
            $db = static::getDB();

            $sql = "INSERT INTO leads_franchises SET
                   franchise_id    = :franchise_id,
                   franchisor_id   = :franchisor_id,
                   franchise_name  = :franchise_name";
            $stmt = $db->prepare($sql);

            foreach($franchises as $franchise)
            {
                $result = $stmt->execute([
                    ':franchise_id'   => $franchise['franchise_id'],
                    ':franchisor_id'  => $franchise['franchisor_id'],
                    ':franchise_name' => $franchise['franchise_name']
                ]);
            }

            return $result;
        }
        catch(PDOException $e)
        {
            echo $e->getMessage();
            exit();
        }
    }
}

$vals = [
    [
        "franchise_id" => 3,
        "franchisor_id" => 3,
        "franchise_name" => "Fitness Freaks"
    ],
    [
        "franchise_id" => 4,
        "franchisor_id" => 3,
        "franchise_name" => "Gyms Galore"
    ]
];

Franchises::setLeadData($vals);

and leads_franchises table create code is:

CREATE TABLE `leads_franchises` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `franchise_id` INT(11) NULL DEFAULT '0',
    `franchisor_id` INT(11) NULL DEFAULT '0',
    `franchise_name` VARCHAR(128) NULL DEFAULT '',
    PRIMARY KEY (`id`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB;

Thanks for taking the time to do this. I was next planning to test the function outside of the Ajax request, thinking that maybe there's a bug somewhere in it. I will duplicate your test, and let you know if I achieve the same results, which I expect to. — JimB814, Jan 28 '18 at 18:19
I tested the function independently and achieved the same results, proving that the function as written works. The answer to my question, then, is that the function as written in the various forms does work, and therefore I have an issue elsewhere. Thanks for taking time to answer my post! — JimB814, Jan 28 '18 at 18:35

Duplicate sets of data inserted into table

1 Answers1

Linked