Page 1 of 1

Updating multiple rows in MySQL with php

Posted: Mon Nov 23, 2009 6:34 am
by Eddies
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.";
}
?>

Re: Updating multiple rows in MySQL with php

Posted: Mon Nov 23, 2009 8:35 am
by AbraCadaver
I don't see where you get $i:

$id = $_POST["id"][$i];

Try this:

$id = $_POST["id"];

-Shawn

Re: Updating multiple rows in MySQL with php

Posted: Mon Nov 23, 2009 9:23 am
by Eddies
Hi Shawn,

I appreciate your quick response. Anyway, the [$i] was left accidentally from the trial and errors previously. As instructed, I removed it but outcome is still the same.

It shows that my data is updated but when refreshed and looked into my database, it did not get updated. The strange thing is that if i update the last line item, it will update as intended.

For eg. my last item id is "5" and when i edit the fields belonging to id = "5", it will update correctly.

Am I experiencing the same problem as him?

http://codingforums.com/showthread.php?t=169965

Re: Updating multiple rows in MySQL with php

Posted: Mon Nov 23, 2009 12:32 pm
by AbraCadaver
I notice that you don't have a closing </form> tag.

Also, try:

Code: Select all

$result = mysql_query($sql) or die(mysql_error());

Re: Updating multiple rows in MySQL with php

Posted: Mon Nov 23, 2009 7:40 pm
by Eddies
Thanks mate,

A simple /form got the whole thing to work! Thanks alot people.