Show field and its mysql database content

0

How about!

I have a detail when presenting a query

This is my code where I consult and show the result:

$consulta_A = mysqli_query($con,"SELECT * FROM articulo8 WHERE fraccion = 'VI'AND inciso ='Inciso H' AND ano = '2017' ORDER BY mes DESC") or die("Error en consulta Agenda".mysqli_error($con));

              while ($row = mysqli_fetch_array($consulta_A)) {
                $documento = $row["documento"];                
                 $mes = $row["mes"];
                 $ruta = $row["ruta"];
                 $departamento = $row["departamento"];
                  ?>                    
                <ul><?php echo $mes; ?>
                  <li><a href="<?php echo "plataforma/".$ruta.$documento; ?>" target="_blank"><?php echo $departamento; ?></a></li> 
                </ul>

The problem is that the result I get the month is repeated as many times as it exists in the database:

  

September

     
  • LEGAL
  •   

October

     
  • LEGAL
  •   

October

     
  • TRANSPARENCY
  •   

May

     
  • LEGAL
  •   

May

     
  • TRANSPARENCY
  •   

and I need to show only the name of the month and all the documents within that month, like this:

  

September

     
  • LEGAL
  •   

October

     
  • LEGAL
  •   
  • TRANSPARENCY
  •   

May

     
  • LEGAL
  •   
  • TRANSPARENCY
  •   

I hope and you can help me!

    
asked by Sharly Infinitywars 31.01.2018 в 19:03
source

2 answers

1

Well I would do so, although there should be a simpler way:

$consulta_A = mysqli_query($con,"SELECT * FROM articulo8 WHERE fraccion = 'VI'AND inciso ='Inciso H' AND ano = '2017' ORDER BY mes DESC") or die("Error en consulta Agenda".mysqli_error($con));
                $data = array();

              while ($row   = mysqli_fetch_array($consulta_A)) {
                  $documento    = $row["documento"];
                  $mes          = $row["mes"];
                  $ruta         = $row["ruta"];
                  $departamento = $row["departamento"];

                  if(array_key_exists($mes,$data)){
                    array_push($data[$mes], array("mes"=>$mes,"ruta"=>"ruta","departamento"=>"departamento"));
                  }else{
                    $data[$mes] = array(
                        array("mes"=>$mes,"ruta"=>"ruta","departamento"=>"departamento")
                    );
                  };
                };//end while

                foreach($data as $key => $value){
                    echo "<ul><li>".$key."<ul>";

                    foreach($value as $item){
                        echo '<li><a href="plataforma/'.$item["ruta"].$item["documento"].'" target="_blank">'.$item["departamento"].'</a></li>';
                    };

                    echo "</ul></li></ul>";
                };

You can see a working sample here: SAMPLE

    
answered by 31.01.2018 в 19:29
1

You could implement a query like this:

SELECT mes, 
       GROUP_CONCAT(CONCAT_WS('_',documento, departamento, ruta) ORDER BY mes SEPARATOR '|') datos_mes
       FROM articulo8 WHERE fraccion = 'VI' AND inciso ='Inciso H' AND ano = '2017' 
       GROUP BY mes 
       ORDER BY mes DESC

That will bring you the data grouped by month, more or less like this:

Mes        datos_mes
Octubre    JURIDICO_dpto juridico_ruta juridico|TRANSPARENCIA_dpto..._ruta...

Then you can do a double explode , first about | to divide the elements, in the case of the example this explode would divide first like this:

JURIDICO_dpto juridico_ruta juridico

And ...

TRANSPARENCIA_dpto..._ruta...

Then you make another explode on _ to get each element, more or less like this:

JURIDICO
    dpto juridico
    ruta juridico

TRANSPARENCIA
    dpto...
    ruta...

In the column outside the month would go.

Here is the code. I have not tried it, but it should work.

$strSQL="SELECT mes, GROUP_CONCAT(CONCAT_WS('_',documento, departamento, ruta) ORDER BY mes SEPARATOR '|') datos_mes
         FROM articulo8 WHERE fraccion = 'VI' AND inciso ='Inciso H' AND ano = '2017' 
         GROUP BY mes 
         ORDER BY mes DESC";
$consulta_A = mysqli_query($con,$strSQL) or die("Error en consulta Agenda".mysqli_error($con));
$strHTML='';
              while ($row = mysqli_fetch_array($consulta_A)) {
                    $strHTML .= '<ul>'.$row['mes'].'</ul>';
                    $arrDatos=explode('|',$row["datos_mes"]);
                    foreach ($arrDatos as $v){
                      $arrDetalle=explode('_',$v);
                      $documento=$arrDetalle[0];
                      $departamento=$arrDetalle[1];
                      $ruta=$arrDetalle[2];
                      $strHTML .= '<li><a href="plataforma/'.$ruta.$documento.'" target="_blank">'.$departamento.'</a></li>';                     
                    }
$strHTML .='</ul>';
echo $strHTML;
    
answered by 01.02.2018 в 00:16