You can use as many JOIN
as you need (even the same table, in this case to the table usuario
) to get translate the identifiers of each user in each message:
SELECT
id_foro, descripcion, fecha, foros,
ur.nombre ur_nombre,
ur.apellido ur_apellido,
ur.idnivel_usuario ur_idnivel_usuario,
ud.nombre ud_nombre,
ud.apellido ud_apellido,
ud.idnivel_usuario ud_idnivel_usuario
FROM
foro f
INNER JOIN
usuario ur
ON
f.fk_cedula_admin = ur.id
INNER JOIN
usuario ud
ON
f.fk_cedula_user = ud.id
WHERE
f.foros IN ('pregunta', 'respuesta')
AND
(
f.fk_cedula_admin = '$id_usuario'
OR
f.fk_cedula_user = '$id_usuario'
)
Where tables ur
are the sender users and ud
the target users.
I have edited the query to obtain the data from the tables and assign them a alias
with which you can easily access them from PHP.
As I am not clear who is the addressee or addressee according to the scheme of your table, I assume that you should match the id
user with fk_cedula_admin
or fk_cedula_user
of the table of the forum.
The idea is that you understand how to obtain the data through unions ( JOIN
) of tables.
Edit: I repeat code from the previous answer
I insist that in the session variables you should not save the username and password, but rather something that identifies the user of the session in a univocal way.
I suggested using your primary key or id
and I put the following code to obtain it from the session variables that you had already defined:
<?php
if (!isset($_SESSION['usuario.id'])) {
$login = mysql_real_escape_string($_SESSION['login']);
$password = mysql_real_escape_string($_SESSION['password']);
$sql = "SELECT id FROM usuario WHERE login='$login' AND
password='$password'";
$resultl = mysql_query($sql,$link);//devuelve la consulta
if ($resultl === false) {
die(mysql_error());
}
$_editar_linea = mysql_fetch_assoc($resultl);
if ($_editar_linea === false) {
die('Acceso denegado');
}
$_SESSION['usuario.id'] = $_editar_linea['id'];
}
$id_usuario = mysql_real_escape_string($_SESSION['usuario.id']);
Note that it is possible to do the query without this variable, but it would add an additional level of union between tables unnecessary.
Editing with the final content
After sharing the complete code and schema of the database in the chat, the complete result has been:
<?php
if (!isset($_SESSION['usuario.id'])) {
$login = mysql_real_escape_string($_SESSION['login']);
$password = mysql_real_escape_string($_SESSION['password']);
$sql = "SELECT id FROM usuario WHERE login='$login' AND
password='$password'";
$resultl = mysql_query($sql,$link);//devuelve la consulta
if ($resultl === false) {
die(mysql_error());
}
$_editar_linea = mysql_fetch_assoc($resultl);
if ($_editar_linea === false) {
die('Acceso denegado');
}
$_SESSION['usuario.id'] = $_editar_linea['id'];
}
$id_usuario = mysql_real_escape_string($_SESSION['usuario.id']);
$sql = "
SELECT
id_foro, descripcion, fecha, foros,
IF(foros = 'respuesta', ur.nombre, ud.nombre) ur_nombre,
IF(foros = 'respuesta', ur.apellido, ud.apellido) ur_apellido,
IF(foros = 'respuesta', ur.idnivel_usuario, ud.idnivel_usuario) ur_idnivel_usuario,
IF(foros = 'respuesta', ud.nombre, ur.nombre) ud_nombre,
IF(foros = 'respuesta', ud.apellido, ur.apellido) ud_apellido,
IF(foros = 'respuesta', ud.idnivel_usuario, ur.idnivel_usuario) ud_idnivel_usuario
FROM
foro f
INNER JOIN
usuario ur
ON
f.fk_cedula_admin = ur.id
INNER JOIN
usuario ud
ON
f.fk_cedula_user = ud.id
WHERE
f.foros IN ('pregunta', 'respuesta')
AND
(
f.fk_cedula_admin = '$id_usuario'
OR
f.fk_cedula_user = '$id_usuario'
)
";
$resultl = mysql_query($sql,$link) or die("Error en: $sql: " .
mysql_error());
//$campo = mysql_fetch_assoc($resultl);
while ($lista=mysql_fetch_array($resultl)){
?>
<br>
<!-- MENSAJE-->
<div class="col s12 m12">
<div class="card-panel green lighten-4 z-depth-1">
<div class="right-align">
<form action='eliminarforo.php' method='POST'>
<input type='hidden' name='codforo' value="<?= htmlspecialchars($lista['id_foro']) ?>">
<input type='image' src='../imagenes/cerrar.png' width='25' height='25' title='borrar'>
</form>
</div>
<div class="row valign-wrapper">
<div class="col s2">
<img src="../avatar/avatar.png" class="circle responsive-img" width="100px" height="100px">
</div>
<div class="col s4 card-panel blue lighten-2">
<h5 class="black-text text-darken-2 left CONDENSED LIGHT5"><?= htmlspecialchars($lista['ur_idnivel_usuario']) ?>:
<?= htmlspecialchars($lista['ur_nombre']) ?> <?php echo htmlspecialchars($lista['ur_apellido']) ?></h5>
</div>
<div class="col s6 card-panel grey darken-2">
<h5 class="white-text text-darken-2 left CONDENSED LIGHT5">
Dice al <?= htmlspecialchars($lista['ud_idnivel_usuario']) ?>:
<?= htmlspecialchars($lista['ud_nombre']) ?> <?php echo htmlspecialchars($lista['ud_apellido']) ?>
</h5>
</div>
</div>
<div class="card-panel green lighten-4 z-depth-2">
<div style="width:100%; word-wrap: break-word;">
<h6 class="black-text left-align">Comentario: <?php echo $lista['descripcion']; ?></h6>
</div>
</div>
<div class="col s2 card-panel blue-grey darken-4 right">
<h6 class="white-text text-darken-2 center-align CONDENSED LIGHT5">
<?php echo $lista['fecha']; ?>
</h6>
</div>
<br>
</div>
</div>
<!-- FIN MENSAJE-->
<?php
}
Modifications include:
- SQL proposed in this answer.
- Change from
<?php echo ... ?>
to <?= htmlspecialchars( ... ) ?>
.
- Convert two loops (sent messages and received messages) to a single one in which the sender and recipient are obtained from the registry and not from the session variables.
- Exchange of senders and recipients in case it is of type
pregunta
or respuesta
(easy made difficult).