0

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,

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

?>

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);
}
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, $column4, 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();
3
Contributors
21
Replies
130
Views
1 Week
Discussion Span
Last Post by rproffitt
0

If you want to update only column3 then do not need put in form other columns

<?php
echo '
<form action="crqretrieve_status.php" method="post">
    <table>';

foreach ($result as $row => $info) {
    echo '
<tr>
    <td align="center">
        '.$info['column1'].'
    </td>
    <td align="center">
        '.$info['column2'].'
    </td>
    <td align="center">
        <input type="text" name="id['.$info['id'].']" value="'.$info['column3'].'" />
    </td>
</tr>'; 
    }

echo '
    </table>
</form>';
?>

Update foreach

<?php
foreach($_POST['id'] as $key => $val)
{
    $id = filter_var($key, FILTER_VALIDATE_INT);
    $column3 = filter_var($val, FILTER_SANITIZE_STRING);

    // if everything is fine, update the record in the database
    if($id !== NULL && $column3 !== NULL)
    {
        if ($stmt = $mysqli->prepare("UPDATE sample_table SET column3 = ? WHERE id = ? "))
        {
            $stmt->bind_param("si", $column3, $id);
            $stmt->execute();
            $stmt->close();
        }
    }
}
?>

Edited by AndrisP

0

Hi, sorry for the late reply, got so busy. But I managed to changed my code but nothing happens when i click on submit, also I would need the other columns as I will work on sending it via email along with the newly updated value later on.

<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'];

// 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");
}
}
}

?>
0

Your input tags is not not closed in to the lines 79..84
Lines 15..17 is duplicat of lines 11..13
I recommend use $date = filter_input(INPUT_POST, 'date'); and $date1 = filter_input(INPUT_POST, 'date1'); instead of $date = $_POST['date']; because it raise warning - undefined index if variables is not set. And then check if($date !== NULL && $date1 !== NULL){ ... } before SELECT (line 28)

Edited by AndrisP

0

Sorry for that, I've already corrected the code with your suggestions. The fetch part seems to work but not the editing record part. When I hit submit the page just refresh and clears the content. I dont know how to update/edit my post here as well.

0

Update functions should be before line 28 to take update effects in your SELECT
All POST variables would be better with filter_input_array() function e.g.

$args = array(
    'id' => array(
        'filter'    => FILTER_VALIDATE_INT
    ),
    'changeid' => array(
        'filter'    => FILTER_FLAG_NO_ENCODE_QUOTES
    ),
    'taskid' => array(
        'filter'    => FILTER_FLAG_NO_ENCODE_QUOTES
    ),
    'summary' => array(
        'filter'    => FILTER_FLAG_NO_ENCODE_QUOTES
    ),
    'type' => array(
        'filter'    => FILTER_FLAG_NO_ENCODE_QUOTES
    ),
    'reviewed_approved_by' => array(
        'filter'    => FILTER_FLAG_NO_ENCODE_QUOTES
    ),
    'scheduled_start_date' => array(
        'filter'    => FILTER_FLAG_NO_ENCODE_QUOTES
    ),
    'implemented_by' => array(
        'filter'    => FILTER_FLAG_NO_ENCODE_QUOTES
    )
);

$post = filter_input_array(INPUT_POST, $args);

instead of lines 115..122

Edited by AndrisP

0

I cant put the update before line 28 as i want to retrieved the data first before i edit and update db with the new values.

0

You are wrong - update will be skipped when it is not set but if you submit updates then need set updates in to the table before you again select data else you see old data in form.

Edited by AndrisP

1

Try this

<?php

define('DBhost', 'localhost');
define('DBname', 'trackerdb');
define('DBport', 3306);
define('DBuser', 'root');
define('DBpswd', '');

$message = array(
    'error' => array(),
    'warning' => array(),
    'info' => array()
);
$bgcol = array(
    'error' => '#FFDDDD',
    'warning' => '#FFFFDD',
    'info' => '#DDFFDD'
);

function check_date(&$message, $value, $per){
    if($value == "" || $value === NULL){
        if(isset($_POST[$per])){ // empty variable is set in form
            $message['warning'][] = "Date ".$per." is not set";
            } // else without message (form not submited)
        return NULL;
        }
    elseif(preg_match('/^[0-9]{4}\-[0-9]{2}\-[0-9]{2}$/', $value)){
        return $value;
    }
    $message['error'][] = "Invalid date: ".$value;
    return NULL;
}

$date_args = array(
    'from' => array(
        'filter'    => FILTER_CALLBACK,
        'options'   => (function($value) use(&$message){
            return check_date($message, $value, "from");
        })
    ),
    'to' => array(
        'filter'    => FILTER_CALLBACK,
        'options'   => (function($value) use(&$message){
            return check_date($message, $value, "to");
        })
    )
);

$update_args = array(
    'id' => array(
        'filter' => FILTER_VALIDATE_INT,
        'flags' => FILTER_REQUIRE_ARRAY
    ),
    'changeid' => array(
        'filter' => FILTER_FLAG_NO_ENCODE_QUOTES,
        'flags' => FILTER_REQUIRE_ARRAY
    ),
    'taskid' => array(
        'filter' => FILTER_FLAG_NO_ENCODE_QUOTES,
        'flags' => FILTER_REQUIRE_ARRAY
    ),
    'summary' => array(
        'filter' => FILTER_FLAG_NO_ENCODE_QUOTES,
        'flags' => FILTER_REQUIRE_ARRAY
    ),
    'type' => array(
        'filter' => FILTER_FLAG_NO_ENCODE_QUOTES,
        'flags' => FILTER_REQUIRE_ARRAY
    ),
    'reviewed_approved_by' => array(
        'filter' => FILTER_FLAG_NO_ENCODE_QUOTES,
        'flags' => FILTER_REQUIRE_ARRAY
    ),
    'scheduled_start_date' => array(
        'filter' => FILTER_FLAG_NO_ENCODE_QUOTES,
        'flags' => FILTER_REQUIRE_ARRAY
    ),
    'implemented_by' => array(
        'filter' => FILTER_FLAG_NO_ENCODE_QUOTES,
        'flags' => FILTER_REQUIRE_ARRAY
    )
);

$update = filter_input_array(INPUT_POST, $update_args);
$date = filter_input_array(INPUT_POST, $date_args);

$dsn = 'mysql:dbname='.DBname.';host='.DBhost.';port='.DBport;
try {
    $conn = new PDO($dsn, DBuser, DBpswd);
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    if(isset($update['id']) && is_array($update['id']) && !empty($update['id'])){
        $sql = "UPDATE `crqtracker`
            SET `changeid` = :bv_changeid
            ,`taskid` = :bv_taskid
            ,`summary` = :bv_summary
            ,`type` = :bv_type
            ,`reviewed_approved_by` = :bv_reviewed_approved_by
            ,`scheduled_start_date` = :bv_scheduled_start_date
            ,`implemented_by` = :bv_implemented_by
            WHERE `id` = :bv_id ";
        if($stmt = $conn->prepare($sql)){
            $stmt->bindParam(':bv_changeid', $changeid, PDO::PARAM_INT);
            $stmt->bindParam(':bv_taskid', $taskid, PDO::PARAM_INT);
            $stmt->bindParam(':bv_summary', $summary, PDO::PARAM_STR);
            $stmt->bindParam(':bv_type', $type, PDO::PARAM_STR);
            $stmt->bindParam(':bv_reviewed_approved_by', $reviewed_approved_by, PDO::PARAM_STR);
            $stmt->bindParam(':bv_scheduled_start_date', $scheduled_start_date, PDO::PARAM_STR);
            $stmt->bindParam(':bv_implemented_by', $implemented_by, PDO::PARAM_STR);
            $stmt->bindParam(':bv_id', $id, PDO::PARAM_INT);

            $updateRowCount = 0;
            // update multiple rows - all of selected in form
            foreach($update['id'] as $key => $val){
                $changeid = $update['changeid'][$val];
                $taskid = $update['taskid'][$val];
                $summary = $update['summary'][$val];
                $type = $update['type'][$val];
                $reviewed_approved_by = $update['reviewed_approved_by'][$val];
                $scheduled_start_date = $update['scheduled_start_date'][$val];
                $implemented_by = $update['implemented_by'][$val];
                $id = $val;
                $stmt->execute();
                $updateRowCount += $stmt->rowCount();
                }
            if($updateRowCount > 0){
                $message['info'][] = "Updated ".$updateRowCount." row";
                }
            else {
                $message['warning'][] = "Not updated - maybe submited values with no changes";
                }
            }
        else {
            $message['error'][] = "Prepare error!!!";
            }
        }
    if($date['from'] !== NULL && $date['to'] !== NULL){
        // get table data
        $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';
        $stmt = $conn->prepare($sql);
        $stmt->bindParam(':d1', $date['from'], PDO::PARAM_STR);
        $stmt->bindParam(':d2', $date['to'], PDO::PARAM_STR);
        $stmt->execute();
        $result = $stmt->fetchAll(PDO::FETCH_ASSOC);

        // get column comments
        $sql = "select t.column_name, t.column_comment 
        from information_schema.columns t 
        where t.table_name = 'crqtracker'";
        $stmt = $conn->prepare($sql);
        $stmt->execute();
        $colcomments = $stmt->fetchAll(PDO::FETCH_KEY_PAIR);
        }
    }
catch(PDOException $e){
    $message['error'][] = $e->getMessage();
    }
?><!DOCTYPE html>
<html>
<head>
    <title>Table Updates</title>
</head>
<body><?php

foreach($message as $key => $val){
    // show error, warning and info messages
    if(!empty($val)){
        echo '<div style="margin:4px;background:'.$bgcol[$key].';border:2px solid grey;border-radius:8px;">';
        foreach($val as $item){
            echo '<div style="margin:8px;">'.$item.'</div>';
            }
        echo '</div>';
        }
    }
?>
    <div>
    <form action="crqretrieve_status.php" method="post"><?php
if(is_array($result)){
    echo '
    <fieldset>
        <legend>Set updates</legend>
        <div>Changes will affect selected rows only</div>
        <table>
            <thead>
                <tr>';
    // column comment from DB as column header
    foreach($result[0] as $key => $val){
        echo '<th>'.$colcomments[$key].'</th>';
        }
    echo '
                </tr>
            </thead>
            <tbody>';
    foreach($result as $row => $info){
        echo '
    <tr>';
        foreach($info as $key => $val){
            if($key=='id'){
                echo '
        <td title="'.$colcomments[$key].'">'.$val.'. 
            <input type="checkbox" name="'.$key.'['.$info['id'].']"
                    value="'.$val.'" id="rowid_'.$val.'" />
            <label for="rowid_'.$val.'">update</label></td>';
                }
            else {
                echo '
        <td title="'.$colcomments[$key].'">
            <input type="text" name="'.$key.'['.$info['id'].']"
                    value="'.$val.'" />
        </td>';
                }
            }
        echo '
    </tr>'; 
        }
    echo '
            </tbody>
        </table>
    </fieldset>';
    }
?>
    <fieldset>
        <legend>Select period</legend>
        <div>Select date from and date to</div>
        <input type="date" name="from" value="<?=$date['from']; ?>" />
        <input type="date" name="to" value="<?=$date['to']; ?>" />
        <div><input type="submit" value="Submit" /></div>
    </fieldset>
    </form>
    </div>
</body>
</html>

Edited by AndrisP

0

Thanks AndrisP...that seems to work except for the Undefined variable notice whenever i load the page,

Notice: Undefined variable: result in C:\*\test4.php on line 223

Which actually refers to -

if(is_array($result)){

I cant seem to add header so it would look like these

CID | TID | Summary | Type | ReviewedBy | ScheduledStartDate | Owner
R1 | R1 | R1 | R1 | R1 | R1 | R1

On these part of the code,

    <fieldset>
        <legend>Set updates</legend>
        <div>Changes will affect selected rows only</div>
        <table width=auto cellpadding=1px cellspacing=0px border=1 align=center>

            <thead>
            <tr>';      

    // column comment from DB as column header
    foreach($result[0] as $key => $val){
        echo '<th>'.$colcomments[$key].'</th>';
        }
    echo '
            </tr>
            </thead>
            <tbody>';
    foreach($result as $row => $info){
        echo '
    <tr>';
        foreach($info as $key => $val){
            if($key=='id'){
                echo '
        <td title="'.$colcomments[$key].'">'.$val.'. 
            <input type="checkbox" name="'.$key.'['.$info['id'].']"
                    value="'.$val.'" id="rowid_'.$val.'" />
            <label for="rowid_'.$val.'"></label></td>';
                }
            else {
                echo '
        <td title="'.$colcomments[$key].'">
            <input type="text" name="'.$key.'['.$info['id'].']"
                    value="'.$val.'" />
        </td>';
                }
            }
        echo '
    </tr>'; 
        }
    echo '
            </tbody>
        </table>
    </fieldset>';

In that part I'm also trying to add a checkbox header which would select all if I click on it,

This is the script,

<script>
jQuery(document).ready(function () {
  jQuery("input[name=checkall]").click(function () {
    jQuery('input[name=checkall]').prop('checked', this.checked);
    jQuery('input[name=checkbox]').prop('checked', this.checked);
  });

  // if all checkbox are selected, check the selectall checkbox
  // and viceversa
  jQuery("input[name=checkbox]").click(function(){

    if(jQuery("input[name=checkbox]").length == jQuery("input[name=checkbox]:checked").length) {
        jQuery("input[name=checkall]").prop("checked", true);
    } else {
        jQuery("input[name=checkall]").prop("checked", false);
    }
  });
});

</script>

The checkbox all,

echo "<td align='center'>" . "<input type=checkbox name=checkbox id=checkbox value=" . $info['id'] . " </td>";

 <th align='center'><input id=checkall name=checkall id=checkall type=checkbox value='' /></th>

Edited by cainam29

0

About Undefined variable: declare variables $result and also $colcomments - before try { in my code example in to the line 86 - e.g. $result = NULL; $colcomments = NULL;
Column comments should be in MySQL table column comments. Modify existing columns via phpMyAdmin or MySQLworkbench e.g.

ALTER TABLE `crqtracker` CHANGE COLUMN `changeid` `changeid` INT NOT NULL COMMENT 'CID';
ALTER TABLE `crqtracker` CHANGE COLUMN `taskid` `taskid` INT NOT NULL COMMENT 'TID';
-- etc

Edited by AndrisP

0

That was awesome. I learned a lot from you. It was working now, but I just have one more question. How can I make a checkbox header so that it will allow me to select/deselect ALL. I dont know how I can implement that since we only get the comments from the db itself as the headers.

Edited by cainam29

0

Easiest way to select all by default add attribute checked. Or replace type "checkbox" to "hidden" and remove labels if you dont want deselect.

Edited by AndrisP

0

In fact checkboxes is redundant because rows with no changes will not updated - replace all "checkbox" to "hidden"

Edited by AndrisP

0

The fact that we would pull up data by dates actually means we need to update all that will be retrieved thus the need to have the checkbox - checkall option in the header. I just dont know how to put a checkbox on the header as this is a new code to me (pulling the columns comments to make it the header).

0

But yeah, you're right the checkbox is just a redundant I just set it to 'hidden' and update still works beautifully.

My only remaining concern now is how can I make the other columns a read-only and just leave the last column the only one that is editable. Its hard to figure it out but your code really gives me other options to think about. It really helped me a lot and I really appreciate your help.

0

No worries, i got it figured out thru the use of CSS, much easier though messy implementation. Thanks again for all the help, it is much appreciated.

#assign th:first-child + th, #assign td:first-child + td {
  pointer-events: none;
}

#assign th:first-child + th + th, #assign td:first-child + td + td {
  pointer-events: none;
}

#assign th:first-child + th + th + th, #assign td:first-child + td + td + td {
  pointer-events: none;
}

#assign th:first-child + th + th + th + th, #assign td:first-child + td + td + td + td {
  pointer-events: none;
}

#assign th:first-child + th + th + th + th + th, #assign td:first-child + td + td + td + td + td {
  pointer-events: none;
}

#assign th:first-child + th + th + th + th + th + th, #assign td:first-child + td + td + td + td + td + td {
  pointer-events: none;
}
0

I hope its not too much yet, but would you know how to implement a send mail function after updating the database. I would want to email the exact table with the updated column values.

0

@cainam29. Since the top post issues appear to be solved, you should start a new thread for your new issue or problem. I don't see a mention of send email in your top post.

Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.