Distinguish user roles in query

0

Greetings friends I have a problem with a consultation in sessions, I have a mini internal forum between users and administrator he registers me well and the mjs are shown well the only problem I have is when it comes to showing the names I mean:

admin message

  

Administrador: Admin Dice al Usuario: oswaldo moriche (aqui deberia decir el nombre y apellido del usuario y dice es el nombre y apellido del administrador)

user message

  

Usuario: Admin(aqui deberia ir el nombre del usuario) Dice al Administrador: Admin

annex the questions and their image to see if I understand better

 consulta para los mjs del administrador

 <?php
 $login = mysql_real_escape_string($_SESSION['login']);
 $password = mysql_real_escape_string($_SESSION['password']);
 $sql3 = "SELECT * FROM foro, usuario WHERE 
 usuario.login='$login' AND 
 usuario.password='$password' AND 
 foro.foros='respuesta' AND
 foro.fk_cedula_admin=usuario.id";
 $resultl=mysql_query($sql3,$link) or die("Error en: $sql: " . 
 mysql_error());
 while ($lista=mysql_fetch_array($resultl)){
 ?>

 consulta para los mensajes de los usuarios

 <?php
 $login= $_SESSION['login'];
 $password=$_SESSION['password'];
 $sql4 = "SELECT * FROM foro, usuario WHERE usuario.login='$login' AND 
 usuario.password='$password' AND foro.foros='pregunta' AND
 foro.fk_cedula_admin=usuario.id";
 $resultl=mysql_query($sql4,$link) or die("Error en: $sql: " . 
 mysql_error());
 while ($lista=mysql_fetch_array($resultl)){
 ?>

forum table:

 id, descripcion, fecha, fk_cedula_admin, fk_cedula_user,foros

user table:

 id, cedula, nombre, apellido, login, password, idnivel_usuario

Support images:

    
asked by yoclens 03.05.2017 в 01:10
source

1 answer

1

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).
answered by 03.05.2017 / 06:28
source