I need a system in which I check if a Primary key (string) already exists.

If it does, then I want to add 1 to a second column.

If it doesn't, then I want to add a new row and set the second column value to 1.

Here is my attempt:

$foodWishNameStr = ($_POST["foodWishName"]);

$foodWishPoint = 1;


$sanitized_foodWish = sanitize_text_field($foodWishNameStr); /* Checks for problematic things in the string like invalid UTF-8, it converts < characters to entities, strips all tags, removes line breaks, tabs and extra white space. */


global $wpdb;


    //my column names in the table are foodWishName and foodWishPoints:
    $sql = "INSERT INTO foodWishes (foodWishName,foodWishPoints) VALUES (%s,%d) ON DUPLICATE KEY UPDATE foodWishPoints + 1 = %d";
    // var_dump($sql); // debug
    $sql = $wpdb->prepare($sql,$sanitized_foodWish,$foodWishPoint, $foodWishPoint);
    // var_dump($sql); // debug
    $wpdb->query($sql);

    if($sql){ //return the response
        echo $sql;
    }else{
    echo "something went very wrong";
    }

    exit();

That gives me this error:

<div id="error"><p class="wpdberror"><strong>WordPress database error:</strong> [You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near &#039;+ 1 = 1&#039; at line 1]<br /><code>INSERT INTO foodWishes (foodWishName,foodWishPoints) VALUES (&#039;redwinevinegar&#039;,1) ON DUPLICATE KEY UPDATE foodWishPoints + 1 = 1</code></p></div>INSERT INTO foodWishes (foodWishName,foodWishPoints) VALUES ('redwinevinegar',1) ON DUPLICATE KEY UPDATE foodWishPoints + 1 = 1

I need a system in which I check if a Primary key (string) already exists.

If it does, then I want to add 1 to a second column.

If it doesn't, then I want to add a new row and set the second column value to 1.

Here is my attempt:

$foodWishNameStr = ($_POST["foodWishName"]);

$foodWishPoint = 1;


$sanitized_foodWish = sanitize_text_field($foodWishNameStr); /* Checks for problematic things in the string like invalid UTF-8, it converts < characters to entities, strips all tags, removes line breaks, tabs and extra white space. */


global $wpdb;


    //my column names in the table are foodWishName and foodWishPoints:
    $sql = "INSERT INTO foodWishes (foodWishName,foodWishPoints) VALUES (%s,%d) ON DUPLICATE KEY UPDATE foodWishPoints + 1 = %d";
    // var_dump($sql); // debug
    $sql = $wpdb->prepare($sql,$sanitized_foodWish,$foodWishPoint, $foodWishPoint);
    // var_dump($sql); // debug
    $wpdb->query($sql);

    if($sql){ //return the response
        echo $sql;
    }else{
    echo "something went very wrong";
    }

    exit();

That gives me this error:

<div id="error"><p class="wpdberror"><strong>WordPress database error:</strong> [You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near &#039;+ 1 = 1&#039; at line 1]<br /><code>INSERT INTO foodWishes (foodWishName,foodWishPoints) VALUES (&#039;redwinevinegar&#039;,1) ON DUPLICATE KEY UPDATE foodWishPoints + 1 = 1</code></p></div>INSERT INTO foodWishes (foodWishName,foodWishPoints) VALUES ('redwinevinegar',1) ON DUPLICATE KEY UPDATE foodWishPoints + 1 = 1

• wpdb

• foodWishPoints + 1 = %d doesn't make much sense, and doesn't match any of the examples, foodWishPoints + 1 = 1 sounds like a contradiction or an algebra test that forces foodWishPoints to always be 0. The very first google result for insert on duplicate shows a very different result: ON DUPLICATE KEY UPDATE c1 = VALUES(c1) + 1; which makes much more sense. I believe you do not need WordPress help here, you need MySQL help, and that your query would fail even if you put it in a generic SQL client such as PHPMyAdmin or Sequel Pro and bypassed WordPress completely. – Tom J Nowell ♦ Commented Feb 21, 2022 at 23:36
• Thank you for pointing me in the right direction, with the help of one of the search results I was finally able to make it work correctly. – user44109 Commented Feb 22, 2022 at 11:16

1 Answer 1

This ended up being the correct formula in my case:

$sql = "INSERT INTO foodWishes(foodWishName,foodWishPoints) VALUES (%s,%d) ON DUPLICATE KEY UPDATE foodWishPoints = foodWishPoints +1";
    // var_dump($sql); // debug
    $sql = $wpdb->prepare($sql,$sanitized_foodWish,$foodWishPoint,);
    // var_dump($sql); // debug
    $wpdb->query($sql);

    if($sql){ //return the response
        echo $sql;
    }else{
    echo "something went very very wrong";
    }

    exit();