I have a database with a table 'customer'. In this row most of the customers have a value or number. I'm trying to get the highest number so the next one who registers will get the highest number + 1. The row 'usr' is varchar(255).
It was working with the following code:
$query = "SELECT MAX(usr) AS usr FROM customer";
$result = mysql_query($query);
$record = mysql_fetch_assoc($result);
$maxID = $record['id'];
$nieuwID = $maxID + 1;
When I checked today I saw that the two latest registers both had number 10. When I checked my query it was returning 9. Why is it returning 9 and not 10? How can I fix this so my registers will have a unique number?
usr should be integer. alter the table field structure
Don't store numbers as character strings. You are getting a string maximum. For a numeric maximum, convert it to a number:
select max(usr + 0) as usr
from customer
But, as recommended in a comment, if you want an auto-incrementing field, use auto_increment and define the column to be a bona fide number.
You can not use MAX() with varchar field. if you want to use MAX() then you have to convert that field to integer instead of varchar.
Any operation which required addition, subtraction, avg, max etc. will not work on varchar field type
OK firstly @DeDevelopers is right auto_incroment should be inforced to stop this from happening.
I strongly advise using this within you insert or update statement as even though the probability of duplicates is low it can happen ;)
I would also recommend re visiting your table architecture to support @DeDevelopers comment but if not then place somesort of error checking in place or unique key identifier against that table column