orgI正在尝试更正此查询。我想在提交表单时将记录插入数据库,但前提是该记录不存在。如果记录存在,那么我希望在数据库中更新它。
发生了什么:表单提交后,每次都会向数据库中插入一条新记录。即使是复制品。
UPDATE:我添加了一个名为"u_id"的列,它为数据库中的每个联系人保存唯一的信息。因此,我将此列为我的"唯一密钥"专栏
if($_POST['submit']){
$con=mysqli_connect("localhost","username","password","database_name");
// Check connection
if (mysqli_connect_errno())
{
echo "Failed to connect to MySQL: " . mysqli_connect_error();
}
$contact = ($_POST['contact']);
$u = ($_POST['uid']);
$org = mysql_real_escape_string($_POST['organization']);
$namefirst = mysql_real_escape_string($_POST['firstName']);
$namelast = mysql_real_escape_string($_POST['lastName']);
$emailaddy = mysql_real_escape_string($_POST['email']);
$phonenum = mysql_real_escape_string($_POST['phone']);
$appquestion = mysql_real_escape_string($_POST['appquestion']);
$banner = mysql_real_escape_string($_POST['banner']);
$bulletin = mysql_real_escape_string($_POST['bulletin']);
$giveaway = mysql_real_escape_string($_POST['giveaway']);
$app = mysql_real_escape_string($_POST['app']);
$tshirt = mysql_real_escape_string($_POST['tshirt']);
$tshirtp = mysql_real_escape_string($_POST['tshirtp']);
$print = mysql_real_escape_string($_POST['print']);
$party = mysql_real_escape_string($_POST['party']);
$orgnotes = mysql_real_escape_string($_POST['notes']);
$sql="INSERT INTO database_name (contact_id, u_id, first_name, last_name, email_address, phone_number, org, appquestion, banner, bulletin, giveaway, app, tshirt, promised_tee, print, party, org_notes)
VALUES
('$contact', '$u', '$namefirst','$namelast','$emailaddy','$phonenum','$org','$appquestion','$banner','$bulletin','$giveaway','$app','$tshirt','$tshirtp','$print','$party','$orgnotes')
ON DUPLICATE KEY UPDATE first_name = '$namefirst', last_name = '$namelast', email_address = '$emailaddy', phone_number = '$phonenum', org = '$org', appquestion = '$appquestion', banner = '$banner', bulletin = '$bulletin', giveaway = '$giveaway', app = '$app', tshirt = '$tshirt', promised_tee = '$tshirtp', print = '$print', party = '$party', org_notes = '$orgnotes'" ;
if (!mysqli_query($con,$sql))
{
die('Error: ' . mysqli_error($con));
}
echo "1 record added";
mysqli_close($con);
}
根据我所读到的内容,我需要在表单提交时使用ON DUPLICATE KEY UPDATE将数据库中的旧信息替换为新信息。当我的代码的插入部分工作时,带有ONDUPLICATEKEYUPDATE的部分不工作。
为什么这部分代码可能不起作用?是否有更好的方法插入或更新信息?
我也尝试过REPLACE INTO(而不是INSERT和ON DUPLICATE KEY UPDATE),但也不起作用。以下是我在MySQL数据库中的列结构:
+-------------+-------------+------+-----+-----------+-------------------+
Field | Type | Null | Key | Default | Extra
+-------------+-------------+------+-----+-----------+-------------------+
contact_id | int(1) | NO | PRI | NULL | auto_increment
u_id | char(32) | NO | UNI | NULL |
title | varchar(80) | NO | | NULL |
first_name | varchar(100)| NO | | NULL |
last_name | varchar(100)| NO | | NULL |
job_title | varchar(255)| NO | | NULL |
address_1 | varchar(255)| NO | | NULL |
address_2 | varchar(255)| NO | | NULL |
org_city | varchar(100)| NO | | NULL |
org_state | varchar(100)| NO | | NULL |
zip_code | varchar(8) | NO | | NULL |
country | varchar(100)| NO | | NULL |
phone_number | varchar(15) | NO | | NULL |
email_address | varchar(100)| NO | | NULL |
org | varchar(150)| NO | | NULL |
appquestion | tinyint(1) | NO | | NULL |
banner | tinyint(1) | NO | | NULL |
bulletin | tinyint(1) | NO | | NULL |
giveaway | tinyint(1) | NO | | NULL |
app | tinyint(1) | NO | | NULL |
tshirt | tinyint(1) | NO | | NULL |
promised_tee | tinyint(1) | NO | | NULL |
print | tinyint(1) | NO | | NULL |
party | tinyint(1) | NO | | NULL |
org_notes | varchar(255)| NO | | NULL |
notes | varchar(255)| NO | | NULL |
+-------------+-------------+------+-----+-----------+-------------------+
谢谢你能给我的任何帮助或指导!我是PHP和MySQL的新手。我已经研究这个概念三天了,读了很多关于它的信息,但仍然无法使它发挥作用。
我想联系人id是你的密钥,它是一个自动递增的身份值?在这种情况下,请尝试此insert语句。
INSERT INTO database_name
(first_name, last_name, email_address, phone_number, org, appquestion, banner, bulletin, giveaway, app, tshirt, promised_tee, print, party, org_notes)
VALUES
('$namefirst','$namelast','$emailaddy','$phonenum','$org','$appquestion','$banner','$bulletin','$giveaway','$app','$tshirt','$tshirtp','$print','$party','$orgnotes')
ON DUPLICATE KEY UPDATE
first_name = '$namefirst', last_name = '$namelast', email_address = '$emailaddy', phone_number = '$phonenum', org = '$org', appquestion = '$appquestion', banner = '$banner', bulletin = '$bulletin', giveaway = '$giveaway', app = '$app', tshirt = '$tshirt', promised_tee = '$tshirtp', print = '$print', party = '$party', org_notes = '$orgnotes'" ;