Tag Archives: cast

substring()

Task was to insert into the database next invoice number. The problem was that invoice number looked like this MB-13. I needed this account number in two pieces MB-- and the last biggest number. In MySQL there is a function substring() and counting starts from 1.
$latest_invoice = mysql_query('SELECT SUBSTRING(Invoice, 4) FROM table WHERE Invoice LIKE "%MB-%"');
Now how to get the biggest number? Tried this:
$latest_invoice = mysql_query('SELECT MAX(SUBSTRING(Invoice, 4)) FROM table WHERE Invoice LIKE "%MB-%"');
It gave 99. But it was wrong, it had to be 255. Invoice number was in a string-format, but I needed a number. Solutions: 1) - add 0 and MySQL automatically converts it.
$latest_invoice = mysql_query('SELECT MAX(SUBSTRING(Invoice, 4) + 0) FROM table WHERE Invoice LIKE "%MB-%"');
The result was my needed 255. Now I had to add 1 to get the next number, but PHP treats it still as a string. To convert to number, as I needed only full numbers, I used (int).
  
2) Use only number part of the string — I know that first three characters in my string will always be MB-.
$latest_invoice = mysql_query('SELECT MAX(CAST(substring(Invoice, 4, length(Invoice)-3) AS UNSIGNED)) FROM table WHERE Invoice LIKE "%MB-%"');
and in PHP only add 1: