PHP & MySQL - 从数据库中的整数生成发票号
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/33602376/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
PHP & MySQL - Generate invoice number from an integer from the database
提问by Learning and sharing
I need to generate an invoice number from an integer of a table with an auto incrementing ID of the database where the user purchases saved.
我需要从一个表的整数生成一个发票号，并带有用户购买保存的数据库的自动递增 ID。
Example of the table invoice database:
表格发票数据库示例：
The invoice number format floor do one of two ways.
发票编号格式楼做两种方式之一。
Example 1: of the number of invoices without prefix:
例一：无前缀发票数量：
0000001 | 0000002 | 0000003 | 0000004 | 0000005
0000001 | 0000002 | 0000003 | 0000004 | 0000005
Example 2: the number of invoices with prefixes:
示例2：带前缀的发票数量：
F-0000001 | F-0000002 | F-0000003 | F-0000004 | F-0000005
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?
1）？最好的方法是什么，你可以直接从 MySQL 或 PHP 做？
2) ?What is the most appropriate format Example 1 or Example 2?
2) ?什么是最合适的格式示例 1 或示例 2？
I appreciate your support as always!
我一如既往地感谢您的支持！
回答by Learning and sharing
Thanks to Gordon Linoff, I could get a way to solve this.
感谢Gordon Linoff，我可以找到解决这个问题的方法。
I will share an example, perhaps someone may be interested.
我将分享一个例子，也许有人会感兴趣。
SQL - Invoice without prefix:SELECT id, LPAD(id,7,'0') FROM invoice WHERE id = 1;
SQL - 没有前缀的发票：SELECT id, LPAD(id,7,'0') FROM invoice WHERE id = 1;
Result:0000001
结果：0000001
SQL - Invoice with prefix:SELECT id, CONCAT( 'F-', LPAD(id,7,'0') ) FROM invoice;
SQL - 带前缀的发票：SELECT id, CONCAT( 'F-', LPAD(id,7,'0') ) FROM invoice;
Result:F-0000001
结果：F-0000001
回答by Wolverine
You can write a good helper function in PHP to use it wherever you want in your application to return an invoice number. The following helper function can simplify your process.
您可以在 PHP 中编写一个很好的辅助函数，以便在应用程序中的任何位置使用它来返回发票编号。以下辅助函数可以简化您的过程。
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 are done writing the helper function, you don't need to use LPADor CONCATMySQL functions every time in your query to return ID with padding zeros or zeros with prefix. If you have global access to the helper function in the entire application, you only need to invoke it wherever you want to generate an invoice number.
完成辅助函数的编写后，您无需每次在查询中都使用LPAD或CONCATMySQL 函数来返回带有填充零或带前缀的零的 ID。如果您可以全局访问整个应用程序中的辅助函数，则只需在要生成发票编号的任何地方调用它即可。
回答by LOKESH
Fetch last ID from database and store it in a PHP variable.
从数据库中获取最后一个 ID 并将其存储在 PHP 变量中。
For example, if last record is 100, then increment it by 1.
例如，如果最后一条记录是100，则将其增加1。
$last = 100; // This is fetched from database
$last++;
$invoice_number = sprintf('%07d', $last);
Finally, the answer for second question is,
最后，第二个问题的答案是，
$number = "F-". $number;
回答by Sapan Mohanty
1 - 0000001 | 0000002 | 0000003 | 0000004 | 0000005
1 - 0000001 | 0000002 | 0000003 | 0000004 | 0000005
$dbValue = 1; echo $dbValue = str_pad($dbValue, 7, "0", STR_PAD_LEFT); // it will give 0000001;
$dbValue = 1; echo $dbValue = str_pad($dbValue, 7, "0", STR_PAD_LEFT); // 它会给出 0000001;
2 - F-0000001 | F-0000002 | F-0000003 | F-0000004 | F-0000005
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;
$dbValue = 1; echo $dbValue = "F-".str_pad($dbValue, 7, "0", STR_PAD_LEFT); // 它会产生 F-0000001;
回答by Abbas
Ans 1):
答案 1)：
You can do this with PHP(directly by concator use str-pad) as well as with MySQL( LPAD) also
您可以使用 PHP（直接通过concat或使用str-pad）以及 MySQL( LPAD) 执行此操作
But as per my view you should do this by PHP, so that you can change it according to your requirements e.g. extend zeroes as per number of id's in DB.So that not to change SQL queries and make it heavy.
但是根据我的观点，您应该通过 PHP 来执行此操作，以便您可以根据自己的要求进行更改，例如根据 DB 中的 id 数量扩展零。这样就不会更改 SQL 查询并使其变得繁重。
Ans 2): You can use both formats but if you want to be more specific about particular user or any thing else, then use second format.
Ans 2): 您可以使用两种格式，但如果您想更具体地了解特定用户或其他任何内容，请使用第二种格式。
I think second format can give you more information about data
我认为第二种格式可以为您提供有关数据的更多信息
