Multiple update of rows in a single column

cainam29 · August 8, 2017

I am trying to retrieved data from database and update a single column.

Here is a sample of how my page would look like, first part is when we initially upload it, this is during the approval process

column1 | column2  | 
value1  | value1   |
value2  | value2   |

Now the second part is when we try to retrieved the data from the db with the added column3 because we want to assign it to someone, thus adding their name before we re-upload it to the db

column1 | column2 | column3
value1  | value1  | -------
value2  | value2  | ------- <=== this column3 rows does not have value in database yet as it is not part of the
...                              page I used when we initially uploaded the data, so it will be blank when 
                                 I retrieved the data

I am trying to retrieved multiple rows here with column3 being editable so we can add different values to it. Then I want to re-upload those different values in column3 alone.

Here is how I'm fetching the data,

<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "sample_db";

// check data before use it and convert from string to expected type, use try, not like here:
$date = $_POST['date'];
$date1 = $_POST['date1'];
// use valid data to select rows
try {
    //1. connect to MySQL database
    $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);

    //2. set the PDO error mode to exception
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    //3. create query string (here is answer on your question)
    $sql = 'SELECT column1, column2, column3 FROM sample_table WHERE scheduled_start_date BETWEEN :d1 AND :d2';

    //4. prepare statement from query string
    $stmt = $conn->prepare($sql);

    //5. bind optional parameters
    //if ($status != 'All') $stmt->bindParam(':st', $status);

    //6. bind parameters
    $stmt->bindParam(':d1', $date);
    $stmt->bindParam(':d2', $date1);

    //7. execute statement
    $stmt->execute();

    //8. returns an array containing all of the result set rows 
    $result = $stmt->fetchAll(PDO::FETCH_ASSOC);

    //get count of rows
    $numrow = count($result);

    //print array - there is many solution to print array,
    //to debug you can do: 
    //print_r($result);

} catch(PDOException $e) {
    echo "Error: " . $e->getMessage();
}
$conn = null;

if($numrow == 0) 
  echo "No results found.";
else 
  echo "Count: $numrow</br>";
{

echo "<table width='auto' cellpadding='1px' cellspacing='0px' border=1 align='center'>
<tr>
<!--<th align='center'><input id=checkall name=checkall id=checkall type=checkbox value='' /></th>-->
<th align='center'><strong>Column1</strong></th>
<th align='center'><strong>Column2</strong></th>
<th align='center'><strong>Column3</strong></th>
</tr>"; 

foreach ($result as $row => $info) {
echo "<form action='crqretrieve_status.php' method='post'>";
echo"<tr>"; 
echo  "<td align='center'>" . $info['column1'] . "<input type=hidden name=column1 value=" . $info['column1'] . " </td>";
echo  "<td align='center'>" . $info['column2'] . "<input type=hidden name=column2 value=" . $info['column2'] . " </td>"; 
echo  "<td align='center'>" . "<input name=column3 value='' </td>";
echo "</tr>"; 
echo "</form>";
}
}
echo "</table>";

?>

Now, I have a code here which I am using to update status but it is only applicable for single row update which is based on the "ID". How can i use this for multiple row update? I've read about using a "case" but i dont know how to implement it using the code below.

Here is the code to retrieved data from db and update row but only for single ID, i cant make it to work when retrieving multiple ID's and updating multiple ID's after clicking a Submit button.

<?php
/*
Allows the user to both create new records and edit existing records
*/


// connect to the database
include('include/connect-db.php');


// creates the new/edit record form
// since this form is used multiple times in this file, I have made it a function that is easily reusable
function renderForm($column1 = '', $column2 = '', $column3 = '', $column4 = '', $error = '', $id = '')
{ ?>


<html xmlns="http://www.w3.org/1999/xhtml">
<body class="oneColFixCtrHdr">


<div id="container">


<?php if ($error != '') {
echo "<div style='padding:4px; border:1px solid red; color:red'>" . $error
. "</div>";
} ?>


<div id="mainContent">
<form action="" method="post">
<table width="450" border="0" align="center">


<?php if ($id != '') { ?>
<input type="hidden" name="id" value="<?php echo $id; ?>" />
<p>ID: <?php echo $id; ?></p>
<?php } ?>


<tr>
<td>A1:<span style="color: #ff0000;"><strong>*</strong></span></td>
<td colspan="2"><input type="text" name="column1" value="<?php echo $column1;?>"/></td>
</tr>
<tr>
<td>A2:<span style="color: #ff0000;"><strong>*</strong></span></td>
<td colspan="2"><input type="text" name="column2" value="<?php echo $column2; ?>" readonly="readonly"/></td>
</tr>
<tr colspan="3">
<td>A3:<span style="color: #ff0000;"><strong>*</strong></span></td>
<td colspan="2"><input type="text" name="column3" id="column3" value="<?php echo $column3; ?>"/></td>
</tr>
<tr>
<td><input type="submit" name="submit" value="Submit" /></td>
</tr>
</table>
</form>


<!-- end #mainContent --></div>
<!-- end #container --></div>
</body>
</html>


<?php }


/*


EDIT RECORD


*/
// if the 'id' variable is set in the URL, we know that we need to edit a record
if (isset($_GET['id']))
{
// if the form's submit button is clicked, we need to process the form
if (isset($_POST['submit']))
{
// make sure the 'id' in the URL is valid
if (is_numeric($_POST['id']))
{
// get variables from the URL/form
$id = $_POST['id'];
$column1 = $_POST['column1'];
$column2 = htmlentities($_POST['column2'], ENT_QUOTES);
$column3 = htmlentities($_POST['column3'], ENT_QUOTES);


// check that fields are not empty
if ($column1 == '' || $column2 == '' || $column3 == '')
{
// if they are empty, show an error message and display the form
$error = 'ERROR: Please fill in all required fields!';
renderForm($column1, $column2, $column3, $error, $id);
}
else
{
// if everything is fine, update the record in the database
if ($stmt = $mysqli->prepare("UPDATE sample_table SET column1 = ?, column2 = ?, column3 = ?,
WHERE id=?"))
{
$stmt->bind_param("sssi", $column1, $column2, $column3, $id);
$stmt->execute();
$stmt->close();
}
// show an error message if the query has an error
else
{
echo "ERROR: could not prepare SQL statement.";
}


// redirect the user once the form is updated
header("Location: list.php");
}
}
// if the 'id' variable is not valid, show an error message
else
{
echo "Error!";
}
}
// if the form hasn't been submitted yet, get the info from the database and show the form
else
{
// make sure the 'id' value is valid
if (is_numeric($_GET['id']) && $_GET['id'] > 0)
{
// get 'id' from URL
$id = $_GET['id'];


// get the record from the database
if($stmt = $mysqli->prepare("SELECT column1, column2, column3 FROM sample_table WHERE id=?"))
{
$stmt->bind_param("i", $id);
$stmt->execute();


$stmt->bind_result($column1, $column2, $column3);
$stmt->fetch();


// show the form
renderForm($column1, $column2, $column3, NULL, $id);


$stmt->close();
}
// show an error if the query has an error
else
{
echo "Error: could not prepare SQL statement";
}
}
// if the 'id' value is not valid, redirect the user back to the view.php page
else
{
header("Location: list.php");
}
}
}
// close the mysqli connection
$mysqli->close();
?>

Edited August 8, 2017 by cainam29

Barand · August 8, 2017

Assuming the ID is primary key or a unique key and you want to apply the following updates to col3

ID | col3

----+--------

1 | 2

5 | 1

6 | 3

Then you can use

INSERT INTO sample_table (ID, col3) VALUES
(1,2), 
(5,1), 
(6,3)
ON DUPLICATE KEY UPDATE 
    col3 = VAUES(col3);

The CASE method that you mentioned would be

UPDATE sample_table
SET col3 = CASE 
             WHEN ID = 1 THEN 2
             WHEN ID = 5 THEN 1
             WHEN ID = 6 THEN 3
             ELSE col3
           END

cainam29 · August 10, 2017

Hi Barand, yes the ID is the primary key and i want to know if the values that you provided in column3 is it arbitrary, can I use that code directly? I actually managed to change my code but nothing happens when I click on submit. Sorry got really stuck here, dont know how to proceed, this code is used to call my main code

$(document).ready(function(){
        $("#RetrieveList").on('click',function() {
            var status = $('#status').val();
            var date = $('#Date').val();
            var date1 = $('#Date1').val();
            $.post('retrieve_status',{status:status, date:date, date1:date1}, function(data){
            $("#results").html(data);
            });
            return false;
        });

Start Date:
<input type="text" name="Date" id="Date" size="8"/>
End Date:
<input type="text" name="Date1" id="Date1" size="8"/>
<input name="action" type="submit" id="RetrieveList" value="Retrieve List" />
<input name="Clear" type="reset" id="Clear" value="Clear" onclick="window.location.reload()" />
<input type="submit" name="submit" value="Submit" />

Then this is the main code that I am using to process the data,

<html>
<head>
</head>
<body>
<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "trackerdb";


// check data before use it and convert from string to expected type, use try, not like here:
$date = $_POST['date'];
$date1 = $_POST['date1'];




// use valid data to select rows
try {
    //1. connect to MySQL database
    $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);


    //2. set the PDO error mode to exception
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);


    //3. create query string (here is answer on your question)
    $sql = 'SELECT id, changeid, taskid, summary, type, reviewed_approved_by, scheduled_start_date, implemented_by FROM crqtracker WHERE scheduled_start_date BETWEEN :d1 AND :d2';


    //4. prepare statement from query string
    $stmt = $conn->prepare($sql);


    //5. bind optional parameters
    //if ($status != 'All') $stmt->bindParam(':st', $status);


    //6. bind parameters
    $stmt->bindParam(':d1', $date);
    $stmt->bindParam(':d2', $date1);


    //7. execute statement
    $stmt->execute();


    //8. returns an array containing all of the result set rows 
    $result = $stmt->fetchAll(PDO::FETCH_ASSOC);


    //get count of rows
    $numrow = count($result);


    //print array - there is many solution to print array,
    //to debug you can do: 
    //print_r($result);


} catch(PDOException $e) {
    echo "Error: " . $e->getMessage();
}
$conn = null;


if($numrow == 0) 
  echo "No results found.";
else 
  echo "CRQ Count: $numrow</br>";
{


echo "<table width='auto' cellpadding='1px' cellspacing='0px' border=1 align='center'>
<tr>
<!--<th align='center'><input id=checkall name=checkall id=checkall type=checkbox value='' /></th>-->
<th align='center'><strong>Change ID</strong></th>
<th align='center'><strong>Task ID</strong></th>
<th align='center'><strong>Summary</strong></th>
<th align='center'><strong>Type</strong></th>
<th align='center'><strong>Reviewed/Approved By</strong></th>
<th align='center'><strong>Scheduled Start Date</strong></th>
<th align='center'><strong>Implementer</strong></th>
</tr>"; 






foreach ($result as $row => $info) {
echo "<form action='crqretrieve_status.php' method='post'>";
echo"<tr>"; 
echo  "<td align='center'>" . $info['changeid'] . "<input type=hidden name=changeid value=" . $info['changeid'] . " </td>";
echo  "<td align='center'>" . $info['taskid'] . "<input type=hidden name=taskid value=" . $info['taskid'] . " </td>"; 
echo  "<td align='center'>" . $info['summary'] . "<input type=hidden name=summary value=" . $info['summary'] . " </td>";
echo  "<td align='center'>" . $info['type'] . "<input type=hidden name=type value=" . $info['type'] . " </td>";
echo  "<td align='center'>" . $info['reviewed_approved_by'] . "<input type=hidden name=reviewed_approved_by value=" . $info['reviewed_approved_by'] . " </td>";
echo  "<td align='center'>" . $info['scheduled_start_date'] . "<input type=hidden name=scheduled_start_date value=" . $info['scheduled_start_date'] . " </td>";
echo  "<td align='center'><input name='implemented_by[" . $info['id'] . "]' value='' /> </td>";  
echo "</tr>"; 
echo "</form>";


}
}
echo "</table>";


?>


</body>
</html>


<?php 


/*


EDIT RECORD


*/
// if the 'id' variable is set in the URL, we know that we need to edit a record
if (isset($_GET['id']))
{
// if the form's submit button is clicked, we need to process the form
if (isset($_POST['submit']))
{
// make sure the 'id' in the URL is valid
if (is_numeric($_POST['id']))
{
// get variables from the URL/form
$id = $_POST['id'];
$changeid = htmlentities($_POST['changeid'], ENT_QUOTES);
$taskid = htmlentities($_POST['taskid'], ENT_QUOTES);
$summary = htmlentities($_POST['summary'], ENT_QUOTES);
$type = htmlentities($_POST['type'], ENT_QUOTES);
$reviewed_approved_by = htmlentities($_POST['reviewed_approved_by'], ENT_QUOTES);
$scheduled_start_date = htmlentities($_POST['scheduled_start_date'], ENT_QUOTES);
$implemented_by = htmlentities($_POST['implemented_by'], ENT_QUOTES);


// check that fields are not empty
if ($changeid == '' || $taskid == '' || $summary == '' || $type == '' || $reviewed_approved_by == '' || $scheduled_start_date == '' || $implemented_by == '')
{
// if they are empty, show an error message and display the form
$error = 'ERROR: Please fill in all required fields!';
renderForm($changeid, $taskid, $summary, $type, $reviewed_approved_by, $scheduled_start_date, $implemented_by, $error, $id);
}
else
{
// if everything is fine, update the record in the database
if ($stmt = $mysqli->prepare("UPDATE crqtracker SET implemented_by = ?
WHERE id=?"))
{
$stmt->bind_param("si", $implemented_by, $id);
$stmt->execute();
$stmt->close();
}
// show an error message if the query has an error
else
{
echo "ERROR: could not prepare SQL statement.";
}


// redirect the user once the form is updated
header("Location: list.php");
}
}
// if the 'id' variable is not valid, show an error message
else
{
echo "Error!";
}
}
// if the form hasn't been submitted yet, get the info from the database and show the form
else
{
// make sure the 'id' value is valid
if (is_numeric($_GET['id']) && $_GET['id'] > 0)
{
// get 'id' from URL
$id = $_GET['id'];


// get the record from the database
if($stmt = $mysqli->prepare("SELECT * FROM crqtracker WHERE id=?"))
{
$stmt->bind_param("i", $id);
$stmt->execute();


$stmt->bind_result($changeid, $taskid, $summary, $type, $reviewed_approved_by, $scheduled_start_date, $implemented_by);
$stmt->fetch();


// show the form
renderForm($changeid, $taskid, $summary, $type, $reviewed_approved_by, $scheduled_start_date, $implemented_by, NULL, $id);


$stmt->close();
}
// show an error if the query has an error
else
{
echo "Error: could not prepare SQL statement";
}
}
// if the 'id' value is not valid, redirect the user back to the view.php page
else
{
header("Location: list.php");
}
}
}


?>

Barand · August 10, 2017

I would leave the single query solutions (above) until you convert to PDO, It can be a PIA with mysqli. The simplest (although less efficient) solution would be to prepare the update query then loop through the post data, executing the query with each pair of parameters

if ($stmt = $mysqli->prepare("UPDATE crqtracker SET implemented_by = ?WHERE id=?")){
    foreach ($_POST['implemented_by'] as $id => $implemented_by)
  {
        $stmt->bind_param("si", $implemented_by, $id);
        $stmt->execute();
    }
  }
  $stmt->close();

If you are only updating the one column, why all the other form fields?

Apologies for formatting - editing on android tablet

Edited August 10, 2017 by Barand

cainam29 · August 10, 2017

The reason for having the other columns fetch as well is so that we have a view of the details of the ticket before we update and eventually send only the updated value to db. I'll be honest that i do not know how to implement this with my code above.

INSERT INTO sample_table (ID, col3) VALUES
(1,2), 
(5,1), 
(6,3)
ON DUPLICATE KEY UPDATE
col3 = VAUES(col3);

Barand · August 10, 2017

This is how to do it using PDO (not mysqli)

$dsn   = "mysql:dbname=??????;host=127.0.0.1";
$username = '????????';
$password = '????????';

$db = new pdo($dsn, $username,$password, 
    [
        PDO::ATTR_ERRMODE          => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_EMULATE_PREPARES => false
    ]);



$placeholders = [];
$data = [];

foreach ($_POST['implemented_by'] as $id => $implemented_by) {
    if (trim($implemented_by) != '') {
        $placeholders[]  = '(?, ?)';
        array_push($data, $id, $implemented_by);
    }
}

$sql = "INSERT INTO crqtracker (id, implemented_by) VALUES " .
       join(',', $placeholders) .
       " ON DUPLICATE KEY UPDATE implemented_by = VALUES(implemented_by)";
$stmt  = $db->prepare($sql);
$stmt->execute($data);

cainam29 · August 12, 2017

Hi Barand,

Here is my Edit Record part but for some reason I'm getting this error:

Parse error: syntax error, unexpected 'else' (T_ELSE) in C:\*\retrieve_status_test2.php on line 150

That is when I tried to incorporate your PDO code to my edit record,

<?php 


/*


EDIT RECORD


*/
// if the 'id' variable is set in the URL, we know that we need to edit a record
if (isset($_GET['id']))
{
// if the form's submit button is clicked, we need to process the form
if (isset($_POST['submit']))
{
// make sure the 'id' in the URL is valid
if (is_numeric($_POST['id']))
{
// get variables from the URL/form
$id = $_POST['id'];
$changeid = htmlentities($_POST['changeid'], ENT_QUOTES);
$taskid = htmlentities($_POST['taskid'], ENT_QUOTES);
$summary = htmlentities($_POST['summary'], ENT_QUOTES);
$type = htmlentities($_POST['type'], ENT_QUOTES);
$reviewed_approved_by = htmlentities($_POST['reviewed_approved_by'], ENT_QUOTES);
$scheduled_start_date = htmlentities($_POST['scheduled_start_date'], ENT_QUOTES);
$implemented_by = htmlentities($_POST['implemented_by'], ENT_QUOTES);
$placeholders = [];
$data = [];


// check that fields are not empty
if ($changeid == '' || $taskid == '' || $summary == '' || $type == '' || $reviewed_approved_by == '' || $scheduled_start_date == '' || $implemented_by == '')
{
// if they are empty, show an error message and display the form
$error = 'ERROR: Please fill in all required fields!';
renderForm($changeid, $taskid, $summary, $type, $reviewed_approved_by, $scheduled_start_date, $implemented_by, $error, $id);
}
else
{
// if everything is fine, update the record in the database


foreach ($_POST['implemented_by'] as $id => $implemented_by) {
    if (trim($implemented_by) != '') {
        $placeholders[]  = '(?, ?)';
        array_push($data, $id, $implemented_by);
    }
}


$sql = "INSERT INTO crqtracker (id, implemented_by) VALUES " .
       join(',', $placeholders) .
       " ON DUPLICATE KEY UPDATE implemented_by = VALUES(implemented_by)";
$stmt  = $db->prepare($sql);
$stmt->execute($data);
}
// show an error message if the query has an error
else
{
echo "ERROR: could not prepare SQL statement.";
}


// redirect the user once the form is updated
header("Location: list.php");
}
}
// if the 'id' variable is not valid, show an error message
else
{
echo "Error!";
}
}
// if the form hasn't been submitted yet, get the info from the database and show the form
else
{
// make sure the 'id' value is valid
if (is_numeric($_GET['id']) && $_GET['id'] > 0)
{
// get 'id' from URL
$id = $_GET['id'];


// get the record from the database
if($stmt = $mysqli->prepare("SELECT * FROM crqtracker WHERE id=?"))
{
$stmt->bind_param("i", $id);
$stmt->execute();


$stmt->bind_result($changeid, $taskid, $summary, $type, $reviewed_approved_by, $scheduled_start_date, $implemented_by);
$stmt->fetch();


// show the form
renderForm($changeid, $taskid, $summary, $type, $reviewed_approved_by, $scheduled_start_date, $implemented_by, NULL, $id);


$stmt->close();
}
// show an error if the query has an error
else
{
echo "Error: could not prepare SQL statement";
}
}
// if the 'id' value is not valid, redirect the user back to the view.php page
else
{
header("Location: list.php");
}
}
?>

Barand · August 12, 2017

An "else" should have corresponding "if".

cainam29 · August 12, 2017

Okay...thanks Barand. I can get the page loaded now with the retrieved data but when I update values in implemented_by column by hitting submit. It just refresh the page and the table disappear as if it refreshed the entire page.

Here is the updated code,

<html>
<head>
</head>
<body>
<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "trackerdb";


// check data before use it and convert from string to expected type, use try, not like here:
$date = filter_input(INPUT_POST, 'date');
$date1 = filter_input(INPUT_POST, 'date1');




// use valid data to select rows
try {
    //1. connect to MySQL database
    $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);


    //2. set the PDO error mode to exception
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);


    //3. create query string (here is answer on your question)
    $sql = 'SELECT id, changeid, taskid, summary, type, reviewed_approved_by, scheduled_start_date, implemented_by FROM crqtracker WHERE scheduled_start_date BETWEEN :d1 AND :d2';


    //4. prepare statement from query string
    $stmt = $conn->prepare($sql);


    //5. bind optional parameters
    //if ($status != 'All') $stmt->bindParam(':st', $status);


    //6. bind parameters
    $stmt->bindParam(':d1', $date);
    $stmt->bindParam(':d2', $date1);


    //7. execute statement
    $stmt->execute();


    //8. returns an array containing all of the result set rows 
    $result = $stmt->fetchAll(PDO::FETCH_ASSOC);


    //get count of rows
    $numrow = count($result);


    //print array - there is many solution to print array,
    //to debug you can do: 
    //print_r($result);


} catch(PDOException $e) {
    echo "Error: " . $e->getMessage();
}
$conn = null;


if($numrow == 0) 
  echo "No results found.";
else 
  echo "CRQ Count: $numrow</br>";
{


echo "<table width='auto' cellpadding='1px' cellspacing='0px' border=1 align='center'>
<tr>
<!--<th align='center'><input id=checkall name=checkall id=checkall type=checkbox value='' /></th>-->
<th align='center'><strong>Change ID</strong></th>
<th align='center'><strong>Task ID</strong></th>
<th align='center'><strong>Summary</strong></th>
<th align='center'><strong>Type</strong></th>
<th align='center'><strong>Reviewed/Approved By</strong></th>
<th align='center'><strong>Scheduled Start Date</strong></th>
<th align='center'><strong>Implementer</strong></th>
</tr>"; 






foreach ($result as $row => $info) {
echo "<form action='crqretrieve_status.php' method='post'>";
echo"<tr>"; 
echo  "<td align='center'>" . $info['changeid'] . "<input type=hidden name=changeid value=" . $info['changeid'] . " /></td>";
echo  "<td align='center'>" . $info['taskid'] . "<input type=hidden name=taskid value=" . $info['taskid'] . " /></td>"; 
echo  "<td align='center'>" . $info['summary'] . "<input type=hidden name=summary value=" . $info['summary'] . " /></td>";
echo  "<td align='center'>" . $info['type'] . "<input type=hidden name=type value=" . $info['type'] . " /></td>";
echo  "<td align='center'>" . $info['reviewed_approved_by'] . "<input type=hidden name=reviewed_approved_by value=" . $info['reviewed_approved_by'] . " /></td>";
echo  "<td align='center'>" . $info['scheduled_start_date'] . "<input type=hidden name=scheduled_start_date value=" . $info['scheduled_start_date'] . " /></td>";
echo  "<td align='center'><input name='implemented_by[" . $info['id'] . "]' value='' /> </td>";  
echo "</tr>"; 
echo "</form>";


}
}
echo "</table>";


?>


</body>
</html>


<?php 


/*


EDIT RECORD


*/
// if the 'id' variable is set in the URL, we know that we need to edit a record
if (isset($_GET['id']))
{
// if the form's submit button is clicked, we need to process the form
if (isset($_POST['submit']))
{
// make sure the 'id' in the URL is valid
if (is_numeric($_POST['id']))
{
// get variables from the URL/form
$id = $_POST['id'];
$changeid = htmlentities($_POST['changeid'], ENT_QUOTES);
$taskid = htmlentities($_POST['taskid'], ENT_QUOTES);
$summary = htmlentities($_POST['summary'], ENT_QUOTES);
$type = htmlentities($_POST['type'], ENT_QUOTES);
$reviewed_approved_by = htmlentities($_POST['reviewed_approved_by'], ENT_QUOTES);
$scheduled_start_date = htmlentities($_POST['scheduled_start_date'], ENT_QUOTES);
$implemented_by = htmlentities($_POST['implemented_by'], ENT_QUOTES);


// check that fields are not empty
if ($changeid == '' || $taskid == '' || $summary == '' || $type == '' || $reviewed_approved_by == '' || $scheduled_start_date == '' || $implemented_by == '')
{
// if they are empty, show an error message and display the form
$error = 'ERROR: Please fill in all required fields!';
renderForm($changeid, $taskid, $summary, $type, $reviewed_approved_by, $scheduled_start_date, $implemented_by, $error, $id);
}
else
{
// if everything is fine, update the record in the database
$placeholders = [];
$data = [];


foreach ($_POST['implemented_by'] as $id => $implemented_by) {
    if (trim($implemented_by) != '') {
        $placeholders[]  = '(?, ?)';
        array_push($data, $id, $implemented_by);
    }
}


if ($sql = "INSERT INTO crqtracker (id, implemented_by) VALUES " .
       join(',', $placeholders) .
       " ON DUPLICATE KEY UPDATE implemented_by = VALUES(implemented_by)"
)
{
$stmt  = $db->prepare($sql);
$stmt->execute($data);
}
// show an error message if the query has an error
else
{
echo "ERROR: could not prepare SQL statement.";
}


// redirect the user once the form is updated
header("Location: list.php");
}
}
// if the 'id' variable is not valid, show an error message
else
{
echo "Error!";
}
}
// if the form hasn't been submitted yet, get the info from the database and show the form
else
{
// make sure the 'id' value is valid
if (is_numeric($_GET['id']) && $_GET['id'] > 0)
{
// get 'id' from URL
$id = $_GET['id'];


// get the record from the database
if($stmt = $mysqli->prepare("SELECT * FROM crqtracker WHERE id=?"))
{
$stmt->bind_param("i", $id);
$stmt->execute();


$stmt->bind_result($changeid, $taskid, $summary, $type, $reviewed_approved_by, $scheduled_start_date, $implemented_by);
$stmt->fetch();


// show the form
renderForm($changeid, $taskid, $summary, $type, $reviewed_approved_by, $scheduled_start_date, $implemented_by, NULL, $id);


$stmt->close();
}
// show an error if the query has an error
else
{
echo "Error: could not prepare SQL statement";
}
}
// if the 'id' value is not valid, redirect the user back to the view.php page
else
{
header("Location: list.php");
}
}
}


?>

Sign In

Multiple update of rows in a single column

Recommended Posts

cainam29

Link to comment

Share on other sites

Barand

Link to comment

Share on other sites

cainam29

Link to comment

Share on other sites

Barand

Link to comment

Share on other sites

cainam29

Link to comment

Share on other sites

Barand

Link to comment

Share on other sites

cainam29

Link to comment

Share on other sites

Barand

Link to comment

Share on other sites

cainam29

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information