How to list records with subrecords in mysql and php

Question

How to list records with subrecords in mysql and php

Navigation

#1 by (0 votes)

2

What I want to do is the following, I have the following tables in the mysql database manager

Tabla - comentarios
id_foro int (11)
id_comentarios int(11)
id_user_comment int(11)
file_comment text
date_comment

Tabla - respuestas_comentarios
id_respuesta int(11)
id_comentarios int (11)
file_respuesta text
date_respuesta

Tabla - usuarios
id_usuario int(11)
nombre_usuario text

The three tables are related, the user table with the comments table and the table answered with the comments table

What I want is to be able to load all the comments, that is, all the records in the comments table with their answers (With the corresponding records of the response table).

So far I have made a query that brings me all the comments with their answers, but when printing them on the screen with php the same comment is repeated twice with each different answer, if the case is the following, if said comment has more than one answer, the comment is duplicated to show the second this is my code in php plus the query.

Query

SELECT com.id_comentarios, com.file_comment, com.date_comment, res.id_respuesta, res.file_response_c, res.file_respuesta 
FROM c_comment_foro com 
LEFT JOIN response_comment_ res ON res.id_comentarios = com.id_comentarios 
WHERE com.id_foro = 1

Indeed it brings me the records of comments with their respective answer would be something like this

But at the moment of wanting to render the data with php in the html in the following way

Comentario 
-- Cantidad de respuestas ....

When the comments have a single answer my php that I will post later, the data renders very well, but there is a problem, the error is, when a comment has more than one answer, it is repeated to show the next answer something like this I'll give you an example.

I have two comments:
* comentario 1 , with the text hola dsdsds with a single answer - hola como estas
* comentario 2 , with the text tengo el siguiente problema ... which has two responses: 1 - si claro te podemos ayudar and 2 - esta es la solucion

And when rendering them with php I have the following problem

comentario 1- 

Hola dsdsds
respuestas 
1 - hola como estas


Comentario 2 -

Tengo este  problema ...
respuestas
1 - si claro te podemos ayudar


Comentario 2 -
Tengo este problema
respuestas
2 - esta es la solucion

If you see, in detail the comment two is repeated to show the second answer, and what I want is that it shows in each comment all your answers in this way

Comentario 2 -

Tengo este  problema ...
respuestas
1 - si claro te podemos ayudar
2 - esta es la solución

My code in php is as follows.

$stmt = $this->con->prepare('SELECT com.id_comentarios,com.file_comment,com.date_comment,res.id_respuesta,res.file_response_c,res.file_respuesta FROM c_comment_foro com LEFT JOIN response_comment_ res ON res.id_comentarios = com.id_comentarios WHERE com.id_foro = 1');
    $stmt->execute();

    if($stmt){
        if($stmt->rowCount() > 0){
            while($data = $stmt->fetch()){

                //transferimos el archivo donde se guardan los comentarios a un array
                $file_comment = file($data['file_comment']);
                $text = "";

                //recorremos los archivos el array y lo aguardamos en un variable
                foreach ($file_comment as  $value) {
                        $text = $value;
                }

                //transferimos el archivo donde se guardan las  respuestas de los comentarios a un array
                $file_response = file($data['file_respuesta']);
                $data_response = "";

                //recorremos los archivos el array y lo aguardamos en un variable
                foreach ($file_response as $response_) {
                    $date_response = $response_;
                    echo $response_;
                }

                //imprimimos o renderizamos los datos

                echo '<p> Comentario </p>
                     <p>'.$text.'</p>
                     <div>respuestas</div>
                     <p>'.$date_response.'</p>';

            }
        }else{
            echo "Este foro no tiene comentarios";
        }
    }else{
        echo "error";
    }

How can I fix that problem? thank you very much for the attention.

mysql sql php

asked by Jhon Dember Murillo Mendez 30.07.2018 в 16:35

source

1 answer

Android LocationManager using getLastKnownLocation () value is not obtained [Security: 090398] Invalid Subject: principals = [weblogic, Administrators, CrossDomainConnectors]

score 0 · Answer 1

In your example, you are reading the content of the comment or its response from a file. For purposes of my answer, we will assume that your tables have the structure:

comment:

id_comentario
texto_comentario

answer:

id_respuesta
id_comentario
texto_respuesta

Then, within the loop, you store the comment ID and only write the text of the comment if the ID has changed with respect to the previous iteration:

$stmt = $this->con->prepare("SELECT 
            com.id_comentario,
            com.texto_comentario,
            res.texto_respuesta
       FROM comentario com LEFT JOIN respuesta res ON 
       res.id_comentario = com.id_comentario");
$stmt->execute();

if(!$stmt){
  echo "Se produjo un error";
} else if(stmt->rowCount() === 0) {
  echo "No hay comentarios";
} else {
    $id_comentario=0; // inicializo el ID vigente
    while($data = $stmt->fetch()){
      $new_id = $data['id_comentario'];
      if($new_id !== $id_comentario) { // si no es el mismo comentario
         $id_comentario = $new_id; // actualizo el ID vigente
          echo '<p> Comentario </p>';
          echo '<p>'.$data['texto_comentario'].'</p>';
          echo '<p> Respuestas: </p>';
      }

      $texto_respuesta = $data['texto_respuesta'];
      if($texto_respuesta === null) { // el left join no encontró respuestas
         echo "Este comentario no tiene respuestas";
      } else {
         echo "<p>".$texto_respuesta."</p>";
      }
   }
}