After a long time of receiving help, I have finally created my account here. However, this time I couldn't find an answer to my question.
I am new to MySqli and I am currently attempting to run a script that previously worked in phpMyAdmin.
The issue arises when I try to utilize a 'mysql variable'. Below is the code and the error message:
$consulta = "SET @var= (SELECT ticket_id FROM ost_ticket WHERE number = 410037);
SELECT DISTINCT a.number, a.created, b.address,
SELECT DISTINCT title FROM ost_ticket_thread a WHERE a.ticket_id = @var AND a.source LIKE 'API')title,
SELECT DISTINCT body FROM ost_ticket_thread a WHERE a.ticket_id = @var AND a.source LIKE 'API')body,
d.state,
c.body resposta
FROM ost_ticket a
LEFT JOIN ost_user_email b ON b.user_id = a.user_id
LEFT JOIN ost_ticket_thread c ON c.ticket_id = a.ticket_id
LEFT JOIN ost_ticket_status d ON d.id = a.status_id
WHERE a.ticket_id = @var AND c.id = (SELECT MAX( a.id ) FROM ost_ticket_thread a WHERE a.ticket_id = @var ) ";
$resultado = $MySQLi->query($consulta) OR trigger_error($MySQLi->error, E_USER_ERROR);
$cont = 0;
while ($informacao = $resultado->fetch_object()) {
$data[$cont] = array(
'numprot' => $informacao->number,
'email' => $informacao->address,
'assunto' => $informacao->title,
'status' => $informacao->state,
'body' => $informacao->body,
'resposta' => $informacao->resposta,
'dtcriacao' => $informacao->created,
);
$cont++;
}
echo "{'lista':".json_encode($data)."}";
Even though it worked perfectly fine in phpMyAdmin, I encountered the following error when using the mysqli query function:
Fatal error: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'SELECT distinct a.number,a.created,b.address, (select distinct title from ost_t' at line 3 in /home/u605553750/public_html/JSONResposta.php on line 22
The issue is found on line 22 which corresponds to:
$resultado = $MySQLi->query($consulta) OR trigger_error($MySQLi->error, E_USER_ERROR);
I am seeking guidance regarding the correct way to execute this script using MySqli.
Thank you,
Bruno