CODEWITHSUNDEEP
phpmysqlcodeigniter
ynnhoj24
ynnhoj24

Reputation: 71

How to insert value into another table after I inserted values into a different table

How can I insert or change the 'submitted' field in 'contestants' table into '1' after I inserted data into 'tbl_rate' table ??

Table 'tbl_rate'

---------------------------------
rate_id  judge_id  cont_id  score
---------------------------------
14      | 36     |     5 |  10
---------------------------------

Table 'contestants'

id  event_id  name  gender  address  date_created  submitted
------------------------------------------------------------
5     | 25  |john doe |male |Texas |  2017-03-06|   0
------------------------------------------------------------

WHERE 'id' in 'contestants' table is foreign key in 'cont_id' on 'tbl_rate'

Upvotes: 1

Views: 61

Answers (2)

manian
manian

Reputation: 1438

Please try the below code,

$sql = "INSERT INTO tbl_rate (judge_id, cont_id, score) VALUES ('".$judge_id."', '".$cont_id."', '".$score."')";

if ($conn->query($sql) === TRUE) {
  $sql = "UPDATE contestants SET submitted=1 WHERE id=".$cont_id;
  if ($conn->query($sql) === TRUE) {
    echo "Record updated successfully";
  } else {
    echo "Error updating record: " . $conn->error;
  }
 } else {
   echo "Error: " . $sql . "<br>" . $conn->error;
 }

and define the variables before your query

Upvotes: 0

Mayank Pandeyz
Mayank Pandeyz

Reputation: 26258

Make two individual query, one for tbl_rate (insert) and another for contestants (update) and run it in sequence i.e. tbl_rate first and on success of it run second query.

You can also use transaction to maintain the data integrity.

Upvotes: 1

Related Questions