将下拉数据从一个表插入另一个表

Question

I have a table which consists of just id & category, I then have a form with a drop down box which uses the category column to populate it. The next step is when the submit button is clicked it sends all the data into another table [product_table], All the data submits to the product table, but rather than the category name inserting it is inserting the id. I've tried multiple fixes to no avail, I'm thinking the easiest way would be to take outputted category '1','2','3'... and use a query to convert it to 'hat,'shirt',shoes'... using the category_table. I'm not 100% sure how to do this though,

index.php

<div id="addProduct">           
    <?PHP require_once("addProduct.php"); ?>                
            <div id="pageSubTitle" style="display:block">               
                <form method="post" action="">
                    <h2>Add a Product</h2>                      
                        <table>
                            <tr>
                                <td><label>Product Name: </label> <input type="text" id="productName" name="productName" required></td>
                                    <td><label id="categoryLabel"> Catagory: </label>
                                    <?php
                                        mysql_connect("localhost", "root","") or die(mysql_error());
                                        mysql_select_db("web_scripting") or die(mysql_error());

                                        $query = "SELECT id,category FROM catagory_table ORDER BY category ASC";
                                        $result = mysql_query($query) or die(mysql_error()."[".$query."]");
                                    ?>                                  
                                    <select type="text" id="category" name="category">
                                    <?php 
                                        while ($row = mysql_fetch_array($result))
                                        {
                                            echo "<option value='".$row['id']."'>'".$row['category']."'</option>";
                                        }
                                    ?>  
                                    </select><p id="refreshCatagoryOnNewProduct"> New catagory not appearing?<br /> <a href="index.php">Click Here</a></p></td>
                            </tr>                               
                            <tr>
                                <td><label>Stock: </label> <input type="number" id="stock" name="stock" required></td>
                                <td><label id="costLabel">Cost: </label> <input type="text" id="cost" name="cost" required></td>
                            </tr>                               
                            <tr>
                                <td><label>Description: </label> <textarea type="text" id="description" name="description" cols="40" rows="5" maxlength="250" placeholder="Enter Description Here&#10;(Max 250 Characters...)" required></textarea></td>
                                <td></td>                                   
                            </tr>                               
                            <tr>
                                <td><input id="productSubmit" type="submit" value="Add Product" onclick="submitProduct()"></td>
                            </tr>
                        </table>                            
                </form>                 
            </div>              
    </div>

addProduct.php

<?PHP

$db_host = "localhost";
$db_name = "web_scripting";
$db_root = "root";
$db_pass = "";

$odb = new PDO("mysql:host=$db_host;dbname=$db_name", $db_root, $db_pass);

if(isset($_POST['productName'])) {
    $productName = $_POST['productName'];
    $categoryName = $_POST['category'];
    $stock = $_POST['stock'];
    $cost = $_POST['cost'];
    $description = $_POST['description'];
    $q = "INSERT INTO Product_table(name, category, stock, cost, description) VALUES(:name, :category, :stock, :cost, :description);";
    $query = $odb->prepare($q);
    $results = $query->execute(array(
        ":name" => $productName,
        ":category" => $categoryName,
        ":stock" => $stock,
        ":cost" => $cost,
        ":description" => $description
    ));
}

?>

==> I know there's a few spelling mistakes in the code (usually category/catagory) but it's consistent throughout,

Any help/advice would be great appreciated

Answer 1

The select element is used to create a drop-down list but The option value="" tags inside the element define the available options in the list with values.

In your case, the mistake is here, as you are putting $row['id'] in value

echo "<option value='".$row['id']."'>'".$row['category']."'</option>";

So if you want the name instead of the id, have to change the option value

echo "<option value='".$row['category']."'>'".$row['category']."'</option>";