0

I am trying to create event using mysqli prepared statement but the code is not working i found out that when i put the mysqli statement inside the prepared statement quot some of the clause such as ON,WHERE,SET,AND,UPDATE all turn red in color I have been trying to figure it out but not yet and the code is not working please i need someone to fix my code.

$d_sql = $connect->prepare("CREATE EVENT stop_fad_1 ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 MINUTE ON COMPLETION PRESERVE DO UPDATE fad SET active_status=? WHERE fad_id=? AND sender=? AND reciever=?");
             $d_sql->bind_param("iiii",$complete_status,$fad_id,$sender,$reciever);
             $d_sql->execute();

then am getting error like this but i have check out my data i supplied to the statement they are all correct

Fatal error: Call to a member function bind_param() on boolean in C:\xampp\htdocs\Alertmedia\fad\php\comfirm_gp_fad.php on line 35
Samuel Liew
  • 76,741
  • 107
  • 159
  • 260
alertme
  • 61
  • 7

1 Answers1

0

CREATE VIEW is not supported in a prepared statement.

https://dev.mysql.com/doc/refman/5.7/en/sql-syntax-prepared-statements.html lists the SQL statements that may be used in prepared statements.

You will have to interpolate your variables into the CREATE EVENT statement before it is parsed. This means you must be careful to ensure the variables include only expected format (for example, an integer).

$complete = (int) $complete;
fad_id = (int) $fad_id;
$sender = (int) $sender;
$reciever = (int) $reciever;

$d_sql = $connect->query("
  CREATE EVENT stop_fad_1 ON SCHEDULE 
    AT CURRENT_TIMESTAMP + INTERVAL 1 MINUTE 
    ON COMPLETION PRESERVE 
  DO 
    UPDATE fad SET active_status=$complete_status 
    WHERE fad_id=$fad_id AND sender=$sender AND reciever=$reciever
  ");
Bill Karwin
  • 538,548
  • 86
  • 673
  • 828