Updating multiple rows in MySQL with php

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
Eddies
Forum Newbie
Posts: 5
Joined: Mon Nov 23, 2009 6:30 am

Updating multiple rows in MySQL with php

Post 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.";
}
?>
User avatar
AbraCadaver
DevNet Master
Posts: 2572
Joined: Mon Feb 24, 2003 10:12 am
Location: The Republic of Texas
Contact:

Re: Updating multiple rows in MySQL with php

Post by AbraCadaver »

I don't see where you get $i:

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

Try this:

$id = $_POST["id"];

-Shawn
mysql_function(): WARNING: This extension is deprecated as of PHP 5.5.0, and will be removed in the future. Instead, the MySQLi or PDO_MySQLextension should be used. See also MySQL: choosing an API guide and related FAQ for more information.
Eddies
Forum Newbie
Posts: 5
Joined: Mon Nov 23, 2009 6:30 am

Re: Updating multiple rows in MySQL with php

Post 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
User avatar
AbraCadaver
DevNet Master
Posts: 2572
Joined: Mon Feb 24, 2003 10:12 am
Location: The Republic of Texas
Contact:

Re: Updating multiple rows in MySQL with php

Post 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());
mysql_function(): WARNING: This extension is deprecated as of PHP 5.5.0, and will be removed in the future. Instead, the MySQLi or PDO_MySQLextension should be used. See also MySQL: choosing an API guide and related FAQ for more information.
Eddies
Forum Newbie
Posts: 5
Joined: Mon Nov 23, 2009 6:30 am

Re: Updating multiple rows in MySQL with php

Post by Eddies »

Thanks mate,

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