Introduction
Application with MySQL: After successfully designing and creating a MySQL database, the next step is making it accessible through a web application. In this tutorial, we will learn how to build a PHP web application that connects to a MySQL database, allowing users to interact with data through a graphical interface instead of raw SQL queries.
What is PHP?
Application with MySQL: PHP (Hypertext Preprocessor) is a popular server-side scripting language used for creating dynamic web applications. It runs on the web server, processes user requests, interacts with databases like MySQL, and generates HTML for web browsers.
Why Use PHP for Web Applications?
✅ Open Source & Free – PHP is completely free to use, just like MySQL.
✅ Built-in MySQL Support – PHP provides functions specifically designed for MySQL interactions.
✅ Cross-Platform – Works on Windows, macOS, and Linux.
✅ Only Server Installation Needed – No PHP installation is required on client machines—only a web browser is needed.
✅ Easy Integration – Works well with popular web servers like Apache and Nginx.
Setting Up PHP & MySQL for Web Development
Application with MySQL: To build a PHP web application, you need a development environment that includes:
🔹 Apache (Web Server)
🔹 MySQL (Database Management System)
🔹 PHP (Scripting Language)
Installing XAMPP (Recommended)
Instead of installing each component separately, you can use XAMPP, which includes Apache, MySQL, and PHP in one package.
- Download XAMPP from apachefriends.org
- Install & Start Apache and MySQL
- Place PHP Files in the htdocs Folder
- Open Your Browser and Access localhost
Now, you’re ready to start coding in PHP with MySQL!
Connecting PHP to MySQL
Method 1: Using mysqli_connect()
Application with MySQL: The mysqli_connect() function is a simple way to establish a connection between PHP and MySQL.
php
<?php
$servername = “localhost”;
$username = “root”;
$password = “”;
$database = “myflixdb”;
// Create connection
$conn = mysqli_connect($servername, $username, $password, $database);
// Check connection
if (!$conn) {
die(“Connection failed: ” . mysqli_connect_error());
}
echo “Connected successfully”;
?>
🔹 localhost – The database server (same system where MySQL is installed).
🔹 root – Default username for MySQL in XAMPP.
🔹 password – Default is empty in XAMPP.
🔹 myflixdb – Name of the database we want to connect to.
Method 2: Using PHP Data Object (PDO)
PDO is a more secure and flexible way to connect to MySQL.
php
<?php
try {
$dbconn = new PDO(“mysql:host=localhost;dbname=myflixdb”, “root”, “”);
$dbconn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
echo “Connected successfully”;
} catch(PDOException $e) {
echo “Connection failed: ” . $e->getMessage();
}
?>
✅ Advantages of PDO:
✔ Supports multiple database types (not just MySQL).
✔ More secure, prevents SQL injection.
✔ Better error handling.
Building a Simple PHP Web Application
Application with MySQL: Let’s create a video library management system where users can view and edit movie details stored in the MySQL database.
1️⃣ Creating a Database Table for Movies
Application with MySQL: Run the following SQL query in phpMyAdmin or MySQL Workbench to create a movies table.
sql
CREATE TABLE movies (
id INT(11) AUTO_INCREMENT PRIMARY KEY,
title VARCHAR(255) NOT NULL,
genre VARCHAR(100) NOT NULL,
release_year INT(4) NOT NULL
);
2️⃣ Inserting Sample Data
sql
INSERT INTO movies (title, genre, release_year) VALUES
(‘Inception’, ‘Sci-Fi’, 2010),
(‘The Dark Knight’, ‘Action’, 2008),
(‘Titanic’, ‘Romance’, 1997);
3️⃣ Fetching Data and Displaying It in PHP
Application with MySQL: Create a file movies.php in the htdocs folder with the following PHP code:
php
<?php
$servername = “localhost”;
$username = “root”;
$password = “”;
$database = “myflixdb”;
// Create connection
$conn = mysqli_connect($servername, $username, $password, $database);
// Check connection
if (!$conn) {
die(“Connection failed: ” . mysqli_connect_error());
}
// Fetch movies from database
$sql = “SELECT * FROM movies”;
$result = mysqli_query($conn, $sql);
echo “<h2>Movie List</h2>”;
echo “<table border=’1′><tr><th>ID</th><th>Title</th><th>Genre</th><th>Year</th></tr>”;
while ($row = mysqli_fetch_assoc($result)) {
echo “<tr><td>{$row[‘id’]}</td><td>{$row[‘title’]}</td><td>{$row[‘genre’]}</td><td>{$row[‘release_year’]}</td></tr>”;
}
echo “</table>”;
mysqli_close($conn);
?>
How It Works:
- Connects to MySQL
- Retrieves movie data
- Displays it in an HTML table
4️⃣ Editing Movie Details (Update Feature)
Application with MySQL: To allow users to edit movie details, create a edit_movie.php file.
php
<?php
$conn = mysqli_connect(“localhost”, “root”, “”, “myflixdb”);
if (isset($_POST[‘update’])) {
$id = $_POST[‘id’];
$title = $_POST[‘title’];
$genre = $_POST[‘genre’];
$release_year = $_POST[‘release_year’];
$query = “UPDATE movies SET title=’$title’, genre=’$genre’, release_year=’$release_year’ WHERE id=’$id'”;
mysqli_query($conn, $query);
header(“Location: movies.php”);
}
// Fetch movie details
$id = $_GET[‘id’];
$result = mysqli_query($conn, “SELECT * FROM movies WHERE id=’$id'”);
$movie = mysqli_fetch_assoc($result);
?>
<form method=”post”>
<input type=”hidden” name=”id” value=”<?php echo $movie[‘id’]; ?>”>
<label>Title:</label>
<input type=”text” name=”title” value=”<?php echo $movie[‘title’]; ?>”>
<label>Genre:</label>
<input type=”text” name=”genre” value=”<?php echo $movie[‘genre’]; ?>”>
<label>Year:</label>
<input type=”number” name=”release_year” value=”<?php echo $movie[‘release_year’]; ?>”>
<button type=”submit” name=”update”>Update Movie</button>
</form>
🔹 This form allows users to update movie details.
Final Thoughts
Key Takeaways:
✔ Application with MySQL: PHP & MySQL make it easy to create dynamic web applications.
✔ mysqli_connect() and PDO are two ways to connect PHP to MySQL.
✔ PHP Forms allow users to view, add, and edit database records.
✔ Web applications provide a user-friendly interface for database interactions.
By following this guide, you can build your first PHP web application with MySQL and expand it with features like user authentication, search functionality, and more!
