Add consecutive fouls

Question

Add consecutive fouls

Navigation

#1 by (1 votes)
#2 by (0 votes)

5

I am creating a journal module on line, I need to get students with 3 consecutive absences, follow the input select, when the student has more than 2 hours of class, 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 structure of the table where you save 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;

php mysql

asked by anonymous 24.09.2015 / 13:37

2 answers

Is there any modeling tool for NoSQL database? Could not perform the edit because another user changed the record

score 1 · Answer 1

I do not 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 is in the same table, simply 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));

score 0 · Answer 2

So I understood d_status = 6 is right fault? then follows 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 ;