提交一个或另一个查询

Question

I'm continuing to hack away at my newbie php/mySQL 'Invoicer' app.

I now have a form page in which I want to run one of two queries - either an INSERT or an UPDATE, depending on whether an ID is present. When present, the ID is used to retrieve the record and pre-populate the form accordingly, which I have working. My problem now is that my conditional bits are obviously not right because in either case when submitting the form the INSERT query is run, can't get the UPDATE to run, and I've exhausted my understanding (and guess-ology).

I'd love to know why this ain't working, even if it's not the best approach, and I'm definitely open to suggestions to move the queries to a process.php, etc. I'm also wondering if I should use 'if(isset($_GET['ID'])' to simply include one block or the other.

Many thanks in advance for any help or suggestions. (p.s. my intention is to overhaul for best practices/security once I've got the broad strokes wired up)

cheers, s

<?php

    // CASE I: 'EDIT RECORD':
    //  If there's an ID ...
    if  (isset($_GET['ID']) && is_numeric($_GET['ID'])) {
        $id = $_GET['ID'];
        echo "<p class=\"status\"><strong>ID IS SET ... ergo we're editing/UPDATING an existing record</strong></p>";

        //  ... retrieve the record ....
        $query = sprintf("SELECT * FROM Invoices WHERE ID = %s", $id);
        $result = mysql_query($query) or die(mysql_error());
        $row = mysql_fetch_array($result);

        //  ... assign variables to pre-populate the form
        $id         = $row['ID'];
        $invNumber  = $row['invNumber'];
        $invDate        = $row['invDate'];
        // [ snip: more variables > field data ]

        // on submit: get the form values ...
        // no worky: if (isset($_GET['ID']) && isset($_POST['submit'])) {
        if  (isset($_POST['submit']))   {
            $invNumber  = $_POST['invoice-number'];
            $invDate        = $_POST['invoice-date'];
            $projNumber = $_POST['project-number'];
            // [ snip: more variables > field data ]


            // ... and UPDATE the db:
            $qUpdate = "UPDATE Invoices SET invNumber='$invNumber', invDate='$invDate', projNumber='$projNumber', client='$client', task='$task', issueDate='$issueDate', subTotal='$subTotal', tax='$tax', invTotal='$invTotal', datePaid1='$datePaid1', datePaid2='$datePaid2', comments='$comments' WHERE ID='3'";
            $result = mysql_query($qUpdate) or die(mysql_error());
            if($result) {
                echo "<p class=\"status\"><strong>SUCCESS: RECORD UPDATED!</strong></p>";
            }
            else die("DAMMIT JIM I'M A DOCTOR NOT A DB ADMIN!" . mysql_error());

        } // CLOSE '(isset($_POST['submit']))
    } // END CASE I: ID present


    // CASE II: 'NEW RECORD'; query = INSERT
    elseif (empty($_GET['ID'])) {
        echo "<p class=\"status\"><strong>No ID ... ergo we're INSERTING a new record:</strong></p>";

        // on submit: get the form values ...
        if (isset($_POST['submit']))    {
            $invNumber  = $_POST['invoice-number'];
            $invDate        = $_POST['invoice-date'];
            $projNumber = $_POST['project-number'];
            // [ snip: more variables > field data ]

            $qInsert =  "INSERT INTO Invoices (invNumber,invDate,projNumber,client,task,issueDate,subTotal,tax,invTotal,datePaid1,datePaid2,comments)
                VALUES('$invNumber','$invDate','$projNumber','$client','$task','$issueDate','$subTotal','$tax','$invTotal','$datePaid1','$datePaid2','$comments')";
            $result = mysql_query($qInsert) or die(mysql_error());
            if($result) {
                echo "<p class=\"status\"><strong>SUCCESS: NEW RECORD INSERTED!</strong></p>";
            }
            else die("DAMMIT JIM I'M A DOCTOR NOT A DB ADMIN!" . mysql_error());
        } // CLOSE '(isset($_POST['submit']))
    } // END CASE II: No ID present

?>

and:

<form id="invoiceData" method="post" action="/html/form.php">       

Answer 1

When you submit the form, you need to include the ID again, otherwise it is silently dropped off since you are posting to the hard-coded value /html/form.php (with ID removed). This will cause the empty($_GET['ID']) part to match and run, causing the INSERT. You can simply include the ID value back into the action of every form post like this:

<form
  id="invoiceData"
  method="post"
  action="/html/form.php?ID=<?php echo $_GET['ID']; ?>"
>

This should work in both the cases of the UPDATE and the INSERT, because if there was no ID to begin with, this will render as /html/form.php?ID=, which will match the case of ID being empty, I believe. You may want to test this logic out for sure.

Hope this helps!