0
$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

michal
  • 1,534
  • 5
  • 28
  • 62
  • delimit your statements with ';' and seems odd if you put all your query in one call. – RoMEoMusTDiE Jan 23 '18 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' – michal Jan 23 '18 at 20:11
  • @michal @@ROWCOUNT doesn't exist in MySQL. It's a SQL Server construct. – flip Jan 23 '18 at 20:11
  • but normally you don't do this kind of coding – RoMEoMusTDiE Jan 23 '18 at 20:13
  • @flip Yes, you're right, but why dont work second solution, when I use `If` and `else` – michal Jan 23 '18 at 20:16
  • @michal You can only use the IF ELSE construct inside of a Stored Procedure or Trigger. If you don't want to put the logic in PHP, then you should look at creating a stored procedure to perform the logic and use PHP to call the stored procedure. https://dev.mysql.com/doc/refman/5.7/en/if.html – flip Jan 23 '18 at 20:25
  • Why don't you use `INSERT INTO ... ON DUPLICATE KEY UPDATE ...`? – Barmar Jan 23 '18 at 20:56

1 Answers1

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.

Barmar
  • 741,623
  • 53
  • 500
  • 612