Multiple update of rows in a single column
Results 1 to 4 of 4

Thread: Multiple update of rows in a single column

  1. #1
    Junior Member
    Join Date
    Jun 2014
    Posts
    5

    Multiple update of rows in a single column

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

    Code:
    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,

    Code:
    $(document).ready(function(){
            $("#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){
                $("#results").html(data);
                });
                return false;
            });
    This is the PHP code,

    PHP Code:
    <?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_ERRMODEPDO::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>";

    ?>
    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.

    PHP Code:
    // 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);
    }
    else
    {
    // 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);
    $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, column4 FROM sample_table WHERE id=?"))
    {
    $stmt->bind_param("i"$id);
    $stmt->execute();

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

    // show the form
    renderForm($column1$column2$column3$column4NULL$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(); 

  2. #2
    High Energy Magic Dept. NogDog's Avatar
    Join Date
    Aug 2006
    Location
    Ankh-Morpork
    Posts
    14,801
    Since no one else is answering, I'll preface this by saying I have not looked at every line of your code. My excuse is lack of sleep and generally feeling crappy today, plus that's a lot of text in your post.

    So, based on what I think you are asking -- and I may well be wrong -- you want to display several rows of data, allow the user to make changes in any/all of them, then submit it once?

    If so, then you want your <form></form> tag to encompass all the rows, not one set of tags for each row. On top of that, you'll want to identify each form field submitted with a particular row. The way I'd probably do that is to use the array notation option, putting whatever is the best unique ID for your circumstances in there. So for now, assuming that identifier is in $info['id'] when you do your foreach() loop on $result, you might name that input element as:
    PHP Code:
    echo  "<td align='center'><input name='column3[" $info['id'] . "]' value='' </td>"
    Then when processing the request, you can loop through them to do your updates:
    PHP Code:
    foreach($_POST['column3'] as $id => $value) {
        
    // update the DB where id = $id and setting column3 to $value

    "Well done....Consciousness to sarcasm in five seconds!" ~ Terry Pratchett, Night Watch

    How to Ask Questions the Smart Way (not affiliated with this site, but well worth reading)

    My Blog
    cwrBlog: simple, no-database PHP blogging framework

  3. #3
    Junior Member
    Join Date
    Jun 2014
    Posts
    5
    Thanks for the response, what I would want to happen is that the user would be able to fetch the data from database based on the start/end date and then edit only a single column which in my sample is column3 alone. The reason why i want the other column to show up is so that user would know the details of what is being edited, because again we are just fetching data based on dates. Then after manually entering different values in column3 then user would hit the submit button to update the db.

    Here is my code again, the fetch part seems to work but the editing record part does not as the page would just refresh when hitting submit.


    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 tracker 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='retrieve_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 tracker 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");
    }
    }
    }
    
    ?>

  4. #4
    Senior Member
    Join Date
    Apr 2016
    Posts
    112
    Sorry for what sounds like a lecture, but don't use the form processing code that you are showing us. Beyond that it is designed to edit a single row, it has several implementation problems that make it a bunch of verbose and badly designed code that isn't worth saving.

    Next, you need to decide if you actually want to update multiple rows at one time or update a single row at a time. Unless this is an assignment to see if you can figure out how to submit multiple values and update them, this operation won't get used enough to justify the code needed to update multiple values at one time, particularly if you are going to dynamically build a single multi-value prepared sql query to do this or execute a single value prepared query inside of a loop.

    If you do want to submit and update multiple values at one time, you need to do something that NogDog wrote in his reply above, and put a (one) <form></form> tag around all the rows being edited. You are currently still putting a separate form tag around each row being edited.

    You also need to remove the hidden form fields. You can retrieve and display the information for reference, but there's no point in putting it into hidden form fields, since it isn't being used. Only the submitted id (which is the field array name index) and implemented_by field value are being used.

    You should also change the implemented_by form field to a select/option menu, so that you are just selecting from the available users. The option value='...' attribute would be the user id, which is what should be stored in the tracker table. By requiring someone to type in a user name, they will need to know how to correctly spell the name, in which order the first/last name is being used, if the user has permission to be assigned to a task, and if the user is available to be assigned to a task. If you instead use a select/option menu, dynamically produced from the user data, whoever is assigning users to tasks will only be able to pick from the available users.

    Regardless of the type of form field you use for the implemented_by field, it needs have any current value from the tracker table set, either as the selected option or the field value, so that you can see what any current value is and so that rows that you are not changing don't cause the value in the tracker table to be set to empty values. You would want the ability to clear an existing value, so, you must submit any existing values to prevent data from being cleared.

    As to your form processing code -

    1) It needs to be near the top of your file, before the start of the html document.

    2) It needs to use the php PDO extension, the same as what your SELECT query code is using.

    3) It doesn't use htmlentities(). Htmlentities is an output function. It is only used on values when you output them to the browser. It is not used on data being used by an sql query.

    4) Only the submitted id and implemented_by field values are being used and there should only be code for these two values. NogDog showed how you can loop over the submitted data to get each pair of ids/values.

    5) To use a single-value prepared UPDATE query inside of a loop, you would prepare the query once, before the start of the loop, then just get the current id/value inside the loop and execute the prepared query.

    If I/someone has time, they will post an example of using PDO to do this.
    Programming should not be a painful activity. If you are experiencing pain while programming, you are probably doing something wrong.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •