Worksheet No: 2.
Student Name : Yashasvi Tripathi UID : 21MCC2009
Branch : MCA CC & DevOps Section/Group : 21MCD-A/1
Semester : 4th Semester Date of Performance : 05-04-2023
Subject Name : Web Tech. LAB Subject Code : 21CAP-781
1. Aim/Overview of the practical: To study about the integration of SQL along with
PHP.
2. Requirement: VS Code, PHP, HTML, CSS.
3. Outcome: Identify the components of PHP for dynamic web page creation.
4. Theory:
PHP & MySQL: -
• The code demonstrates the use of PHP and MySQL for building dynamic web
application.
• PHP is a server-side scripting language used for web development, and MySQL is a
popular open-source relational database management system used for managing data.
• The code provided is an example of PHP and MySQL integration to create a
database, a table and insert data into it.
• It also demonstrates how to retrieve data from the table and display it in an HTML
table format.
• The code uses various PHP functions such as mysqli_query, mysqli_num_rows, and
mysqli_fetch_assoc to execute SQL queries and fetch data.
• Overall, the code provides a basic understanding of how to work with PHP and
MySQL together to create, manipulate, and retrieve data from databases.
5. Task To be Done:
• Create a new PHP file in order to present its connection to your database server, create
database and create table with different constraints.
6. Step/Flowchart involved to perform practical:
Initiate the Xampp Control Panel:
Open the Apache service & SQL service from the Xampp Control Panel.
Then open the myPhpAdmin from the panel by clicking on the Admin tab.
Initiate the PHP web application
Move inside the Xampp > htdocs >create new folder > create all file.
Web Server
In the web write path for the directory from where registration page is created
localhost/directoryName.
/localhost/php_yashi/exp_6/
About the Code
• The code is of PHP web application that connects to a MySQL database server, creates
a new database and table with some constraints, and displays the contents of the table
on a web page with some basic styling.
• Here's a step-by-step explanation of what the code does:-
i. The [Link] file establishes a connection to the MySQL server using the
mysqli_connect() function. It sets the hostname, username, and password for the
MySQL server, and then attempts to connect to it. If the connection is successful,
it prints a message saying "Connected successfully". If the connection fails, it
prints an error message and exits the script.
ii. The create_table.php file includes the [Link] file and then uses the
mysqli_query() function to create a new database called "myDB". If the database
creation is successful, it prints a message saying "Database created successfully".
If there is an error, it prints an error message and exits the script.
Then, it selects the newly created database using the mysqli_select_db() function,
and creates a new table called "MyGuests" with some columns and data types
using the CREATE TABLE SQL statement.
It also adds two constraints to the table using the ALTER TABLE SQL
statement: a UNIQUE constraint on the "firstname" and "lastname" columns, and
a CHECK constraint on the "email" column to ensure that it contains an "@"
symbol. If the table creation is successful, it prints a message saying "Table
MyGuests created successfully". If there is an error, it prints an error message and
exits the script.
Then the data is inserted into the table by using the query “insert itno MyGuests”,
again it checks wethere the data was inserted into the table else it gives the error
message. Finally, it closes the database connection using the mysqli_close()
function.
iii. The [Link] it first includes the "[Link]" file which contains the
connection details to the database.
Then, it selects the "myDB" database using the mysqli_select_db() function.
After selecting the database, it runs a SELECT query to retrieve all the data from
the "MyGuests" table using mysqli_query() function.
The result set is stored in the $result variable. The code then checks if the query
execution is successful by checking if the $result variable is not false. If the query
fails, the code terminates and displays the error message.
If the query is successful, the code then checks if there are any rows returned by
the query using mysqli_num_rows() function.
If no rows are returned, the code displays "0 results". If there are rows returned,
the code displays the data in an HTML table using mysqli_fetch_assoc() function
to retrieve each row of data as an associative array.
iv. The [Link] file is the main entry point for the web application. It includes the
[Link] file to display the contents of the "MyGuests" table on the web page.
It also includes a basic HTML structure and a link to a separate [Link] file for
styling the HTML table.
v. The [Link] file contains some basic CSS rules for styling the HTML table, such
as setting the border-collapse property to collapse, adding some padding to the
table cells, and adding a background color to the table header row.
vi. When the web application is run in a web browser, it first connects to the MySQL
server using the [Link] file. Then, it creates a new database and table with
some constraints using the create_table.php file. Finally, it displays the contents
of the table on the web page using the [Link] file, and applies some basic
styling to the HTML table using the [Link] file.
7. Code: -
[Link]
<?php
$servername = "localhost";
$username = "root";
$password = "";
// Create connection
$conn = mysqli_connect($servername, $username, $password);
// Check connection
if (!$conn) {
die("Connection failed: " . mysqli_connect_error());
}
echo "Connected successfully<br>";
?>
create_table.php
<?php
include "[Link]";
// Create database
$sql = "CREATE DATABASE myDB";
//check if database was created or not
if (mysqli_query($conn, $sql)) {
echo "Database created successfully<br>";
} else {
echo "Error creating database: " . mysqli_error($conn);
}
// Select database
mysqli_select_db($conn, "myDB");
// Create table
$sql = "CREATE TABLE MyGuests (
id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
firstname VARCHAR(30) NOT NULL,
lastname VARCHAR(30) NOT NULL,
email VARCHAR(50),
reg_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE
CURRENT_TIMESTAMP
)";
//check if the table was created successfully
if (mysqli_query($conn, $sql)) {
echo "Table MyGuests created successfully<br>";
}
else {
echo "Error creating table: " . mysqli_error($conn);
}
// Add constraints
mysqli_query($conn, "ALTER TABLE MyGuests ADD CONSTRAINT UC_Person
UNIQUE (firstname,lastname)");
mysqli_query($conn, "ALTER TABLE MyGuests ADD CONSTRAINT CK_email
CHECK (email LIKE '%@%')");
// Insert data into the table
$sql = "INSERT INTO MyGuests (firstname, lastname, email) VALUES
('John', 'Doe', 'john@[Link]'),
('Jane', 'Doe', 'jane@[Link]'),
('Dave', 'Smith', 'dave@[Link]'),
('Sarah', 'Johnson', 'sarah@[Link]')";
//check if the data was inserted into the table
if (mysqli_query($conn, $sql)) {
echo "Data inserted successfully<br>";
} else {
echo "Error inserting data: " . mysqli_error($conn);
}
mysqli_close($conn);
?>
[Link]
<?php
include '[Link]';
// Select the database
if (!mysqli_select_db($conn, "myDB")) {
die("Database selection failed: " . mysqli_error($conn));
}
// Select data from the table
$sql = "SELECT * FROM MyGuests";
$result = mysqli_query($conn, $sql);
// Check if the query execution is successful
if (!$result) {
die("Query execution failed: " . mysqli_error($conn));
}
// Check if there are any rows returned by the query
if (mysqli_num_rows($result) == 0) {
echo "0 results";
} else {
// Display the data in an HTML table
echo "<table>";
echo "<tr><th>ID</th><th>First Name</th><th>Last
Name</th><th>Email</th></tr>";
while ($row = mysqli_fetch_assoc($result)) {
echo "<tr><td>" . $row["id"] . "</td><td>" . $row["firstname"] .
"</td><td>" . $row["lastname"] . "</td><td>" . $row["email"] . "</td></tr>";
}
echo "</table>";
}
// Close the database connection
mysqli_close($conn); ?>
[Link]
<!DOCTYPE html>
<html>
<head>
<title>MyGuests Table</title>
<link rel="stylesheet" type="text/css" href="[Link]">
</head>
<body>
<h1>MyGuests Table</h1>
<!-- include [Link] file for displaying the table -->
<?php
include "[Link]";
?>
</body>
</html>
[Link]
body {
font-family: Arial, sans-serif;
background-color: #f2f2f2;
}
h1 {
color: #333;
text-align: center;
}
table {
border-collapse: collapse;
width: 100%;
margin: 20px 0;
}
th, td {
text-align: left;
padding: 8px;
border-bottom: 1px solid #ddd;
}
th {
background-color: #4CAF50;
color: white;
}
tr:nth-child(even) {
background-color: #f2f2f2;
}
tr:hover {
background-color: #ddd;
}
8. Output
[Link]
create_table.php
[Link]
[Link]
[Link]
[Link]
create_table.php
Database created successfully
Table MyGuests created successfully
Data inserted successfully
phpMyAdmin localhost
[Link]
Learning outcomes (What I have learned):
1. Connecting to a MySQL server using PHP: The code demonstrates how to establish a
connection to a MySQL server using PHP, and how to handle errors if the connection
fails.
2. Creating a new database and table using PHP: The code shows how to create a new
database and table using PHP and SQL statements, and how to handle errors if the
creation process fails.
3. Adding constraints to a table using PHP and SQL: The code demonstrates how to add
constraints to a table using PHP and SQL, such as UNIQUE and CHECK constraints,
and how to handle errors if the constraint addition process fails.
4. Displaying data from a MySQL database on a web page: The code shows how to
retrieve data from a MySQL database using PHP and SQL, and how to display it on a
web page using HTML and PHP.
Evaluation Grid:
Sr. No. Parameters Marks Obtained Maximum Marks
1. Worksheet Completion 10 marks
2. Demonstrate Performance 5 marks
3. Post Lab Quiz 5 marks