Add or update MySQL

Question

I don't know how to proceed to do the following thing on my specific table.

Let's say I have the following table param, with 3 columns tck, label, value . tck is my primary key.

The data are coming once everyday. I would like to update the value of the existing tck, and if the data sent contain a new tck, I would like to add it to the table...

I hope I'm clear enough... Thank you for your help.

The code I'm using is the following one :

<?php try {
    $bdd = new PDO('mysql:host='.$_ENV['localhost'].';dbname=eip_tasks','root'); } catch(Exception $e) {
    die('Erreur : '.$e->getMessage()); }

$data = $_POST['field1'];

$phpArray = json_decode($data, true); foreach ($phpArray as $u) {  
        //$req = $bdd->prepare('INSERT INTO param (tck, label, value) VALUES(:tck, :label, :value)');
        $req = $bdd->prepare('UPDATE param SET value=:value WHERE tck=:tck');
        $req->execute(array(
            ':tck'=>$u['tck'],
            ':value'=>$u['value']
        )); } ?>

Here is the code I'm using now :

<?php
try
{
    $bdd = new PDO('mysql:host='.$_ENV['localhost'].';dbname=eip_tasks','root');
}
catch(Exception $e)
{
    die('Erreur : '.$e->getMessage());
}

$data = $_POST['field1'];
$phpArray = json_decode($data, true);

$sourceTck = array();
foreach ($phpArray as $u) {array_push($sourceTck, $u['tck']);
    $req = $bdd->prepare("INSERT INTO param (tck, label, value) VALUES (:tck, :label, :value) ON DUPLICATE KEY UPDATE value=:value");
    $req->execute(
        array(
            ':tck'=>$u['tck'],
            ':label'=>$u['label'],
            ':value'=>$u['value']
        )
    );
}

if(count($sourceTck) > 0){
    $sourceTckClause = implode("," , $sourceTck);
    $req = $bdd->prepare("DELETE FROM param WHERE tck NOT IN ($sourceTckClause)");
    $req->execute(); 
}


?>

[Basically, an `INSERT … ON DUPLICATE KEY UPDATE …` query](http://stackoverflow.com/a/1361368/112968) — knittl, Sep 20 '13 at 13:01

score 1 · Answer 1 · edited May 23 '17 at 12:11

Use ON DUPLICATE KEY syntax to update the row instead of insert (if tck was exist):

$req = $bdd->prepare("
    INSERT INTO param (tck, label, value) 
        VALUES 
    (:tck, :label, :value)
    ON DUPLICATE KEY UPDATE value=:value
");

Update: Also don't forget to bind :label. As your comment, To delete a data which would be in the table and not in the source, You should push source tck values to an array and then run a delete query where NOT IN your array:

$sourceTck = array();
foreach ($phpArray as $u) {
    array_push($sourceTck, $u['tck']);
    $req = $bdd->prepare("
        INSERT INTO param (tck, label, value)
            VALUES
        (:tck, :label, :value)
        ON DUPLICATE KEY UPDATE value=:value
    ");
    $req->execute(
        array(':tck'=>$u['tck'], ':label'=>$u['label'], ':value'=>$u['value'])
    );
}

I found this answer useful to prepare and bind tck values, But for small solution, you can convert array values to int to prevent sql injection in this case:

if(count($sourceTck) > 0){
    $sourceTckClause = implode("," , array_map('intval', $sourceTck));
    $bdd->query("DELETE FROM param WHERE tck NOT IN ($sourceTckClause)");
}

It works great but what do I have to do to delete a data which would be in the table and not in the source anymore ? I mean a complete sync... Am I clear enough ? — Henri, Sep 20 '13 at 13:28
It is not working and I cannot identify why... ? When the data is deleted from the source, the line stays in the table... — Henri, Sep 20 '13 at 14:22
sorry no error and no exception... I'm gonna edit my question with the code I paste — Henri, Sep 20 '13 at 14:32

score 0 · Answer 2 · answered Sep 20 '13 at 13:07

0

What you want to do is called an upsert or merge. MySQL does support it.

INSERT INTO param VALUES (:tck, :label, :value)
ON DUPLICATE KEY UPDATE value = :value
-- change :label as well? , label = :label

answered Sep 20 '13 at 13:07

Explosion Pills

188,624
52
326
405

Add or update MySQL

2 Answers2