How to accommodate the values using html code in PHP?

Question

I'm trying to accommodate the value from a MySQL table to PHP, I am using HTML code but I am not sure how to show the values.

Here is my code:

$codigoHTML= '<table width="100%" border="1" class="table table-

hover">';
$consultaSucursales = "SELECT id,empresa FROM empresa";
$ejecutar = mysql_query($consultaSucursales);
while($fila = mysql_fetch_array($ejecutar))
{
    $codigoHTML.= '<td><strong><center>'.$fila['empresa'].'</center></strong></td>';
    $respuesta = datos($fila['id']);
    $codigoHTML.= $respuesta;
}

$codigoHTML.='
</tbody>
</table>';

echo $codigoHTML;

function datos($id_sucursal)
{
    $consultaCantidades = "SELECT cantidad FROM producto WHERE id_sucursal = '$id_sucursal'";
    $ejecutar2 = mysql_query($consultaCantidades);
    $codigoHTML2 = "";
    while($fila2 = mysql_fetch_array($ejecutar2))
    {
        $codigoHTML2.= '<tr>';                   
            $codigoHTML2.= '<td>'.$fila2['cantidad'].'</td>';
        $codigoHTML2.= '</tr>';    
    }
    return $codigoHTML2;
}

The column Id in table product is not in table is an example — Diego Freyre, Apr 20 '18 at 18:04
the data that I show is not accomodate that I want in the picture — Diego Freyre, Apr 20 '18 at 18:13
I'm not able to execute your code, so I'm not sure what's going wrong. What does your current code do and how does it differ from what you want? — showdev, Apr 20 '18 at 18:16
when the cycle finishes the next "empresa" is shown below the latest results and what I want is to show it to the side in the form of columns for each company as shown in the image — Diego Freyre, Apr 20 '18 at 18:20
It seems that your first `while` loop outputs `` elements directly inside of a ``, without any ``. Also there's a mismatched `` without an opening ``. Just to clarify, are the "empressa" intended to be the table headers showing company names? And each "empressa" column shows various "cantidad" quantities below? — showdev, Apr 20 '18 at 18:38

showdev · Answer 1 · 2018-04-20T20:05:10.153

I suggest building an array of rows you can easily output.
I'm thinking of a structure something like this:

EMPRESA
(
    [1] => empresa 1
    [2] => empresa 2
    [3] => empresa 3
)

CANTIDAD
(
    [1] => Array
        (
            [1] => 2
            [2] => 7
            [3] => 9
        )

    [2] => Array
        (
            [1] => 56
            [2] => 5
            [3] => 8
        )

    [3] => Array
        (
            [1] => 78
            [2] => 5
            [3] => 9
        )

    [4] => Array
        (
            [1] => 100
            [3] => 100
        )

    [5] => Array
        (
            [3] => 1000
        )

)

Here's how I'm building those arrays:

<?php

$empresa=$cantidad=array();

$consultaSucursales = "SELECT e.`id`,e.`empresa`,p.`cantidad`
                       FROM `empresa` e
                       LEFT JOIN `producto` p ON (p.`id_sucursal`=e.`id`)
                       WHERE 1;";

$ejecutar = mysql_query($consultaSucursales);

$row=0;
while($fila = mysql_fetch_array($ejecutar)) {
  $row++;
  if (empty($empresa[$fila['id']])) {
    $empresa[$fila['id']]=$fila['empresa'];
  }
  $cantidad[$row][$fila['id']]=$fila['cantidad'];
}

?>

Then output the data:

<table width="100%" border="1" class="table table-hover">
  <thead>
    <tr>
      <th>Nom</th>
      <th><?=implode('</th><th>',$empresa);?></th>
    </tr>
  </thead>
  <tbody><?php

    foreach ($cantidad as $row_number => $this_row) {

      ?><tr>
        <td><?=$row_number?></td><?php

        foreach (array_keys($empresa) as $empresa_id) {
          ?><td><?=$this_row[$empresa_id]?:''?></td><?php
        }

      ?></tr><?php

    }

  ?></tbody>
</table>

Which generates the following output:

<table class="table table-hover" width="100%" border="1">
  <thead>
    <tr>
      <th>Nom</th>
      <th>empresa 1</th>
      <th>empresa 2</th>
      <th>empresa 3</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <td>1</td>
      <td>2</td>
      <td>7</td>
      <td>9</td>
    </tr>
    <tr>
      <td>2</td>
      <td>56</td>
      <td>5</td>
      <td>8</td>
    </tr>
    <tr>
      <td>3</td>
      <td>78</td>
      <td>5</td>
      <td>9</td>
    </tr>
    <tr>
      <td>4</td>
      <td>100</td>
      <td></td>
      <td>100</td>
    </tr>
    <tr>
      <td>5</td>
      <td></td>
      <td></td>
      <td>1000</td>
    </tr>
  </tbody>
</table>

I also strongly recommend using mysqli or PDO, as mysql_* functions are depreciated.

score 0 · Answer 2 · answered Apr 20 '18 at 20:59

Thanks to everyone, after making some changes, I decided to take another table as a pivot that is "producto", if I add a new "empresa", it will be dynamically displayed

the result is the following the result is the following

the code is the following

 $codigoHTML= '<table width="100%" border="1" class="table table-hover">';
$consultaSucursales = "SELECT id,empresa FROM empresa";
$ejecutar = mysql_query($consultaSucursales);
$codigoHTML.= '<td><strong><center>Producto</center></strong></td>';
while($fila = mysql_fetch_array($ejecutar))
{
    $codigoHTML.= '<td><strong><center>'.$fila['empresa'].'</center></strong></td>';
}

$consultaCods = "SELECT DISTINCT cod FROM producto";
$ejecutaC = mysql_query($consultaCods);
while($filac = mysql_fetch_array($ejecutaC))
{
    $cod = $filac['cod'];
    $codigoHTML.='<tr>';
    $codigoHTML.= '<td>'.$cod.'</td>';
       $consultaSucursales = "SELECT id,empresa FROM empresa";
       $ejecutar = mysql_query($consultaSucursales);
       while($fila = mysql_fetch_array($ejecutar))
       {
           $id_sucursal = $fila['id'];
           $respuesta = datos($id_sucursal,$cod);
           $codigoHTML.= $respuesta;
       }
    $codigoHTML.='</tr>';
}


$codigoHTML.='
</table>';

echo $codigoHTML;

function datos($id_sucursal,$cod)
{
    $consultaCantidades = "SELECT cantidad FROM producto WHERE id_sucursal = '$id_sucursal' AND cod = '$cod'";
    $ejecutar2 = mysql_query($consultaCantidades);
    $codigoHTML2 = "";

    while($fila2 = mysql_fetch_array($ejecutar2))
    {                              
        $codigoHTML2.= '<td>'.$fila2['cantidad'].'</td>';           
    }
    return $codigoHTML2;
}

How to accommodate the values using html code in PHP?

2 Answers2