从三重内连接中获得正确的结果

Question

Im trying to create a list of messages with the responses above each message with response. message table and response table are connected by an foreign id. everything is okay saving data. I want to show the responses attached to his own message father.

it's works fine until when i try to save two responses at the same message father it creates another message father with the other response instead of the both responses in the same message.

here s

function getFormattedDateTime(dateTimeToFormat) {
    dateTimeToFormat = new Date(dateTimeToFormat);
    var monthOptions = {
        month: 'long', year: 'numeric', day: 'numeric',
        hour: '2-digit', minute: '2-digit'
    };

    return dateTimeToFormat.toLocaleTimeString('en-us', monthOptions);

Sql query:

$totalMensajesR = mysql_num_rows($numMsgRespuesta);
$totalRows = mysql_num_rows($registroTriple);

  $registroTriple = mysql_query("SELECT 
      mensajes.ID as 'm_ID',
      mensajes.USER_NAME,
      mensajes.CONTENIDO as 'm_CONTENIDO',
      mensajes.FECHA as 'm_FECHA',
      respuestas.ID as 'r_ID',
      respuestas.ID_F as 'r_IDF',
      respuestas.USER_NAME as 'r_USERNAME',
      respuestas.FECHA as 'r_FECHA',
      respuestas.CONTENIDO as 'r_CONTENIDO',
      usuarios.ID_USER as 'u_idUSER',
      usuarios.USER_NAME,
      usuarios.CORREO
      FROM mensajes
        LEFT OUTER JOIN respuestas ON (respuestas.ID_F = mensajes.ID)
        LEFT OUTER JOIN usuarios ON (usuarios.USER_NAME=mensajes.USER_NAME)",$conexion) or die("Problemas en el select:".mysql_error());

What I need to show

if ($totalRows!=0){



    while ($row= mysql_fetch_assoc($registroTriple))
                    {
                        echo "<br />";
                        echo "<div id='$row[m_ID]' class='borderMensajes'>";
                        echo "<form name='formulario' action='' onsubmit='enviarDatos(); return false'>";
                        echo "<img id='imagenUsuario' src='getImageFromUsuarios.php?ID=$row[u_idUSER]'/>";
                        //echo "<span class='divDin2'>Product: </span>";
                        echo "<span class='divDin2'>$row[USER_NAME]</span>";
                        echo "<br/><br/>";
                        echo "<span class='divDinamico'>Mensaje: </span>";
                        echo $row["m_CONTENIDO"];
                        echo "<br/><br/>";
                        echo "<span class='divDinamico'>Fecha: </span>";
                        echo $row['m_FECHA'];
                        echo "<br/>";
                        echo "<h5></h5>";
                        echo "<input class='btRespuesta' id='_botonArchivar' onclick='AddResponse($row[m_ID])' name='responder' type='submit' value='Responder'/>";
                        echo "<div>";
                            //$row =  mysql_fetch_assoc($registroTriple);   

                            if ($row['r_IDF']==$row['m_ID'])
                            {
                                echo $row['r_USERNAME'];
                                echo "<span class='divDinamico'>Mensaje: </span>";
                                echo $row["r_CONTENIDO"];
                                echo "<span class='divDinamico'>Fecha: </span>";
                                echo $row["r_FECHA"];
                            }

                        echo "</div>";      
                        echo "<br /><br />";    
                        echo "</form>";
                        echo "</div>";

                    }
            `

Answer 1

I dont know if understand correct but i think ...

This query that you have will generate additional entries for every response that you have for the same message so if you have one row on Message table and 3 rows on Response table you will get 3 rows example

  Message ID | Response ID |
  1 | 1
  1 | 2
  1 | 3

At your php output i dont see any mechanism to detect and group the responses so every response will be displayed as another message

i think you have to break your triple join query to 2 queries, one for the messages and one for the responses while you looping the messages you have to call the response query to get the responses

while ($row = mysql_fetch_assoc($sqlMessages)) {

    echo 'Message';
    echo displayResponses($row['messageId']);
}

function displayResponses($messageId) {

    while ($row = mysql_fetch_assoc($sqlResponse)) {

        echo $row['response'];
    }
}

i hope this help.