Only first record of an array of multiple arrays in a foreach loop is storing in mysql database

Question

I have some php variables that capture multiple data in the form of an array and i want to input these values in a mysql table.

$surname = unserialize(base64_decode($_POST['surname']));
$firstname = unserialize(base64_decode($_POST['firstname']));
$othername = unserialize(base64_decode($_POST['othername']));
$dob = unserialize(base64_decode($_POST['dob']));
$gender = unserialize(base64_decode($_POST['gender']));
$email = unserialize(base64_decode($_POST['email']));
$phone = unserialize(base64_decode($_POST['phone']));
$location = unserialize(base64_decode($_POST['location']));

$stmt = $connQlife->prepare("INSERT INTO cimbooking (surname, firstname, othername, dob, gender, email, phone, location) VALUES (?, ?, ?, ?, ?, ?, ?, ?)");

foreach($surname as $key=>$sname){              

    $stmt->bind_param('ssssssss', $sname, $firstname[$key], $othername[$key], $dob[$key], $gender[$key], $email[$key], $phone[$key], $location[$key]);
    $stmt->execute();

}
$stmt->close();

What happens when this runs is what i can't seem to understand. Looking at the code, this should work just fine. Now in a scenario where the number of records is supposed to be 3, I should see 3 records in the table after submission, but only the first record is being entered into the mysql table. Furthermore, on troubleshooting, i find that when the code runs, the first record that enters the table occupies say id 1. If i refresh the page so the code runs again, the first record of that new command occupies id 4 and not id 2 like it should be. (The id field in the database is already set to auto increment).

Which seems to me like when one record enters the table, the other records enter and disappear or something. I don't seem to understand why.

Could you give us some example data for the surname/firstname etc arrays — Sam Dean
– Sam Dean, Commented Nov 28, 2019 at 16:04
@Sam Dean These are the values of the array: Array ( [0] => Aderemi [1] => Aderemi [2] => Aderemi )---- Array ( [0] => m1 [1] => m2 [2] => m3 )--- Array ( [0] => [email protected] [1] => [email protected] [2] => [email protected] ) — Michael Agbogidi
– Michael Agbogidi, Commented Nov 28, 2019 at 16:17
cimID bigint(20) NO PRI NULL auto_increment, surname varchar(50) YES NULL, firstname varchar(50) YES NULL, othername varchar(50) YES NULL, dob varchar(20) YES NULL, gender varchar(10) YES NULL, email varchar(128) YES NULL, phone varchar(50) YES NULL, location varchar(256) YES NULL; — Michael Agbogidi
– Michael Agbogidi, Commented Nov 28, 2019 at 16:23

Sam Dean · Accepted Answer · 2019-11-28 16:28:06Z

1

I believe the problem is that once you've called $stmt->bind_param() then you can't do it again. So the $stmt->execute() is submitting the same data to the database each time. For some reason this is failing due to, I guess, some duplication rules.

Try using $stmt->bind_result() to see what happens when you bind and $stmt->get_result() to get the result of each execute.

I think you need to use $stmt->reset() after each execution so you can do the next one.

PHP.net manual for mysqli_stmt has links to all the above methods.

answered Nov 28, 2019 at 16:28

Sam Dean

4048 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Michael Agbogidi Over a year ago

Thanks for taking time to help. Firstly, there is no duplication rules in effect on the table. secondly, in this case i'm inserting records to a table. Do i need to get the results and bind the results since i'm not actually printing to screen?

Sam Dean Over a year ago

In that case my guess would be that binding the second and future times is "breaking it" and so the insert is failing. You can see the results of the insertion using get_results()

Michael Agbogidi Over a year ago

I got it working now. All I did was to delete the table and recreate it. Now all is fine.

Collectives™ on Stack Overflow

Only first record of an array of multiple arrays in a foreach loop is storing in mysql database

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related