Wrox Programmer Forums
Go Back   Wrox Programmer Forums > PHP/MySQL > PHP Databases
PHP Databases Using PHP in conjunction with databases. PHP questions not specific to databases should be directed to one of the other PHP forums.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the PHP Databases section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old January 7th, 2005, 06:56 PM
Authorized User
Join Date: Aug 2004
Posts: 16
Thanks: 0
Thanked 0 Times in 0 Posts
Default Better way to update a list?


I am setting up a Purchase Order form for my company. I would start the mysql data table with such information as PO Number, Supplier, etc etc. Then I would get to the list of 30 possible items, starting with A1, D1, C1, TC1 - in other words, Amount1, Description1, Cost1, and Item Total Cost1 all the way up to A30, D30, C30, TC30.

Whenever the user updates the list, the # after A, D, etc etc would be incremented by 1. Here is the code. $CR is the current item number (Current Row)

if ($_GET['Action'] == 'Update') {
    $PO = $_SESSION['Current_PO'];
    $Amount = $_POST['Amount'];
    $Description = $_POST['Description'];
    $Cost = $_POST['Cost'];
    $ICost = $_POST['ICost'];

Here is where it can get confusing. I've set PHP variables to represent the actual column name so the first line would represent A1, next line represent the next column D1, etc etc
    $A = 'A' . $CR;
    $D = 'D' . $CR;
    $C = 'C' . $CR;
    $TC = 'TC' . $CR;

The query itself I've actually tested it and does work properly - which is kinda surprising since I didn't know I could manipulate the query the way I did. This query would literally "SET A1 = '$Amount'" on the first add and then "SET A2 = '$Amount' on the next add, etc etc.
    $query = "Update purchaseorder SET '$A' = '$Amount', '$D' = '$Description', ";
    $query = $query . "'$C' = '$Cost', '$TC' = '$ICost' ";
    $query = $query . "where PONumber = '$PO'";
    $result = mysql_query($query);
// echo $query;
    if ($result) error_message(sql_error());
$_SESSION['CR'] ++;
header("location: PurchaseOrders.php?Action=Add");

This is my question then: Is there a better way to do this? As it stands it doesn't seem very efficient (to me). I've tried setting up an array of 30 rows and 4 columns to be posted to the "save to db" page, but it didn't seem to work. Instead it is updating one row at a time (up to 30 rows). Please bear with me and my horrible code, but, like I say, it is working....

Old January 8th, 2005, 01:25 AM
Authorized User
Join Date: Jan 2005
Posts: 82
Thanks: 0
Thanked 0 Times in 0 Posts

You should have a Order_ID in your mysql table, and set that field to auto_increment. That way you don't have to worry about chasing the number after A or c...
and by doing that, you can use mysql_insert_id to get that number and manipulate it however you wish.

there might be a better way, but that's how I'd do it.

Old January 10th, 2005, 01:21 PM
Authorized User
Join Date: Aug 2004
Posts: 16
Thanks: 0
Thanked 0 Times in 0 Posts

I would LOVE to use the auto_increment feature.

However ...

What happens if the PO was canceled? The PO # itself would have been generated already (since the first "item" of the list is saved onto the db). If the PO was canceled, then there would be the ominous gap in the numbering like so:
If 453 was canceled by the user, the list would become:
450,451,452,454 (if 454 was saved)
since this is using the auto increment feature. The gap itself is not good, since it HAS to be in order, aka no gaps in the PO numbering.

To bypass this, I used a seperate table to keep track of the current PO number. If the PO was initially saved (first item) and then canceled during the entry of the 2nd item, I can easily decrease the current PO number so the "canceled" PO number is reused, thus, no gap.

What I'm looking for is (perhaps) a better way of saving the list of data as per my original message instead of manipulating the column names like I'm doing. If not, it's not a big deal. All I was hoping for was to do this a bit more efficiently :D

Similar Threads
Thread Thread Starter Forum Replies Last Post
Dynamic List in UPDATE page neolynx Classic ASP Databases 1 November 21st, 2007 03:22 AM
C# - update list box after write dataset xm rrflore2 ASP.NET 2.0 Basics 2 July 9th, 2007 08:36 AM
update list/menu value dynamically anjila Classic ASP Basics 1 February 5th, 2004 05:41 AM
Update textbox when List menu is Selected rylemer Javascript 3 November 11th, 2003 10:53 AM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.