We help IT Professionals succeed at work.
Troubleshooting Question

Either Update or Insert a Record

David Schure
David Schure asked
on
35 Views
Last Modified: 2020-11-15
I am trying to write code that will either Update a record if it exists (bucket_id, client_id) or insert a record if it does not.  If the record is to be Updated then it will increase the count field by the number indicated.  Otherwise it will Insert a new record (bucket_id, client_id,bucket_count)
// Get the credit card details submitted by the form
$tokenid = $_POST['tokenid'];

if ($_GET['package']==1) { $amount = 7500; $package_name = "Individual Therapy - Pay As You Go"; $plan_name=""; }
if ($_GET['package']==2) { $amount = 8000; $package_name = "Couples Therapy - Pay As You Go"; $plan_name=""; }
if ($_GET['package']==3) { $amount = 6500; $package_name = "Group Therapy - Pay As You Go"; $plan_name=""; }

if ($_GET['package']==4) { $amount = 28000; $package_name = "Individual Therapy - Monthly"; $plan_name="price_1Hi1OAEkBUYaFdGgvRLrMLvI"; }
if ($_GET['package']==5) { $amount = 30000; $package_name = "Couples Therapy - Monthly"; $plan_name="price_1Hi1PdEkBUYaFdGgKp4s1M3a"; }
if ($_GET['package']==6) { $amount = 24000; $package_name = "Group Therapy - Monthly"; $plan_name="price_1Hi1QAEkBUYaFdGgUJo5Fyom"; }

$fname=addslashes($_GET['fname']);
$address=addslashes($_GET['address']);
$city=addslashes($_GET['city']);
$state=addslashes($_GET['state']);
$zip=addslashes($_GET['zip']);
$country=addslashes($_GET['country']);
$email=addslashes($_GET['email']);

try{
    $pdo = new PDO("mysql:host=localhost;dbname=HMS2020A", "", "");
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch(PDOException $e){
    die("ERROR: Could not connect. " . $e->getMessage());
}



try{
    $sql = "
    INSERT INTO `tbl_bucket` (
        `client_id`, 
        `bucket_id`, 
        `bucket_count`)
    VALUES (
        :client_id, 
        :bucket_id, 
        :bucket_count)";

    if ($stmt = $pdo->prepare($sql)) {
        $data = [
            'client_id' => $_SESSION['the_client_id'],
            'bucket_id' => $_GET['package'],
            'bucket_count' => 1
        ];
    
        $stmt->execute($data);
        //$client_id = $pdo->lastInsertId();
    
    } else {
        die("Could not prepare Statement");
    }
}catch(Exception $e){
    echo $e->getMessage();
}


Comment
Watch Question

lcohanDatabase Analyst
CERTIFIED EXPERT

Commented:
As it looks like you're using MySQL database I think you could use a "UPSERT" type statement as detailed at link below that should be easy to adapt to your structure and code.
https://dev.mysql.com/doc/refman/5.7/en/insert-on-duplicate.html
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
Hey David,

You want to be running an INSERT ON DUPLICATE KEY query. Something like this:

INSERT INTO `tbl_bucket` (`client_id`, `bucket_id`, `bucket_count`)
VALUES (:client_id, :bucket_id, :bucket_count)
ON DUPLICATE KEY UPDATE bucket_count = bucket_count + 1

Open in new window

It will use either the PRIMARY key or a UNIQUE index key, so if you haven't already, you'll want to add a UNIQUE index on the client_id / bucket_id composite

Author

Commented:
Hi Chris...Like this?
try{
    $sql = "
    INSERT INTO `tbl_bucket` (
        `client_id`, 
        `bucket_id`, 
        `bucket_count`)
    VALUES (
        :client_id, 
        :bucket_id, 
        :bucket_count)";
    ON DUPLICATE KEY UPDATE bucket_count = bucket_count + 1
    if ($stmt = $pdo->prepare($sql)) {
        $data = [
            'client_id' => $_SESSION['the_client_id'],
            'bucket_id' => $_GET['package'],
            'bucket_count' => 1
        ];
    
        $stmt->execute($data);
        //$client_id = $pdo->lastInsertId();
    
    } else {
        die("Could not prepare Statement");
    }
}catch(Exception $e){
    echo $e->getMessage();
}
I do have an Unique Index Key...
buck_id   client_id   bucket_id   bucket_count
13         348         1            3
12         348         1            1
11         348         1            1


CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
Yeah - your code is correct, but you need a UNIQUE index on whatever it is that you intend to use to trigger the duplicate. Based on your question, that is likely to be a composite UNIQUE index on BOTH the client_id and bucket_id. The table records you've shown above indicates that you don't have that (you've got more than one record with the same client_id & bucket_id)
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
You'd need to run something like this:

ALTER TABLE `tbl_bucket` 
ADD UNIQUE INDEX `ix_client_bucket` (`client_id`, `bucket_id`);

Open in new window

Author

Commented:
Hi, based on this each client can have up to six records (no more) of those six the bucket_count would change.
if ($_GET['package']==1) { $amount = 7500; $package_name = "Individual Therapy - Pay As You Go"; $plan_name=""; }
if ($_GET['package']==2) { $amount = 8000; $package_name = "Couples Therapy - Pay As You Go"; $plan_name=""; }
if ($_GET['package']==3) { $amount = 6500; $package_name = "Group Therapy - Pay As You Go"; $plan_name=""; }

if ($_GET['package']==4) { $amount = 28000; $package_name = "Individual Therapy - Monthly"; $plan_name="price_1Hi1OAEkBUYaFdGgvRLrMLvI"; }
if ($_GET['package']==5) { $amount = 30000; $package_name = "Couples Therapy - Monthly"; $plan_name="price_1Hi1PdEkBUYaFdGgKp4s1M3a"; }
if ($_GET['package']==6) { $amount = 24000; $package_name = "Group Therapy - Monthly"; $plan_name="price_1Hi1QAEkBUYaFdGgUJo5Fyom"; }

Author

Commented:

CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
That table shot you've shown looks correct - you have no duplicate records based on the client_id / bucket_id combination. So, to get the query to work, you need to let the table know what is considered a UNIQUE KEY so that it knows whether to run the INSERT part or the UPDATE part, and that key is the UNIQUE key that you add to your table containing BOTH the client_id and the bucket_id. This would prevent a record being added with the same client_id/bucket_id combination and trigger the ON DUPLICATE KEY UPDATE part of your query (instead of the INSERT part)

Author

Commented:
Not sure where this would go...
BOTH the client_id and the bucket_id  Do I need to wrap the insert in an IF statement?
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
When you create an INDEX, you specify what columns that INDEX is on, so as the code above shows, we just add it to BOTH columns:

ALTER TABLE `tbl_bucket` 
ADD UNIQUE INDEX `ix_client_bucket` (`client_id`, `bucket_id`);

Open in new window

Run that SQL in your PHPMyAdmin (or DB client of choice). We're adding a UNIQUE INDEX called ix_client_bucket (call it whatever you like) to the tbl_bucket table and including 2 columns in the index - client_id & bucket_id

Author

Commented:
HMM. I don't think I did this right as it's not showing.

CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
What do you mean by 'it's not showing'. The Index tab of the above screen shot clearly shows that you have a UNIQUE index on the client_id/bucket_id columns.

Easiest way to test it is to manually try adding a record using a client_id/bucket_id combination that already exists in the table

Author

Commented:
:)  Here in Navicat I don't see it.  Am I not suppose to?

CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
That screenshot is just showing records from your table - the INDEX is part of the structure (schema). The view of the structure in PHPMyAdmin shows you have the index in place, so it all looks good.

Have you tried running your INSERT/UPDATE query yet ?

Author

Commented:
Okay it's inserting but not properly...

Author

Commented:
From this page I did this..
Then I ran it again but it did not increment by 1 either
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
First off - test your database outside of your code. Run a query directly in PHPMyAdmin:

INSERT INTO tbl_bucket (client_id, bucket_id, bucket_count)
VALUES (348, 1, 1)
ON DUPLICATE KEY UPDATE bucket_count = bucket_count + 1
;

Open in new window

Then check your database. Run it a few times and keep checking your database. It should update the bucket_count by 1 each time on the 348/1 key combination.
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
From your screenshot above it looks like it's ran the query 3 times with a bucket_id of 1 and a client_id of 3 - incrementing the bucket_count each time

Author

Commented:
Its saying this..

Author

Commented:
It increased number 13...

CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
OK ... and what do the records in your table now look like ??
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
That's not showing an increase from your previous screen shot. Exactly the same data !!

Author

Commented:

Author

Commented:
It increased by 2.  I ran the query twice so this is right
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
Right - perfect ! So you know the INDEX on your table is working correctly and the SQL you have in your code is working correctly. It seems that everything is working as expected.

Like I said, it seems that your code is working fine - just maybe not with the client_id that you thought you expected

Author

Commented:
Yes. It is but the trick now is to increase it by the number that is associated with the package.
if ($_GET['package']==1) { $amount = 7500; $package_name = "Individual Therapy - Pay As You Go"; $plan_name=""; }
if ($_GET['package']==2) { $amount = 8000; $package_name = "Couples Therapy - Pay As You Go"; $plan_name=""; }
if ($_GET['package']==3) { $amount = 6500; $package_name = "Group Therapy - Pay As You Go"; $plan_name=""; }

if ($_GET['package']==4) { $amount = 28000; $package_name = "Individual Therapy - Monthly"; $plan_name="price_1Hi1OAEkBUYaFdGgvRLrMLvI"; }
if ($_GET['package']==5) { $amount = 30000; $package_name = "Couples Therapy - Monthly"; $plan_name="price_1Hi1PdEkBUYaFdGgKp4s1M3a"; }
if ($_GET['package']==6) { $amount = 24000; $package_name = "Group Therapy - Monthly"; $plan_name="price_1Hi1QAEkBUYaFdGgUJo5Fyom"; }
The first three would increase it by 1  the last three by 4

Author

Commented:
I need the amounts there as its for the credit card payment...do I need an AND added to this?  And I have the QTY to consider as well..

CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
OK - I'm lost. Why does the first three increase by 1 and the last 3 increase by 4? No idea where Qty and Amount come into play on this. The query I've provided will update an existing record by adding 1 to the existing bucket count - you can change that to whatever you want:

INSERT INTO tbl_bucket (client_id, bucket_id, bucket_count)
VALUES (348, 1, 1)
ON DUPLICATE KEY UPDATE bucket_count = bucket_count + 7;

Author

Commented:
Okay.  It works like this.  The client selects a package.  The individual ones are 1 session  the monthly are 4 sessions.  In addition to that they can select anywhere up to 10 individual sessions which would ean the number would increase by 10 or whatever qty they select.  Is this clearer?
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
OK. A litte clearer, but not really related to your original question.

Couple of points - in your code above, when you create a new record, you insert a bucket_count of 1 - why ? If the bucket_count is related to the package and the quantity, then surely 1 doesn't make any sense.

You're going to have to run some logic in your code to read in the package id, decide whether that means 1 or 4, and then add the quantity to that - this will give you a total number to increase by (or to INSERT for new records). Then use that variable in your query.

So, for example, is a client chooses a month package (let's say ID of 5 and a quantity of 10, then you'll probably want to INSERT a new record with a bucket_count of 14, and UPDATE an existing record with an addition of 14.

Author

Commented:
Yes, Chris.  I will play around with it and perhaps start a new post?
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
OK. It's not entirely clear from your code where you getting the qty from, so I can't give you answer, but something like this might give you some ideas:

$total = in_array($_GET['package'], [1, 2, 3]) ? 1 : 4;
$total += (int)($_GET['qty'] ?? 0);

$sql = <<<EOT
INSERT INTO tbl_bucket (client_id, bucket_id, bucket_count)
VALUES (:client, :bucket, :total)
ON DUPLICATE KEY UPDATE bucket_count = bucket_count + $total
EOT;

$stmt = $db->prepare($sql);
$stmt->execute([
    'client' => $_SESSION['the_client_id'],
    'bucket' => $_GET['package'],
    'total' => $total,
]);

Open in new window

Author

Commented:
Thank you Chris.  I will play around with it.  I have to go into a meeting now.  Working on it.

Author

Commented:
Something like this?
try{
    $total = in_array($_GET['package'], [1, 2, 3]) ? 1 : 4;
   $total += (int)($_GET['qty'] ?? 0);

$sql = <<<EOT
INSERT INTO tbl_bucket (client_id, bucket_id, bucket_count)
VALUES (:client, :bucket, :total)
ON DUPLICATE KEY UPDATE bucket_count = bucket_count + $total
EOT;

$stmt = $db->prepare($sql);
$stmt->execute([
    'client' => $_SESSION['the_client_id'],
    'bucket' => $_GET['package'],
    'total' => $total,
]);
   
    
        $stmt->execute($data);
        $client_id = $pdo->lastInsertId();
    
    } else {
        die("Could not prepare Statement");
    }
}catch(Exception $e){
    echo $e->getMessage();
}
Does not UPDATE nor INSERT...
Checks if a record exists for client_id and bucket_id
If no record exists then it INSERTS a new record
   client_id, bucket_id, bucket_number (bucket_number is either 1 (if 1,2, or 3) or 4 (if 4, 5 , or 6) X QTY)
If a record exists then it UPDATES the bucket_number field.  Increases by either 1 (if 1,2, or 3) or 4 (if 4, 5 , or 6) X QTY) 
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
OK. Your code looks a little off. Make sure you have error reporting turned on ALWAYS WHEN DEVELOPING !

You're running execute() twice - once with a non-existant $data variable, and you have an else statement in there but no opening if.

Can't really see why you're assigning the last insert ID to Client ID either - you not creating a new Client record - you're creating a new Bucket record !

You still haven't confirmed where you're getting the quantity from. My code above assumes it's coming from the query string : ?package=1&qty=7

While testing, I would recommend you dump out some data, just to make sure everything is going according to plan. Run this code as it is nad see what you get. Ig gets the qty from the querystring, but if it doesn't exist, it sets a default qty of 1. It then mulitplies the qty with the package value (1 or 4) to give you a total

try {
    $package = $_GET['package'];
    $qty = (int)($_GET['qty'] ?? 1);

    $total = in_array($package, [1, 2, 3]) ? 1 : 4;
    $total *= $qty;

    $sql = <<<EOT
        INSERT INTO tbl_bucket (client_id, bucket_id, bucket_count)
        VALUES (:client, :bucket, :total)
        ON DUPLICATE KEY UPDATE bucket_count = bucket_count + $total
    EOT;

    $data = [
        'client' => 1,
        'bucket' => $package,
        'total' => $total,
    ];

    var_dump($total, $sql, $data);

    $stmt = $db->prepare($sql);
    $stmt->execute($data);
} catch (\Throwable $error) {
    echo $error->getMessage();
}

Open in new window

Author

Commented:
Error codes on...nothing coming back.  No INSERT no UPDATE.
<?php
include("../stripe/init.php");
\Stripe\Stripe::setApiKey("sk_test_51HhyPBEkBUYaFdGgV8x4CRqLQEk0PG5Gt3viA7sO4vCpB9QN7ErPoR7zakC3fNL5mFsfkqDdIFRVascIReHauTHE00Vj6PfRQ4");

// Get the credit card details submitted by the form
$tokenid = $_POST['tokenid'];

if ($_GET['package']==1) { $amount = 7500; $package_name = "Individual Therapy - Pay As You Go (Quantity: ".$_GET['quantity'].")"; $plan_name=""; }
if ($_GET['package']==2) { $amount = 8000; $package_name = "Couples Therapy - Pay As You Go (Quantity: ".$_GET['quantity'].")"; $plan_name=""; }
if ($_GET['package']==3) { $amount = 6500; $package_name = "Group Therapy - Pay As You Go (Quantity: ".$_GET['quantity'].")"; $plan_name=""; }

if ($_GET['package']==4) { $amount = 28000; $package_name = "Individual Therapy - Monthly"; $plan_name="price_1Hi1OAEkBUYaFdGgvRLrMLvI"; }
if ($_GET['package']==5) { $amount = 30000; $package_name = "Couples Therapy - Monthly"; $plan_name="price_1Hi1PdEkBUYaFdGgKp4s1M3a"; }
if ($_GET['package']==6) { $amount = 24000; $package_name = "Group Therapy - Monthly"; $plan_name="price_1Hi1QAEkBUYaFdGgUJo5Fyom"; }

$fname=addslashes($_GET['fname']);
$lname=addslashes($_GET['lname']);
$email=addslashes($_GET['email']);
$zip=addslashes($_GET['zip']);

$total_amount = $amount * $_GET['quantity'];

try{
    $pdo = new PDO("mysql:host=localhost;dbname=", "", "");
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch(PDOException $e){
    die("ERROR: Could not connect. " . $e->getMessage());
}



$package = $_GET['package'];
    $qty = (int)($_GET['qty'] ?? 1);

    $total = in_array($package, [1, 2, 3]) ? 1 : 4;
    $total *= $qty;

    $sql = <<<EOT
        INSERT INTO tbl_bucket (client_id, bucket_id, bucket_count)
        VALUES (:client, :bucket, :total)
        ON DUPLICATE KEY UPDATE bucket_count = bucket_count + $total
    EOT;

    $data = [
        'client' => 1,
        'bucket' => $package,
        'total' => $total,
    ];

    var_dump($total, $sql, $data);

    $stmt = $db->prepare($sql);
    $stmt->execute($data);
} catch (\Throwable $error) {
    echo $error->getMessage();
}
    
    } else {
        die("Could not prepare Statement");
    }
}catch(Exception $e){
    echo $e->getMessage();
}


try {
   $stripeInfo = \Stripe\Token::retrieve($tokenid);
   

   if ($_GET['package']<=3) {
      $customer = \Stripe\Customer::create(array(                           
         "source" => $tokenid,
         "email" => $stripeInfo->email,
         "metadata" => array("First Name" => $fname, "Last Name" => $lname, "Email" => $email, "Zip Code" => $zip, "Client ID" => $_GET['users_id']))
      );
      
      $charge = \Stripe\Charge::create([
         "amount" => $total_amount, // amount in pennies                           
         "currency" => "USD",
         "description" => "Arise Payment - " . $package_name,
         "customer" => $customer->id]
      );   
   } else {
      $customer = \Stripe\Customer::create(array(                           
         "source" => $tokenid,
         "email" => $stripeInfo->email,
         "plan" => $plan_name,
         "metadata" => array("First Name" => $fname, "Last Name" => $lname, "Email" => $email, "Zip Code" => $zip, "Client ID" => $_GET['users_id']))
      );
      
      
   }
   
   echo 'success';
   

} catch(\Stripe\Error\Card $e) {
   // The card has been declined
   echo 'There has been a problem with your payment. Please try again or contact us.';

}
?>

Author

Commented:
Its pulling from this HTML...
<form>
<table style="width:100%">
<tr class="blu">
    <th>Type</th>
    <th>Select</th>
    <th>Session</th>
   <th>Quantity</th>
</tr>
  
<tr>
    <td><strong>INDIVIDUALS</strong></td>
    <td><input class="single-checkbox" type="checkbox" id="indy1" name="package" value="1"></td>
    <td>Individual Therapy&nbsp; $75.00 per session</td>
   <td><select name="qty1" id="qty1" class="single-quantity">
     <option selected>Select Quantity...</option>
   <option value="1">1</option>
     <option value="2">2</option>
     <option value="3">3</option>
     <option value="4">4</option>
   <option value="5">5</option>
     <option value="6">6</option>
     <option value="7">7</option>
     <option value="8">8</option>
   <option value="9">9</option>
     <option value="10">10</option>   
   </select></td>   
</tr>
<tr>
    <td></td>
    <td><input class="single-checkbox" type="checkbox" id="indy2" name="package" value="4"></td>
    <td>Individual Therapy&nbsp;$280.00 per month</td>
   <td><select name="qty2" id="qty2" class="single-quantity">
     <option selected>Select Quantity...</option>
   <option value="1">1</option>
   </select></td>  
</tr>

<tr class="blu">
<td colspan=4>&nbsp;</td>
</tr>
   
<tr>
    <td><strong>COUPLES</strong></td>
    <td><input class="single-checkbox" type="checkbox" id="couples1" name="package" value="2"></td>
    <td>Couples Therapy &nbsp;$80.00 per session</td>
   <td><select name="qty3" id="qty3" class="single-quantity">
     <option selected>Select Quantity...</option>
   <option value="1">1</option>
     <option value="2">2</option>
     <option value="3">3</option>
     <option value="4">4</option>
   <option value="5">5</option>
     <option value="6">6</option>
     <option value="7">7</option>
     <option value="8">8</option>
   <option value="9">9</option>
     <option value="10">10</option>   
   </select></td>   
</tr>
<tr>
    <td></td>
    <td><input class="single-checkbox" type="checkbox" id="couples2" name="package" value="5"></td>
    <td>Couples Therapy &nbsp;$300.00 per month</td>
   <td><select name="qty4" id="qty4" class="single-quantity">
     <option selected>Select Quantity...</option>
   <option value="1">1</option>
   </select></td>     
  </tr>

<tr class="blu">
<td colspan=4>&nbsp;</td>      
</tr>         

<tr>
    <td><strong>GROUPS</strong></td>
    <td><input class="single-checkbox" type="checkbox" id="group1" name="package" value="3"></td>
    <td>Group Therapy&nbsp; $65.00 per session</td>
   <td><select name="qty5" id="qty5" class="single-quantity">
     <option selected>Select Quantity...</option>
   <option value="1">1</option>
     <option value="2">2</option>
     <option value="3">3</option>
     <option value="4">4</option>
   <option value="5">5</option>
     <option value="6">6</option>
     <option value="7">7</option>
     <option value="8">8</option>
   <option value="9">9</option>
     <option value="10">10</option>   
   </select></td>   
  </tr>
     <tr>
    <td></td>
    <td><input class="single-checkbox" type="checkbox" id="group2" name="package" value="6"></td>
    <td>Group Therapy&nbsp; $240.00 per month</td>
   <td><select name="qty6" id="qty6" class="single-quantity">
     <option selected>Select Quantity...</option>
   <option value="1">1</option>
   </select></td>  
  </tr>
</table>         
           <button type="button" name="submit" class="btn3" id="payButtonStripe">Submit</button>   
         </div>
         </form>
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
When I said run the code as it is, I meant run it as it is !! We can't track down problems with so much white noise going on. Strip it back to basics and then we get to figure out the problems - at the moment, your code has 4 or 5 different things going on, so you have no way of knowing which part is breaking.

Your HTML form has NO method or action, so it's gonna make a GET request back to itself. This is fine assuming the PHP code is part of the HTML page, but then in your script you're trying to use the POST array which doesn't exist. You're also trying to pull GET keys such as quantity, qty, fname, lname, email, zip which just don't exist in your HTML. You say you've got errors turned on, but nothing's coming back - that's a massive problem because the code you've got should be throwing lots of errors.

First off, if that PHP code is in a different file, then you need to tell your form to submit to that file - you do that with the action property:

<form action="SomePhpScript.php">

For testing purposes, just make sure the code I've sent you is the only code you're running. You'll need to include your DB connection, but nothing else should be going on. I can't help you track down problems if you keep changing the code I provide.

Author

Commented:
Hi Chris sorry.  You mean like this?
<?php


try{
    $pdo = new PDO("mysql:host=localhost;dbname=HMS2020A", "HMS2020", "David@2020");
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch(PDOException $e){
    die("ERROR: Could not connect. " . $e->getMessage());
}



$package = $_GET['package'];
    $qty = (int)($_GET['qty'] ?? 1);

    $total = in_array($package, [1, 2, 3]) ? 1 : 4;
    $total *= $qty;

    $sql = <<<EOT
        INSERT INTO tbl_bucket (client_id, bucket_id, bucket_count)
        VALUES (:client, :bucket, :total)
        ON DUPLICATE KEY UPDATE bucket_count = bucket_count + $total
    EOT;

    $data = [
        'client' => 1,
        'bucket' => $package,
        'total' => $total,
    ];

    var_dump($total, $sql, $data);

    $stmt = $db->prepare($sql);
    $stmt->execute($data);
} catch (\Throwable $error) {
    echo $error->getMessage();
}
    
    } else {
        die("Could not prepare Statement");
    }
}catch(Exception $e){
    echo $e->getMessage();
}



?>
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
Better, but still not quite there. What we're doing here is developing good practice - we're isolating a problem we're trying to solve by removing everything else and focussing on the issue at hand.

Now, in your code above, you've still got the else without an if. You've also got 2 closing catch blocks without an opening try. This is how it should look (and don't forget to turn on error reporting !!)

<?php
error_reporting(E_ALL);
ini_set('display_errors', 1);

try {
    $pdo = new PDO("mysql:host=localhost;dbname=HMS2020A", "HMS2020", "David@2020");
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch(PDOException $e){
    die("ERROR: Could not connect. " . $e->getMessage());
}

try {
    $package = $_GET['package'];
    $qty = (int)($_GET['qty'] ?? 1);

    $total = in_array($package, [1, 2, 3]) ? 1 : 4;
    $total *= $qty;

    $sql = <<<EOT
        INSERT INTO tbl_bucket (client_id, bucket_id, bucket_count)
        VALUES (:client, :bucket, :total)
        ON DUPLICATE KEY UPDATE bucket_count = bucket_count + $total
    EOT;

    $data = [
        'client' => 1,
        'bucket' => $package,
        'total' => $total,
    ];

    var_dump($total, $sql, $data);

    $stmt = $db->prepare($sql);
    $stmt->execute($data);
} catch (\Throwable $error) {
    echo $error->getMessage();
}

Open in new window

Now make sure that whatever you've called that script, you point your form to it with the action property

<form action="your-script.php">

Author

Commented:
Tried it. No errors, no insert, no update....
Login/ kevin@gmail.com  Test@123  Sessions / Buy Sessions
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
When I click the Submit button, I get the Stripe pop-up - WHITE NOISE !!!

Author

Commented:
Chris that's a tough one to dig out.  Not sure how I would even do that.
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION

Author

Commented:
Getting an Error..

This page isn’t working

arise.plus is currently unable to handle this request.

HTTP ERROR 500



CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
OK - which page are you getting this on? I forget to add error handling to the HTML page, so add that at the top:

<?php
error_reporting(E_ALL);
ini_set('display_errors', 1);
?>
<!DOCTYPE html>
<html lang="en">
    <head>
        <meta charset="utf-8">

Open in new window

Author

Commented:
on this page after I select and hit GO
https://arise.plus/CHRISHTML.php
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
Hmmm - post up the full content of CHRISprocess.php
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
Actually - just noticed we call the DB connection $pdo and then use $db later on. Change that:

$stmt = $pdo->prepare($sql);

Author

Commented:
changed..
<?php
error_reporting(E_ALL);
ini_set('display_errors', 1);

$username = "";
$password = "";

try {
    $pdo = new PDO("mysql:host=localhost;dbname=HMS2020A", $username, $password);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch(PDOException $e){
    die("ERROR: Could not connect. " . $e->getMessage());
}

try {
    var_dump($_GET); // see what the form is submitting !

    $package = (int)$_GET['package'];
    $qty = (int)($_GET['quantity'][$package] ?? 1);

    $total = in_array($package, [1, 2, 3]) ? 1 : 4;
    $total *= $qty;

    $sql = <<<EOT
        INSERT INTO tbl_bucket (client_id, bucket_id, bucket_count)
        VALUES (:client, :bucket, :total)
        ON DUPLICATE KEY UPDATE bucket_count = bucket_count + $total
    EOT;

    $data = [
        'client' => 1, // keep this hardcoded for the time being - change it to a different value for testing!
        'bucket' => $package,
        'total' => $total,
    ];

    var_dump($total, $sql, $data);

    $stmt = $pdo->prepare($sql);
    $stmt->execute($data);
} catch (\Throwable $error) {
    echo $error->getMessage();
}

Author

Commented:
Getting this in the console
fetch("https://arise.plus/CHRISprocess.php?package=1&quantity%5B1%5D=1&quantity%5B4%5D=Select+Quantity...&quantity%5B2%5D=Select+Quantity...&quantity%5B5%5D=Select+Quantity...&quantity%5B3%5D=Select+Quantity...&quantity%5B6%5D=Select+Quantity...&go=Go", {
  "headers": {
    "accept": "text/html,application/xhtml+xml,application/xml;q=0.9,image/avif,image/webp,image/apng,*/*;q=0.8,application/signed-exchange;v=b3;q=0.9",
    "accept-language": "en-US,en;q=0.9",
    "sec-fetch-dest": "document",
    "sec-fetch-mode": "navigate",
    "sec-fetch-site": "same-origin",
    "sec-fetch-user": "?1",
    "upgrade-insecure-requests": "1"
  },
  "referrer": "https://arise.plus/CHRISHTML.php",
  "referrerPolicy": "strict-origin-when-cross-origin",
  "body": null,
  "method": "GET",
  "mode": "cors",
  "credentials": "include"
});
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
OK. Not sure what's going on or why you're not getting any error messages. You're gonna need to take this back a step further. Start your process.php script as simple as it gets, with just the following code:

<?php
error_reporting(E_ALL);
ini_set('display_errors', 1);

var_dump($_GET);

Open in new window

Nothing else in there (comment out EVERYTHING else). Now run it, and make sure you're getting a dump of the request data. If you do, then start to add code back in - for example, start by adding in the DB connection stuff - try it again. Keep adding to code until it breaks and you'll then know exactly where the problem is.

Author

Commented:
Hi Chris,  Did as you said.  It worked up until I added the first  "TRY" then it stopped.
<?php
error_reporting(E_ALL);
ini_set('display_errors', 1);

$username = "";
$password = "";

try {
    $pdo = new PDO("mysql:host=localhost;dbname=HMS2020A", $username, $password);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch(PDOException $e){
    die("ERROR: Could not connect. " . $e->getMessage());
}

try {
    var_dump($_GET); // see what the form is submitting !

    //$package = (int)$_GET['package'];
    //$qty = (int)($_GET['quantity'][$package] ?? 1);

    //$total = in_array($package, [1, 2, 3]) ? 1 : 4;
    //$total *= $qty;

    //$sql = <<<EOT
        //INSERT INTO tbl_bucket (client_id, bucket_id, bucket_count)
        //VALUES (:client, :bucket, :total)
       /// 'client' => 1, // keep 
        //ON DUPLICATE KEY UPDATE bucket_count = bucket_count + $total
    //EOT;

   // $data = [this hardcoded for the time being - change it to a different value for testing!
        //'bucket' => $package,
        //'total' => $total,
    //];

   // var_dump($total, $sql, $data);

    //$stmt = $pdo->prepare($sql);
    //$stmt->execute($data);
//} catch (\Throwable $error) {
    //echo $error->getMessage();
}

CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
OK - you'll need to uncomment the final catch block as you can't have a try without a catch. For now also, change the catchable to Exception instead of Throwable

try {

    var_dump($_GET); // see what the form is submitting !

} catch (Exception $error) {
    echo $error->getMessage();
}

Open in new window

Author

Commented:
I removed a line and now I am getting this..   ""this hardcoded for the time being - change it to a different value for testing! ""
array(3) { ["package"]=> string(1) "4" ["quantity"]=> array(6) { [1]=> string(1) "2" [4]=> string(18) "Select Quantity..." [2]=> string(18) "Select Quantity..." [5]=> string(18) "Select Quantity..." [3]=> string(18) "Select Quantity..." [6]=> string(18) "Select Quantity..." } ["go"]=> string(2) "Go" } int(0) string(205) " INSERT INTO tbl_bucket (client_id, bucket_id, bucket_count) VALUES (:client, :bucket, :total) 'client' => 1, // keep ON DUPLICATE KEY UPDATE bucket_count = bucket_count + 0" array(2) { ["bucket"]=> int(4) ["total"]=> int(0) } SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens

CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
Hmmm - a little confused by that, as it appears that you've changed the code I provided. In the example above, that was just a comment after the client value, but then in the last code you posted, you seem to have just dumped it in the main content !!

This is what I provided:

$data = [
    'client' => 1, // keep this hardcoded for the time being - change it to a different value for testing!
    'bucket' => $package,
    'total' => $total,
];

This is what you ended up with !

$data = [this hardcoded for the time being - change it to a different value for testing!
    'bucket' => $package,
    'total' => $total,
];

You need the client in the $data array for this to work. The comment was just to indicate to you that for testing purposes, just leave a hard-coded value (such as 1) in there. You can change that out later on. This is what you need for the $data array:

$data = [
    'client' => 1,
    'bucket' => $package,
    'total' => $total,
];

Author

Commented:
Not sure how that happened!  Fixed..Getting this.
array(3) { ["package"]=> string(1) "1" ["quantity"]=> array(6) { [1]=> string(1) "7" [4]=> string(18) "Select Quantity..." [2]=> string(18) "Select Quantity..." [5]=> string(18) "Select Quantity..." [3]=> string(18) "Select Quantity..." [6]=> string(18) "Select Quantity..." } ["go"]=> string(2) "Go" } int(7) string(197) " INSERT INTO tbl_bucket (client_id, bucket_id, bucket_count) VALUES (:client, :bucket, :total) 'client' => 1, ON DUPLICATE KEY UPDATE bucket_count = bucket_count + 7" array(3) { ["client"]=> int(1) ["bucket"]=> int(1) ["total"]=> int(7) } SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''client' => 1, ON DUPLICATE KEY UPDATE bucket_count = bucket_count + 7' at line 3
<?php
error_reporting(E_ALL);
ini_set('display_errors', 1);

$username = "";
$password = "";

try {
    $pdo = new PDO("mysql:host=localhost;dbname=HMS2020A", $username, $password);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch(PDOException $e){
    die("ERROR: Could not connect. " . $e->getMessage());
}

try {
    var_dump($_GET);

    $package = (int)$_GET['package'];
    $qty = (int)($_GET['quantity'][$package] ?? 1);

    $total = in_array($package, [1, 2, 3]) ? 1 : 4;
    $total *= $qty;

    $sql = <<<EOT
        INSERT INTO tbl_bucket (client_id, bucket_id, bucket_count)
        VALUES (:client, :bucket, :total)
        'client' => 1, 
        ON DUPLICATE KEY UPDATE bucket_count = bucket_count + $total
EOT;

    $data = [
    'client' => 1,
    'bucket' => $package,
    'total' => $total,
];

    var_dump($total, $sql, $data);

    $stmt = $pdo->prepare($sql);
    $stmt->execute($data);
} catch (\Throwable $error) {
    echo $error->getMessage();
}
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
Now you've thrown a 'client' => 1 bit into the query itself !!!!

$sql = <<<EOT
        INSERT INTO tbl_bucket (client_id, bucket_id, bucket_count)
        VALUES (:client, :bucket, :total)
        'client' => 1,  <!--- WHAT IS THIS BIT ALL ABOUT
        ON DUPLICATE KEY UPDATE bucket_count = bucket_count + $total
EOT;

Author

Commented:
I am so confused.  I have not a clue how that got in there!  Removing.
Better?
<?php
error_reporting(E_ALL);
ini_set('display_errors', 1);

$username = "";
$password = "";

try {
    $pdo = new PDO("mysql:host=localhost;dbname=HMS2020A", $username, $password);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch(PDOException $e){
    die("ERROR: Could not connect. " . $e->getMessage());
}

try {
    var_dump($_GET);

    $package = (int)$_GET['package'];
    $qty = (int)($_GET['quantity'][$package] ?? 1);

    $total = in_array($package, [1, 2, 3]) ? 1 : 4;
    $total *= $qty;

    $sql = <<<EOT
        INSERT INTO tbl_bucket (client_id, bucket_id, bucket_count)
        VALUES (:client, :bucket, :total)
       
EOT;

    $data = [
    'client' => 1,
    'bucket' => $package,
    'total' => $total,
];

    var_dump($total, $sql, $data);

    $stmt = $pdo->prepare($sql);
    $stmt->execute($data);
} catch (\Throwable $error) {
    echo $error->getMessage();
}

Author

Commented:
It inserted!  Number 18run again.  It did not update the count on a second run.

CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
Progress :)

Now run it again, selecting the same package and see if it updates that record

Author

Commented:
Nope not updating.  But definitely Inserting!

CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
That looks like you've selected a different package - the first time it was package 1, the second time it was package 6. It will only update if the client and package match an existing record - that's why we set the UNIQUE key on those two columns.
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
AHHH ! Just noticed that you've changed the code I sent again. I don't really understand why you keep doing this.

The query needs to look like this:

$sql = <<<EOT
        INSERT INTO tbl_bucket (client_id, bucket_id, bucket_count)
        VALUES (:client, :bucket, :total)
        ON DUPLICATE KEY UPDATE bucket_count = bucket_count + $total
EOT;

Open in new window

Author

Commented:
I know.  I was just checking.  I did select package 1 and it did not update.

Author

Commented:
This is what success looks like!

Author

Commented:
Now...I have to fold this back into the original page....or should I wait?
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
Phew - we've finally made some progress :)

Right - the next step is to make sure your script works with the AJAX call you have in your live site. In order to do that we need to make a couple of changes and then call the page slightly differently. First off, in the PHP script, we're gonna change how the quantity is assigned:

Change this line:

$qty = (int)($_GET['quantity'][$package] ?? 1);

To this line:

$qty = (int)($_GET['quantity'] ?? 1);

Once you've done that, you'll want to test it by calling the script directly. This means that instead of using the form and clicking on the Submit button, we're just going to type the address into the browser:

https://arise.plus/CHRISprocess.php?package=8&quantity=2

Run that a couple of times to make sure your DB is inserting / updating correctly.

If it is, then we can move on to the next bit.

Author

Commented:
Looks good!  Number 21  Not sure what the package 0 is though on 20

CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
Good stuff - package=0 would happen if you didn't pass a package into the querystring (or of the value you passed in couldn't be converted to an int). Later on, you might consider adding in some error checking for that, but for now, we can move on to getting this into your live script.

I may need to see that script in order to give you specifics on exactly what to do, but if you're up for it, transfer the code we've been working on into your main script. You probably won't need the var_dumps for the time being, and you may or may not need the DB connection - depending on whether you already include that in your main script file.

You'll probably also want to update the hard-coded Client ID part of the $data array to grab the proper value from wherever you need to.

Author

Commented:
Okay...getting there.  Here is the live script...I will play around with it on my end...
<?php
include("../stripe/init.php");
\Stripe\Stripe::setApiKey("sk_test_51HhyPBEkBUYaFdGgV8x4CRqLQEk0PG5Gt3viA7sO4vCpB9QN7ErPoR7zakC3fNL5mFsfkqDdIFRVascIReHauTHE00Vj6PfRQ4");

// Get the credit card details submitted by the form
$tokenid = $_POST['tokenid'];

if ($_GET['package']==1) { $amount = 7500; $package_name = "Individual Therapy - Pay As You Go (Quantity: ".$_GET['quantity'].")"; $plan_name=""; }
if ($_GET['package']==2) { $amount = 8000; $package_name = "Couples Therapy - Pay As You Go (Quantity: ".$_GET['quantity'].")"; $plan_name=""; }
if ($_GET['package']==3) { $amount = 6500; $package_name = "Group Therapy - Pay As You Go (Quantity: ".$_GET['quantity'].")"; $plan_name=""; }

if ($_GET['package']==4) { $amount = 28000; $package_name = "Individual Therapy - Monthly"; $plan_name="price_1Hi1OAEkBUYaFdGgvRLrMLvI"; }
if ($_GET['package']==5) { $amount = 30000; $package_name = "Couples Therapy - Monthly"; $plan_name="price_1Hi1PdEkBUYaFdGgKp4s1M3a"; }
if ($_GET['package']==6) { $amount = 24000; $package_name = "Group Therapy - Monthly"; $plan_name="price_1Hi1QAEkBUYaFdGgUJo5Fyom"; }

$fname=addslashes($_GET['fname']);
$lname=addslashes($_GET['lname']);
$email=addslashes($_GET['email']);
$zip=addslashes($_GET['zip']);

$total_amount = $amount * $_GET['quantity'];

try{
    $pdo = new PDO("mysql:host=localhost;dbname=HMS2020A", "HMS2020", "David@2020");
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch(PDOException $e){
    die("ERROR: Could not connect. " . $e->getMessage());
}



try{
    $sql = "
    INSERT INTO `tbl_bucket` (
        `client_id`, 
        `bucket_id`, 
        `bucket_count`)
    VALUES (
        :client_id, 
        :bucket_id, 
        :bucket_count)";

    if ($stmt = $pdo->prepare($sql)) {
        $data = [
            'client_id' => $_GET['users_id'],
            'bucket_id' => $_GET['package'],
            'bucket_count' => $_GET['quantity']
        ];
    
        $stmt->execute($data);
        //$client_id = $pdo->lastInsertId();
    
    } else {
        die("Could not prepare Statement");
    }
}catch(Exception $e){
    echo $e->getMessage();
}


try {
   $stripeInfo = \Stripe\Token::retrieve($tokenid);
   

   if ($_GET['package']<=3) {
      $customer = \Stripe\Customer::create(array(                           
         "source" => $tokenid,
         "email" => $stripeInfo->email,
         "metadata" => array("First Name" => $fname, "Last Name" => $lname, "Email" => $email, "Zip Code" => $zip, "Client ID" => $_GET['users_id']))
      );
      
      $charge = \Stripe\Charge::create([
         "amount" => $total_amount, // amount in pennies                           
         "currency" => "USD",
         "description" => "Arise Payment - " . $package_name,
         "customer" => $customer->id]
      );   
   } else {
      $customer = \Stripe\Customer::create(array(                           
         "source" => $tokenid,
         "email" => $stripeInfo->email,
         "plan" => $plan_name,
         "metadata" => array("First Name" => $fname, "Last Name" => $lname, "Email" => $email, "Zip Code" => $zip, "Client ID" => $_GET['users_id']))
      );
      
      
   }
   
   echo 'success';
   

} catch(\Stripe\Error\Card $e) {
   // The card has been declined
   echo 'There has been a problem with your payment. Please try again or contact us.';

}
?>
I'm thinking to just replace everything in bold?
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
Yeah - everything you've highlighted in bold should be replaced by the try/catch block from the code we've been working on.

Author

Commented:
This is the page...not inserting or updating...
<?php
include("../stripe/init.php");
\Stripe\Stripe::setApiKey("sk_test_51HhyPBEkBUYaFdGgV8x4CRqLQEk0PG5Gt3viA7sO4vCpB9QN7ErPoR7zakC3fNL5mFsfkqDdIFRVascIReHauTHE00Vj6PfRQ4");

// Get the credit card details submitted by the form
$tokenid = $_POST['tokenid'];

if ($_GET['package']==1) { $amount = 7500; $package_name = "Individual Therapy - Pay As You Go (Quantity: ".$_GET['quantity'].")"; $plan_name=""; }
if ($_GET['package']==2) { $amount = 8000; $package_name = "Couples Therapy - Pay As You Go (Quantity: ".$_GET['quantity'].")"; $plan_name=""; }
if ($_GET['package']==3) { $amount = 6500; $package_name = "Group Therapy - Pay As You Go (Quantity: ".$_GET['quantity'].")"; $plan_name=""; }

if ($_GET['package']==4) { $amount = 28000; $package_name = "Individual Therapy - Monthly"; $plan_name="price_1Hi1OAEkBUYaFdGgvRLrMLvI"; }
if ($_GET['package']==5) { $amount = 30000; $package_name = "Couples Therapy - Monthly"; $plan_name="price_1Hi1PdEkBUYaFdGgKp4s1M3a"; }
if ($_GET['package']==6) { $amount = 24000; $package_name = "Group Therapy - Monthly"; $plan_name="price_1Hi1QAEkBUYaFdGgUJo5Fyom"; }

$fname=addslashes($_GET['fname']);
$lname=addslashes($_GET['lname']);
$email=addslashes($_GET['email']);
$zip=addslashes($_GET['zip']);

$total_amount = $amount * $_GET['quantity'];

try{
    $pdo = new PDO("mysql:host=localhost;dbname=HMS2020A", "", "");
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch(PDOException $e){
    die("ERROR: Could not connect. " . $e->getMessage());
}



try {
    var_dump($_GET);

    $package = (int)$_GET['package'];
    //$qty = (int)($_GET['quantity'][$package] ?? 1);
   $qty = (int)($_GET['quantity'] ?? 1);

    $total = in_array($package, [1, 2, 3]) ? 1 : 4;
    $total *= $qty;

    $sql = <<<EOT
        INSERT INTO tbl_bucket (client_id, bucket_id, bucket_count)
        VALUES (:client, :bucket, :total)
        ON DUPLICATE KEY UPDATE bucket_count = bucket_count + $total
EOT;

    $data = [
    'client' => $client_id,
    'bucket' => $package,
    'total' => $total,
];

    var_dump($total, $sql, $data);

    $stmt = $pdo->prepare($sql);
    $stmt->execute($data);
} catch (\Throwable $error) {
    echo $error->getMessage();
}


try {
   $stripeInfo = \Stripe\Token::retrieve($tokenid);
   

   if ($_GET['package']<=3) {
      $customer = \Stripe\Customer::create(array(                           
         "source" => $tokenid,
         "email" => $stripeInfo->email,
         "metadata" => array("First Name" => $fname, "Last Name" => $lname, "Email" => $email, "Zip Code" => $zip, "Client ID" => $_GET['users_id']))
      );
      
      $charge = \Stripe\Charge::create([
         "amount" => $total_amount, // amount in pennies                           
         "currency" => "USD",
         "description" => "Arise Payment - " . $package_name,
         "customer" => $customer->id]
      );   
   } else {
      $customer = \Stripe\Customer::create(array(                           
         "source" => $tokenid,
         "email" => $stripeInfo->email,
         "plan" => $plan_name,
         "metadata" => array("First Name" => $fname, "Last Name" => $lname, "Email" => $email, "Zip Code" => $zip, "Client ID" => $_GET['users_id']))
      );
      
      
   }
   
   echo 'success';
   

} catch(\Stripe\Error\Card $e) {
   // The card has been declined
   echo 'There has been a problem with your payment. Please try again or contact us.';

}
?>
I replace client = 1 as well
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
OK - you'll need to do some debugging. Open up your WebDev console, and keep an eye on the Network tab - run through the process in your web page and once you're done, you should see an AJAX request being made on to your script. Click on it and take a look at the request and response tabs - that should show you what's been sent to the script and what it responded with.

Author

Commented:
Under the response tab..
array(2) {
  ["package"]=>
  string(1) "1"
  ["quantity"]=>
  string(1) "9"
}
int(9)
string(173) "        INSERT INTO tbl_bucket (client_id, bucket_id, bucket_count)
        VALUES (:client, :bucket, :total)
        ON DUPLICATE KEY UPDATE bucket_count = bucket_count + 9"
array(3) {
  ["client"]=>
  NULL
  ["bucket"]=>
  int(1)
  ["total"]=>
  int(9)
}
SQLSTATE[23000]: Integrity constraint violation: 1048 Column 'client_id' cannot be null
My guess is that the client_id isn't getting there?
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
Yep - you're not getting the client id. Take a look at your $data dump:

array(3) {
  ["client"] => NULL
  ["bucket"] => int(1)
  ["total"] => int(9)
}

Plus your error message:

SQLSTATE[23000]: Integrity constraint violation: 1048 Column 'client_id' cannot be null

Looking at your code, you have this:

$data = [
    'client' => $client_id,
    'bucket' => $package,
    'total' => $total,
];

but nowhere in your code do you ever set the $client_id variable !

Author

Commented:
Like this?
<?php
include("../stripe/init.php");
\Stripe\Stripe::setApiKey("sk_test_51HhyPBEkBUYaFdGgV8x4CRqLQEk0PG5Gt3viA7sO4vCpB9QN7ErPoR7zakC3fNL5mFsfkqDdIFRVascIReHauTHE00Vj6PfRQ4");

// Get the credit card details submitted by the form
$tokenid = $_POST['tokenid'];

if ($_GET['package']==1) { $amount = 7500; $package_name = "Individual Therapy - Pay As You Go (Quantity: ".$_GET['quantity'].")"; $plan_name=""; }
if ($_GET['package']==2) { $amount = 8000; $package_name = "Couples Therapy - Pay As You Go (Quantity: ".$_GET['quantity'].")"; $plan_name=""; }
if ($_GET['package']==3) { $amount = 6500; $package_name = "Group Therapy - Pay As You Go (Quantity: ".$_GET['quantity'].")"; $plan_name=""; }

if ($_GET['package']==4) { $amount = 28000; $package_name = "Individual Therapy - Monthly"; $plan_name="price_1Hi1OAEkBUYaFdGgvRLrMLvI"; }
if ($_GET['package']==5) { $amount = 30000; $package_name = "Couples Therapy - Monthly"; $plan_name="price_1Hi1PdEkBUYaFdGgKp4s1M3a"; }
if ($_GET['package']==6) { $amount = 24000; $package_name = "Group Therapy - Monthly"; $plan_name="price_1Hi1QAEkBUYaFdGgUJo5Fyom"; }

$fname=addslashes($_GET['fname']);
$lname=addslashes($_GET['lname']);
$email=addslashes($_GET['email']);
$zip=addslashes($_GET['zip']);

$total_amount = $amount * $_GET['quantity'];

try{
    $pdo = new PDO("mysql:host=localhost;dbname=HMS2020A", "HMS2020", "David@2020");
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch(PDOException $e){
    die("ERROR: Could not connect. " . $e->getMessage());
}

$client_id = $_REQUEST['client_id'];

try {
    var_dump($_GET);

    $package = (int)$_GET['package'];
    //$qty = (int)($_GET['quantity'][$package] ?? 1);
   $qty = (int)($_GET['quantity'] ?? 1);

    $total = in_array($package, [1, 2, 3]) ? 1 : 4;
    $total *= $qty;

    $sql = <<<EOT
        INSERT INTO tbl_bucket (client_id, bucket_id, bucket_count)
        VALUES (:client, :bucket, :total)
        ON DUPLICATE KEY UPDATE bucket_count = bucket_count + $total
EOT;


CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
No - you're not submitting the client_id from any forms, so it's not going to exist in the $_REQUEST array !

In your code at the start of this question, you looked to be getting the client from the session:

$_SESSION['the_client_id']

although I couldn't see anywhere that you actually started the session.

I don't know where you get the client id from.

Author

Commented:
Okay I see.  I tried this..no result
$data = [
    'client_id' => $_SESSION['client_id'],
    'bucket' => $package,
    'total' => $total,
It's coming from the edit-buy.php page
Log In kevin@gmail.com  Test@123 / Sessions/ Buy Session
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
The key of the data array needs to be client, NOT client_id - it needs to match the placeholders in the SQL, so this:

$data = [
    'client' => $_SESSION['client_id'],
    'bucket' => $package,
    'total' => $total,
];

Change that and run it again - see what the response tab of the network shows you - that's where any AJAX errors are gonna show.

Author

Commented:
Still coming up NULL
array(2) {
  ["package"]=>
  string(1) "1"
  ["quantity"]=>
  string(1) "8"
}
int(8)
string(173) "        INSERT INTO tbl_bucket (client_id, bucket_id, bucket_count)
        VALUES (:client, :bucket, :total)
        ON DUPLICATE KEY UPDATE bucket_count = bucket_count + 8"
array(3) {
  ["client"]=>
  NULL
  ["bucket"]=>
  int(1)
  ["total"]=>
  int(8)
}
SQLSTATE[23000]: Integrity constraint violation: 1048 Column 'client_id' cannot be null
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
Right - in that case, either you haven't started your session (using session_start()) or your SESSION doesn't contain the client_id key.

In your opening question you had this:

$_SESSION['the_client_id']

and now you're trying to use this:

$_SESSION['client_id']

There's not really much I can do to help you here. I would expect you to know your own app and therefore know where and when you're setting and getting the Client ID.

Author

Commented:
It's coming from the profile.php on the edit-buy.php page.

profile.php
defined('SESSION_ID') or die();
require_once('includes/pdo_connection.php');

// Get the client id - if we don't have one then bounce to the home page.
try {
    $client_id = isset($_SESSION[SESSION_ID]) ? $_SESSION[SESSION_ID] : false;
    if (!$client_id) throw new Exception('Client not logged in');

    $dsn = 'mysql:dbname=' . DB_NAME . ';host=' . DB_SERVER;

    $query = <<< QUERY
SELECT
    `client_name` AS `name`,
    `client_creation_date` AS `creation_date`,
    `client_updation_date` AS `updation_date`,
    `client_street_number` AS `street_number`,
    `client_street` AS `street`,
    `client_apt_number` AS `apt_number`,
    `client_city` AS `city`,
    `client_state` AS `state`,
    `client_zip_code` AS `zip_code`,
    `client_country` AS `country`,
    `client_gender` AS `gender`,
    `client_email` AS `email`
FROM
    `tbl_client` c 
WHERE
    `c`.`client_id` = :client_id
QUERY;

    $db = new PDO($dsn, DB_USER, DB_PASS);
    $stmt = $db->prepare($query);
    $stmt->execute([':client_id' => $client_id]);
    $client = $stmt->fetch(PDO::FETCH_OBJ);
    if (empty($client)) throw new Exception('Client not found');

} catch (PDOException $e) {
    error_log('EXCEPTION: ' . __FILE__ . ' ' . $e->getMessage());
    header('location: ' . HOME_PAGE);
}


CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
Right - ok. If you look at your code, you have this:

$_SESSION[SESSION_ID]

SESSION_ID is a pre-defined constant from somewhere else in your app. That constant value is used as the key to retrieve some info from the session, so you need to make sure you access the session using that key. You also need to make sure you're starting the session. You usually do this with the session_start() function at the beginning of your code.

You've got this at the start of the profile.php page:

defined('SESSION_ID');

That tells me that you're running some other code BEFORE what you've just shown, and somewhere in that code will be a line that defines that constant - something like this:

define("SESSION_ID", "SomeValueGoesHere");

That value - SomeValueGoesHere - acts as the key that's used in your session to define the client id (seems a little odd, but there you go!)

If you're struggling to find your way around your app, it might just be easier to dump the session and see what you've got. In a file on it's own, run the following:

<?php
session_start();
var_dump($_SESSION);

Open in new window

That will show you all the key/value pairs that you have registered in your session.

Author

Commented:
I ran it alone..Got this.
array(0) { } 
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
OK - so you don't have anything in your session !!

Only you know how you track the client id within your own app. If it revolves around a client logging in, then you'll want to start looking at the log in scripts. Surely, you must have some idea on how you use a client id

Author

Commented:
Chris.  I have been using client_id for all of the insert and update php files.  I can't understand how this is different from the others...
Found this...
// Get the client id - if we don't have one then bounce to the home page.
try {
    $client_id = isset($_SESSION[SESSION_ID]) ? $_SESSION[SESSION_ID] : false;
    if (!$client_id) throw new Exception('Client not logged in');


CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
Right - so try that:

try {
    var_dump($_GET);

    $package = (int)$_GET['package'];
    $qty = (int)($_GET['quantity'] ?? 1);

    $total = in_array($package, [1, 2, 3]) ? 1 : 4;
    $total *= $qty;

    $client_id = isset($_SESSION[SESSION_ID]) ? $_SESSION[SESSION_ID] : false;
    if (!$client_id) throw new Exception('Client not logged in');

    ...

    $data = [
        'client' => $client_id,
        'bucket' => $package,
        'total' => $total,
    ];

    ...

Open in new window

It seems to me though that unless you're loading up the session and the file that defines SESSION_ID, this might fail.

Author

Commented:
Nothing...
try {
    var_dump($_GET);

    $package = (int)$_GET['package'];
    //$qty = (int)($_GET['quantity'][$package] ?? 1);
   $qty = (int)($_GET['quantity'] ?? 1);

    $total = in_array($package, [1, 2, 3]) ? 1 : 4;
    $total *= $qty;
   
   $client_id = isset($_SESSION[SESSION_ID]) ? $_SESSION[SESSION_ID] : false;
    if (!$client_id) throw new Exception('Client not logged in');

    $sql = <<<EOT
        INSERT INTO tbl_bucket (client_id, bucket_id, bucket_count)
        VALUES (:client, :bucket, :total)
        ON DUPLICATE KEY UPDATE bucket_count = bucket_count + $total
EOT;

    $data = [
    'client' => $client_id,
    'bucket' => $package,
    'total' => $total,
];

    var_dump($total, $sql, $data);

    $stmt = $pdo->prepare($sql);
    $stmt->execute($data);
} catch (\Throwable $error) {
    echo $error->getMessage();
}
Won't it come from the edit-buy.php file..?

Author

Commented:
I just tried hard coding it and nothing happened  Could it be a different problem?
$data = [
    'client' => 3,
    'bucket' => $package,
    'total' => $total,
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
David - you've got to start thinking about this stuff like a developer.

What exactly does 'nothing' mean ? What did your response tab show you ? Where are you starting the session ? Are you loading up the file that defines the 'SESSION_ID' constant ?

You've said that you've used that $client_id code in other pages, but I'm certain that you've fired off some other code BEFORE using it - either directly or by including / requiring some other file.

What I would suggest you do here is go back to the isolated 2 files we were working on earlier and debug from there - at least that way, you're removing the white noise we've spoken about, so it will be a direct request, rather than an AJAX request - you'll be able to see exactly what's going on.
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
David - very rarely does 'nothing' happen !! Check your WebDev response tab. You're trying to develop and debug this script whilst using an AJAX request, so by definition, everything happens in the background. This is the reason I keep telling you to develop in isolation - piece by piece - it will make your life infinitely easier to do that.

Author

Commented:
Ok. I understand..
array(2) {
  ["package"]=>
  string(1) "1"
  ["quantity"]=>
  string(1) "3"
}
Client not logged in
The bucket_id with 0  is increasing..

CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
Hmmm - I've no idea what's going on anymore. That table looks like you've got a client ID of 1 and a bucket ID of 0, which just doesn't make any sense - the data you've just dumped shows a bucket_id of 1 and NO CLIENT ID. And I don't get how your database is even updating at all because you're throwing a 'Client Not Logged in' exception, so the query execution should be skipped.

Go back to the isolated files.

Find out exactly where and how you're starting the session. Find out exactly where and how you're defining the SESSION_ID constant. Until you can figure that out, you can't move forward. You absolutely MUST be running some code somewhere that starts the session - without that, your entire application will fail - your users can't login and you can't track them - So I know for a FACT that it must be happening somewhere.

Assuming you're using a decent IDE, search your entire codebase for session_start(). Search it for SESSION_ID - you've got to find out how you're managing your client

Author

Commented:
Its working...!  I added this to the page...
require_once('includes/common.php');
require_once('includes/secure.php');
require_once('includes/profile.php');
require_once('includes/method.php');
Question.  We switched this..
$package = (int)$_GET['package'];
    //$qty = (int)($_GET['quantity'][$package] ?? 1);
   $qty = (int)($_GET['quantity'] ?? 1);
Keep this? Correct?
$qty = (int)($_GET['quantity'] ?? 1);
And not this..
//$qty = (int)($_GET['quantity'][$package] ?? 1);
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
Finally !! So now you're including the files that set up your session and allow you to access the Client ID ! Thank you :)

Yes - keep this:

$package = (int)$_GET['package'];
$qty = (int)($_GET['quantity'] ?? 1);

Author

Commented:
Kept!  Thank you.!