Assign shirt number to new cricketer – PHP PDO MySQL

by Posted on

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.

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:

// 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.

// 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.

// 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.

// 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.

// 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.

// 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.

// 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

<?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>";
Post Views: 175

Published by adnanafzal565