Add consecutive fouls

Question

I am creating an online diary module, I need to get students with 3 consecutive absences, follow the entry select, when the student has more than 2 hours of class, the select displays different.

<select name="d_status[<?=$i?>]" size="1">

<? if ($thora > 2 ) { ?>

<option value="3" <?php if ( $d_status == 3 ) echo 'selected="selected"'; ?>>Presença+Presença</option>
<option value="4" <?php if ( $d_status == 4 ) echo 'selected="selected"'; ?>>Presença+Falta</option>
<option value="5" <?php if ( $d_status == 5 ) echo 'selected="selected"'; ?>>Falta+Presença</option>
<option value="6" <?php if ( $d_status == 6 ) echo 'selected="selected"'; ?>>Falta+Falta</option>

<? } else { ?>

<option value="1" <?php if ( $d_status == 1 ) echo 'selected="selected"'; ?>>Presença</option>
<option value="2" <?php if ( $d_status == 2 ) echo 'selected="selected"'; ?>>Falta</option>

<? } ?>
</select>

The total sum I have:

$sql_1 = mysql_num_rows(mysql_query("SELECT * FROM ****** WHERE 
                        `d_matricula` = '" . $res_['matricula'] . 
                        "' AND `d_ano_semestre` = '" . $sem . 
                        "' AND `d_filial` = '" . $filial . 
                        "' AND `d_curso` = '" . $curso . 
                        "' AND `d_dias` = '" . $dias . 
                        "' AND `d_horario` = '" . $horario . 
                        "' AND d_status = '2' AND MONTH(d_data) = '" . $mes . 
                        "' AND YEAR(d_data)= '".$ano."'"));

The table structure where save the presence or false status:

- Estrutura da tabela `jos_users_cursos_diarios`
--

CREATE TABLE `jos_users_cursos_diarios` (
  `d_matricula` varchar(8) NOT NULL,
  `d_ano_semestre` varchar(4) NOT NULL,
  `d_filial` varchar(50) NOT NULL,
  `d_curso` varchar(6) NOT NULL,
  `d_dossie` varchar(5) NOT NULL,
  `d_dias` varchar(1) NOT NULL,
  `d_horario` varchar(8) NOT NULL,
  `d_data` date NOT NULL,
  `d_status` varchar(10) NOT NULL,
  `d_obs` varchar(100) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Answer 1

I don’t know exactly how your database structure is, but I would do something like this:

    $where = array();

    $where[] = "`d_matricula` = '{$res_['matricula']}' ";
    $where[] = "`d_ano_semestre` = '$sem' ";
    $where[] = "`d_filial` = '$filial' ";
    $where[] = "`d_curso` = '$curso' ";
    $where[] = "`d_dias` = '$dias' ";
    $where[] = "`d_horario` = '$horario' ";
    $where[] = "`d_status` = 2 ";
    $where[] = "DATE_FORMAT(d_data, '%Y-%m') = '{$ano}-{$mes}' ";

$SQL = "SELECT tabela_alunos.*,
             ( SELECT SUM(d_faltas)
               FROM tabela_faltas
               WHERE tabela_alunos.d_id=fk_de_faltas ) total_faltas,
             ( SELECT SUM(d_presencas)
               FROM tabela_presencas
               WHERE d_id=fk_de_faltas ) total_presencas,
        WHERE " . implode(' AND ', $where);

If the data are in the same table, just group it like this:

$SQL = "SELECT tabela_alunos.*,
                tabela_alunos.d_matricula,
                SUM(d_faltas) as total_faltas,
                SUM(d_presencas) as total_presencas,
        WHERE " . implode(' AND ', $where) . " GROUP BY tabela_alunos.d_matricula";

$query = mysql_num_rows(mysql_query($SQL)); 

Answer 2

From what I understood d_status = 6 is wrong? then follow the query for 3 consecutive fouls:

SELECT distinct d_matricula, d_status, d_data as Dia1,
        (SELECT d_data from jos_users_cursos_diarios as t2 WHERE d_status=6 and t2.d_matricula=t.d_matricula and t2.d_data=date_add(t.d_data, INTERVAL 1 DAY) limit 1) AS Dia2, 
        (SELECT d_data from jos_users_cursos_diarios as t3 WHERE d_status=6 and t3.d_matricula=t.d_matricula and t3.d_data=date_add(t.d_data, INTERVAL 2 DAY) limit 1) AS Dia3      
    FROM jos_users_cursos_diarios t 
    WHERE d_status=6 and d_data between '2015-11-05' and '2015-11-17' group by Dia2 having Dia3 ;

Over there where you have this little bit of code between '2015-11-05' and '2015-11-17' is the period you will search, if you need everything just remove if it is another period just adjust.