CODEWITHSUNDEEP
phpmysql
JS1986
JS1986

Reputation: 1950

unable to use ON DUPLICATE KEY UPDATE with php script

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\')\n"
                ."ON DUPLICATE KEY UPDATE\n"
                ."name = VALUES (name),\n"
                ."description = VALUES (description)\n"
                . "");
    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\')\n"
            ."ON DUPLICATE KEY UPDATE\n"
            ."name = VALUES (name),\n"
            ."description = VALUES (description)\n"
            . "") or die("Error: ".mysqli_error($link));

Upvotes: 0

Views: 935

Answers (2)

zerkms
zerkms

Reputation: 254916

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.

Upvotes: 1

Rob
Rob

Reputation: 1365

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.

Upvotes: 1

Related Questions