Assign shirt number to new cricketer – PHP PDO MySQL

Suppose you have a database where data of all players of any sports team is stored. Every player has a name and a shirt number. We will create a program that will generate a new number for new player. We will make sure the new number is not already assigned to any player. We will keep generating new numbers until a unique number is found.

First, create a table “players” in your database by running the following SQL query.

1
2
3
4
5
CREATE TABLE IF NOT EXISTS players2 (
    id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    shirt_number INTEGER(11) NOT NULL
);

Then, create a file named “assign-shirt-number-to-new-player.php” and write the following code in it:

1
2
3
4
// connect with database
$pdo = new PDO("mysql:host=localhost; dbname=test;", "root", "", [
    PDO::ATTR_PERSISTENT => true
]);

This will make a persistent connection with the database. We are using PHP PDO because it has built-in support for SQL injection. After that, you need to fetch all the players already saved in the database.

1
2
3
4
// fetch all the players from the database
$statement = $pdo->prepare("SELECT * FROM players");
$statement->execute([]);
$rows = $statement->fetchAll(PDO::FETCH_OBJ);

Then we will set the range of numbers in which we want to generate a new number.

1
2
// set number of range for shirt numbers
$number_range = 10;

This will generate numbers between 0 and 10. After that, we will create a recursive function that will keep generating a new number unitl a unique shirt number is found.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
// recursive function to return the new shirt number
function get_code()
{
    // define global variables to access them inside function
    global $rows, $number_range;
 
    // if all the numbers are taken, then return -1
    if (count($rows) >= $number_range)
        return -1;
 
    // generate a random number
    $code = rand(0, $number_range);
 
    // check if it already exists in the database
    foreach ($rows as $row)
    {
        if ($row->shirt_number == $code) // if number already exists, then start recursion i.e. generate another number
        return get_code();
    }
 
    // if number not already exists, then return it
    return $code;
}

We need to call this function once to start checking for new unique shirt number.

1
2
// initial call to recursive function
$code = get_code();

There is a possibility that all the numbers from 0 to 10 are already taken, in this case, we will display an error message.

1
2
3
4
5
6
// if all numbers are taken, display an error
if ($code == -1)
{
    echo "<p>All numbers are taken.</p>";
    exit;
}

If everything worked fine, we will have a new unique shirt number in our $code variable. We can simply insert it in our database.

1
2
3
4
5
6
7
8
9
10
11
12
// name of new player
$name = "Adnan";
 
// if shirt number not taken, then insert in database
$statement = $pdo->prepare("INSERT INTO players (name, shirt_number) VALUES (:name, :code)");
$statement->execute([
    ":name" => $name,
    ":code" => $code
]);
 
// display the new shirt number
echo "<p>New code: " . $code . "</p>";

Complete code

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
<?php
 
// connect with database
$pdo = new PDO("mysql:host=localhost; dbname=test;", "root", "root", [
    PDO::ATTR_PERSISTENT => true
]);
 
// fetch all the players from the database
$statement = $pdo->prepare("SELECT * FROM players");
$statement->execute([]);
$rows = $statement->fetchAll(PDO::FETCH_OBJ);
 
// set number of range for shirt numbers
$number_range = 10;
 
// recursive function to return the new shirt number
function get_code()
{
    // define global variables to access them inside function
    global $rows, $number_range;
 
    // if all the numbers are taken, then return -1
    if (count($rows) >= $number_range)
        return -1;
 
    // generate a random number
    $code = rand(0, $number_range);
 
    // check if it already exists in the database
    foreach ($rows as $row)
    {
        if ($row->shirt_number == $code) // if number already exists, then start recursion i.e. generate another number
        return get_code();
    }
 
    // if number not already exists, then return it
    return $code;
}
 
// initial call to recursive function
$code = get_code();
 
// if all numbers are taken, display an error
if ($code == -1)
{
    echo "<p>All numbers are taken.</p>";
    exit;
}
 
// name of new player
$name = "Adnan";
 
// if shirt number not taken, then insert in database
$statement = $pdo->prepare("INSERT INTO players (name, shirt_number) VALUES (:name, :code)");
$statement->execute([
    ":name" => $name,
    ":code" => $code
]);
 
// display the new shirt number
echo "<p>New code: " . $code . "</p>";