Working in PHP and SQL
Soccer League
Create a new database: LEAGUE
Create in the DB LEAGUE a new table: TEAMS
Each team has its identifier (CLUB) and its name. Both fields are required.
Code:
CREATE TABLE TEAMS(
CLUB VARCHAR(3) NOT NULL,
COUNTRY VARCHAR(20) NOT NULL,
PRIMARY KEY(CLUB)
)
Insert some records in TEAMS.
Verify that there cannot be duplicated records.
Code:
INSERT INTO TEAMS VALUES("ROM","Romania");
INSERT INTO TEAMS VALUES("ESP","Spain");
INSERT INTO TEAMS VALUES("GER","Germany");
INSERT INTO TEAMS VALUES("ITA","Italy");
(It’s a league with only four teams)
Show all the records
Code:
SELECT * FROM TEAMS
Create in the DB LEAGUE a new table: MATCHES.
Each team has five fields, all required ones.
There cannot be duplicated records (duplicated matches).
Only teams registered are allowed for matches.
Numerical data cannot be negative.
Matches with the same team at home and away are not allowed.
Code:
CREATE TABLE MATCHES(
DAY INT,
LTEAM VARCHAR(3) NOT NULL,
LGOALS INT NOT NULL,
GTEAM VARCHAR(3) NOT NULL,
GGOALS INT NOT NULL,
PRIMARY KEY(LTEAM,GTEAM),
FOREIGN KEY(LTEAM) REFERENCES TEAMS(CLUB),
FOREIGN KEY(GTEAM) REFERENCES TEAMS(CLUB),
CHECK(LTEAM<>GTEAM),
CHECK(LGOALS>=0),
CHECK(GGOALS>=0),
CHECK(DAY>=1 AND DAY<=6)
)
Insert some records into MATCHES.
Verify that incorrect data are not allowed.
Code:
INSERT INTO MATCHES VALUES(1,"ESP",3,"GER",2);
INSERT INTO MATCHES VALUES(1,"ROM",4,"ITA",0);
INSERT INTO MATCHES VALUES(2,"GER",1,"ROM",1);
INSERT INTO MATCHES VALUES(2,"ITA",0,"ESP",5);
Show all the matches ordered by match day.
Code:
SELECT * FROM MATCHES ORDER BY DAY
Delete all records from MATCHES.
Code:
DELETE FROM MATCHES
Verify that the table has become empty, without records.
Insert again some records into your MATCHES table.
Code:
INSERT INTO MATCHES VALUES(1,"ESP",3,"GER",2);
INSERT INTO MATCHES VALUES(1,"ROM",4,"ITA",0);
INSERT INTO MATCHES VALUES(2,"GER",1,"ROM",1);
INSERT INTO MATCHES VALUES(2,"ITA",0,"ESP",5);
Delete only a single match indicating the two teams involved.
Code:
DELETE FROM MATCHES WHERE LTEAM="ITA" AND GTEAM="ESP"
Show all the records and verify that one of them has disappeared.
Create a new folder in C:\xampp\htdocs (FOOTBALL)
In this folder we are going to create a file system to manage our league:
Brief description of the files
● index provides links to four operations:
Welcome to the League
What do you want to do?
New match
Search a match
Show all matches
Show standings
● new_match contains a form with all the data of a new match, which are sent to the file
inserting to register.
● inserting receives the data sent from the previous file and is responsible for inserting the new
record into the MATCHES table.
This file contains a script to connect to the database, therefore it must have a PHP extension.
● search_match contains a form with two teams of a match, which are sent to the file finding.
● finding receives the data sent from the previous file (two teams).
○ If the match exists, all its data will appear.
In this case there will be the possibility of deleting the match using a link to the
following file. (No form necessary here)
○ Otherwise an error message will be displayed.
This file contains a script to connect to the database, therefore it must have a PHP extension.
● deleting receives the data sent from the previous file (two teams) and deletes the match
(record) that both teams have played.
This file contains a script to connect to the database, therefore it must have a PHP extension.
● show_all_matches queries the records in the MATCHES table and displays them on the
page.
This file contains a script to connect to the database, therefore it must have a PHP extension.
● show_standings queries the records in the TEAM table and displays them on the page.
This file contains a script to connect to the database, therefore it must have a PHP extension.
At first we will do it in a very simple way, later we will complicate it little by little.
