0

I'm trying to insert unique values for each userID, so that one user can't have more than one of the same cardnumber

$stmt = $pdo->prepare('INSERT INTO bankingdetails (userID, cardHolder, cardnumber, expDate, CVV) 
                            VALUES (?, ?, ?, ?, ?) 
                            WHERE NOT EXISTS (SELECT * FROM bankingdetails WHERE userID = ? 
                            AND cardHolder = ? 
                            AND cardnumber = ? 
                            AND expDate = ? 
                            AND CVV = ?)');
    $stmt->execute([$userID, 
                    $_POST['cardname'], 
                    $_POST['cardnumber'], 
                    $expDate, 
                    $_POST['cvv'], 
                    $userID, 
                    $_POST['cardname'], 
                    $_POST['cardnumber'], 
                    $expDate, 
                    $_POST['cvv']]);

After I tried this, it kept adding the same cardnumber combined with the same userID

I've seen this problem before but couldn't find a solution that worked for me.

Any tips are appreciated.

After that I tried something else:

$orderComplete = false;
if($_POST['cardnumber'] == $card['cardnumber'] 
    && $expDate == $card['expDate'] 
    && $_POST['cvv'] == $card['CVV'] 
    && $_POST['cardname'] == $card['cardHolder']) {

    $orderComplete = true;
} else {

    $stmt = $pdo->prepare('INSERT INTO bankingdetails 
(userID, cardHolder, cardnumber, expDate, CVV) 
                            VALUES (?, ?, ?, ?, ?)');

    $stmt->execute([$userID, $_POST['cardname'], $_POST['cardnumber'], 
$expDate, $_POST['cvv']]);
    $orderComplete = true;
}

But that gave me the same result.

Pierre Hoste
  • 1
  • 1
  • This could be useful: https://chartio.com/resources/tutorials/how-to-insert-if-row-does-not-exist-upsert-in-mysql/ – KIKO Software Feb 08 '23 at 08:41
  • A tip: If you write code try to [keep the line length below 80 characters](https://hackaday.com/2020/06/18/ask-hackaday-are-80-characters-per-line-still-reasonable-in-2020/) most of the time. This way your readers don't have to scroll horizontally so much. Remember, you're not only writing code for the computer. – KIKO Software Feb 08 '23 at 08:43
  • Does the select return a row? If so, have you checked the details to see if there are any differences from the `$_POST` values (use `var_dump()` so you can see the details of each field). – Nigel Ren Feb 08 '23 at 08:46
  • 1
    Is it not as easy to create a constraint on your table to make that combination of values unique? All you need to do then is do the INSERT, and handle the error that comes back because of the duplication. – droopsnoot Feb 08 '23 at 08:56
  • What DBMS / SQL dialect is this supposed to be - where INSERT supports a WHERE clause? – CBroe Feb 08 '23 at 09:11
  • @droopsnoot yes, thank you for the tip! I hadn't thought of that. – Pierre Hoste Feb 08 '23 at 09:16

0 Answers0