PHP在空白点插入数据

Question

I have the following table:

Now, what I want to do is, perhaps someone want to be in another 'class', then it's number changes according to the class. The classess look like the following;

• 1xx => Initiatie
• 2xx => Recreatie
• 3xx => Toerisme
• 4xx => Sport

where xx represents the number of the team.

So let's take a look at an example.

Team 202, wants to change to Toerisme, in Toerisme, we have the numbers 301, 304, 305 and 306. So the system must know, if someone changes it's class to 3xx, or registers as a new user in that class, that he/she must get the first number (which is 302). The second number would then be 303, but the third will be 307.

So I have to have a way of checking free spots in those numbers.

The code I'm currently using to assign numbers is the following:

public function getTeamNumber($klasse_start_number)
{

    if(self::whereRaw('LEFT(number, 1) = '.$klasse_start_number)->count() <= 0)
    {
        switch ($klasse_start_number) {
            case '1':
            return '101';
            break;
            case '2':
            return '201';
            break;
            case '3':
            return '301';
            break;
            case '4':
            return '401';
            break;
        }
    }

    $latest_number = self::whereRaw('LEFT(number, 1) = '.$klasse_start_number)->orderBy('number', 'DESC')->latest()->first()->number;
    $number = $latest_number + 1;
    return $number;
}

How can I check for those empty spots and assign those to the correct teams?

Answer 1

In your situation, something like this would work. A couple notes about your method.

1. Consider implementing it as a static method since it doesn't rely on the current instance of your model class.
2. Instead of concatenating the parameter for the SQL Query, you should try to use parameterized queries. It's always a good practice to try and prevent SQL injection where you can.

3. The method name you used is a little vague, the one I shared might be a bit overkill, but it's always a good idea to try and be more clear.

   public static function getNextOpenTeamNumberFromStartNumber($klasse_start_number) {
       // first get all of the numbers ordered by the number
       $team_numbers = self::whereRaw('LEFT(number, 1) = ?', [$klasse_start_number])->orderBy('number', 'ASC')->get();
   
       // the expected first number
       $expected_next_number = ($klasse_start_number * 100) + 1;
   
       if(!empty($team_numbers)) {
           // loop through each of the numbers
           foreach($team_numbers as $team_number) {
               if($team_number->number != $expected_next_number) {
                   return $expected_next_number; // if the team number isn't the expected number that means that the expected number is open
               }
               $expected_next_number++;
           }
       }
   
       // if we went through all the numbers and haven't returned yet the expected next number would be open
       return $expected_next_number;
   }