-2

hi everyone i need help

create table wishlist
(
    userID       int                          null,
    wishlistData longtext collate utf8mb4_bin not null,
    constraint userID
        unique (userID),
    constraint whishlist_user_id_fk
        foreign key (userID) references user (id)
);

first i did this but it make duplication value in wishlistData

$query = "INSERT INTO wishlist (userID, wishlistData)
                VALUES ($userID, JSON_ARRAY('$productId'))
                ON DUPLICATE KEY UPDATE wishlistData = JSON_ARRAY_INSERT(wishlistData, '$[0]', '$productId');";

to insert id product to my wishlistData where userID doesn't exist or update if exist using ON DUPLICATE KEY UPDATE but here if the id product exist on wishlistData don't update i code this but doesn't work

$query = "INSERT INTO wishlist (userID, wishlistData)
                VALUES ($userID, JSON_ARRAY('$productId'))
                ON DUPLICATE KEY UPDATE wishlistData = JSON_ARRAY_INSERT(wishlistData, '$[0]', IF ((SELECT JSON_SEARCH(JSON_EXTRACT(wishlistData, '$'), 'one', '$productId')  IS Not NULL),wishlistData,'$productId'));";
Shadow
  • 33,525
  • 10
  • 51
  • 64
med
  • 1
  • 1
  • 1
    Welcome to SO. Recommend using prepared statements, always. What exactly does "doesn't work" mean? Can you show some data and expected results. Edit the question with these details. – danblack Jun 16 '23 at 01:33
  • 2
    please never say "doesn't work"; say what _does_ happen – ysth Jun 16 '23 at 02:01
  • The duplicate detection only works on the user id field – Shadow Jun 16 '23 at 02:04

1 Answers1

0

first thanks for replaying
this my wishlist table

| userID   | wishlistData   |
| -------- | -------------- |
| 1        | ["90","92"]    |
| 2        | ["65","192"]   |

for this code

$query = "INSERT INTO wishlist (userID, wishlistData) VALUES ($userID, JSON_ARRAY('$productId')) ON DUPLICATE KEY UPDATE wishlistData = JSON_ARRAY_INSERT(wishlistData, '$[0]', '$productId');";

Input1 : $productId=100 userID=1
Result1 = expected1:

| userID   | wishlistData            |
| -------- | ------------------------|
| 1        | ["100", "90", "92"]     |
| 2        | ["65", "192"]           |

Input2 : $productId=90 userID=1
Result2:

| userID   | wishlistData             |
| -------- | ------------------------ |
| 1        | ["90", "100", "90", "92"]|
| 2        | ["65", "192"]            |

expected2:

| userID   | wishlistData          |
| -------- | --------------------- |
| 1        | ["100", "90", "92"]   |
| 2        | ["65", "192"]         |

but the second code didn't even make insert

$query = "INSERT INTO wishlist (userID, wishlistData)
                VALUES ($userID, JSON_ARRAY('$productId'))
                ON DUPLICATE KEY UPDATE wishlistData = JSON_ARRAY_INSERT(wishlistData, '$[0]', IF ((SELECT JSON_SEARCH(JSON_EXTRACT(wishlistData, '$'), 'one', '$productId')  IS Not NULL),wishlistData,'$productId'));";
med
  • 1
  • 1