Mysql edit users orders they have placed

Hi i'm working through an ordering system, i've got the placing an order done and its going into the database fine, but i'm struggling to think how to let the user edit the order once its been inserted.

This is how i grab the order from the page and send it to a PHP insert script:

$('#submit').live('click',function(){ 

                    var postData = {};
                    $('#items tr').not(':first').each(function(index, value) {
                        var keyPrefix = 'data[' + index + ']';
                        postData[keyPrefix + '[supp_short_code]'] = $(this).closest('tr').find('.supp_short_code').text();
                        postData[keyPrefix + '[project_ref]'] = $(this).closest('tr').find('.project_ref').text();
                        postData[keyPrefix + '[om_part_no]'] = $(this).closest('tr').find('.om_part_no').text();
                        postData[keyPrefix + '[description]'] = $(this).closest('tr').find('.description').text();
                        postData[keyPrefix + '[quantity_input]'] = $(this).closest('tr').find('.quantity_input').val();
                        postData[keyPrefix + '[cost_of_items]'] = $(this).closest('tr').find('.cost_of_items').text();
                        postData[keyPrefix + '[cost_total_td]'] = $(this).closest('tr').find('.cost_total_td').text();
                    });

                $.ajax
                    ({
                    type: "POST",
                    url: "order.php",
                    dataType: "json",
                    data: postData,
                    cache: false,
                    success: function()
                        {
                            alert("Order Submitted");
                        }
                    });
            });

And this is the PHP insert:

if (isset($_POST['data']) && is_array($_POST['data'])) {
                foreach ($_POST['data'] as $row => $data) {
                    $result = mysql_query("INSERT INTO orders (id,order_id,project_ref,supp_short_code,om_part_no,description,quantity,cost_of_items,cost_total) VALUES('', '".$order_id."', '".$data['project_ref']."', '".$data['supp_short_code']."', '".$data['om_part_no']."', '".$data['description']."', '".$data['quantity_input']."', '".$data['cost_of_items']."', '".$data['cost_total_td']."') ") or die(mysql_error());
                }
            }

So i'm aware this isnt the cleanest way to do 开发者_开发知识库it, so thats why i'm struggling to find a clean way to let them edit the order. I know how to do "Update" queries but its the fact that i've used for each loops and arrays to insert the order? Anyone have any advice from the code above on what to present to the user?

Depending on the framework you're using (if one at all), just create a view for the order. You could either do this on another page or by loading a form via ajax. Either way, the form would look something like this:

<?php
    if(isset($_POST['submit']) && $_POST['submit'] == "Submit") {
        $database->update("orders",$_POST,$_POST['id']);
    }

    $order = $database->query(
        "select
            id,
            order_id,
            project_ref,
            supp_short_code,
            om_part_no,
            description,
            quantity,
            cost_of_items,
            cost_total
        from orders where id = ".$id);
?>

<form method="post" action="<?php echo basename($_SERVER['PHP_SELF']); ?>">
    <input type="text" name="id"                value="<?php echo $order['id']; ?>">
    <input type="text" name="order_id"          value="<?php echo $order['order_id']; ?>">
    <input type="text" name="project_ref"       value="<?php echo $order['project_ref']; ?>">
    <input type="text" name="supp_short_code"   value="<?php echo $order['supp_short_code']; ?>">
    <input type="text" name="om_part_no"        value="<?php echo $order['om_part_no']; ?>">
    <input type="text" name="description"       value="<?php echo $order['description']; ?>">
    <input type="text" name="quantity"          value="<?php echo $order['quantity']; ?>">
    <input type="text" name="cost_of_items"     value="<?php echo $order['cost_of_items']; ?>">
    <input type="text" name="cost_total"    value="<?php echo $order['cost_total']; ?>">
    <input type="submit" name="submit" value="Submit">
</form>

The code for the $database object is here:

http://www.jtgraphic.net/code/database-object/

I use a third party code-generation project that creates a data grid that is used for inserting/updating records. What they do is run the update query for each item in the data grid everytime there is a update. This does result in some unnecessary updates occuring.

I think that a cleaner method would be to check the difference between the old data set and the new data set and then run the update only for those items that are changed.

I should write something like "this is evil, don't do that" and stuff, but I'll only suggest [strongly!] reading on SQL injection and [a bit less strength here] frameworks discussed here and get to your case.

If you set correct "unique" and "key" attributes in database it might be enough to change INSERT to REPLACE if you already don't care about safety. (So it's ok if it's an academic homework for tommorow :P)

Putting my OO hat on, your best bet here is to treat the order as an object.

Write a class that handles everything you need to do with it, such as creating new, sanitising, validating, saving, retrieving, displaying it to the screen to edit, and deleting. There'll be other higher-level functions such as printing invoices, generating packing slips, etc, as well as these more lower-level methods.

More work to begin with, but infinitely more maintainable, extensible and reuseable.

Not all of this need be done by hand: have a look at the Active Record design pattern and tools like PHP-AR that will do most of the basic getter and setter methods for you.

If this sounds like double-dutch, have a browse for one of the many OO PHP tutorials online & have a look at the wiki entry for 'active record design pattern'.

This is more of a suggestion for an approach than a direct answer to the order editing issue, I realise.

G