PHP在重复密钥更新时将数据插入mysql - 添加后缀

Question

want insert a duplicate entry into mysql with a suffix. here is an example:

+-------+--------------+-------+
| ID    | Title        | url   |
+-------+--------------+-------+

+-------+--------------+-------+
| 1     | test         | test1 |
+-------+--------------+-------+
| 2     | test         | test2 |
+-------+--------------+-------+
| 3     | test         | test3 |
+-------+--------------+-------+

means, i want to add auto number as suffix to end of each url that have duplicate title. titles are duplicated but url are kinda unique.

Here is query:

 $i = 0;
    $i++;

    $sql = "INSERT INTO `content` (title, text, cat, url, tag)
    VALUES ('".$_POST["title"]."', '".$_POST["text"]."', '".$_POST["category"]."', '".$_POST["url"]."', '".$_POST["tag"]."') ON DUPLICATE KEY UPDATE url=".$_POST["url"]."+". $i ."";

note: url made dynamically based on title entry. this query not working, what is wrong with my code?

UPDATE: here is error after run query:

error: INSERT INTO content (title, text, cat, url, tag) VALUES ('test', '', '16', 'test', '') ON DUPLICATE KEY UPDATE url=test+1 Unknown column 'test' in 'field list'

Answer 1

Your code has some strange things. $i = 0; followed by $i++; is just equivalent than doing $i = 1; But then again, what's the purpose on setting a variable that you only use once?

What you really need to do is:

1. INSERT query forgetting about the "url" field and removing the ON DUPLICATE...

2. Get the ID of the last insert. Have a look at the following link to see how to get that value: http://www.w3schools.com/php/php_mysql_insert_lastid.asp

3. UPDATE query to set the value of "url"

   $_POST["url"].$id_of_last_query