使用简单的表单，php和while循环将多个mysqli行更新为true或false

Question

I have set up a form, created from a while loop of around 20 entries with each mysqli entry it echos back to include a switch. I'm trying to get the table to recognize if the switch is on or not, if it is set to on input true into the table row and if the switch is not set input false into the table row, updating all rows with one query and php execution file.

The code below consists of first the PHP AND HTML FORM code to create all the entries with a switch wrapped in a form. The second two examples of code are the php execution attempts.

PHP AND HTML FORM CODE STARTS

<form enctype="multipart/form-data" id="form1" name="form1" method="post" action="add_newsletter.php">

<?php

// get the info from the db 
$query = 'SELECT * FROM news_content ORDER BY article_id DESC';

$results = mysqli_query($dbc, $query)or die('Error querying database');

while ($row = mysqli_fetch_array($results)){

// tileAdminBar START
echo '<div id="tileAdminBar">';

echo '<div id="tileadminBarCreateNewsletterSlider">';
echo '<label class="switch">
<input type="checkbox" name="newsletterToggle" id="newsletterToggle" value="true" >
<span class="slider round"></span>
</label>';
echo '</div>';

echo '</div>';
// tileAdminBar END


echo '<div id="fieldWrapper">';
echo '<input type="hidden" value="' . $_GET['article_id'] . '"name="article_id"/> ';
echo '</div>';

} // end while

?>

<div id="fieldWrapper">
<input class="form" type="submit" name="Submit" id="Submit" value="Create newsletter"/>
</div>
</form>

PHP EXECUTION EXAMPLE ONE START

<?php

$article_id = $_POST['article_id'];
$newsletterToggle = $_POST['newsletterToggle'];
$newsletterToggle = mysqli_real_escape_string($dbc, $newsletterToggle);


$query = 'SELECT * FROM ontent ORDER BY article_id DESC';

$results = mysqli_query($dbc, $query)or die('Error querying database');

while ($row = mysqli_fetch_array($results)){


if($newsletterToggle == "true"){
//post variables to slt_database and insert into content table
$secondQuery = "UPDATE news_content SET newsletter = 'true' WHERE '$row' = '$article_id'";

$results2 = mysqli_query($dbc, $secondQuery)or die('Error querying database');
}else{

//post variables to slt_database and insert into content table
$secondQuery = "UPDATE news_content SET newsletter = 'false' WHERE '$row' = '$article_id'";

$results2 = mysqli_query($dbc, $secondQuery)or die('Error querying database');

}

}
//End of while

//close slt_database conection
mysqli_close($dbc);
?>

PHP EXECUTION EXAMPLE TWO

<?php

$newsletterToggle = $_POST['newsletterToggle'];
$newsletterToggle = mysqli_real_escape_string($dbc, $newsletterToggle);


$query = 'SELECT * FROM content ORDER BY article_id DESC';

$results = mysqli_query($dbc, $query)or die('Error querying database');

while ($row = mysqli_fetch_array($results)){


if($newsletterToggle == "true"){
//post variables to slt_database and insert into news_content table
$secondQuery = "UPDATE news_content SET newsletter = 'true'";

$results2 = mysqli_query($dbc, $secondQuery)or die('Error querying database');
}else{

//post variables to slt_database and insert into content table
$secondQuery = "UPDATE content SET newsletter = 'false'";

$results2 = mysqli_query($dbc, $secondQuery)or die('Error querying database');

}

}
//End of while

//close slt_database conection
mysqli_close($dbc);
?>
/* PHP EXECUTION EXAMPLE TWO END*/

Using PHP EXECUTION EXAMPLE ONE I try grabbing the article_id to help updating, but when executed I don't get any mysqli or php errors, it just doesn't effect any of the rows for the table column.

Using PHP EXECUTION EXAMPLE TWO without the article_id i can switch 1,2,3 on etc out of the 20 rows and it just updates all the table rows to true. If I don't switch any of the switches on all the table rows update to false. My problem is it is not recognizing the switches that have been switch on or off and updating them accordingly

Answer 1

You can use jquery and ajax for that .i.e :

Problem 1(PHP EXECUTION EXAMPLE ONE) :

As there are around 20 entries , if you will click on submit button you will never get which row the user have clicked . So, to solve this you can do like below :

Your Php file :

while ($row = mysqli_fetch_array($results)){

// tileAdminBar START
echo '<div id="tileAdminBar">';

echo '<div id="tileadminBarCreateNewsletterSlider">';
echo '<label class="switch">
 //attaching id with article id , i.e : newsletterToggle1,newsletterToggle2 etc
<input type="checkbox" name="newsletterToggle[]" id="newsletterToggle' . $_GET['article_id'] . '" value="'.$_GET['article_id'].'" >
<span class="slider round"></span>
</label>';
echo '</div>';

echo '</div>';
// tileAdminBar END


echo '<div id="fieldWrapper">';
echo '<input type="hidden" value="' . $_GET['article_id'] . '"name="article_id"/> ';
echo '</div>';
//added button 
echo '<button name="submit" data-value="' . $_GET['article_id'] . '" >Submit </button>
} 

Now your ajax call will be like below :

$(document).ready(function() {
  $('button').on("click",function() {
  //getting id which is present in button
   var article_id=$(this).attr("data-value");
   //to get checkbox value
var newsletterToggle=false; 
if ($('#newsletterToggle'+article_id).is(":checked"))
{
  // it is checked
 newsletterToggle=true;
}else{
//not check
newsletterToggle= false;
}
 //calling ajax
    $.ajax({
      type: "POST",
      url: "yourphppage",
      data: {
        'newsletterToggle': newsletterToggle,//<-passing value to php
        'article_id':article_id 
     },
      success: function(response) {
        //display something 
        alert(response);
      },
      error: function(jqXHR) {

        alert("There was a problem");
      }
    });

   });
});

Your php page:

if($newsletterToggle == "true"){
//post variables to slt_database and insert into content table
$secondQuery = "UPDATE news_content SET newsletter = 'true' WHERE yourcolumnidname = '$article_id'";

$results2 = mysqli_query($dbc, $secondQuery)or die('Error querying database');
}else{

//post variables to slt_database and insert into content table
$secondQuery = "UPDATE news_content SET newsletter = 'false' WHERE yourcolumnidname  = '$article_id'";

$results2 = mysqli_query($dbc, $secondQuery)or die('Error querying database');

}

Problem 2(PHP EXECUTION EXAMPLE TWO) :

You need something to recognize which checkbox is been switched You don't need to use ajax here .Do like below :

echo '<label class="switch">
     //attaching id with article id , i.e : newsletterToggle1,newsletterToggle2 etc
    <input type="checkbox" name="newsletterToggle[]" id="newsletterToggle' . $_GET['article_id'] . '" value="'.$_GET['article_id'].'" >
                            //^value of id 
    <span class="slider round"></span>
    </label>';

In above code value="'.$_GET['article_id'].'" <- this will be used to recognize which rows switch has been change ,so we will update that row only in php file . In you php file PHP EXECUTION EXAMPLE TWO do below changes :

//getting value of checkboxes
$newsletterToggle = $_POST['newsletterToggle'];
if (isset($_POST['newsletterToggle'])) {


    foreach ($newsletterToggle as $values){
    //this will update only those rows where checkbox is selected
    $secondQuery = "UPDATE news_content SET newsletter = 'true' where  youridcolumnname = '$values' ";
    $results2 = mysqli_query($dbc, $secondQuery)or die('Error querying database');  
    }
}

Note : You must use prepared statement it is safe an secure.