Update a multi-student MySQL field

Question

Update a multi-student MySQL field

Navigation

#1 by (0 votes)

0

I have a student table where you have a presence field where present and are missing.

When I created the field, I left everyone as away , and now I need to list and change some to present . I would like to change all at once, but I do not know how to update in all records at once.

form.php

<?php
$conn = mysqli_connect($servidor, $usuario, $senha, $dbname);
$idEvent = $_POST['idsubev'];
?>
<?php
//
$sql = "SELECT u.nome, e.titulo, a.presente, a.id_al FROM sch_usuarios u INNER JOIN sch_acontecimentos e INNER JOIN sch_aluno_acont a WHERE e.id_acon = a.id_acon AND u.id = a.id_al AND e.id_subevent='$idEvent' ORDER BY u.nome";

$query = mysqli_query($conn, $sql);
while ($rows = mysqli_fetch_array($query)) {

echo "
<form method='post' action = 'update.php' >
   <input type='hidden' name='id' value='".$rows['id_al']."'>
   <h1> Alterar presença do aluno</h1>
   <table align='' border='0' bordercolor='#BCBCBC' cellspacing='0'>
    <tr align ='left' bordercolor='#000000' >
        <td valign='middle'>&nbsp;</td>
        <td valign='middle'>&nbsp;</td>
    </tr>
    <tr align ='left' bordercolor='#000000' ><td valign='middle' bgcolor='#E9E9E9'><p><font color=''>Nome:</font> </p></td>
        <td align='left' valign='middle' bgcolor='#E9E9E9'><input type = 'text' size='50' name='nome' value ='".$rows['nome']."'></td>
    </tr>
    <tr><td><font color=''> Curso: </font> </td>
        <td align='left'><input type='text' size='30' name='curso' value=' ".$rows['titulo']."'><font color=''> </font>
        </td>
    <tr align ='left'>

    //QUERO ATUALIZAR ESSE CAMPOS ABAIXO CHAMADO PRESENTE
        <td><font color=''>Presente=<b>".$rows['presente']."</b> </font></td>
        <td align='left'>

            //AQUI MARCAREI O CHECKBOX NOS ALUNOS QUE QUERO MUDAR PARA 1 (PRESENTE)
            Status 1= presente, 2= ausente    
            <input type='checkbox' name='presente' value='1'>Marcar Presente? 
        </td>
     </tr>
</table>

"; /*fecha a tabela apos termino de impressão das linhas*/
}

echo "<input type='submit' value='alterar'>
</form>";
?>

update.php

$id=$_POST['id'];
$presente = $_POST['presente'];
$mysqli = new mysqli('localhost', 'wwwcard_ew3', 'adm22334455', 'wwwcard_ew3');

$sql = "UPDATE sch_aluno_acont SET presente = '$presente' WHERE id_al = '$id'";
$stmt = $mysqli->prepare($sql) or die($mysqli->error);

if(!$stmt){
  echo 'erro na consulta: '. $mysqli->errno .' - '. $mysqli->error;
}

$stmt->bind_param('ssi',$id, $presente);
$stmt->execute();

header("Location: index.php?altera_aluno");

php mysql

asked by anonymous 05.11.2017 / 00:38

1 answer

How to create a scrolling menu? Edit status of multiple records with checkbox checked?

score 0 · Answer 1

It was not clear what you want to do, because you asked how to do it all at once and your script is doing 1 by 1. but if I were to do it with only 1 UPDATE it would do the following:

form.php

<?php
$conn = mysqli_connect($servidor, $usuario, $senha, $dbname);
$idEvent = $_POST['idsubev'];
$sql = "SELECT u.nome, e.titulo, a.presente, a.id_al FROM sch_usuarios u INNER JOIN sch_acontecimentos e INNER JOIN sch_aluno_acont a WHERE e.id_acon = a.id_acon AND u.id = a.id_al AND e.id_subevent='$idEvent' ORDER BY u.nome";
$query = mysqli_query($conn, $sql);

?>

<form method='post' action = 'update.php' >
    <input type='hidden' name='id' value='".$rows['id_al']."'>
    <h1> Alterar presença do aluno</h1>
    <?php while ($rows = mysqli_fetch_array($query)) { ?>
    <table align='' border='0' bordercolor='#BCBCBC' cellspacing='0'>
        <tr align ='left' bordercolor='#000000' >
            <td valign='middle'>&nbsp;</td>
            <td valign='middle'>&nbsp;</td>
        </tr>
        <tr align ='left' bordercolor='#000000' ><td valign='middle' bgcolor='#E9E9E9'><p><font color=''>Nome:</font> </p></td>
            <td align='left' valign='middle' bgcolor='#E9E9E9'><input type = 'text' size='50' name='nome' value ='<?php echo $rows['nome']?>'></td>
        </tr>
        <tr><td><font color=''> Curso: </font> </td>
            <td align='left'><input type='text' size='30' name='curso' value=' <?php echo $rows['titulo'] ?>'><font color=''> </font>
            </td>
        <tr align ='left'>

            <td><font color=''>Presente=<b><?php $rows['presente'] ?></b> </font></td>
            <td align='left'>
                <input type='checkbox' name='presente[]' value='<?php echo $rows['id_al']?>'>Marcar Presente?
                <input type="hidden" name="evento" value="<?php echo $idEvent?>">
            </td>
        </tr>
    </table>
    <?php } ?>
    <button type="submit">Salvar</button>
</form>

update.php

<?php
const PRESENTE = 1;
const AUSENTE = 2;

$arrIds = $_POST['presente'];

$mysqli = new mysqli('localhost', 'wwwcard_ew3', 'adm22334455', 'wwwcard_ew3');

$strIds = '';
foreach($arrIds as $id){
    $strIds .= ','. $id;
}
// AQUI VOCÊ IRÁ MARCAR PRESENÇA PARA OS ALUNOS QUE VOCÊ SELECIONOU NA LISTA
$sqlPresente = "UPDATE sch_aluno_acont SET presente = ". PRESENTE ." WHERE id_al = IN(".$strIds.")";

// AQUI VOCÊ IRÁ MARCAR AUSENCIA PARA OS ALUNOS QUE VOCÊ NÃO SELECIONOU OU DESMARCOU NA LISTA E QUE FAZEM PARTE DO EVENTO QUE VOCÊ FILTROU
$sqlAusente = "UPDATE 
                    sch_aluno_acont a
                INNER JOIN sch_acontecimentos e
                ON e.id_acon = a.id_acon
                SET presente = ".AUSENTE."
                WHERE e.id_subevent = $idEvent
                AND a.presente != ".AUSENTE."
                AND a.id_al NOT IN(".$strIds.")";

$stmtPresente = $mysqli->prepare($sqlPresente) or die($mysqli->error);
$stmtAusente = $mysqli->prepare($sqlAusente) or die($mysqli->error);


$stmtPresente->execute();
$stmtAusente->execute();

header("Location: index.php?altera_aluno");

?>