Good,
And the following I have a query
JOIN
that makes me the query to 3 tables, that searches me in the bank for all the notifications of the followers of the currently logged in user. The problem is that I was now trying to fetch information from the user who sent the notification, to show in the notification but the query stops returning me data. From what you can see it returns 500 error server.
Code
header('Cache-Control: no-cache, must-revalidate');
//Alteramos o cabeçalho para que o retorno seja do tipo JSON
header('Content-Type: application/json; charset=utf-8');
session_start();
require_once("../funcoes/funcoes.php");
$user_actual = $_REQUEST['user_logado'];
$result_notificacoes = $conexao->prepare("SELECT * FROM notificacoes as noti
LEFT JOIN notificacoes_visualizacao as noti_vi ON noti_vi.id_notificacao = noti.id
INNER JOIN users_social as user ON user.id = noti.user_id
WHERE noti.user_id IN (SELECT seg.followed FROM seguidores as seg WHERE seg.follower = :user_actual)
AND noti_vi.id_usuario_que_visualizou is NULL
OR noti.user_destino_notificacao = :user_atual2");
$result_notificacoes->bindValue(':user_actual', $user_actual, PDO::PARAM_INT);
$result_notificacoes->bindValue(':user_atual2', $user_actual, PDO::PARAM_INT);
$result_notificacoes->execute();
$return = array();
$notificacoes = $result_notificacoes->fetchAll(PDO::FETCH_ASSOC);
foreach ($notificacoes as $row_notificacoes) {
$sql = $conexao->prepare("INSERT INTO notificacoes_visualizacao (id_notificacao, id_usuario_que_visualizou) VALUES (:id_notificacao, :id_usuario_que_visualizou)");
$sql->bindValue(':id_notificacao', $row_notificacoes['id'], PDO::PARAM_INT);
$sql->bindValue(':id_usuario_que_visualizou', $user_actual, PDO::PARAM_INT);
$sql->execute();
$return[] = '
<table border="0" class="barra_notificacoes" cellpadding="0" cellspacing="0">
<tr>
<td valign="top" width="10%">
<div style="margin-left: 4px; margin-top: 5px;"><img width="50" height="50" src="images/avatar6.jpg"></div>
</td>
<td valign="top" width="50%">
<div style="margin-left: 5px; margin-top: 2px;"><a href="users/<?= $row_user_anex->slug; ?>">César Sousa</a></div>
<div style="margin-left: 5px;" class="comentario_user">Comentou a tua opinião!</div>
<div style="margin-left: 5px;" class="tempo_do_post">1 segundo atrás</div>
</td>
</tr>
</table>
';
}
echo json_encode($return);
The problem is when I add this line, the query stops working
INNER JOIN users_social as user ON user.id = noti.user_id
Error log
sabeonde/public_html/ajax/mostra_notificacoes.php:22
Stack trace:
#0 /home/sabeonde/public_html/ajax/mostra_notificacoes.php(22): PDOStatement->execute()
#1 {main}
thrown in /home/sabeonde/public_html/ajax/mostra_notificacoes.php on line 22
Try Error
constraint fails ('sabeonde_sabeonde'.'notificacoes_visualizacao', CONSTRAINT 'fk_notificacao_visualizacao_notificacao' FOREIGN KEY ('id_notificacao') REFERENCES 'notificacoes' ('id'))' in /home/sabeonde/public_html/ajax/mostra_notificacoes.php:41
Stack trace:
#0 /home/sabeonde/public_html/ajax/mostra_notificacoes.php(41): PDOStatement->execute()
#1 {main}