Updating multiple rows in MySQL with php
Posted: Mon Nov 23, 2009 6:34 am
Greetings everyone,
currently, I'm working on a simple Point of Sale system for my dad's company and I am stuck at one of the element.
What I expect from this element is for user to click on "Inventories" (inventory.php). At this page, it will call forth all the data from the "items" table from the database.
Instead of displaying in normal text, it will be displayed in text input where users can edit the fields. When users are done editing, there will be a "update" button beside each line item where users click it to update the edited data into the database.
As of now, when I click update, the page will go to "updateinventory.php" but it just will not edit the data. The strange thing is, if i try to edit the last line item of the table, it will update as intended. Just not the rest.
Maybe by looking at my codes, it will help you better understand. Thanks for the help.
inventory.php
<?php
$sql = 'SELECT * FROM items ORDER BY id';
$result = $db->query($sql);
while ($row = $result->fetch()) {
$output[] = '<form action="updateinventory.php" method=post>';
$output[] = '<input type=hidden name="id" value="'.$row['id'].'">';
$output[] = '<table width="920" border="0" align="center" cellpadding="0" cellspacing="0">';
$output[] = ' <tr>';
$output[] = ' <td width="920"><table width="920" border="0" cellspacing="0" cellpadding="0">';
if($trbgcolor != "#FFFFFF"){ $trbgcolor = "#FFFFFF"; }else{ $trbgcolor = "#7EBF41"; }
$output[] = ' <tr bgcolor="'.$trbgcolor.'">';
$output[] = ' <td width="30%" class="text"><input type=text size="40" name="description" value="'.$row['description'].'"></td>';
$output[] = ' <td width="15%" class="tex"><div align="center"><input type=text size="10" name="category" value="'.$row['category'].'"></td>';
$output[] = ' <td width="10%" class="text"><div align="center"><input type=text size="5" name="unit_quantity" value="'.$row['unit_quantity'].'"></td>';
$output[] = ' <td width="10%" class="text"><div align="center"><input type=text size="5" name="uom" value="'.$row['uom'].'"></td>';
$output[] = ' <td width="10%" class="text"><div align="center"><input type=text size="5" name="cost_price" value="'.number_format($row['cost_price'],2).'"></td>';
$output[] = ' <td width="10%" class="text"><div align="center"><input type=text size="5" name="van_price" value="'.number_format($row['van_price'],2).'"></td>';
$output[] = ' <td width="10%" class="text"><div align="center"><input type=text size="5" name="wholesaler_price" value="'.number_format($row['wholesaler_price'],2).'"></td>';
$output[] = ' <td width="5%" class="textheading"><div align="center"><input type="submit" name="update" value="Update"></div></td>';
$output[] = ' </tr>';
$output[] = ' </table></td>';
$output[] = ' </tr>';
$output[] = '</table>';
}
echo join('',$output);
?>
updateinventory.php
<?php
include ("navibar.php");
// Include MySQL class
require_once('inc/mysql.class.php');
// Include database connection
require_once('inc/global.inc.php');
//connect to mysql
//change user and password to your mySQL name and password
mysql_connect("$host","$user","$pass");
//select which database you want to edit
mysql_select_db("$name") or die("Unable to select database");
// Check if button name "Submit" is active, do this
// Check if button name "Submit" is active, do this
if ($_POST["update"])
{
$id = $_POST["id"][$i];
$description = $_POST['description'];
$category = $_POST['category'];
$unit_quantity = $_POST['unit_quantity"'];
$uom = $_POST['uom'];
$cost_price = $_POST['cost_price'];
$van_price = $_POST['van_price'];
$wholesaler_price = $_POST['wholesaler_price'];
$sql = "UPDATE items SET description = '$description',category = '$category', unit_quantity = '$unit_quantity', uom = '$uom', cost_price = '$cost_price', van_price = '$van_price', wholesaler_price = '$wholesaler_price' WHERE id = '$id'";
$result = mysql_query($sql);
echo "Thank you! Information updated.";
}
?>
currently, I'm working on a simple Point of Sale system for my dad's company and I am stuck at one of the element.
What I expect from this element is for user to click on "Inventories" (inventory.php). At this page, it will call forth all the data from the "items" table from the database.
Instead of displaying in normal text, it will be displayed in text input where users can edit the fields. When users are done editing, there will be a "update" button beside each line item where users click it to update the edited data into the database.
As of now, when I click update, the page will go to "updateinventory.php" but it just will not edit the data. The strange thing is, if i try to edit the last line item of the table, it will update as intended. Just not the rest.
Maybe by looking at my codes, it will help you better understand. Thanks for the help.
inventory.php
<?php
$sql = 'SELECT * FROM items ORDER BY id';
$result = $db->query($sql);
while ($row = $result->fetch()) {
$output[] = '<form action="updateinventory.php" method=post>';
$output[] = '<input type=hidden name="id" value="'.$row['id'].'">';
$output[] = '<table width="920" border="0" align="center" cellpadding="0" cellspacing="0">';
$output[] = ' <tr>';
$output[] = ' <td width="920"><table width="920" border="0" cellspacing="0" cellpadding="0">';
if($trbgcolor != "#FFFFFF"){ $trbgcolor = "#FFFFFF"; }else{ $trbgcolor = "#7EBF41"; }
$output[] = ' <tr bgcolor="'.$trbgcolor.'">';
$output[] = ' <td width="30%" class="text"><input type=text size="40" name="description" value="'.$row['description'].'"></td>';
$output[] = ' <td width="15%" class="tex"><div align="center"><input type=text size="10" name="category" value="'.$row['category'].'"></td>';
$output[] = ' <td width="10%" class="text"><div align="center"><input type=text size="5" name="unit_quantity" value="'.$row['unit_quantity'].'"></td>';
$output[] = ' <td width="10%" class="text"><div align="center"><input type=text size="5" name="uom" value="'.$row['uom'].'"></td>';
$output[] = ' <td width="10%" class="text"><div align="center"><input type=text size="5" name="cost_price" value="'.number_format($row['cost_price'],2).'"></td>';
$output[] = ' <td width="10%" class="text"><div align="center"><input type=text size="5" name="van_price" value="'.number_format($row['van_price'],2).'"></td>';
$output[] = ' <td width="10%" class="text"><div align="center"><input type=text size="5" name="wholesaler_price" value="'.number_format($row['wholesaler_price'],2).'"></td>';
$output[] = ' <td width="5%" class="textheading"><div align="center"><input type="submit" name="update" value="Update"></div></td>';
$output[] = ' </tr>';
$output[] = ' </table></td>';
$output[] = ' </tr>';
$output[] = '</table>';
}
echo join('',$output);
?>
updateinventory.php
<?php
include ("navibar.php");
// Include MySQL class
require_once('inc/mysql.class.php');
// Include database connection
require_once('inc/global.inc.php');
//connect to mysql
//change user and password to your mySQL name and password
mysql_connect("$host","$user","$pass");
//select which database you want to edit
mysql_select_db("$name") or die("Unable to select database");
// Check if button name "Submit" is active, do this
// Check if button name "Submit" is active, do this
if ($_POST["update"])
{
$id = $_POST["id"][$i];
$description = $_POST['description'];
$category = $_POST['category'];
$unit_quantity = $_POST['unit_quantity"'];
$uom = $_POST['uom'];
$cost_price = $_POST['cost_price'];
$van_price = $_POST['van_price'];
$wholesaler_price = $_POST['wholesaler_price'];
$sql = "UPDATE items SET description = '$description',category = '$category', unit_quantity = '$unit_quantity', uom = '$uom', cost_price = '$cost_price', van_price = '$van_price', wholesaler_price = '$wholesaler_price' WHERE id = '$id'";
$result = mysql_query($sql);
echo "Thank you! Information updated.";
}
?>