I got this error while inserting a foreign key constraint into the table. I am new to php I don't know how to proceed. here is my php code.
<?PHP
include_once('connection.php');
/**
* created by chandu on 24-03-2018 12:20 pm
*/
$con=mysqli_connect($server_name,$user_name,$password,$db);
if(!$con){
die("Error Connection");
}
if(isset($_POST["workOrderName"]) && isset($_POST["submitted_on"]) && isset($_POST["status"]) && isset($_POST["subject"]) && isset($_POST["notes"])) {
echo "**************************************";
$status = $_POST["status"];
$subject = $_POST["subject"];
$notes = $_POST["notes"];
$result = mysqli_query($con,"insert into ticket_raising(word_order_id,submitted_on,status,subject,notes) values('select word_order_id from workorder_category where workorder_name = 'workOrderName'',NOW(),'$status','$subject','$notes')");
$isInserted = mysqli_num_rows($result);
if ($isInserted>0) {
# code...
echo "Success";
}
else {
echo "unsuccess";
}
}
mysqli_close($con);
?>
The select is going to be inserted as a string, not executed as a SQL query with this execution (if you correct the quoting issue which currently is causing an invalid query). To use a insert select you should put all the values in the select. You also should parameterize the query. Passing values into a SQL query opens you to >a href="https://en.wikipedia.org/wiki/SQL_injection">SQL injections. So you query should be like this:
insert into ticket_raising(word_order_id,submitted_on,status,subject,notes)
select word_order_id, NOW(), ?, ?, ? from workorder_category where workorder_name = ?
Then bind in the values you had, $status, $subject, $notes, and workOrderName.
You can read more about prepared statements with mysqli here: http://php.net/manual/en/mysqli.quickstart.prepared-statements.php
A possible addition you could make to the table structure is making submitted_on's default value the current_timestamp, then you won't need the now().
I think the subquery is returning many records which is not applicable to be fit into that column. Try doing this.
$result = mysqli_query($con,"insert into ticket_raising(word_order_id,submitted_on,status,subject,notes) values('select word_order_id from workorder_category where workorder_name = 'workOrderName' limit 1',NOW(),'$status','$subject','$notes')");
Also i noticed you are doing concatenation in wrong way try using concatenation operator to prevent errors.