Php: retrieving primary key of one table and storing into another table while performing Insert query.

Question

I have this two tables 'fcategory' and 'fthreads'. fcategory fields: category_id, category_name. fthreads fields: thread_id, thread_title, category_name, category_id, user_id.

when i am creating a new thread in php, i want category_id to get fetched along with the category_name and insert it into fthreads table.

here is my php file: threads.php

<form action="threadsp.php" name="myform" method="post">


            <label for="field4"><span>Category</span>
                <?php
                $query = "select * from fcategory";
                $result = mysqli_query($conn, $query); 
                $resultsearch = mysqli_fetch_array($result);


                if(!$result){
                    die('could not get data:'.mysqli_error($conn));
                }
                echo '<select name="category" class="select-field">';

                    while ($row = mysqli_fetch_assoc($result)) {
                        echo '<option value="'.$row['category_name'].'">'.$row['category_name'].'</option>';
                    }

                echo "</select>";
                echo "</label>";

            ?>
            <label for="field1"><span>Thread Title <span class="required">*</span></span>
                <input type="text" class="input-field" name="title" value="" />
            </label>

            <label><span>&nbsp;</span>
                <input type="submit" value="Create"  />
            </label>
        </form>

second file: threadsp.php

 <?php

$catg = $_POST['category'];
$title = $_POST['title'];
$userid = $_SESSION['uid'];


$sql = "select category_id from fcategory where category_name = '$catg'";
$result2 = mysqli_query($conn, $sql);
$catgidresult = mysqli_fetch_array($result2);

$query = "insert into fthreads (category_name,thread_title,user_id,category_id) values('".$catg."','".$title."','".$userid."','".$catgidresult."')";
$result = mysqli_query($conn, $query);

if(!$result){ 
    echo "failed".mysqli_error($conn);
}else{
    header("Location: question.php");
    die();
}


?>

i am able to fetch category_name but value of category_id shows 0. any help will be appreciated. thank you

score 1 · Answer 1 · edited May 23 '17 at 12:31

Why to store category_name in fthreads table if you have category_id in that table ?? Just remove the category_name from your fcategory table . You need to normalize your tables. See this : So, just send category_id from your form as below :

<form action="threadsp.php" name="myform" method="post">


            <label for="field4"><span>Category</span>
                <?php
                $query = "select * from fcategory";
                $result = mysqli_query($conn, $query); 
                $resultsearch = mysqli_fetch_array($result);


                if(!$result){
                    die('could not get data:'.mysqli_error($conn));
                }
                echo '<select name="cat_id" class="select-field">';

                    while ($row = mysqli_fetch_assoc($result)) {
                        echo '<option value="'.$row['category_id'].'">'.$row['category_name'].'</option>';
                    }

                echo "</select>";
                echo "</label>";

            ?>
            <label for="field1"><span>Thread Title <span class="required">*</span></span>
                <input type="text" class="input-field" name="title" value="" />
            </label>

            <label><span>&nbsp;</span>
                <input type="submit" value="Create"  />
            </label>
        </form>

and in your threads.php file you can do this :

<?php

$cat_id = $_POST['cat_id'];
$title = $_POST['title'];
$userid = $_SESSION['uid'];




$query = "insert into fthreads (thread_title,user_id,cat_id) values('".$title."','".$userid."','".$cat_id."')";
$result = mysqli_query($conn, $query);

if(!$result){ 
    echo "failed".mysqli_error($conn);
}else{
    header("Location: question.php");
    die();
}


?>

Also, you need to sanitize your input, other wise you are vulnerable to Sql Injection . See here is the best method to sanitize your inputs . Happy Coding :) .

Budianto IP · Answer 2 · 2016-02-05T06:49:21.950

Try to change (inside threads.php):

while ($row = mysqli_fetch_assoc($result)) {
    echo '<option value="'.$row['category_name'].'">'.$row['category_name'].'</option>';
}

Into this :

while ($row = mysqli_fetch_assoc($result)) {
    echo '<option value="'.$row['category_id'].'">'.$row['category_name'].'</option>';
}

The remove (inside threadsp.php):

$sql = "select category_id from fcategory where category_name = '$catg'";
$result2 = mysqli_query($conn, $sql);
$catgidresult = mysqli_fetch_array($result2);

And change (inside threadsp.php):

$query = "insert into fthreads (category_name,thread_title,user_id,category_id) values('".$catg."','".$title."','".$userid."','".$catgidresult."')";

Into this :

$query = "insert into fthreads (category_name,thread_title,user_id,category_id) values('".$catg."','".$title."','".$userid."','".$catg."')";

Explaination :
You don't have to do second query to get the category_id, instead, put the category_id inside the option tag, under the value property. Basically the option tag will return the contents of value property, so put your category_id there

Thanks all u guys. all the answers helped me alot. i would love to come here again with another query. this is my first project in php. i tried @kamal's solution thanks alot bro. — Nikhil Palyekar, Feb 05 '16 at 12:45

score 0 · Accepted Answer · answered Feb 05 '16 at 07:05

In your threadsp.php file, you are getting array in $catgidresult variable, basically you need to pass category_id in $catgidresult, so your insert query in threadsp.php file should be as below:

$query = "insert into fthreads (category_name,thread_title,user_id,category_id) values('".$catg."','".$title."','".$userid."','".$catgidresult['category_id']."')";

Notice - $catgidresult['category_id']

Instead of generating SQL as you did, I would suggest to use bind_param.

Php: retrieving primary key of one table and storing into another table while performing Insert query.

3 Answers3