HTML table to query 2 tables in PHP

Question

HTML table to query 2 tables in PHP

Navigation

#1 by (1 votes)

1

I have two tables (both related by their ID):

Socios:             Socios_carga
------------        --------------
ID                  ID
Nombre              Nombre
RUT                 RUT

Then with INNER JOIN I generate the query of these related tables

$query="SELECT socios.nombre, socios_carga.nombre_carga, socios_carga.rut_carga FROM socios INNER JOIN socios_carga on socios.id=socios_carga.id_carga ORDER BY socios.nombre"; 

$resource = $conn->query($query); 
$total = $resource->num_rows;

and it shows me something like this:

Partner1 Load1
  Partner1 Load2
  Partner1 Load3
  Partner2 Load1
  Partner2 Load2

And what I need is for you to show me the following in an HTML table:

Socio1
 Carga1 
 Carga2  
 Carga3

Socio2
 Carga1   
 Carga2

I do not know if this is achieved by adding something to the query or by adding some code to PHP.

HTML and PHP code showing the query:

<table id="example" class="display" cellspacing="0" width="100%">
    <thead>
        <tr>
            <th>Titular</th>
            <th>Nombre</th>
            <th>Rut</th>
        </tr>
    </thead>
    <tbody>
        <tr>
        </tr>
        <tr>
            <?php while ($row = $resource->fetch_assoc()){?>
            <td><?php echo $row[nombre]?></td>
            <tr>
            <td><?php echo $row[nombre_carga]?></td>
            <td><?php echo $row[rut_carga]?></td>

            </tr>

        </tr>
      <?}?>
    </tbody>
</table>

================================================================================================= ==============
Table 'partners_load' As you can see the ID = 9 of the partner has no load and the ID = 11 has only 2 charges

And its result in HTML is as follows:

mysql php

asked by Andrés Jara 07.03.2018 в 19:58

source

1 answer

Problem with my route Route [invoices.obtainproducts] not defined CSS Invalid Property Value

score 1 · Accepted Answer

Quote:

This is "Formatted" in PHP, you must have a text variable (initially empty), in the reading loop of the "DataSet", compare this variable with the Partner column, if they are different, "you create the head "and you impersonate the text variable by the data of the partner column, then print the load indistinctly. In the next cycle of the loop, the variable will be equal to the partner data, so the spindle will not be written.

The code would be like this:

<table id="example" class="display" cellspacing="0" width="100%">
    <thead>
        <tr>
        <th>Titular</th>
             <th>Nombre</th>
             <th>Rut</th>
         </tr>
     </thead>
     <tbody>
         <tr>
         </tr>

             <?php $socio = ""; while ($row = $resource->fetch_assoc()){?>
             <?php if ($socio <> $row['nombre']){ $socio = $row['nombre']; echo '<tr><td>' . $row['nombre'] . '</td><td></td><td></td></tr>'} ?>
        <tr>
             <td></td>
             <td><?php echo $row['nombre_carga']?></td>
             <td><?php echo $row['rut_carga']?></td>
        </tr>
              <?php } ?>
    </tbody>
</table>

Note the new variable $socio , this being a string defined outside the loop generated by the while . Initially of value "" , and later (each time the user "changes", the comparison with this one will "detect" the change, and it will be overwritten, to "wait" for the next change.

In parallel, another serious solution, based on the GROUP_CONCAT suggested by A. Cedano. We could leave the sql in the following way:

SELECT socios.nombre, group_concat(concat('<td></td><td>', socios_carga.nombre_carga, '</td><td>', socios_carga.rut_carga , '</td>') separator '</tr><tr>') as cargas
FROM socios 
INNER JOIN socios_carga 
on socios.id=socios_carga.id_carga 
GROUP BY socios.nombre
ORDER BY socios.nombre

I do not recommend giving output formats from the SQL query, but it was too easy for me.

And the code would be the following:

<table id="example" class="display" cellspacing="0" width="100%">
    <thead>
        <tr>
        <th>Titular</th>
             <th>Nombre</th>
             <th>Rut</th>
         </tr>
     </thead>
     <tbody>
         <tr>
         </tr>

             <?php while ($row = $resource->fetch_assoc()){?>
        <tr>
             <td><?php echo $row['nombre'] ?> </td><td></td><td></td>
        </tr> 
        <tr>
             <?php echo $row['cargas']?>
        </tr>
              <?php } ?>
    </tbody>
</table>

PS: I am not sure if it is necessary, but as you will see, I declare empty cells in the table.