0

I have a table with a lot of customer information. Up until now, there was a rule for customer numbers when a new customer was entered - 101xx for customers beginning with A, 102xx for B and so on. Now this is obsolete and we shall use just the next available number. As you can imagine the system mentioned above left a lot of gaps between the customer numbers and I am struggling at the moment to find a solution to determine the next available number to fill these gaps via PHP and/or MySQL.

I only can think of iterating through the table starting with 101000 and if the number is found add 1 and start again till one number cannot be found. But this seems very unpractical and also will take a long time.

My second idea was to generate a random number and check if this one exists, if it does repeat with another random number. But this is also not a good solution in my opinion.

Does someone has an idea how I can achieve this and point me to the right direction?

DarkBee
  • 16,592
  • 6
  • 46
  • 58
Mark
  • 69
  • 10
  • What datatype is customer number? And does having gaps really cause an issue or is it just tidier? – P.Salmon Aug 11 '22 at 06:55
  • 1
    See bohemian's answer, you might want to edit the query to pick only one with a limit 1 – Salketer Aug 11 '22 at 06:58
  • @P.Salmon The customer numbers are int. No, the gaps don't cause any issues. My boss wants these gaps to be filled. ATM my colleagues do that manually with an Excel sheet which is really cumbersome and my boss wants the next available customer number automatically filled in the form. – Mark Aug 11 '22 at 07:00

0 Answers0