Select rows corresponding to a userid

Navigation
0

I am trying to create a query that only selects the data that has the respective userID of my database, but when I send the query the query brings me all the data, I am not very expert in database

My query is the following: 

$query = "SELECT * FROM messages WHERE idEmitter 
IN ($us, $msg[0]) AND idReceiver IN ($msg[0], $us) ORDER BY sent ASC" ;

The variable $us is assigned at the beginning of the session, and the variable $msg is in a foreach

What I want to do is bring me all the data that comes in my database depending on the id that I bring in the url

  

localhost / maybe% 20algo / chat / index.php? id = 4

This is what I should bring from my database

However, in my message system it brings all the data and should only bring those that have a name fernando

In my database, I have the following data in each row

My purpose is to create a private chat, in my first image I show the data of demon, cease and fernando, but as I just mentioned, I want a private chat, that is:

I want you to bring the messages you have in idEmitter and idReceiver depending on the id's they have 

1 con 3
3 con 1

3 con 4
4 con 3

I hope you gave me to explain

This is the code of my foreach, and where all the chat is shown 

<?php session_start();

include 'db.php';
require_once '../functions.php';


$emit = obtener_mensajes($conexion, $us);

comprobarSession(); 

?>

<?php foreach ($emit as $msg): ?>   

<?php 

$query = "SELECT * FROM messages WHERE idEmitter IN ($us) AND idReceiver IN ($msg[0], $us) ORDER BY sent ASC" ;

$run = $conexion->query($query);

while ($row = $run->fetch(PDO::FETCH_ASSOC)):
    //var_dump($row);

?>

        <div id="chat_data">
            <span style="color: green;"><?php echo $row['nombre']; ?> </span><br>
            <span style="color: brown;"><?php echo $row['message']; ?></span>
            <span style="float: right;"><?php echo fecha($row['sent']); ?></span>
        </div>
<?php endwhile;?>
<?php endforeach ?>
  

Edition

The way I show the first notification where I printed the last message is the following 

function obtener_mensajes($conexion, $us) {
    $statement = $conexion->prepare("SELECT SQL_CALC_FOUND_ROWS m.idEmitter, m.nombre, m.message, m.sent, m.idReceiver
FROM messages m
INNER JOIN (SELECT idEmitter, idReceiver, max(idChat) AS idChat
FROM messages
WHERE seenEmpresa = 0
GROUP BY idEmitter, idReceiver) mm
ON m.idChat = mm.idChat
AND m.idEmitter = mm.idEmitter
AND m.idReceiver = mm.idReceiver
WHERE m.idReceiver = $us
AND seenEmpresa = 0");
    $statement->execute();
    return $statement->fetchAll();
}

later I use a file called notifiaciones.view.php to show the notification and get all the data of the previous function 

<?php session_start();

require 'extras/config.php';
require 'functions.php';

comprobarSession();

$conexion = conexion($bd_config);
if (!$conexion) {
    header('Location: error.php');
}

$mensajes = obtener_mensajes($conexion, $us);

if (!$mensajes) {
    $vacio = "<li>No cuentas con mensajes nuevos</li>";

    $guardadas = "<li>Los mensajes se han guardado en chats guardados</li>";
}

require "views/mensajes.view.php";

Next print all the data that the $mensajes variable brings and print them in an html 

    <?php foreach ($mensajes as $mensa): ?>

muchos mas div's

                  <input type="hidden" value="<?php echo $mensa['idEmitter']; ?>"><a href="<?php echo RUTA; ?>/chat/index.php?id=<?php echo $mensa['idEmitter']; ?>">

muchos div's
                </div><hr>
              <?php endforeach;?>

and that's how I send the id of the sender through the url and in the chat section I want to print it 

<?php session_start();

include 'db.php';
require_once '../functions.php';
require_once '../single.mensaje.php';

comprobarSession(); 

?>



<?php 

$query = "SELECT ue.idUsuario, ue.nombre, ur.nombre, c.sent, c.message FROM messages c
              INNER JOIN usuarios ue ON c.idEmitter = ue.idUsuario
              INNER JOIN usuarios ur ON c.idReceiver = ur.idUsuario
              WHERE (c.idEmitter = **AQUI QUIERO IMPRIMIR LA ID** AND c.idReceiver = $us)
              or (c.idEmitter = $us AND c.idReceiver = **AQUI QUIERO IMPRIMIR LA ID**)";

$run = $conexion->query($query);

while ($row = $run->fetch(PDO::FETCH_ASSOC)):
    var_dump($row);

?>

        <div id="chat_data">
            <span style="color: green;"><?php echo $row['nombre']; ?> </span><br>
            <span style="color: brown;"><?php echo $row['message']; ?></span>
            <span style="float: right;"><?php echo fecha($row['sent']); ?></span>
        </div>
<?php endwhile;?>
  

How I try to pick up the id from the url

This is the way I try to get the id from the url 

<?php session_start();

include 'db.php';
require_once '../functions.php';

comprobarSession(); 

$idEmitter = $_POST['id'];  <-- id de la url

?>



<?php 

$query = "SELECT ue.idUsuario, ue.nombre, ur.nombre, c.sent, c.message FROM messages c
              INNER JOIN usuarios ue ON c.idEmitter = ue.idUsuario
              INNER JOIN usuarios ur ON c.idReceiver = ur.idUsuario
              WHERE (c.idEmitter = $idEmitter <-- variable que contiene el id de la url previamente declarado AND c.idReceiver = $us)
              or (c.idEmitter = $us AND c.idReceiver = $idEmitter)";

$run = $conexion->query($query);

while ($row = $run->fetch(PDO::FETCH_ASSOC)):
    var_dump($row);

?>

        <div id="chat_data">
            <span style="color: green;"><?php echo $row['nombre']; ?> </span><br>
            <span style="color: brown;"><?php echo $row['message']; ?></span>
            <span style="float: right;"><?php echo fecha($row['sent']); ?></span>
        </div>
<?php endwhile;?>
  

Error that marks me in the chat

     

Notice: Undefined index: id in C: \ xampp \ htdocs \ maybe   company \ chat \ chat.php on line 8 NULL Fatal error: Uncaught Error: Call   to a member function fetch () on boolean in C: \ xampp \ htdocs \ maybe   company \ chat \ chat.php: 25 Stack trace: # 0 {main} thrown in   C: \ xampp \ htdocs \ maybe company \ chat \ chat.php on line 25

    
asked by Cesar Gutierrez Davalos 23.07.2017 в 01:11
source

2 answers

1

I would propose a design similar to this one.

The code below provides the following:

  • Avoid redundant data: A table dedicated to chat users. That way you do not have to go repeating in the table chat their names. When there are thousands or millions of records, the table would grow in an impressive way and become heavy.
  • Security: Use of PDOs and queries prepared to avoid SQL injection. Since your current code is vulnerable. It is advisable never to build queries in this way: $query = "SELECT * FROM messages WHERE idEmitter IN ($us) AND idReceiver IN ($msg[0], $us) ORDER BY sent ASC" ; all that are variables, that come from the outside, must be replaced by name markers :nombre or position markers ? . When you can, I suggest you do some research on SQL Injection.
  • Easy to read: Reading the messages of the whole chat is done with a single line of code, placed inside a loop: $strHtml.='<span>'.$row["de"].': </span>'.$row["mensaje"].'<br />';

The query that is used to get the chat between two people is this: 

    SELECT ue.nombre_usr de, ur.nombre_usr a, c.mensaje FROM  chat c 
    INNER JOIN chat_usuarios ue ON c.id_emitter=ue.id_usuario 
    INNER JOIN chat_usuarios ur ON c.id_reciever=ur.id_usuario
    WHERE    (c.id_emitter=:usr1 AND c.id_reciever=:usr2) 
          or (c.id_emitter=:usr2 AND c.id_reciever=:usr1)";

The code shows a way to present the data. You are free to implement that part as you wish. If you want to combine different colors for the one that sends and the one that receives, you can use something of type odd , applying CSS rules. As is done for example with the tables, which show a line of one color and another of another. Although the sender may write more than one message followed ... in that case you can apply a class linked to user1, and user2, for example and apply styles based on that.

Here we go:

Código: Ver Demo 

<?php

require "util/public_db_info.php";
$pdo = new PDO($dsn, $user_name, $pass_word);

/**
 *  Crear tablas
 *  e Insertar Datos de prueba
*/

$sql = 
       "CREATE TABLE IF NOT EXISTS chat (
            id_chat       INT PRIMARY KEY,
            id_emitter    INT,
            id_reciever   INT,
            mensaje       TEXT
            );

        DELETE FROM  chat; //¡CUIDADO! Esto es sólo de prueba

        INSERT INTO chat (id_chat, id_emitter, id_reciever, mensaje) VALUES 
        (1,1,2,'Hola Pedro'),
        (2,2,1,'Hola César'),
        (3,1,2,'¿Cómo estás?'),
        (4,2,1,'Bien'),
        (5,1,3,'Hola Juan'),
        (6,3,1,'Hola César, aquí Juan'),
        (7,1,3,'¿Cómo estás Juan?'),
        (8,3,1,'Bien, César')        ;";


$stmt=$pdo->query($sql);

$sql = 
       "CREATE TABLE IF NOT EXISTS chat_usuarios (
            id_usuario    INT PRIMARY KEY,
            nombre_usr    VARCHAR(50)
         );

         DELETE FROM  chat_usuarios;  //¡CUIDADO! Esto es sólo de prueba

         INSERT INTO chat_usuarios (id_usuario, nombre_usr) VALUES  
          (1,'César'),
          (2,'Pedro'),
          (3,'Juan')
          ;";

$stmt=$pdo->query($sql);

$sql="
        SELECT ue.nombre_usr de, ur.nombre_usr a, c.mensaje FROM  chat c 
        INNER JOIN chat_usuarios ue ON c.id_emitter=ue.id_usuario 
        INNER JOIN chat_usuarios ur ON c.id_reciever=ur.id_usuario
        WHERE     (c.id_emitter=:usr1 AND c.id_reciever=:usr2) 
               or (c.id_emitter=:usr2 AND c.id_reciever=:usr1)";



/**
 *  Pruebas 
 *  En el código  se usa  PDO y  consultas preparadas
*/


$usr1=1;
$usr2=2;

$stmt = $pdo->prepare($sql);
$stmt->bindParam("usr1",$usr1);
$stmt->bindParam("usr2",$usr2);
$stmt ->execute();

$arrDatos = $stmt->fetchAll(PDO::FETCH_ASSOC);
imprimir ($arrDatos);


$usr1=1;
$usr2=3;

$stmt = $pdo->prepare($sql);
$stmt->bindParam("usr1",$usr1);
$stmt->bindParam("usr2",$usr2);
$stmt ->execute();
$arrDatos = $stmt->fetchAll(PDO::FETCH_ASSOC);

imprimir ($arrDatos);

$pdo = null;


//Una función para mostrar los datos
function imprimir($arrDatos)
{

    if ($arrDatos)
    {
        echo "<hr />SE ENCONTRARON  ".count($arrDatos). " REGISTROS<br /><hr />";
        /**
         *  Construímos los datos  de forma limpia
        */
        $strHtml='CHAT:<br />';    
        foreach ($arrDatos as $row)
        {
            $strHtml.='<span>'.$row["de"].': </span>'.$row["mensaje"].'<br />';
        }
        echo $strHtml;
    }
}

?>

Resultado: 

Prueba de chat entre César y Pedro

SE ENCONTRARON 4 REGISTROS
CHAT:
César: Hola Pedro
Pedro: Hola César
César: ¿Cómo estás?
Pedro: Bien


Prueba de chat entre César y Juan

SE ENCONTRARON 4 REGISTROS
CHAT:
César: Hola Juan
Juan: Hola César, aquí Juan
César: ¿Cómo estás Juan?
Juan: Bien, César
    
answered by 23.07.2017 / 04:45
source
0

I think your error lies in the $ msg [0] , by indexing it you are not letting it iterate correctly on your foreach, try just leaving $ msg , so implicitly the variable will be loaded with the next value. 

$query = "SELECT * FROM messages WHERE idEmitter IN ($us) AND idReceiver IN ($msg, $us) ORDER BY sent ASC" ;

I hope my answer has helped you, since my php domain is not high. Greetings.

    
answered by 23.07.2017 в 04:38
Problem when exporting MySQL Database to Excel with PHPExcel send modal data to jquery bootstrap4