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
2
  • 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
    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
    Feb 22, 2022 at 11:16

1 Answer 1

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();

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.