doucong4535
doucong4535
2011-02-11 02:23

无法使用PHP脚本使用ON DUPLICATE KEY UPDATE

已采纳

Ok I'm starting to spin out now. Too much trial and error has made me rather grumpy.

I am trying to do an INSERT and UPDATE if EXISTS using a loop. I have excluded the loop code from this example to simplify my problem.

Here is the query in my PHP script:

function insertrating($ratingid, $rating){
    $link = resdb::connect();
    $r = mysqli_query($link, "INSERT INTO propertyrating (id,name)" 
                            ."VALUES (\'$ratingid\',\'$rating\')
"
                ."ON DUPLICATE KEY UPDATE
"
                ."name = VALUES (name),
"
                ."description = VALUES (description)
"
                . "");
    if($r > 0){
        return true;
    }
 }
 $mydbclass = new $dbclass();
 $mydbclass->insertrating('3','3 Star');

Table is as follows: id int name varchar(100) description text

I do not want to add description at this stage. Hence no parameters for that column

I have produced the exact same query in phpMyAdmin and the MySQL console, both work. Thus im guessing its something to do with my syntax.

Also, if I get rid of the ON DUPLICATE KEY UPDATE function and parameters it works. However, obviously doesn't update duplicate rows.

Please ask or correct where I am wrong. Thanks.

EDIT: REQUESTED SQL

INSERT INTO 
 propertyrating 
 (
   id,
   name
 ) 
VALUES 
 (
   '3',
   '3 Star'
 ) 
ON DUPLICATE KEY UPDATE
name = VALUES (name),
description = VALUES (description)

AS REQUESTED UNIQUE * PK

Table is as follows:

id int UNIQUE AND PRIMARY KEY name varchar(100) description text

OK GUYS ERROR RETURNED:

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 'UPDATEname = name,description = description

as you can see there is no space between UPDATE and name (it reads, UPDATEname) i added a character space and now all is gravy.

Many thanks for getting me to mysqli_error($link)

not sure how to give answer to zerkms but u all led me in the right direction, first.

Code now stands as:

$r = mysqli_query($link, "INSERT INTO propertyrating (id,name)" 
                        ."VALUES (\'$ratingid\',\'$rating\')
"
            ."ON DUPLICATE KEY UPDATE
"
            ."name = VALUES (name),
"
            ."description = VALUES (description)
"
            . "") or die("Error: ".mysqli_error($link));
  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享
  • 邀请回答

2条回答

  • duanraotun1674 duanraotun1674 10年前

    Put echo mysqli_error($link); right after mysqli_query to get the exact error happened (if any)

    But make sure you've removed this line after this debugging session.

    点赞 评论 复制链接分享
  • dousi5501 dousi5501 10年前

    Hard to answer without seeing the error you are getting, but I think that you should not include this clause:

    description = VALUES (description)
    

    VALUES(name) returns the value given in the INSERT, and you didn't provide one for description. Leaving it out will cause description to not be updated.

    点赞 评论 复制链接分享

相关推荐