Help with Queries Count via mysql and php

try this:

  $tabla1="alumnos";
    $tabla2="asistencia";

    $consult="SELECT $tabla1.id,$tabla1.apaterno,$tabla1.amaterno,
    $tabla1.nombres,$tabla1.nivel,$tabla1.curso,
    $tabla1.paralelo,$tabla1.gestion,
    sum($tabla2.estado='P') as totalP,
    sum($tabla2.estado='L') as totalL,
    sum($tabla2.estado='F') as totalF,
    sum($tabla2.estado='A') as totalA
    FROM $tabla1
    INNER JOIN $tabla2 ON $tabla1.id=$tabla2.id_alumno
    WHERE $tabla1.nivel='$nivel' AND $tabla1.curso='$curso' AND
     $tabla1.paralelo='$paralelo' AND $tabla2.gestion='$gestion' AND
     $tabla2.bimestre='$bimestre' AND $tabla2.area='$area' 
     GROUP BY $tabla1.id";

What you need is to group the second table and use case to group the data. You can do this before join or during join . As for performance, the second option is better, but I put the first one here because I think the example of what you have to do is clearer:

SELECT 
    $tabla1.id,
    $tabla1.apaterno,
    $tabla1.amaterno,
    $tabla1.nombres,
    $tabla1.nivel,
    $tabla1.curso,
    $tabla1.paralelo,
    $tabla1.gestion,
    TMP.totalP,
    TMP.totalL,
    TMP.totalF,
    TMP.totalA
FROM $tabla1
INNER JOIN (
    select 
        id_alumno,
        sum(case when estado='P' then 1 else 0 end) as totalP,
        sum(case when estado='L' then 1 else 0 end) as totalL,
        sum(case when estado='F' then 1 else 0 end) as totalF,
        sum(case when estado='A' then 1 else 0 end) as totalA
    FROM 
        $tabla2
    WHERE 
        $tabla2.gestion='$gestion' 
        AND $tabla2.bimestre='$bimestre' 
        AND $tabla2.area='$area' 
    GROUP BY id_alumno
)as Tmp
on TMP.id_alumno = $tabla1.id
WHERE $tabla1.nivel='$nivel' 
AND $tabla1.curso='$curso' 
AND $tabla1.paralelo='$paralelo' 

SQL has been mounted on the fly and there may be something wrong, but the idea is this.

I hope it serves you.

Well, as I see your query, it seems that if you extract the data you want, if you only need to print it you can do it with a foreach, that is, instead of the while you have a foreach ... try to place after the line $ result = $ link- > query ($ consult); the following, so that it looks something like this .....

$result=$link->query($consult);
$datos = $result->fetch_array();

foreach($datos as $_data){
    echo "<tr>";
    echo "  <td>".$_data['apaterno']." ".$_data['amaterno']." ".$_data['nombres']."</td>";
    echo "<td>".$data['totalP']."</td>"
    echo "<td>".$data['totalF']."</td>"
    echo "<td>".$data['totalL']."</td>";
    echo "<td>".$data['totalA']."</td>";
    echo "</tr>";


}

If everything goes well, that should fill the table with what you want ... I hope you serve friend ... greetings

Thanks for your time: but I can not make the correct query and print the result I leave the complete code of the search assists Columns: (id, student_id, area, bimester, state, management, month, day) students Columns: (id, apaterno, amaterno, names, level, course, parallel, management) The search form is on the same page:

<form action="<?php echo $_SERVER['PHP_SELF']; ?>" method="get" class="form-inline">

<input type="hidden" name="nivel" value="<?php echo $nivel;?>">
<input type="hidden" name="curso" value="<?php echo $curso;?>">
<input type="hidden" name="paralelo" value="<?php echo $paralelo;?>">

<input type="hidden" name="gestion" value="<?php echo $gestion;?>">

<div class="form-group">
   Bimestre:


 <select name="bimestre" class="form-control">
        <option value=" ">...</option>
        <option value="1BIM">1 Bimestre</option>
        <option value="2BIM">2 Bimestre</option>
        <option value="3BIM">3 Bimestre</option>
        <option value="4BIM">4 Bimestre</option>
   </select>   </div>  <div class="form-group">
       Materia:
   <select name="area" class="form-control">
        <option value=" ">Seleccionar</option>

<?php
include("../conexion.php");    
$consultaf="SELECT area FROM areas";
$resultado=$link->query($consultaf);
    while ($row=$resultado->fetch_array()) {
        $area=$row['area'];
    echo "<option value='".utf8_encode($area)."'>".utf8_encode($area)."</option>";
     //   echo $area;
    }

    ?>
   </select>
   </div>
    || <button type="submit" name="buscar" class="btn btn-warning "> Buscar Informe +</button>
</form>

I publish the code that worked thanks to teachers This is the if where the report is searched on the same page and print the results:

    <?php

    if(isset($_GET['buscar'])){ 
    //$name = $_POST['name'];
    include("../conexion.php");

    $nivel=$_GET['nivel'];
    $curso=$_GET['curso'];
    $paralelo=$_GET['paralelo']; 
    $gestion=$_GET['gestion'];
    $bimestre=$_GET['bimestre'];
    $area=$_GET['area'];
    ?>
    <form action="asis_areas_pdf.php" method="post">
    <table class="table table-bordered table-striped table-hover"> 

                                <tbody>
     <tr> 
        <td colspan="6">RESUMEN DE ASISTENCIA  del Curso: <?php echo $curso." ".$paralelo." ".$nivel;?> </td>

     </tr>
<tr>

                                        <td>N°</td>
                                        <td class="warning"><H3>APELLIDOS Y NOMBRES</H3></td>
                                        <td class="danger">Asistencias</td>
                                        <td class="warning">Faltas</td>
                                        <td class="info">Licencias</td>                                
                                        <td class="success">Abandonos</td>
                                        <td class="success">Total</td>

                                    </tr>

    <?php
    $tabla1="alumnos";
    $tabla2="asistencia";

    $consulta="SELECT $tabla1.id,$tabla1.apaterno,$tabla1.amaterno,$tabla1.nombres,$tabla1.nivel,$tabla1.curso, $tabla1.paralelo,$tabla1.gestion,
        sum($tabla2.estado='P') as totalP,
        sum($tabla2.estado='L') as totalL,
        sum($tabla2.estado='F') as totalF,
        sum($tabla2.estado='A') as totalA,
        count($tabla2.estado)   as total
        FROM $tabla1 INNER JOIN $tabla2 ON $tabla1.id=$tabla2.id_alumno

        WHERE $tabla1.nivel='$nivel' AND $tabla1.curso='$curso' AND
         $tabla1.paralelo='$paralelo' AND $tabla2.gestion='$gestion' AND
         $tabla2.bimestre='$bimestre' AND $tabla2.area='$area'  
         GROUP BY $tabla1.id ORDER BY $tabla1.apaterno,$tabla1.amaterno,$tabla1.nombres ASC";
         if ($resultados=$link->query($consulta)) {
             while ($row=$resultados->fetch_array()) {

       $id=$row['id'];
       $apaterno=$row['apaterno'];
       $amaterno=$row['amaterno'];
       $nombres=$row['nombres'];
       $gestion=$row['gestion'];
       $totalP=$row['totalP'];
       $totalL=$row['totalL'];
       $totalF=$row['totalF'];
       $totalA=$row['totalA'];
       $total=$row['total'];

    echo "<tr>";

    echo "  <td>".$id."</td>
            <td>".$apaterno." ".$amaterno." ".$nombres."</td>
            <td>".$totalP."</td>
            <td>".$totalF."</td>
            <td>".$totalL."</td>
            <td>".$totalA."</td>
            <td>".$total."</td>";
    echo "</tr>";

    }}
    ?>

This is the code that worked, thank you all for your help, and the result is as follows: