i want to insert into a table, a data gotten from another table in the same database in php

Question

I create a relation for two tables, one contain items and the other containing the categories of the items. I am able to populate the option tag with the values from the categories table. Now i want to post the value selected from the list of options to the category_id(which i have set to be int just like the id in the categories table) in the items table but i'm getting the category_id blank while other information are added. How can i fix this? The code below.

<?
    session_start();
    $_SESSION['message'] = "";

    $mysqli = new mysqli('localhost', 'root', '', 'auction');
    if($_SERVER['REQUEST_METHOD'] == 'POST') {
        $item_name = $mysqli->real_escape_string($_POST['item_name']);
        $category_id = $mysqli->real_escape_string($_POST['cat']);
        $item_description = $mysqli->real_escape_string($_POST['item_description']);
        $item_image_path = $mysqli->real_escape_string('Images/item_img/' . $_FILES['item_image']['name']);

        //make sure file is of image type
        if(preg_match("!image!", $_FILES['item_image']['type'])) {
            if(copy($_FILES['item_image']['tmp_name'], $item_image_path)) {
                $_SESSION['item_name'] = $item_name;
                $_SESSION['cat'] = $category_id;
                $_SESSION['item_description'] = $item_description;
                $_SESSION['item_image'] = $item_image_path;
                //inserting into the database
                $sql = "INSERT INTO items (item_name,category_id, item_image, item_description)VALUES('$item_name', '$category_id', '$item_image_path','$item_description')";
                if($mysqli->query($sql) === true) {
                    $_SESSION['message'] = "Item Upload Successful!";
                } else {
                    $_SESSION['message'] = "file upload failed";
                }
            } else {
                $_SESSION['message'] = "file copying failed";
            }
        } else {
            $_SESSION['message'] = "please upload gif, jpg, png";
        }

    }

    $result = $mysqli->query("SELECT * FROM items ORDER BY rand() LIMIT 10") or die($mysqli->error);

?>

<html>

    <head>
        <title>Upload item</title>
        <link rel="StyleSheet" href="Bootstrap/css/bootstrap.main.css">
        <link rel="StyleSheet" href="Bootstrap/css/bootstrap.min.css">
        <link rel="StyleSheet" href="style.css">
        <!--for countdown timer-->
        <script type="text/javascript">
            setInterval(function() {
                var xmlhttp = new XMLHttpRequest();
                xmlhttp.open("GET", "response.php", false);
                xmlhttp.send(null);
                document.getElementById("timer").innerHTML = xmlhttp.responseText;

            }, 1000);
        </script>
    </head>

    <body>
        <div>
            <!--to display records from database-->
            <div class="row col-sm-12 c-head mar-pad">
                <?php
                $A = 0;
                while($auction = $result->fetch_assoc()):
                    ?>
                    <div class="grid ">
                        <h4><?= $auction['item_name'] ?></h4>
                        <img src='<?= $auction[' item_image '] ?>' class='img-responsive'>
                        <span id="timer" class="timer"></span>
                        <button class="c-button" name='bid'>Bid Now!</button>
                    </div>
                    <?php
                    if($A % 4 == 0)
                        echo "<br/>";
                    $A++;
                endwhile;
                ?>
            </div>

            <!--for file upload form-->
            <form class="form-horizontal" role="form" action="auction_upload.php" method="POST" enctype="multipart/form-data">
                <h1><?=$_SESSION['message']?></h1>
                <div class=" form-group">
                    <label class="control-label col-sm-2">Item Name:</label>
                    <div class="col-sm-8">
                        <INPUT type="text" class="form-control" name="item_name" required/>
                    </div>
                </div>
                <div class="form-group">
                    <label class="control-label col-sm-2">Category:</label>
                    <div class="col-sm-8 ">

                        <select class='form-control'>
                            <?php
                                $mysqli = new mysqli('localhost','root','','auction');
                                $result1 = $mysqli->query("SELECT * FROM `categories`");

                                while ($row = mysqli_fetch_array($result1)):;?>
                                    <option name="cat" value="<?=$row[0];?>">
                                        <?=$row[1];?>
                                    </option>

                                <?php endwhile;?>
                        </select>
                    </div>
                </div>
                <div class="form-group">
                    <label class="control-label col-sm-2">Item Image:</label>
                    <div class="col-sm-8">
                        <INPUT type="file" class="form-control" name="item_image" accept="image/*" required/>
                    </div>
                </div>
                <div class="form-group">
                    <label class="control-label col-sm-2">Item Description:</label>
                    <div class="col-sm-8">
                        <textarea class="form-control" name="item_description" required>
                        </textarea>
                    </div>
                </div>

                <div class="form-group">
                    <div class="col-sm-offset-2 col-sm-8">
                        <button type="submit" class="btn btn-default" name="upload">Upload</button>
                    </div>
                </div>
            </form>
        </div>
    </body>
</html>

[Little Bobby](http://bobby-tables.com/) says **[your script is at risk for SQL Injection Attacks](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php)**. Learn about [Prepared Statements](http://en.wikipedia.org/wiki/Prepared_statement) for [MySQLi](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php). Even **[escaping the string](http://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string)** is not safe! — GrumpyCrouton, Aug 07 '17 at 15:26
Some sensible code indentation would be a good idea. It helps us read the code and more importantly it will help **you debug your code**. [Take a quick look at a coding standard](http://www.php-fig.org/psr/psr-2/) for your own benefit. You may be asked to amend this code in a few weeks/months and you will thank me in the end. — GrumpyCrouton, Aug 07 '17 at 15:26

score 0 · Accepted Answer · answered Aug 07 '17 at 15:27

0

Your select element has no name:

<select class ='form-control' >

So the value isn't being sent to the server at all. In order to include the value of that element in the form post, the form element needs a name (which is the key in the key/value pair).

Since you're looking in $_POST['cat'] then the name would be "cat":

<select class ='form-control' name="cat" >

answered Aug 07 '17 at 15:27

David

208,112
36
198
279

Thanks. It worked. I actually thought the name is supposed to be in the option tag. It's showing only the id of the category. Is there any way i can make it show the category name instead? – epospiky Aug 07 '17 at 15:33
@epospiky: Presumably then that's the value you're setting in the form element: `value="=$row[0];?>"` If you want the value to be something else, that's where you'd change it. Perhaps to: `value="=$row[1];?>"` Though it's not clear why you'd want to do that. If the column name is `category_id` then wouldn't you want it to be, well, the category ID? Isn't that the foreign key to the category table? – David Aug 07 '17 at 15:35
Yeah the category_id is the foreign key. But i'm wondering if i can make the id display the category name in the item table instead of the id which i would have to click to be redirected to the category table before figuring out the name of the category. And if i change the value to `value="=$row[1];?>` nothing will be inserted in the category_id – epospiky Aug 07 '17 at 15:51
@epospiky: That sounds like a feature request you'd send to whatever vendor built whatever tool you're using to view your tables in your database. The value is the ID, so it shows the ID. I don't know of any tools which would change that behavior by default. – David Aug 07 '17 at 15:53
Alright. Thanks for your time. I so much appreciate – epospiky Aug 07 '17 at 16:09

i want to insert into a table, a data gotten from another table in the same database in php

1 Answers1