Multiple update of rows in a single column

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

column1 | column2 | column3 
value1  | value1  | -------
value2  | value2  | -------   <=== this column3 rows does not have value in database yet so it will be blank when I retrieved the data

Now I would like to update column3 rows by putting values to it and send it back to the database with the new value in column3 rows.

The current code that I am using now can only retrieved and update a single ID and I’m stuck here. What I want to happen is to retrieved multiple ID’s and update the same column for all of the ID’s with different values.

Here is a sample of the code that I am using to retrieved data,

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

This is the PHP code,

$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

    //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

    //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: 

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

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

echo "<table width='auto' cellpadding='1px' cellspacing='0px' border=1 align='center'>
<!--<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>

foreach ($result as $row => $info) {
echo "<form action='crqretrieve_status.php' method='post'>";
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>";


From the code above, it has > name=column3 value=‘’, which I want then to assign a value and save it to db.

Tried searching around and I’m not sure how a case would be useful as my update would depend on the ID’s.

Here is the code that I am using for a single query update and I dont know how to associate it to my code above. I would appreciate any help on this.

// 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);
$column4 = htmlentities($_POST['column4'], ENT_QUOTES);

// check that fields are not empty
if ($column1 == '' || $column2 == '' || $column3 == ''|| $column4 == '')
// if they are empty, show an error message and display the form
$error = 'ERROR: Please fill in all required fields!';
renderForm($column1, $column2, $column3, $column4, $error, $id);
// if everything is fine, update the record in the database
if ($stmt = $mysqli->prepare("UPDATE sample_table SET column1 = ?, column2 = ?, column3 = ?, column4 = ?
WHERE id=?"))
$stmt->bind_param("ssssi", $column1, $column2, $column3, $column4, $id);
// show an error message if the query has an error
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
echo "Error!";
// if the form hasn't been submitted yet, get the info from the database and show the form
// 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, column4 FROM sample_table WHERE id=?"))
$stmt->bind_param("i", $id);

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

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

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

// close the mysqli connection

If those columns where never meant to be empty, why are they allowed to be empty for that long? Furthermore, if you can fill in the values automatically, wouldn’t it make sense to fill in the missing values when you created the entry?

Despite that, updating multiple entries in a script is the same as updating a single entry, only that you use a loop.

Hi, thanks for checking this. There are two process involve here, first is we insert the first two columns using other page during the approval process. Then on another process we are trying to assign each row to a specific user thus updating only the column3 rows using another page not the same page we used to upload initially. I hope my explanation is clear.

Then surely you need to echo the id as a hidden field in each form, so that your update query knows which row is to be updated?

This line:

// if the 'id' variable is set in the URL, we know that we need to edit a record
if (isset($_GET['id']))

suggests you need to put it in the URL for your form action parameter, rather than in a hidden field. But you can do it either way as long as the code supports it. But then this line

// make sure the 'id' in the URL is valid
if (is_numeric($_POST['id']))

means that you need to put it in both the URL, and as a hidden field in the form, which seems wasteful.

To my mind:

  • add the id for each row into each form as a hidden variable.
  • validate the entry for column 3
  • use the query as “update tablename set column3 = whatever where id = id”

I doubt if it’s the cause of your problem - looking at viewsource might confirm - but the closing </table> looks like it should be moved up a curly brace.

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.