我是mysql的新手,我正在尝试创建一个数据库,该数据库可以在一个表上存储用户的电子邮件和密码,并在另一个表上存储他们输入的值,我如何加入表以确保输入的值链接到正确的用户。这是我一直在使用的代码,但是在运行外键时不允许存储该值,但是如果删除外键,则可以存储该值。请帮忙。
CREATE TABLE IF NOT EXISTS `data` (
`user_id` int(11) NOT NULL AUTO_INCREMENT,
`email` varchar(51) NOT NULL,
`password` varchar(15) NOT NULL,
PRIMARY KEY (`user_id`),
UNIQUE KEY `email_UNIQUE` (`email`)
)
CREATE TABLE IF NOT EXISTS `gluco` (
`G_id` int(11) NOT NULL AUTO_INCREMENT,
`bloods` decimal(4,2) NOT NULL,
`time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`user_id` int(11) NOT NULL,
FOREIGN KEY (`user_id`) REFERENCES `data`(`use_id`),
UNIQUE KEY `G_id_UNIQUE` (`G_id`)
)
<?php
include('db.php');
if (!isset($_POST['reading'])) { //checking if user has entered this page directly
include('contactus.php');
} else {
if (isset($_POST['reading'])&&$_POST['reading']==""||!isset($_POST['reading'])) {
$error[] = "fill in your blood/glucose";
}
$reading = mysql_real_escape_string($_POST['reading']);
$sql = "SELECT * FROM gluco WHERE bloods = '$reading'";
if(isset($error)){
if(is_array($error)){
echo "<div class=\"error\"><span>please check the errors and refill the form<span><br/>";
foreach ($error as $ers) {
echo "<span>".$ers."</span><br/>";
}
echo "</div>";
include('contactus.php');
}
}
if(!isset($error)){
$sreading=mysql_real_escape_string($_POST['reading']);
$sip=mysql_real_escape_string($_SERVER['HTTP_HOST']);
$save = mysql_query("INSERT INTO `gluco` ( `bloods` )VALUES ('$sreading')");
if($save){
echo "<div class=\"success\"><span>Your reading has been successfully stored</span><br/></div>";
} else {
echo "<div class=\"warning\"><span>Some Error occured during processing your data</div>";
}
}
}
?>
最佳答案
您的代码逻辑正确。但是引用的列名存在错误:
CREATE TABLE IF NOT EXISTS `data` (
`user_id` int(11) NOT NULL AUTO_INCREMENT,
`email` varchar(51) NOT NULL,
`password` varchar(15) NOT NULL,
PRIMARY KEY (`user_id`),
UNIQUE KEY `email_UNIQUE` (`email`)
)
CREATE TABLE IF NOT EXISTS `gluco` (
`G_id` int(11) NOT NULL AUTO_INCREMENT,
`bloods` decimal(4,2) NOT NULL,
`time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`user_id` int(11) NOT NULL,
FOREIGN KEY (`user_id`) REFERENCES `data`(`user_id`),
UNIQUE KEY `G_id_UNIQUE` (`G_id`)
)
在这一行上:
$save = mysql_query("INSERT INTO `gluco` ( `bloods` )VALUES ('$sreading')");
您未在insert语句中设置user_id,因此,外键将不起作用,并且将无法进行插入。因此,您需要将用户ID存储在变量中(由于我不知道代码中的上下文和范围,因此我无法帮助您设置此变量)。因此,您的代码应如下所示:
$save = mysql_query("INSERT INTO gluco (bloods, user_id)VALUES ('$sreading', $user_id)");