将数据插入具有外键字段的php表中 [英] Insert Data into a table in php which has a foreign key field

问题描述

我通过向其添加反馈表来修改现有项目.我需要将反馈表单数据存储到表调用 feedback_formtb 中.我对sql进行编码以创建此表.还有一个已经创建的表调用 profile_request ，我想从此 profile_request 表中获取一个外键.所以我将request_id字段添加为外键.(由于该部分已经开发，因此我无权编辑profile_request表) 我创建了一个文件调用 feedback_test.php .

I am modifiny an existing project by adding a feedback form to it. I need to store feedback form data into a table call feedback_formtb. I code the sql to create this table. And also there is an already created table call profile_request and I want to take a foreign key from this profile_request table. So I add the request_id field as the foreign key.(I have no permission to edit profile_request table because that part is already developed) I crate a file call feedback_test.php.

现在，我想将反馈表单数据插入到feedback_formtb表中.根据我的理解，我已经做到了.但是由于外键，我不确定这个sql插入查询是否正确，并且我是否正确地将数据插入到了表中(我没有用户界面，因为我要将此反馈表单添加到现有项目中). 如果有人可以帮助我告诉您可以的话，请多谢您的帮助.预先感谢.

Now I want to insert feedback form data to the feedback_formtb table. I have done it according to my understanding. But I am not sure whether this sql insert query is correct because of the foreign key and I is this correctly insert data to the table.(I have no user interfaces since i am asking to add this feed back form to the existing project). Really appreciate your help if some one can help me to tell where this is ok. Thanks in advance.

============== feedback_formtb表create ==================

===============feedback_formtb table create===================

DROP TABLE IF EXISTS `feedback_formtb`;
 CREATE TABLE IF NOT EXISTS `feedback_formtb` (
 `fid` int(10) NOT NULL,
 `job_complete` tinyint(2) NOT NULL,
 `satisfaction` double NOT NULL,
 `reason` int(20) NOT NULL,
 `comment` text NOT NULL,
 `request_id` int(10) NOT NULL,
  PRIMARY KEY (`fid`),
  FOREIGN KEY (`request_id`) REFERENCES profile_requests(`id`)
 )ENGINE=InnoDB DEFAULT CHARSET=latin1;

============= profile_requests表=================

=============profile_requests Table=================

DROP TABLE IF EXISTS `profile_requests`;
CREATE TABLE IF NOT EXISTS `profile_requests` (
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `created_by` int(10) UNSIGNED NOT NULL,
  `updated_by` int(10) UNSIGNED NOT NULL,
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` int(10) UNSIGNED NOT NULL,
  `profile_id` int(10) UNSIGNED NOT NULL,
  `expected_date` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `lat` float UNSIGNED NOT NULL,
  `lng` float UNSIGNED NOT NULL,
  `city_id` int(11) NOT NULL,
  `message` varchar(100) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `state` tinyint(3) UNSIGNED NOT NULL DEFAULT 1 COMMENT '1:new request, 2:accepted,3:rejected',
  `urgent` tinyint(3) UNSIGNED NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=69 DEFAULT CHARSET=latin1;

================ feedback_test.php ================

=================feedback_test.php=================

<?php 
require_once 'auth.php';

// assigning values

$id = $_JSON['fid'] ?? NULL;
$request_id = $_JSON['$request_id'] ?? NULL;

$job_complete = $_JSON['job_complete'] ?? NULL;
$satisfaction = $_JSON['satisfaction'] ?? NULL;
$reason = $_JSON['reason'] ?? NULL;
$comment = $_JSON['comment'] ?? NULL;


$success = TRUE;  


$submit = $_JSON['submit'] ?? NULL;
if ($submit !== NULL) { // if submit success
    if ($job_complete === NULL) { // if job_complete fails
        echo json_encode(['error' => 'job_complete not provided']);
        die;
    }else if ($satisfaction === NULL) {  // if satisfaction fails
        echo json_encode(['error' => 'satisfaction not provided']);
        die;
    }else if ($reason === NULL) { //if reason fails
        echo json_encode(['error' => 'job_complete not provided']);
        die;
    }else if ($comment === NULL) { //if comment fails
        echo json_encode(['error' => 'job_complete not provided']);
        die;
    }

    // Insert Data 


     $ips = $mysqli->prepare('INSERT INTO feedback_formtb (job_complete, satisfaction, reason, comment, request_id) VALUES (?, ?, ?, ?, ( SELECT id FROM profile_requests WHERE id = ? ))'); 
     $ips->bind_param('idisi', $job_complete, $satisfaction, $reason, $comment, $request_id);

     if($ips->execute()){
            $success = TRUE;
     }if (!$ips->execute()) {
        echo json_encode(['error' => 'Fail to submit']);
        die;
     }


}


 ?>

推荐答案

您不需要子查询.只需使用$request_id作为列的值即可.

You don't need the subquery. Just use $request_id as the value of the column.

 $ips = $mysqli->prepare('
    INSERT INTO feedback_formtb (job_complete, satisfaction, reason, comment, request_id) 
    VALUES (?, ?, ?, ?, ?)'); 

外键约束将确保$request_id有效.如果您尝试插入profile_requests中不存在的ID，则会收到错误消息.

The foreign key constraint will ensure that $request_id is valid. If you try to insert an ID that doesn't exist in profile_requests, this will get an error.

这篇关于将数据插入具有外键字段的php表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！