将所选数据作为外键和SQLSTATE插入[23000]:完整性约束违规:1048 [英] insert slected data as Foreign key and SQLSTATE[23000]: Integrity constraint violation: 1048
问题描述
感谢您的光临!
我希望能够从<$ c $中选择 acc_id
c> account_info 表并将其插入 patient_info
表格中,作为外键
i want to be able to select acc_id
from account_info
table and insert it in patient_info
table as Foreign Key
我收到此错误:
在我的localhost中,我可以看到它想要返回的值。
我发现这个错误是因为我没有插入 acc_id
但是 SQLSTATE [23000]:完整性约束违规:1048列'acc_id'不能为空
In my localhost i can see the value it's suppose to return.
And this error which I suppose it occurs because i haven't inserted acc_id
yet SQLSTATE[23000]: Integrity constraint violation: 1048 Column 'acc_id' cannot be null
此外 var_dump($ data)
返回 array(1){[0] => object(stdClass)#3(1){[acc_id] => int(124)}}
php文件:
<?php
header('Access-Control-Allow-Origin: *');
// Define database connection parameters
$hn = 'localhost';
$un = 'root';
$pwd = '';
$db = 'ringabell';
$cs = 'utf8';
// Set up the PDO parameters
$dsn = "mysql:host=" . $hn . ";port=3306;dbname=" . $db . ";charset=" . $cs;
$opt = array(
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_OBJ,
PDO::ATTR_EMULATE_PREPARES => false,
);
// Create a PDO instance (connect to the database)
$pdo = new PDO($dsn, $un, $pwd, $opt);
// Retrieve specific parameter from supplied URL
$data = array();
try{
$stmt = $pdo->query('SELECT acc_id FROM account_info ORDER BY acc_id DESC LIMIT 1');
$data = $stmt->fetchAll(PDO::FETCH_OBJ);
// Return data as JSON
echo json_encode($data);
var_dump($data);
$sql= "INSERT INTO patient_info(acc_id, p_fname, p_lname, p_gender, p_condition, p_birthdate, p_emergencycontact)
VALUES(:data, :p_fname, :p_lname, :p_gender, :p_condition, :p_birthdate, :p_emergencycontact)";
$stmt = $pdo->prepare($sql);
$stmt->bindParam(':p_fname', $p_fname, PDO::PARAM_STR);
$stmt->bindParam(':p_lname', $p_lname, PDO::PARAM_STR);
$stmt->bindParam(':p_gender', $p_gender, PDO::PARAM_STR);
$stmt->bindParam(':p_condition', $p_condition, PDO::PARAM_STR);
$stmt->bindParam(':p_birthdate', $p_birthdate, PDO::PARAM_STR);
$stmt->bindParam(':p_emergencycontact', $p_emergencycontact, PDO::PARAM_STR);
$stmt->bindParam(':data', $acc_id, PDO::PARAM_STR);
$stmt->execute();
echo json_encode(array('message' => 'Congratulations the record was added to the database'));
}
catch(PDOException $e)
{
echo $e->getMessage();
}
?>
推荐答案
您的代码中似乎存在多个问题或者你想要做的事情。请检查以下代码。它应该工作。我添加了一些内联评论。请检查它们:
It seems there are multiple issues in your code or the way you are trying to do things. Please check the code below. It should work. I have added few inline comments. Please check them:
<?php
// Define database connection parameters
$hn = 'localhost';
$un = 'root';
$pwd = '';
$db = 'ringabell';
$cs = 'utf8';
// Set up the PDO parameters
$dsn = "mysql:host=" . $hn . ";port=3306;dbname=" . $db . ";charset=" . $cs;
$opt = array(
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_OBJ,
PDO::ATTR_EMULATE_PREPARES => false,
);
// Create a PDO instance (connect to the database)
$pdo = new PDO($dsn, $un, $pwd, $opt);
// Retrieve specific parameter from supplied URL
$data = array();
try {
$stmt = $pdo->query('SELECT acc_id FROM account_info ORDER BY acc_id DESC LIMIT 1');
$data = $stmt->fetchAll(PDO::FETCH_OBJ);
// You do not need to return response from here
// echo json_encode($data);
// var_dump($data);
$sql= "INSERT INTO patient_info(acc_id, p_fname, p_lname, p_gender, p_condition, p_birthdate, p_emergencycontact)
VALUES(:acc_id, :p_fname, :p_lname, :p_gender, :p_condition, :p_birthdate, :p_emergencycontact)";
$stmt = $pdo->prepare($sql);
// the $p_fname, $p_lname, $p_gender etc variables in your code were never initiated. You would get
// notice for this if you had all error_reporting on. I am not sure from where you intend to get this info;
// so, I just added some dummy data.
$p_fname = 'Patient first name';
$p_lname = 'Patient last name';
$p_gender = 'm';
$p_condition = 'condition';
$p_birthdate = '1999-01-01';
$p_emergencycontact = 'Contact';
$stmt->bindParam(':p_fname', $p_fname, PDO::PARAM_STR);
$stmt->bindParam(':p_lname', $p_lname, PDO::PARAM_STR);
$stmt->bindParam(':p_gender', $p_gender, PDO::PARAM_STR);
$stmt->bindParam(':p_condition', $p_condition, PDO::PARAM_STR);
$stmt->bindParam(':p_birthdate', $p_birthdate, PDO::PARAM_STR);
$stmt->bindParam(':p_emergencycontact', $p_emergencycontact, PDO::PARAM_STR);
// You do not have any $acc_id variable in your code. To get data from your fetch you need to do
// like this:
$stmt->bindParam(':acc_id', $data[0]->acc_id, PDO::PARAM_STR);
$stmt->execute();
header('Access-Control-Allow-Origin: *');
// If you want to get the acc_id in your client side through the AJAX call, combine both
// mesage and data in the same JSON object.
echo json_encode(
array(
'message' => 'Congratulations the record was added to the database'
'data' => $data
)
);
} catch(PDOException $e) {
// make sure to send the proper status code
http_response_code(500);
// even error should be sent back as in json so that your javascript client can
// easily parse it
echo json_encode(
array(
'error' => $e->getMessage()
)
);
}
?>
这篇关于将所选数据作为外键和SQLSTATE插入[23000]:完整性约束违规:1048的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!