How can I use the database to store an integer so that it can be incremented instantly each time it is accessed

[HowardR]

I have written an improvement to the PayPal Advanced payment module for OSCommerce version 2.3.3 and have installed it in my own stores by "cheating." Instead of storing it in the MySql database, I stored the needed integer value in a disk file. Each time I access the integer, I immediately increment it and immediately write the incremented value back to the disk file.

 

I assign the integer value to the "ORDERID" variable fed to PayPal during the pre-processing stage of the order. This simple fix solves a problem present in all payment modules: How do you give each order a unique pre-processing identifier?

 

The usual solutions each have their problems:

1. Updating the order number (orders_id) before pre-processing causes order numbers of payment failures to be skipped, which makes it harder to catch shipping mistakes.
   
2. Not providing PayPal with a unique ORDERID very occasionally leads to "hiccups" when a customer clicks the submission button more than once and then gets charged more than once for an order.
   

I would like to make my improvement available to the PayPal community. But to do so, I will have to store the integer in the database, instead of on disk. Unfortunately, I don't know my way around MySql well enough to program this.

 

Could anybody tell me how to do this simple task? How can I use the database the same way that I currently use a disk file to store an integer so that it can be incremented instantly each time it is accessed?

 

If possible, I would like to put all of the new code, including the code that initializes the integer in the database, into the one php file that I am changing.

 

Howard

[tgely]

How do you save a value like auto increment?

 

oscommerce PHP code:

tep_db_query("update `counter_table` set counter=counter+1 WHERE id=1");

 

Create sql table:

CREATE TABLE `counter_table` (
`id` INT NOT NULL AUTO_INCREMENT ,
`counter` INT NOT NULL ,
PRIMARY KEY ( `id` ) ) ENGINE = MYISAM

[Jack_mcs]

You would need to create a table in the database and set the field you create to auto-increment. I would think it would be easier to just create a random number and send it. In either case, I've never seen this problem and think, if it does exist, that it could be better handled by just ghosting the submit button once clicked, or something like that.

[HowardR]

Gergely and Jack,

 

Thank you for the suggestion.

 

Could I put in a php command in the php file that asks if the table created by Gergely's code already exists and that then creates it using Gergely's code if it doesn't? If so, what would that command look like?

 

Also, if I use Gergely's code, what would the command be that would access that integer (increment it) and assign it to the php variable $num?

 

Howard

[Bob Terveuren]

Hi

 

For the first bit - you can edit this to your ends - pop it into the function install() in your payment module so it will only check for table exists once and install it if missing.

 

    //new database table
    tep_db_query("CREATE TABLE IF NOT EXISTS `my_data` (
                 `my_id` int(11) NOT NULL auto_increment,
                 `anything_else` int(11) NOT NULL,
                 PRIMARY KEY  (`stripe_id`)
               )   AUTO_INCREMENT=1 ;");

 

Too early in the day for me to think about the query.....

 

How about this though (more than one way to skin a cat)

 

Use a PHP session - not sure exactly which PayPal module you have there but, wherever the order number is getting passed to PayPal add a bit of code that says (it's non osC stuff + pseudo code but you'll get the idea)

 

if (session exists['paypal_check']){session['paypal_check']=session['paypal_check']+1;}

else{session['paypal_check']=1;}

 

Then add the value of session['paypal_check'] to the order id

 

Kill that session in checkout_process (or whereever else the tep_session_unregister code is - may be in the payment module)

[HowardR]

Bob,

 

Thank you very much. You and Gergely have given me all of the parts of the code that are tricky. I will program it. Test it. Then once I am sure it is working fine, offer it as an improvement to my PayPal Payflow Payment module add-on.

 

Howard

[MrPhil]

Won't an auto-increment field only be incremented on the insertion of a new record? That would mean INSERT INTO (plus deleting an older record).

 

Is there any existing field that could be used for this, such as an Order ID? If failed payments, etc. mean that an ID gets skipped and messes up your shipping checks, perhaps you could build a list of failed order IDs to ignore (depends on how you use the order list). It also should be possible to disable "submit" (set disabled attribute in the button HTML, via Javascript?) to prevent a customer from clicking it twice, or set a flag to ignore a second form submission. There ought to be something that can be done to take care of those cases, rather than tracking yet another ID of some sort.