Update with check box in PHP MYSQL

mehidy · October 7, 2017

Hi I'm trying to update my DB table with below with below code.When I select all row or only 1st row its working fine, but when I select 2nd row or another row, its updating the 1st row value to my DB table. Please see & try to help me.

update.php


<table width="100%" border="0" cellspacing="1" cellpadding="0">
<tr><td>

<form name="namestoupdate" method="post" action="updateproc.php">
<table width="100%" border="0" cellspacing="1" cellpadding="1"> 
<tr bgcolor="#FDEEF4">
<th align="center"><input type="checkbox"  id="selectall" onClick="selectAll(this)" ></th>
<th align="center">ID</th>
<th align="center">ExcelID</th>
<th align="center">STYLE</th>
<th align="center">ORDER NO</th>

<th align="center"><strong>COLOR</strong></th>
<th align="center"><strong>SIZE</strong></th>
<th align="center"><strong>QTY</strong></th>
<th align="center"><strong>CTN-QTY</strong></th>
<th align="center"><strong>INVOICE</strong></th>
<th align="center">KCGMT</th>
<th align="center">BUYER</th>
<th align="center">FACTORY</th>
</tr>

<?php
  include 'db_connection.php';

  $sql = "SELECT * FROM freddyshipment WHERE style='$style' AND invoice='$invoice' AND kcgmt='$kcgmt' AND buyer='$buyer' AND factory='$factory'";
  $result = mysqli_query($conn,$sql) or die (mysqli_error($conn));
  $size = count($_POST['chk']);
  $i = 0;
  while ($Update = mysqli_fetch_array($result)) {
  print "</tr>\n";
  print "<td align='center'><input type='checkbox' name='chk[]' value='{$Update['id']}'/></td>";
  print "<td align='center'>{$Update['id']}</td>";
  print "<td align='center'><input type='text' size='4px' name='excelid[$i]' value='{$Update['excelid']}' /></td>";
  print "<td align='center'><input type='text' size='18px' name='style[$i]' value='{$Update['style']}' /></td>";
  print "<td align='center'><input type='text' size='4px' name='orderno[$i]' value='{$Update['orderno']}' /></td>";
  print "<td align='center'><input type='text' size='3px' name='col[$i]' value='{$Update['col']}' /></td>";
  print "<td align='center'><input type='text' size='2px' name='sizes[$i]' value='{$Update['sizes']}' /></td>\n";
  print "<td align='center'><input type='text' size='3px' name='qty[$i]' value='{$Update['qty']}' /></td>\n";
  print "<td align='center'><input type='text' size='3px' name='ctnqty[$i]' value='{$Update['ctnqty']}' /></td>\n";
  print "<td align='center'><input type='text' size='7px' name='invoice[$i]' value='{$Update['invoice']}' /></td>\n";  
  print "<td align='center'><input type='text' size='7px' name='kcgmt[$i]' value='{$Update['kcgmt']}' /></td>";
  print "<td align='center'><input type='text' size='7px' name='buyer[$i]' value='{$Update['buyer']}' /></td>";
  print "<td align='center'><input type='text' size='7px' name='factory[$i]' value='{$Update['factory']}' /></td>";
  print "</tr>\n";
  ++$i;
  }
  echo mysqli_error($conn); 
  //mysql_close();
?>
<tr> <td colspan="12" align="center"><input type="submit" name="btn" value="Update"></td></tr>";
</table>
</td>
</tr>
</form>
</table>

updateproc.php

<?php
error_reporting(0);
include_once('db_connection.php');

if(isset($_POST['btn'])){
 if(!empty($_POST['chk'])){	
echo $size = count($_POST['chk']);
echo "-";
$i = 0;
while ($i < $size) {
echo $id = $_POST['chk'][$i];
$excelid = $_POST['excelid'][$i];
$style = $_POST['style'][$i];
$orderno = $_POST['orderno'][$i];
$col = $_POST['col'][$i];
$sizes = $_POST['sizes'][$i];
$qty = $_POST['qty'][$i];
$ctnqty = $_POST['ctnqty'][$i];
$invoice = $_POST['invoice'][$i];
$kcgmt = $_POST['kcgmt'][$i];
$buyer = $_POST['buyer'][$i];
$factory = $_POST['factory'][$i];


$query = "UPDATE `freddyshipment` SET 

excelid = '$excelid', 
style = '$style',
orderno = '$orderno', 
col = '$col',
sizes = '$sizes',
qty = '$qty',
ctnqty = '$ctnqty',
invoice = '$invoice',
kcgmt = '$kcgmt',
buyer = '$buyer',
factory = '$factory'
WHERE `id` = '$id' LIMIT 1";
$udateq=mysqli_query($conn,$query) or die ("Error in query: $query");

print "
<tr>
<td align='center'><p>$id</p></td>
<td align='center'>$excelid</td>
<td align='center'>$style</td>
<td align='center'>$orderno</td>
<td align='center'>$col</td>
<td align='center'>$sizes</td>
<td align='center'>$qty</td>
<td align='center'>$ctnqty</td>
<td align='center'>$invoice</td>
<td align='center'>$kcgmt</td>
<td align='center'>$buyer</td>
<td align='center'>$factory</td>
</tr>
";
++$i;
}
 }
	else
	{
	    echo "Please Select Check Box";
	}
 
	}
mysqli_close($conn);
?>

Psycho · October 8, 2017

You are making it hard on yourself. Make the field names a multi-dimensional array using the ID of the records as the primary index and the field names as the secondary id. Also, keep your PHP and HTML separate - it helps make your code easier to manage. Just put the PHP at the top of the file for now. Use variables to hold the output.

Also, VERY IMPORTANT, you should never use data from a user directly in a query or as output on a web page directly. For database, you should always use prepared statements. I was too lazy to add it below. You really must do this. For output to the page you need to use htmlentities or htmlspecialchars

name='record[{$row['id']}][excelid]'
name='record[{$row['id']}][style]'
name='record[{$row['id']}][orderno]'

Then, just reference each 'record' based on the checked items.

<?php
 
  include 'db_connection.php';
 
  $sql = "SELECT *
          FROM freddyshipment
          WHERE style='$style'
            AND invoice='$invoice'
            AND kcgmt='$kcgmt'
            AND buyer='$buyer'
            AND factory='$factory'";
  $result = mysqli_query($conn,$sql) or die (mysqli_error($conn));
 
  $formFields = '';
  while ($row = mysqli_fetch_array($result))
  {
      $formFields .= "<tr>\n";
      $formFields .= "  <td align='center'><input type='checkbox' name='update[{$row['id']}]' value='{$row['id']}'/></td>\n";
      $formFields .= "  <td align='center'>{$Update['id']}</td>\n";
      $formFields .= "  <td align='center'><input type='text' size='4px'  name='records[{$row['id']}][excelid]' value='".htmlspecialchars($row['excelid'])."' /></td>\n";
      $formFields .= "  <td align='center'><input type='text' size='18px' name='records[{$row['id']}][style]'   value='".htmlspecialchars($row['style'])."' /></td>\n";
      $formFields .= "  <td align='center'><input type='text' size='4px'  name='records[{$row['id']}][orderno]' value='".htmlspecialchars($row['orderno'])."' /></td>\n";
      $formFields .= "  <td align='center'><input type='text' size='3px'  name='records[{$row['id']}][col]'     value='".htmlspecialchars($row['col'])."' /></td>\n";
      $formFields .= "  <td align='center'><input type='text' size='2px'  name='records[{$row['id']}][sizes]'   value='".htmlspecialchars($row['sizes'])."' /></td>\n";
      $formFields .= "  <td align='center'><input type='text' size='3px'  name='records[{$row['id']}][qty]'     value='".htmlspecialchars($row['qty'])."' /></td>\n";
      $formFields .= "  <td align='center'><input type='text' size='3px'  name='records[{$row['id']}][ctnqty]'  value='".htmlspecialchars($row['ctnqty'])."' /></td>\n";
      $formFields .= "  <td align='center'><input type='text' size='7px'  name='records[{$row['id']}][invoice]' value='".htmlspecialchars($row['invoice'])."' /></td>\n";
      $formFields .= "  <td align='center'><input type='text' size='7px'  name='records[{$row['id']}][kcgmt]'   value='".htmlspecialchars($row['kcgmt'])."' /></td>\n";
      $formFields .= "  <td align='center'><input type='text' size='7px'  name='records[{$row['id']}][buyer]'   value='".htmlspecialchars($row['buyer'])."' /></td>\n";
      $formFields .= "  <td align='center'><input type='text' size='7px'  name='records[{$row['id']}][factory]' value='".htmlspecialchars($row['factory'])."' /></td>\n";
      $formFields .= "</tr>\n";
  }
  echo mysqli_error($conn);
  //mysql_close();
?>
<html>
<head></head>
 
<body>
 
<table width="100%" border="0" cellspacing="1" cellpadding="0">
  <tr>
    <td>
    <form name="namestoupdate" method="post" action="updateproc.php">
    <table width="100%" border="0" cellspacing="1" cellpadding="1">
        <tr bgcolor="#FDEEF4">
            <th align="center"><input type="checkbox"  id="selectall" onClick="selectAll(this)" ></th>
            <th align="center">ID</th>
            <th align="center">ExcelID</th>
            <th align="center">STYLE</th>
            <th align="center">ORDER NO</th>
 
            <th align="center"><strong>COLOR</strong></th>
            <th align="center"><strong>SIZE</strong></th>
            <th align="center"><strong>QTY</strong></th>
            <th align="center"><strong>CTN-QTY</strong></th>
            <th align="center"><strong>INVOICE</strong></th>
            <th align="center">KCGMT</th>
            <th align="center">BUYER</th>
            <th align="center">FACTORY</th>
        </tr>
        <?php echo $formFields; ?>
        <tr><td colspan="12" align="center"><input type="submit" name="btn" value="Update"></td></tr>";
    </table>
    </form>
    </td>
  </tr>
</table>
 
</body>
</html>

<?php
error_reporting(0);
include_once('db_connection.php');
 
$output = '';
if(isset($_POST['btn']))
{
    if(!isset($_POST['update']))
    {
        $output .= "Please Select Check Box";
    }
    else
    {
        //Get array of records to be updated
        $updateChk = $_POST['update'];
        $records = $_POST['record'];
        $updateRecords = array_intersect_key($records, $updateChk);
        //echo $size = count($_POST['chk']);
        //echo "-";
        foreach($updateRecords as $id => $record)
        {
            $query = "UPDATE `freddyshipment`
                      SET excelid = '{$record['excelid']}',
                          style   = '{$record['style']}',
                          orderno = '{$record['orderno']}',
                          col     = '{$record['col']}',
                          sizes   = '{$record['sizes']}',
                          qty     = '{$record['qty']}',
                          ctnqty  = '{$record['ctnqty']}',
                          invoice = '{$record['invoice']}',
                          kcgmt   = '{$record['kcgmt']}',
                          buyer   = '{$record['buyer']}',
                          factory = '{$record['factory']}'
                      WHERE `id` = '{$id}'
                      LIMIT 1";
            $udateq=mysqli_query($conn, $query) or die ("Error in query: $query");
 
            $output .= "
            <tr>
                <td align='center'><p>".htmlspecialchars($id)."</p></td>
                <td align='center'>".htmlspecialchars($record['excelid'])."</td>
                <td align='center'>".htmlspecialchars($record['style'])."</td>
                <td align='center'>".htmlspecialchars($record['orderno'])."</td>
                <td align='center'>".htmlspecialchars($record['col'])."</td>
                <td align='center'>".htmlspecialchars($record['sizes'])."</td>
                <td align='center'>".htmlspecialchars($record['qty'])."</td>
                <td align='center'>".htmlspecialchars($record['ctnqty'])."</td>
                <td align='center'>".htmlspecialchars($record['invoice'])."</td>
                <td align='center'>".htmlspecialchars($record['kcgmt'])."</td>
                <td align='center'>".htmlspecialchars($record['buyer'])."</td>
                <td align='center'>".htmlspecialchars($record['factory'])."</td>
            </tr>";
        }
    }
}
mysqli_close($conn);
 
//Output wihtin the HTML
echo $output;
?>

There may be a few errors as I didn't test this. But, you should get the idea.

Edited October 8, 2017 by Psycho

mehidy · October 8, 2017

You are making it hard on yourself. Make the field names a multi-dimensional array using the ID of the records as the primary index and the field names as the secondary id. Also, keep your PHP and HTML separate - it helps make your code easier to manage. Just put the PHP at the top of the file for now. Use variables to hold the output.

Also, VERY IMPORTANT, you should never use data from a user directly in a query or as output on a web page directly. For database, you should always use prepared statements. I was too lazy to add it below. You really must do this. For output to the page you need to use htmlentities or htmlspecialchars

name='record[{$row['id']}][excelid]'
name='record[{$row['id']}][style]'
name='record[{$row['id']}][orderno]'

Then, just reference each 'record' based on the checked items.

<?php
 
  include 'db_connection.php';
 
  $sql = "SELECT *
          FROM freddyshipment
          WHERE style='$style'
            AND invoice='$invoice'
            AND kcgmt='$kcgmt'
            AND buyer='$buyer'
            AND factory='$factory'";
  $result = mysqli_query($conn,$sql) or die (mysqli_error($conn));
 
  $formFields = '';
  while ($row = mysqli_fetch_array($result))
  {
      $formFields .= "<tr>\n";
      $formFields .= "  <td align='center'><input type='checkbox' name='update[{$row['id']}]' value='{$row['id']}'/></td>\n";
      $formFields .= "  <td align='center'>{$Update['id']}</td>\n";
      $formFields .= "  <td align='center'><input type='text' size='4px'  name='records[{$row['id']}][excelid]' value='".htmlspecialchars($row['excelid'])."' /></td>\n";
      $formFields .= "  <td align='center'><input type='text' size='18px' name='records[{$row['id']}][style]'   value='".htmlspecialchars($row['style'])."' /></td>\n";
      $formFields .= "  <td align='center'><input type='text' size='4px'  name='records[{$row['id']}][orderno]' value='".htmlspecialchars($row['orderno'])."' /></td>\n";
      $formFields .= "  <td align='center'><input type='text' size='3px'  name='records[{$row['id']}][col]'     value='".htmlspecialchars($row['col'])."' /></td>\n";
      $formFields .= "  <td align='center'><input type='text' size='2px'  name='records[{$row['id']}][sizes]'   value='".htmlspecialchars($row['sizes'])."' /></td>\n";
      $formFields .= "  <td align='center'><input type='text' size='3px'  name='records[{$row['id']}][qty]'     value='".htmlspecialchars($row['qty'])."' /></td>\n";
      $formFields .= "  <td align='center'><input type='text' size='3px'  name='records[{$row['id']}][ctnqty]'  value='".htmlspecialchars($row['ctnqty'])."' /></td>\n";
      $formFields .= "  <td align='center'><input type='text' size='7px'  name='records[{$row['id']}][invoice]' value='".htmlspecialchars($row['invoice'])."' /></td>\n";
      $formFields .= "  <td align='center'><input type='text' size='7px'  name='records[{$row['id']}][kcgmt]'   value='".htmlspecialchars($row['kcgmt'])."' /></td>\n";
      $formFields .= "  <td align='center'><input type='text' size='7px'  name='records[{$row['id']}][buyer]'   value='".htmlspecialchars($row['buyer'])."' /></td>\n";
      $formFields .= "  <td align='center'><input type='text' size='7px'  name='records[{$row['id']}][factory]' value='".htmlspecialchars($row['factory'])."' /></td>\n";
      $formFields .= "</tr>\n";
  }
  echo mysqli_error($conn);
  //mysql_close();
?>
<html>
<head></head>
 
<body>
 
<table width="100%" border="0" cellspacing="1" cellpadding="0">
  <tr>
    <td>
    <form name="namestoupdate" method="post" action="updateproc.php">
    <table width="100%" border="0" cellspacing="1" cellpadding="1">
        <tr bgcolor="#FDEEF4">
            <th align="center"><input type="checkbox"  id="selectall" onClick="selectAll(this)" ></th>
            <th align="center">ID</th>
            <th align="center">ExcelID</th>
            <th align="center">STYLE</th>
            <th align="center">ORDER NO</th>
 
            <th align="center"><strong>COLOR</strong></th>
            <th align="center"><strong>SIZE</strong></th>
            <th align="center"><strong>QTY</strong></th>
            <th align="center"><strong>CTN-QTY</strong></th>
            <th align="center"><strong>INVOICE</strong></th>
            <th align="center">KCGMT</th>
            <th align="center">BUYER</th>
            <th align="center">FACTORY</th>
        </tr>
        <?php echo $formFields; ?>
        <tr><td colspan="12" align="center"><input type="submit" name="btn" value="Update"></td></tr>";
    </table>
    </form>
    </td>
  </tr>
</table>
 
</body>
</html>

<?php
error_reporting(0);
include_once('db_connection.php');
 
$output = '';
if(isset($_POST['btn']))
{
    if(!isset($_POST['update']))
    {
        $output .= "Please Select Check Box";
    }
    else
    {
        //Get array of records to be updated
        $updateChk = $_POST['update'];
        $records = $_POST['record'];
        $updateRecords = array_intersect_key($records, $updateChk);
        //echo $size = count($_POST['chk']);
        //echo "-";
        foreach($updateRecords as $id => $record)
        {
            $query = "UPDATE `freddyshipment`
                      SET excelid = '{$record['excelid']}',
                          style   = '{$record['style']}',
                          orderno = '{$record['orderno']}',
                          col     = '{$record['col']}',
                          sizes   = '{$record['sizes']}',
                          qty     = '{$record['qty']}',
                          ctnqty  = '{$record['ctnqty']}',
                          invoice = '{$record['invoice']}',
                          kcgmt   = '{$record['kcgmt']}',
                          buyer   = '{$record['buyer']}',
                          factory = '{$record['factory']}'
                      WHERE `id` = '{$id}'
                      LIMIT 1";
            $udateq=mysqli_query($conn, $query) or die ("Error in query: $query");
 
            $output .= "
            <tr>
                <td align='center'><p>".htmlspecialchars($id)."</p></td>
                <td align='center'>".htmlspecialchars($record['excelid'])."</td>
                <td align='center'>".htmlspecialchars($record['style'])."</td>
                <td align='center'>".htmlspecialchars($record['orderno'])."</td>
                <td align='center'>".htmlspecialchars($record['col'])."</td>
                <td align='center'>".htmlspecialchars($record['sizes'])."</td>
                <td align='center'>".htmlspecialchars($record['qty'])."</td>
                <td align='center'>".htmlspecialchars($record['ctnqty'])."</td>
                <td align='center'>".htmlspecialchars($record['invoice'])."</td>
                <td align='center'>".htmlspecialchars($record['kcgmt'])."</td>
                <td align='center'>".htmlspecialchars($record['buyer'])."</td>
                <td align='center'>".htmlspecialchars($record['factory'])."</td>
            </tr>";
        }
    }
}
mysqli_close($conn);
 
//Output wihtin the HTML
echo $output;
?>

There may be a few errors as I didn't test this. But, you should get the idea.

Thank you very very much for taking time do this. It just unbelievable that you have made the full correction in the code & reorganize the full script.

First part is working except selecting all the chekbox, its not working. I have added below script inside the head in html.

<script language="JavaScript">
	function selectAll(source) {
		checkboxes = document.getElementsByName('update[]');
		for(var i in checkboxes)
	    checkboxes[i].checked = source.checked;
	}
</script>

2nd part updateproc.php --- is not working, after submit the form i just get a blank page from updateproc.php

ginerjm · October 9, 2017

Is updateproc still not working for you? Why do you then have error reporting turned off? Now would be a good time to have it on.

See my signature.

Psycho · October 9, 2017

Thank you very very much for taking time do this. It just unbelievable that you have made the full correction in the code & reorganize the full script.

First part is working except selecting all the chekbox, its not working. I have added below script inside the head in html.
<script language="JavaScript">
	function selectAll(source) {
		checkboxes = document.getElementsByName('update[]');
		for(var i in checkboxes)
	    checkboxes[i].checked = source.checked;
	}
</script> 
2nd part updateproc.php --- is not working, after submit the form i just get a blank page from updateproc.php

Your javaScript is selecting the checkboxes by name. That was fine when all the checkboxes had the name "chk[]", but now they are all different since the ID is part of the name: "update[2]", "update[4]", "update[7]", etc. The code I provided uses the ID within those field names on the back-end on the line to get just the records being updated:

        $updateChk = $_POST['update'];
        $records = $_POST['record'];
        $updateRecords = array_intersect_key($records, $updateChk);

You have two choices:

1. Remove the ID from the field names that are generated, so the checkboxes have the same name. Then you will need to modify the above two lines to reference the field values to the array of records being updated

        $updateChk =  array_fill_keys($_POST['update'], 0);
        $records = $_POST['record'];
        $updateRecords = array_intersect_key($records, $updateChk);

2. Change the JavaScript to reference the fields by some other means. If you were to use JQuery, then you can give all the fields a class and reference them using that class.

Edited October 9, 2017 by Psycho

Sign In

Update with check box in PHP MYSQL

Recommended Posts

mehidy

Link to comment

Share on other sites

Psycho

Link to comment

Share on other sites

mehidy

Link to comment

Share on other sites

ginerjm

Link to comment

Share on other sites

Psycho

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information