Hello.

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....

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.

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:
450,451,452,453
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