display the data of a multiple SQL query

0

I have this code that is perfect and everything, but my question is, how would you do to show the data? I mean when I make a simple query, it's enough for me to write:

**while($datosx = mysqli_fetch_array($consulta)){
 echo $datosx["fila de la tabla"]; }**

but now my code is as follows:

$consulta= "SELECT publicaciones.usuario, amigos.id_1, amigos.id_2, 
             publicaciones.id
             FROM publicaciones, amigos 
             WHERE publicaciones.usuario = 
             amigos.id_1 and amigos.id_2 = '$id' ";
             $resultado_consulta = mysqli_query($conexion,$consulta);

I related two tables to be able to filter only elements that I want, but my question is how can I show the data I take?

    
asked by Boyd Robert 07.12.2018 в 02:46
source

2 answers

0

In the select you have to alias each field, and call it exactly the same. Example:

$consulta= "SELECT publicaciones.usuario AS user, amigos.id_1 AS id_1, amigos.id_2 AS id_2, 
             publicaciones.id AS id
             FROM publicaciones, amigos 
             WHERE publicaciones.usuario = 
             amigos.id_1 and amigos.id_2 = '$id' ";
             $resultado_consulta = mysqli_query($conexion,$consulta);

and you call it with:

echo $datosx["user"]; echo $datosx["id_1"]; echo $datosx["id_2"]; echo $datosx["id"];

    
answered by 07.12.2018 / 09:41
source
0

Only as a complement to what @guillem answered, my main point is the security of that query. I think it would be better to fix it the way.

$consulta= '
    SELECT
        publicaciones.usuario AS user,
        amigos.id_1 AS id_1,
        amigos.id_2 AS id_2,
        publicaciones.id AS id
    FROM
        publicaciones pub
    JOIN 
        amigos am ON pub.usuario = am.id_1 
    WHERE 
        amigos.id_2 = ? '; // este signo '?' se llenará de forma segura más adelante.

$stmt = $dbConnection->prepare($consulta);
$stmt->bind_param('s', $id); // Aquí le pasas el id de forma segura con bind_param para que enlace el valor del $id de forma segura con la primer coincidencia del caracter '?'. 's' es de *string*

$stmt->execute();
$result = $stmt->get_result();

With bind_param is a way to make sure you do not try to do sql injection in your code.

To see more information on prevention of sql injection attacks.

link

To see more about bind_param.

link

    
answered by 07.12.2018 в 16:30