PHP variables entered as 0 in MySQL database

Question

I'm relatively new to php and mysql. What I'm trying to do in this section of code is update 2 columns of information in a database based on the inputs of 2 text boxes. Whenever I try and update the values in the database they update to 0. I placed echo statements after I declared the $ variables and the values of both variables was the same as what I had typed into the boxes. But when I run the sql_query the new values in the database are 0 rather than the values of the 2 $ variables. Any help would be much appreciated! Thanks in advance!

<?php

    $result = mysql_query("SELECT * from place_order ORDER BY item_name;");

    echo "<form action='' method='POST'>Select an item:<select name='selection'><option>Select...</option>";

    while ($row = mysql_fetch_assoc($result)) 
        {
            $item_name = $row["item_name"];

            echo "<option>$item_name</option>";

        }

    echo "</select>
    <input type='submit' value='Select Item' style='float:right;'/>";

    $selection = $_POST['selection'];

    echo "<br><br>Type the updated information into the text fields<br><br>";
    echo "
    <table width='300'>
    <tr>
        <td align='left'>Item Cost(&#8364;): </td>
        <td align='left'><input type='text' name='cost'></td>
    </tr>
    <tr>
        <td align='left'>Item Quantity: </td>
        <td align='left'><input type='text' name='quantity'></td>
    </tr>
    <tr>
        <td align='left'></td>
        <td align='left'><input type='submit' name='button' value='Submit'></td>
    </tr>   
    </table>
    ";

    $cost = $_POST['cost'];
    $quantity = $_POST['quantity'];
    $selection = $_POST['selection'];

    $sql = "UPDATE place_order SET item_cost='$cost', quantity='$quantity' WHERE item_name='$selection' ORDER BY item_name;;";

    $query_update = mysql_query($sql);

    if($query_update)
    {
        echo "Table updated! Click View Stock in the menu to view the updated table";
    }

    echo"</form>";
?>

Answer 1

There are two forms in this script:

1. one with the select box
2. another with the input boxes

each of these forms post individually.

Let's have some kind of state transition table:

1. forms are showing first time initially, all values are either empty or default.
2. you select an item name and submit the first form.
3. option list is posted and $_POST['select'] has a value which then is properly populated in the second form
4. the script runs til the end and immediately inserts an empty row to your database with the selected item name
5. you make your inputs in the second form and submit it
6. this has two input variables: cost and quantity which get posted
7. due to the fact that the option list is in the other form, it will not be posted and $select is empty at that state
8. the update statement will run but with no result since there is no empty item_name in the db - useless update.

simple solution: make just one form out of that and you're done (except error handling which is missing entirely ...)

second solution: if only $_POST['select'] is set, put the value of $select in a hidden field and exit the script after echo-ing the second form.