我的表结构(MySQL/每一个都与下面相同)
+-------+--------------+------+------+-------------------+
| Field | Type | Null | Key | Default |
+-------+--------------+------+------+-------------------+
| id | int(11) | NO | PRI | AUTO INCREMENT |
| lesson| varchar(255) | NO | | LESSON_NAME |
| exam | char(50) | NO |UNIQUE| NO DEFAULT |
| quest | text | NO | | NO DEFAULT |
| answer| text | NO | | NO DEFAULT |
| note | text | NO | | NO DEFAULT |
+-------+--------------+------+------+-------------------+
我发布了一些值来通过ajax($post)-PHP 5.0添加此表
在database.php中,有一个函数可以获取发布的数据并添加到表中
function update_table ($proper_table, $name, $question, $answer, $note) {
$sql = "INSERT INTO $proper_table (id, lesson, exam, quest, answer, note) VALUES ('', '', $name, $question,$answer,$note) ON DUPLICATE KEY UPDATE exam = $name, quest = $question, answer = $answer, note = $note";
$result= mysql_query($sql)or die(mysql_error());
}
$proper_table变量被另一个变量用来将该记录添加到正确的表中
(注意:原始表字段和变量不同(土耳其语),与英语相比更容易理解,但语法与您看到的相同。)
问题:我想检查一下,如果有一个记录的考试字段是相同的,那么所有这些变量都将用于更新这个记录,否则让函数将这个记录作为新记录放在适当的表中
但我得到了如下错误
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near
编码有问题吗?解决方案是什么
现在谢谢。。。
function update_table ($proper_table, $name, $question, $answer, $note) {
$sql = "INSERT INTO $proper_table (lesson, exam, quest, answer, note) VALUES ('', '$name', '$question','$answer','$note') ON DUPLICATE KEY UPDATE quest = VALUES(quest), answer = VALUES(answer), note = VALUES(note)";
$result= mysql_query($sql)or die(mysql_error());
}
我会详细说明的变化
$sql = "INSERT INTO $proper_table
// Removed the PK (primary key) AI (auto increment) field - don't need to specify this
(lesson, exam, quest, answer, note)
// Likewise removed PK field, and added quotes around the text fields
VALUES ('', '$name', '$question','$answer','$note')
ON DUPLICATE KEY UPDATE
// If you specify VALUES(fieldName) it will update with the value you specified for the field in the conflicting row
// Also removed the exam update, as exam is the UNIQUE key which could cause conflicts so updating that would have no effect
quest = VALUES(quest), answer = VALUES(answer), note = VALUES(note)";
例如,您需要在SQL '$name'
中用单引号包装字符串变量。否则,mysql会认为您在引用列名。
使用该查询,当您添加ON DUPLICATE KEY UPDATE。。。当id与您发送的id相同时,它会更新,在这种情况下,您没有将id作为参数发送,因此它永远不会更新,因为您的id是自动递增的。
一个解决方案可以是,你阅读表格,其中考试等于你发送的参数,类似这样:
SELECT id FROM $proper_table;
如果它为null,则执行插入,如果它不为null,将从选择中获得的id作为参数进行更新
id
自动递增,因此您可能不想将空字符串设置为id
。
尝试:
$sql = "INSERT INTO $proper_table (lesson, exam, quest, answer, note) VALUES ('', $name, $question,$answer,$note) ON DUPLICATE KEY UPDATE exam = $name, quest = $question, answer = $answer, note = $note";
你必须让它像这个
<?php
function update_table($proper_table, $name, $question, $answer, $note, $id) {
$sqlQuery = "INSERT INTO '".$proper_table."' SET
name = '".$name."',
question = '".$question."',
answer = '".$answer."',
note = '".$note."' WHERE id = '".$id."'";
$result= mysql_query($sqlQuery)or die(mysql_error());
}
?>