checking query execution in foreach loops

[coderscoven]

I have an issue that is bothering me here. I have a php script with several queries which stores information into mysql database. But before doing so, i first check to see whether their execution is okay. For example;

$query1 = $conn->prepare("insert into student(name) values('$stud_name')");
$check1 = $query1->execute();

foreach($subject as $key=>$n){
$query2 = $conn->prepare("insert into subjects(subject_id, title) values('$n', '$title[$key]')");
$check2 = $query2->execute();
}
foreach($teacher as $key=>$n){
$query3 = $conn->prepare("insert into subjects(teacher_id, teacher_name)values('$n', '$teacher[$key]')");
$check3 = $query3->execute();
}

if($check1 && $check2 && $check3){
    echo "Saved";
}
else{
    echo "Error";
}

Now as you see am checking a normal insertion with an array which seems "abnormal". If am to do the checking within the foreach loop, the message echoing would be repeated basing on the number of inputs. Any assistance is welcome.

 

 

 

[requinix]

What bothers me is that you've totally missed the point of prepared statements.

 

- Prepare a statement once before the loop that uses it.

- Don't put values directly into the query. Use placeholders.

- Execute the query with the values for the placeholders.

$query1 = $conn->prepare("insert into student(name) values(:name)");
$check1 = $query1->execute(["name" => $stud_name]);

$query2 = $conn->prepare("insert into subjects(subject_id, title) values(:subject_id, :title)");
foreach($subject as $key=>$n){
$check2 = $query2->execute(["subject_id" => $n, "title" => $title[$key]]);
}

$query3 = $conn->prepare("insert into subjects(teacher_id, teacher_name)values(:teacher_id, :teacher_name)");
foreach($teacher as $key=>$n){
$check3 = $query3->execute(["teacher_id" => $n, "teacher_name" => $teacher[$key]]);
}

if($check1 && $check2 && $check3){
    echo "Saved";
}
else{
    echo "Error";
}

As for your question, you have to decide what you want to do in case of failure. Ignore and keep inserting? Stop inserting and reverse all the additions? Reverse just the additions to the affected table?

[coderscoven]

Okay thanks for that help on prepared statements. Actually I want to stop inserting and reverse all the additions incase of failure.

[requinix]

Then you need transactions. Start a transaction at the start, monitor each operation for success and failure, then commit if everything was successful or rollback if anything failed.

[coderscoven]

To be honest here, I had never heard of transactions in PHP. Its time I learned something new. Thanks

[benanamen]

To be honest here, I had never heard of transactions in PHP. Its time I learned something new. Thanks

 

 

That's because there are no Transaction in Php. That is a Mysql function. Here are a couple links to get you going.

 

http://www.mysqltutorial.org/mysql-transaction.aspx

 

https://www.w3resource.com/mysql/mysql-transaction.php

 

And of course you want to RTFM

 

https://dev.mysql.com/doc/refman/5.7/en/commit.html

[coderscoven]

Great thanks.

[Psycho]

Yes, you need transactions. But, just from a logic perspective the way you were checking for errors was faulty. If $teacher, for example, has five records, the value of $check3 only contains the result of the query for the last record. You could have checked the result of the execution and increment the number of errors on each execution.

[coderscoven]

Yes that's true and its why i was looking for a way to cover that issue.