C - Insert
Create a PHP website that connects to a Cities database.
The home page (home.php) should automatically retrieve and display all records from the database when the page loads. The user does not need to complete a form or press a search button. The PHP code runs a SELECT query as soon as the page is opened and displays the results in a table.
The Add City page (add.php) should contain a form that allows the user to enter a city name, country and population. The form should use POST and call itself when submitted using action=”add.php”.
When the form is submitted, PHP should collect the entered data and use an INSERT INTO query to add the new record to the database. An if statement should then check whether the query was successful and display an appropriate message.
The website should allow the user to move between the View Cities and Add City pages.
Task
$sql = "SELECT * FROM city"; $result = mysqli_query($conn, $sql);
$sql = "INSERT INTO city (cityName, country, population)
VALUES ('$cityName', '$country', '$population')";
if (mysqli_query($conn, $sql))
{
echo "<p>City added successfully.</p>";
}
else
{
echo "<p>City could not be added.</p>";
}
CREATE DATABASE cities;
USE cities;
CREATE TABLE city
(
cityID INT AUTO_INCREMENT PRIMARY KEY,
cityName VARCHAR(50),
country VARCHAR(50),
population INT
);
INSERT INTO city (cityName, country, population)
VALUES
('Glasgow', 'Scotland', 635000),
('London', 'England', 8866000),
('Paris', 'France', 2103000),
('Rome', 'Italy', 2755000),
('Madrid', 'Spain', 3332000);