Update only records marked with checkbox at once php

Asked

Viewed 367 times

2

I have a select that carries a series of records. Behold, in these records I need to apply an up and that, in my head, the best way would be using a checkbox field, so that when the user checked the checkbox and then clicked on the "send" button, only the checkbox field of all the records - would be updated at the same time (with 1or 2 depending on whether the field was marked 1 or not 2). Espe.. Thanks for your help! I’m racking my brain here and I’ve never been researching and asking and I didn’t get any conclusive answers

php form.

<?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'>
<input type='checkbox' name='presente' value='2'>
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?
php mysqli checkbox sql-update

  • You are creating a form per student, not correct

  • I need help with that

1 answer

1


From what I understand, this is a student attendance list.

Suggestion for this first solution: in the column presente as Default Value equal to 2, hence it would only be necessary to update only one checkbox would be marked if the student is present.

Values of checkboxes are the id_al coming from the consultation that will be used in the clause where in the update of the column presente worthwhile 1 of checkboxes marked.

php form. 

<?php
$conn = new mysqli("localhost", "USUARIO", "SENHA", "NOME_DB");
$idEvent = $_POST['idsubev'];

$sql = "SELECT ....................
$query = mysqli_query($conn, $sql);

echo "<form method='post' action = 'Update.php' >

<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>";


while ($rows = mysqli_fetch_assoc($query)) {

    echo "<tr align ='left' bordercolor='#000000' ><td valign='middle' bgcolor='#E9E9E9'><p><font color=''>Nome:</font> </p></td>
        <td align='left' valign='middle' bgcolor='#E9E9E9'>".$rows['nome']."</td>
    </tr>

    <tr><td><font color=''> Curso: </font> </td>
        <td align='left'>".$rows['titulo']."</td>
    </tr>
    <tr align ='left'>
        <td><font color=''>Presente=<b>presente</b> </font></td>
        <td align='left'>
            Status marcado = presente <input type='checkbox' name='presente[]' value='".$rows['id_al']."'>Marcar Presente?
        </td>
     </tr>";

}

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

?>

Update.php 

<?php

$presente = $_POST['presente'];

$conn = new mysqli("localhost", "USUARIO", "SENHA", "NOME_DB");

$sql = 'UPDATE sch_aluno_acont SET presente = 1 WHERE id_al IN (' . implode(',', array_map('intval', $presente)) . ')';
$data = mysqli_query($conn, $sql);

?>

To update all checked or unmarked checkboxes

In this solution there is no need of the column presente was Default Value

php form.

.............................
.............................
while ($rows = mysqli_fetch_assoc($data)) {

    echo "<tr align ='left' bordercolor='#000000' ><td valign='middle' bgcolor='#E9E9E9'><p><font color=''>Nome:</font> </p></td>
        <td align='left' valign='middle' bgcolor='#E9E9E9'>".$rows['nome']."</td>
    </tr>

    <tr><td><font color=''> Curso: </font> </td>
        <td align='left'>".$rows['titulo']."</td>
    </tr>
    <tr align ='left'>
        <td><font color=''>Presente=<b>presente</b> </font></td>
        <td align='left'>
            Status marcado = presente <input type='checkbox' name='presente[]' value='".$rows['id_al']."'>
Marcar Presente?
        </td>
     </tr>";
 //todos os id_al 
 $all_id_al .=$rows['id_al'].",";   
}

echo "<input type='hidden' name='all_id_al' value='".substr($all_id_al, 0, -1)."'>";

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

?>

Update.php

<?php

$presente = $_POST['presente'];

$all_id_al = $_POST['all_id_al'];

$all_id = explode(",",$all_id_al);

$result=array_diff($all_id,$presente);

$conn = new mysqli("localhost", "USUARIO", "SENHA", "NOME_DB");

//marcados
$sql = 'UPDATE sch_aluno_acont SET presente = 1 WHERE id_al IN (' . implode(',', array_map('intval', $presente)) . ')';
$data = mysqli_query($conn, $sql);

//não marcados
$sql2 = 'UPDATE sch_aluno_acont SET presente = 2 WHERE id_al IN (' . implode(',', array_map('intval', $result)) . ')';
$data2 = mysqli_query($conn, $sql2);

?>

form comes a string with all id_al we transform into an array $all_id.

$result=array_diff($all_id,$presente); returns the values of $all_id who are not present in $presente and will be used to update unmarked checkboxes.

  • Exactly the solution 1 that I needed just to mark the gifts, I will test and I tell you, Leo would be possible in the form page to implement a page before giving the Submit, because it has enough records, thank you very much

  • Ola Leo, it worked out the way that Oce did but I’m with another problem, : have students who are enrolled in various courses, ie the student id is the same , but the course id in the id_curso column, it is different for each user id, so every time I use the script and frame a student who has more than one course the script updates all lines of the other courses also where the user id is equal

  • 1

    @Claudio without seeing the structure of the table gets complicated, I think a AND in the Where clause would solve your problem, but as I said, without seeing the structure gets complicated

  • TABLE sch_aluno_acont ( id_al bigint(14) NOT NULL, id_acon bigint(14) NOT NULL, presente int(11) NOT NULL, pago int(11) NOT NULL ) ENGINE=Innodb DEFAULT CHARSET=latin1; - -- Extracting table data sch_aluno_acont - INSERT INTO sch_aluno_acont (id_al, id_acon, presente, pago) VALUES (100242, 171007074752, 2, 1), (100242, 171007074838, 2, 2), (100242, 171020141939, 2, 1);

  • I put an Hidden input with id_acon and I did an AND in the correct update with the implode same thing of id_al, but it did not help...

  • 1

    has to watch it calmly because now has more options that would be the courses and this is not in HTML

  • OK I’ll be waiting very long thank you

  • 1

    @Claudio, it is better to ask another question with the new situation so that the answer is not outside the focus of the question. If this answer fully answered your original question mark it as accepted, see how and why in this post https://pt.meta.stackoverflow.com/questions/1078/como-e-por-que-aceitar-uma-resposta/1079#1079

  • 1

    @Claudio, I’m waiting for you to post the new question with the new situation to post the answer I did. As soon as you post a comment here so I know and look for her

  • Follow link https://answall.com/questions/253401/actualizr-campo-de-tabela-apenas-tagged

Show 5 more comments

Browser other questions tagged php mysqli checkbox sql-update

You are not signed in. Login or sign up in order to post.