0

I have a table book_assign and when a new record has been inserted, I want to update previous entries for the stud_id and set the column 'status' to become 0.

 public function insert() {
    $stud_id = mysql_real_escape_string($this->stud_id);
    $teach_id = mysql_real_escape_string($this->teach_id);
    $book_id = mysql_real_escape_string($this->book_id);
    $status = mysql_real_escape_string($this->status);
    $dates = mysql_real_escape_string($this->dates);
    $rating = mysql_real_escape_string($this->rating);
    $comments = mysql_real_escape_string($this->comments);


    $insert = "Insert into book_assign(stud_id,teach_id, book_id, dates, status, rating, comments)
                values('$stud_id', '$teach_id', '$book_id', '$dates', '1', '$rating', '$comments')";



    $res = mysql_query($insert);
    $last_id = mysql_insert_id();
    return $res;
}

This is my code so far, I believe I may have to use a trigger does that have to be done in phpmyadmin?

What I'm also wondering is can this be done using two different queries within this function, I have tried but it doesn't seem to work.

user3103074
  • 5
  • 4

1 Answers1

1

Yes you can with two queries. Write this query after your insert :

update book_assign set status=0 where stud_id='$stud_id';

This can also achieve with trigger. You can set trigger from phpmyadmin.

Please dont use mysql_*. It is deprecated and removed from PHP 7. Use mysqli_* or PDO.

Also your query is open for sql injection.Please read this : How can I prevent SQL injection in PHP?

Community
  • 1
  • 1
Mr. Engineer
  • 3,522
  • 4
  • 17
  • 34