Mysqli Insert Prepared Statement In For Loop Is Not Working Except For The First Iteration

[sonal]

Following script inserts only one (first iteration) record. For next iterations, mysqli_stmt_execute never works. Why?

 

 

 

 

$dbc = database connection //just to shortan the script here

$mid[] = {1,2,3,4}; //to short out the script here.

$q5 = "INSERT INTO user_book_trn(user_id, member_id, book_id, date_read, lang_id) VALUES (?, ?, ?, now(), ?)";

 

$s5 = mysqli_prepare($dbc, $q5);

 

//Bind the variables:

mysqli_stmt_bind_param($s5, 'iiii', $user_id, $member_id, $book_id, $lang_id);

foreach($mid as $mk => $mv) {

echo "member id value : $mv \n";

$q2 = "SELECT user_id, member_id, book_id from user_book_trn where user_id = {$_SESSION['myuser']['userid']} and member_id = {$mv} and book_id= {$w}";

$r3 = mysqli_query($dbc, $q2);

 

if (mysqli_num_rows($r3) == 0) { //title is available for this user.

 

 

//Assign the values to variables:

$user_id = (int)$_SESSION['myuser']['userid'];

$member_id = (int)$mv;

$book_id = (int)$w;

$lang_id = (int)$_SESSION['lid'];

 

//just to check each iteration gets new values:

echo "user_id : $user_id \n";

echo "member_id : $member_id \n";

echo "book_id : $book_id \n";

 

//Execute the query:

mysqli_stmt_execute($s5);

if(mysqli_affected_rows($dbc) == 1)

{

//If it ran OK..

 

echo "<p><b> The book $t is added. </b></p>";

$_SESSION['bookid'] = $book_id;

 

}

I'm using prepared statements as they are more secure. Is this the right way to use it?

[sonal]

I got the error. There was a primary key violation in my query, so the second row was never inserted. Got it solved!!

[Larry]

Kudos for figuring it out and thanks for sharing.