Duplicate sets of records in MySQL table on INSERT INTO using foreach loop

Question

I cannot figure out why the foreach loop iterates more times than the count of elements.

The function passes two associative arrays: form data and franchise data. The form data elements are converted to variables. I want to loop through the franchise array (unknown length) and insert the form data for each unique franchise ID. Each record will contain identical form data (from user) but different data for the franchise_id, franchisor_id and franchise_name fields.

What's happening is that if one franchise is selected two identical records are inserted. If two, then a total of four are inserted (duplicate set of the two). However, if three are selected, it inserts nine rows (three sets of the three choices). This, despite the array count being 3.

I have modified the code countless times without success.

Here's the function:

public static function setLeadData($franchises, $formData)
{

    $first_name    = $formData[0]['value'];
    $last_name     = $formData[1]['value'];
    $telephone     = $formData[2]['value'];
    $email         = $formData[3]['value'];
    $zipcode       = $formData[4]['value'];
    $networth      = $formData[5]['value'];
    $liquidcapital = $formData[6]['value'];
    $message       = $formData[7]['value'];

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

        $sql = "INSERT INTO leads_franchises SET
                franchise_id    = :franchise_id,
                franchisor_id   = :franchisor_id,
                franchise_name  = :franchise_name,
                first_name      = :first_name,
                last_name       = :last_name,
                telephone       = :telephone,
                email           = :email,
                zipcode         = :zipcode,
                networth        = :networth,
                liquidcapital   = :liquidcapital,
                message         = :message";
        $stmt = $db->prepare($sql);

        foreach($franchises as $franchise)
        {
            $parameters = [
                ':franchise_id'   => $franchise['franchise_id'],
                ':franchisor_id'  => $franchise['franchisor_id'],
                ':franchise_name' => $franchise['franchise_name'],
                ':first_name'     => $first_name,
                ':last_name'      => $last_name,
                ':telephone'      => $telephone,
                ':email'          => $email,
                ':zipcode'        => $zipcode,
                ':networth'       => $networth,
                ':liquidcapital'  => $liquidcapital,
                ':message'        => $message
            ];

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

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

Here's the franchises[] with three selections:

[0] => Array
    (
        [franchise_id] => 2
        [franchisor_id] => 3
        [franchise_name] => Franchise One
        [logo] => image2.jpg
        [logo_thumb] => thumb_image2.jpg
        [min_capital] => 200000
        [description] => description
        [website] => example2.com
        [inquiry_email] => info@gmail.com
        [created_at] => 2018-01-17 13:05:51
        [updated_at] => 2018-01-25 17:09:51
    )

[1] => Array
    (
        [franchise_id] => 3
        [franchisor_id] => 3
        [franchise_name] => Franchise Two
        [logo] => image3.jpg
        [logo_thumb] => thumb_image3.jpg
        [min_capital] => 100000
        [description] => description
        [website] => example3.com
        [inquiry_email] => info@gmail.com
        [created_at] => 2018-01-21 18:03:39
        [updated_at] => 2018-01-25 17:10:02
    )

[2] => Array
    (
        [franchise_id] => 4
        [franchisor_id] => 3
        [franchise_name] => Franchise Three
        [logo] => image4.jpg
        [logo_thumb] => thumb_image4.jpg
        [min_capital] => 100000
        [description] => description
        [website] => example4.com
        [inquiry_email] => info@gmail.com
        [created_at] => 2018-01-25 10:25:52
        [updated_at] => 2018-01-25 17:58:08
    )

If anyone can see my error(s) in the code or my approach, I would really appreciate it.

Check this answer: https://stackoverflow.com/a/21653119/4813127 — tyro, Jan 27 '18 at 17:39
@tyro Unfortunately, I cannot interpret the code at this link and apply it to my situation. Hopefully there is a more straightforward method. — JimB814, Jan 27 '18 at 19:32

score 1 · Answer 1 · answered Jan 27 '18 at 18:36

1

add :

 $stmt->reset();

to the end of your loop after this line:

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

answered Jan 27 '18 at 18:36

USER249

1,080
7
14

try movinge this as the first line in the loop $stmt = $db->prepare($sql); and if it doesn't work try using close() instead of reset. – USER249 Jan 27 '18 at 20:09
Thanks! Bringing `$stmt = $db->prepare($sql);` into the `foreach` loop produced the same duplication. Error thrown using `$stmt->reset();` or `$stmt->close();` right before `foreach` loops closing curly brace. – JimB814 Jan 27 '18 at 20:24
throws a 500 internal error `jquery-3.2.1.min.js:4 POST http://localhost/contact-franchisor 500 (Internal Server Error`. I'm echoing results back to the console.log because it begins as an Ajax request. I just now am testing inserting without placeholders, putting everything inside the foreach loop. It threw an error, which seems impossible, so I am looking for errors. – JimB814 Jan 27 '18 at 20:46

score 0 · Answer 2 · answered Feb 07 '18 at 00:37

I can't tell from the question if the OP is asking about the 500 error or the duplicate rows. If it's the duplicate rows, I have to ask "Is franchise_id the only component of your Primary Key, or do you have a compound primary key that might include other fields (such as franchisor_id)?". If it's the 500 error, I'd say to stay away from the "INSERT INTO tablename SET". I don't know what version of MySQL you're using but "SET" is better suited for an UPDATE statement. INSERT should have "INSERT INTO tablename (col1, col2, col3) VALUES (int_1, 'char2' 'varchar3') or something like that. You'd need to either use the '?' placeholder for the values or bind some variables to use. Please see https://www.w3schools.com/php/php_mysql_prepared_statements.asp

The answer is [here](https://stackoverflow.com/questions/48488420/duplicate-sets-of-data-inserted-into-table). The error was in the Ajax request. It's explained with useful links in the UPDATE section of the question. — JimB814, Feb 08 '18 at 16:09
Oh. You didn't post the Ajax portion (or I didn't see it) so that would have been helpful to see. — McAuley, Feb 09 '18 at 20:54

Duplicate sets of records in MySQL table on INSERT INTO using foreach loop

2 Answers2