php - 如果mysql中存在，如何更新一行，或者如果不存在，如何创建一个新行

Question

org我正在尝试正确获取此查询。我想在提交表单时将记录插入数据库，但前提是该记录尚不存在。如果该记录存​​在，那么我希望它在数据库中更新。

发生了什么:提交表单后，每次都会将新记录插入数据库中。即使它是重复的。

更新:我添加了一个名为“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 将旧信息替换为数据库中的新信息。虽然我的代码的插入部分正在工作，但 ON DUPLICATE KEY UPDATE 的部分不起作用。

为什么这部分代码可能不起作用？有没有更好的方法来插入或更新信息？

我也尝试过 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 的新手。我已经研究这个概念三天了，并阅读了大量相关信息，但仍然无法让它发挥作用。

Answer 1

我猜联系人 ID 是您的 key ，它是一个自动递增的身份值？在这种情况下，请尝试此插入语句。

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')<br/>
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'" ;