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>" ; |