CODEWITHSUNDEEP
phpmysql
Yateesh Ravi
Yateesh Ravi

Reputation: 57

How to add category id to another table on selection of category?

I have two tables ( post , category )

CREATE TABLE post    
(pid INT(10) NOT NULL AUTO_INCREMENT,    
post_title VARCHAR(255),   
post_result VARCHAR(255),  
post_content VARCHAR(500), 
cid INT(10), 
PRIMARY KEY (pid),
FOREIGN KEY (cid) REFERENCES category(cid));   

CREATE TABLE category (  
cid INT(10) NOT NULL AUTO_INCREMENT,  
cname VARCHAR(50),  
PRIMARY KEY (cid));

This is my insert statement: 

$id = isset($_POST['pid'])?$_POST['pid']:'';
$title=$_POST['post_title'];
$result=$_POST['post_result'];
$content=$_POST['post_content'];
$catid=$_POST['cid'];
$cat=$_POST['cname'];
$insertquery="INSERT INTO review (pid,post_title,post_result,post_content)
VALUES('','$title','$result','$content')";

Problem what im facing is, in my post table, the values of category id ( cid ) are NULL for every record.
How do make it to store the value of selected category.

This is my form: 

<form id="create" action="insert.php" method="post">
<input type="text" name="post_title" placeholder="Title" /><br> 
<input type="text" name="post_result" placeholder="Final comment" /><br>
<textarea name="post_content" placeholder="Review" ></textarea><br>

    Category:
    <select name=cname>
    <option value="select"></option>
    <option value="1">Option 1</option>
    <option value="2">Option 2</option>
    <option value="3">Option 3</option>
    <option value="4">Option 4</option>
    <option value="5">Option 5</option>
        </select><br><br>

<input class="submit" name="submit" type="submit" value="Submit"/>

</form>

Do help in getting the output .

Upvotes: 0

Views: 1254

Answers (3)

peterm
peterm

Reputation: 92795

First of all I hope that you populate your category select from db and value attribute contains category id. That being said it's better to change name attribute to cid (because that what you expect in POST):

<select name="cid">

Then var_dump($_POST) will look like this:

array(5) {
  ["post_title"]=>
  string(2) "aa"
  ["post_result"]=>
  string(2) "bb"
  ["post_content"]=>
  string(2) "cc"
  ["cid"]=>
  string(1) "2"
  ["submit"]=>
  string(6) "Submit"
}

Secondly you can ditch following php lines:

$id = isset($_POST['pid'])?$_POST['pid']:'';
$cat=$_POST['cname'];

because you don't send those values with the form and don't use them.

Thirdly you need to correct your insert statement

$insertquery="INSERT INTO post (post_title,post_result,post_content,cid)
              VALUES('$title','$result','$content','$catid')";

Other considerations:

  • You better ensure on the client side that category name has been chosen, otherwise you'll get error trying to insert the record in post.
  • As a1ex07 mentioned you need to check and sanitize all user input before you put in db
  • Switch to prepared statements

Upvotes: 1

tmuguet
tmuguet

Reputation: 1165

In your INSERT query, you forgot to add the value of cname:

$insertquery="INSERT INTO review (pid,post_title,post_result,post_content,cid)
VALUES('','$title','$result','$content',$cname)";

Upvotes: 0

a1ex07
a1ex07

Reputation: 37364

Assuming category name is unique, you can do

$insertquery="INSERT INTO review (pid,post_title,post_result,post_content,cid)
VALUES('','$title','$result','$content', 
 (SELECT cid FROM category WHERE cname ='$cname'))"

You also need to escape all variables that come from user (preferable way is to use pdo or mysqli).

Upvotes: 0

Related Questions