PHP & MySQL - Generate invoice number from an integer from the database

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

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;

Result: 0000001

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

Result: F-0000001

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.

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 LPAD or CONCAT MySQL 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.

Fetch last ID from database and store it in a PHP variable.

For example, if last record is 100, then increment it by 1.

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

Finally, the answer for second question is,

$number = "F-". $number;