Renumbering the ID field of the wp_posts table

A few weeks ago one of our followers raised a database problem in a Google+ group.

In his post José Costa Ros raised a serious problem that had come up for one of his clients, specifically the online shop TuBabyShop, with the ID field of the wp_posts table, which had reached a total length of 13 digits, which made it impossible for him to take payment for orders through Redsys.

The problem is caused by the strict rules of Redsys when it comes to processing orders, since it makes the use of 4 digits and 8 characters essential, and as the ID field had a code 13 digits long it was materially impossible to complete the orders in WooCommerce, since the code received, the first 4 digits of the 13 of the order number, made it impossible to know which order the payment referred to and so they were always left waiting for payment.

The best solution that can be given to a problem like the one our friend José raised is to renumber the ID field of the wp_posts table of the database, but how?

Solution

To do so, the first thing we did was look at what had happened in the wp_posts table and we discovered that the problem had arisen almost a year earlier when, all of a sudden, it jumped from code 1302 to a completely wild number 13 digits long. The problem most likely arose as a result of some badly executed SQL query or some kind of one-off failure in the MySQL server, although we did not dig much further either since we found no more related problems.

Once the problem had been located we proceeded to create a small PHP script with some SQL queries that we placed in the WordPress root directory and that would automate the task of renumbering the ID field from number 1303 onwards, but not only in the wp_posts table, but in all the other tables of the database that are related to it. Specifically the PHP code we used was this:

Thanks to it we were able to reorder the ID field of the wp_posts table and of all the other tables related to this same field. In addition we modified the table’s auto-increment value to prevent those 13-digit codes from continuing to appear.

The truth is that right now this situation is no longer a problem if you use the WooCommerce Sermepa payment gateway plugin and its new feature, requested by us ourselves. Another day we will publish a couple of functions for getting round this same problem using the new wc_myredsys_merchant_order_encode and wc_myredsys_merchant_order_decode filters.

We hope you find this small PHP script useful and that it gets you out of more than one tight spot.