CODEWITHSUNDEEP
phpmysql
user3095221
user3095221

Reputation: 11

Save data displayed in a dropdown from one table to another

I'm a PHP-MySQL newbie and facing a problem when I try to display in a dropdown field value from another table and save it into current table. I found a complete CRUD tutorial on the net, but it has only simple create, update, display features using only input fields, with no select field, and other complicated stuff.

Currently I have 2 tables:

  1. Customers, with the following fields:

    customer_id, customer_name, customer_address, customer_country.

  2. Orders, with fields:

    order_id, customer_id, product_name, product_price, product_qty.

Having the following orders.php I would like to use a dropdown for customer_id to display customer_name from customers table and selecting a customer name to save it's customer_id in current table's (orders) customer_id field.

<?php
    // creates the new record form
    // since this form is used multiple times in this file, I have made it a function  that is easily reusable
    function renderForm($customerid, $productname, $productprice, $productqty, $error)
    {
?>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
    "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
   <meta charset="utf-8">
   <title>ORDERS</title>
</head>

<body>
    <form action="" method="post">
        <label for="customer_id">Customer ID:* </label>
        <select name="customer_id">
        <?php
            $sql = "SELECT * FROM customers ORDER BY customer_name ASC;";
            $res = mysql_query($sql);
            while($row= mysql_fetch_assoc($res)) {
                echo "<option value='" . $row['customer_id']
                        . "'>" . $row['customer_name'] . "</option>";
            }
        ?>
        </select>
    <label for="product_name">Product name:*</label>
    <input type="text" name="product_name" value="<?php echo $productname; ?>" /><br/>
    <label for="product_price">Price:* </label>
    <input type="text" name="product_price" value="<?php echo $productprice; ?>" /><br/>
    <label for="product_qty">Qty:* </label>
    <input type="text" name="product_qty" value="<?php echo $productqty; ?>" /><br/>
    <input type="submit" name="submit" class="btn-global" value="Save">
    </form>
</body>
</html>
<?php 
    }

    // connect to the database
    include('connect.php');

    // check if the form has been submitted. If it has, start to process the form and save it to the database
    if (isset($_POST['submit']))
    { 
        // get form data, making sure it is valid
        $id = mysql_real_escape_string(htmlspecialchars($_POST['customer_id']));
        $product = mysql_real_escape_string(htmlspecialchars($_POST['product_name']));
        $price = mysql_real_escape_string(htmlspecialchars($_POST['product_price']));
        $qty = mysql_real_escape_string(htmlspecialchars($_POST['product_qty']));

        // check to make sure both fields are entered
        if ($id == '' || $product == '' || $price == '' || $qty == '')
        {
            // generate error message
            $error = 'ERROR: Please fill in all required fields!';

           // if either field is blank, display the form again
           renderForm($customerid, $productname, $productprice, $productqty, $error);

        }
        else
        {
            // save the data to the database
            mysql_query("INSERT orders SET customer_id='$id', product_name='$product',  product_price='$price', product_qty='$qty'")
            or die(mysql_error()); 

            // once saved, redirect back to the view page
            header("Location: some.php"); 
         }
    }
    else    // if the form hasn't been submitted, display the form
    {
        renderForm('','','','','');
    }
?>

Upvotes: 1

Views: 1127

Answers (1)

Paul Lo
Paul Lo

Reputation: 6138

Your sql is wrong, INSERT should be like this:

INSERT INTO orders (customer_id, product_name, product_price, product_qty) VALUES ('$id', '$product', '$price','$qty')

When you update an existing record, you can use something like:

UPDATE orders SET product_name='$someValue', product_price='$someValue2' WHERE customer_id='$id'

Upvotes: 0

Related Questions