MYSQL query update or insert in PHP using if else

Question

$getCoins = $this->code = $this->conn->prepare("UPDATE coins SET name = ? WHERE id_name = ? IF @@ROWCOUNT = 0 INSERT INTO coins (id_name) VALUES (?)");

for ($i=0; $i < count($coins) ; $i++) { 

    $id_name = $coins[$i]["id"];
    $name = $coins[$i]["name"];


    $getCoins = $this->code->bind_param('sss', $name, $id_name, $id_name);
    $getCoins = $this->code->execute();
    $getCoins = $this->code->store_result();

}

Script return me error:

Fatal error: Uncaught Error: Call to a member function bind_param() on boolean ...

The problem is with IF @@ROWCOUNT = 0 I tryied using IF ELSEl

IF(SELECT COUNT(id_name) FROM coins WHERE id_name = 'bitcoin')
    UPDATE coins SET name = 'xxx2' WHERE id_name = 'bitcoin'
ELSE
    INSERT INTO coins (name) VALUES ('new_coins')
END IF

but hear is error:

#1064 - Something is wrong in your syntax obok 'UPDATE coins SET name = 'xxx2' WHERE id_name = 'bitcoin' ELSE INSERT INTO coi' w linii 2

I using this answer link

delimit your statements with ';' and seems odd if you put all your query in one call. — RoMEoMusTDiE
– RoMEoMusTDiE, Commented Jan 23, 2018 at 20:04
@maSTAShuFu Like this: UPDATE coins SET name = 'xxx2' WHERE id_name = 'bitcoin'; IF @@ROWCOUNT = 0; INSERT INTO coins (name) VALUES ('new_coins'); I have error: #1193 - Unknown system variable 'ROWCOUNT' — major697
– major697, Commented Jan 23, 2018 at 20:11
@michal @@ROWCOUNT doesn't exist in MySQL. It's a SQL Server construct. — flip
– flip, Commented Jan 23, 2018 at 20:11
@flip Yes, you're right, but why dont work second solution, when I use If and else — major697
– major697, Commented Jan 23, 2018 at 20:16

Barmar · Accepted Answer · 2018-01-23 21:02:32Z

1

Use the ON DUPLICATE KEY UPDATE option to INSERT.

INSERT INTO coins (id_name, name)
VALUES (?, ?)
ON DUPLICATE KEY UPDATE name = VALUES(name)

If id_name already exists, the name column will be updated.

answered Jan 23, 2018 at 21:02

Barmar

789k57 gold badges555 silver badges669 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MYSQL query update or insert in PHP using if else

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related