Multiple queries mysqli

Question

I'm trying to input multiple queries guys using mysqli. Yet it's not populating the database. Any ideas?

$q2="UPDATE ticketinfo SET ticketstatus = $status where ticketno = $ticket; 
  insert into ticketinfo (remarks) values ('$remarks')";

$ex2= mysqli_multi_query($conn,$q2);

Answer 1

SQL queries should be executed sequentially. Never use mysqli_multi_query() with variable input. You should be using parameterized prepared statements. There is hardly any use case for mysqli_multi_query() at all.

Your code should look like this:

// your mysqli connection
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$mysqli = new mysqli('localhost', 'username', 'password', 'dbname');
$mysqli->set_charset('utf8mb4'); // always set the charset

// First query
$stmt = $mysqli->prepare('UPDATE ticketinfo SET ticketstatus = ? WHERE ticketno = ?');
$stmt->bind_param('ss', $status, $ticket);
$stmt->execute();

// Second query
$stmt = $mysqli->prepare('INSERT INTO ticketinfo (remarks) VALUES (?)');
$stmt->bind_param('s', $remarks);
$stmt->execute();

I used two prepared statements and bound the input separately. This much better, cleaner and safer option than mysqli_multi_query().