Jump to content


Photo

Update with check box in PHP MYSQL

php mysql

  • Please log in to reply
4 replies to this topic

#1 mehidy

mehidy
  • Members
  • PipPip
  • Member
  • 28 posts

Posted 07 October 2017 - 07:18 PM

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);
?>

Attached File  update.JPG   136.64KB   0 downloads



#2 Psycho

Psycho
  • Moderators
  • Move along, nothing to see here
  • 11,936 posts
  • LocationCanada

Posted 08 October 2017 - 05:43 AM

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 by Psycho, 08 October 2017 - 05:44 AM.

The quality of the responses received is directly proportional to the quality of the question asked.

I do not always test the code I provide, so there may be some syntax errors. In 99% of all cases I found the solution to your problem here: http://www.php.net

#3 mehidy

mehidy
  • Members
  • PipPip
  • Member
  • 28 posts

Posted 08 October 2017 - 08:21 AM

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



#4 ginerjm

ginerjm
  • Members
  • PipPipPip
  • Handball player
  • 4,194 posts
  • LocationVoorheesville NY

Posted 09 October 2017 - 02:24 PM

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.


JG
PS - If you're posting here you should be using:
        error_reporting(E_ALL);
        ini_set('display_errors', '1');

at the top of ALL php code while you develop it!

#5 Psycho

Psycho
  • Moderators
  • Move along, nothing to see here
  • 11,936 posts
  • LocationCanada

Posted 09 October 2017 - 08:26 PM

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 by Psycho, 09 October 2017 - 08:32 PM.

The quality of the responses received is directly proportional to the quality of the question asked.

I do not always test the code I provide, so there may be some syntax errors. In 99% of all cases I found the solution to your problem here: http://www.php.net




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users