PHP and MySQL - generate an account number from an integer from a database

Question

I need to generate an account number from an integer number of a table with an automatically increasing identifier of the database in which user purchases are stored.

An example database of table accounts:

Gender in the invoice format does one of two ways.

Example 1: the number of invoices without a prefix:

0000001 | 0000002 | 0000003 | 0000004 | 0000005

Example 2: number of invoices with prefixes:

F-0000001 | F-0000002 | F-0000003 | F-0000004 | F-0000005

Question:

1) ¿What is the best way to do this, you can do directly from MySQL or PHP?

2) ¿What is the most suitable format for Example 1 or Example 2?

I appreciate your support, as always!

Answer 1

Thanks to Gordon Linoff, I could find a way to solve this problem.

I will give an example, maybe someone might be interested.

SQL - invoice without prefix: SELECT id, LPAD(id,7,'0') FROM invoice WHERE id = 1;

Result: 0000001

SQL - invoice with the prefix: SELECT id, CONCAT( 'F-', LPAD(id,7,'0') ) FROM invoice;

Result: F-0000001

Answer 2

Get the last identifier from the database and save it in a PHP variable.

For example, if the last record is 100 , increase it by 1 .

 $last = 100; // This is fetched from database $last++; $invoice_number = sprintf('%07d', $last); 

Finally, the answer to the second question:

 $number = "F-". $number; 

Answer 3

1 - 0000001 | 0000002 | 0000003 | 0000004 | 0000005

$ dbValue = 1; echo $ dbValue = str_pad ($ dbValue, 7, "0", STR_PAD_LEFT); // it will give 0000001;

2 - F-0000001 | F-0000002 | F-0000003 | F-0000004 | F-0000005

$ dbValue = 1; echo $ dbValue = "F -". str_pad ($ dbValue, 7, "0", STR_PAD_LEFT); // it will produce F-0000001;

Answer 4

You can write a good helper function in PHP to use it where you want the account number to be returned in your application. The following helper function can simplify your process.

 function invoice_num ($input, $pad_len = 7, $prefix = null) { if ($pad_len <= strlen($input)) trigger_error('<strong>$pad_len</strong> cannot be less than or equal to the length of <strong>$input</strong> to generate invoice number', E_USER_ERROR); if (is_string($prefix)) return sprintf("%s%s", $prefix, str_pad($input, $pad_len, "0", STR_PAD_LEFT)); return str_pad($input, $pad_len, "0", STR_PAD_LEFT); } // Returns input with 7 zeros padded on the left echo invoice_num(1); // Output: 0000001 // Returns input with 10 zeros padded echo invoice_num(1, 10); // Output: 0000000001 // Returns input with prefixed F- along with 7 zeros padded echo invoice_num(1, 7, "F-"); // Output: F-0000001 // Returns input with prefixed F- along with 10 zeros padded echo invoice_num(1, 10, "F-"); // Output: F-0000000001 

Once you finish writing the helper function, you will not need to use CONCAT MySQL LPAD or CONCAT in your query each time to return an identifier with padding zeros or prefix zeros. If you have global access to an auxiliary function throughout the application, you need to call it only where you want to generate an account number.

Answer 5

Ans 1):

You can do this with PHP (directly through concat or using str-pad ), as well as with MySQL ( LPAD ) as well

But in my opinion, you should do this with PHP so that you can change it to suit your requirements, for example. increment zeros according to the number of identifiers in the DB. Therefore, in order not to modify SQL queries and make them heavy.

Ans 2): You can use both formats, but if you want to clarify a specific user or something else, use the second format.

I think the second format can give you more information about the data.

Answer 6

You get this solution at this link

https://myphppractice.blogspot.com/2018/12/how-to-create-auto-generated-id-with.html