Create database and tables in MySQL using the WAMP/XAMPP server
Step 1. Launch the WAMP/XAMPP control panel and start the Apache and MySQL services.
Open phpMyAdmin:
Step 2. Open your web browser and navigate to http://localhost/phpmyadmin. This will open the phpMyAdmin interface.
Step 3. In the phpMyAdmin interface, click on the “Databases” tab. Enter a name for your database (e.g., “student”) in the “Create database” field.
Click the “Create” button to create the database.
Step 4. After creating the database, click on its name in the left sidebar to select it. Click on the “SQL” tab to run SQL queries. Create a “students” Table: In the SQL tab, enter the following SQL query to create the “students” table:
CREATE TABLE students (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(50) NOT NULL,
age INT,
email VARCHAR(50),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);Click the “Go” button to execute the query and create the table.
That’s it! You have created a student database with a “students” table. You can now perform CRUD operations on these tables using PHP and MySQL.
CRUD operations on these tables using PHP and MySQL
You can create a project folder like the following structure.
How to Create a MySQL Database Connection?
The following code acts as the connection between the webpage and the database where the data from the webpage will be stored.
The name of the file is dbconfig.php.
<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "student";
$conn = new mysqli($servername, $username, $password, $dbname);
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
?>How to Create Records?
The name of the file is create-student.php.
<!DOCTYPE html>
<html>
<title>Student Database</title>
<body>
<h2>Student Form</h2>
<form action="" method="POST">
<fieldset>
<legend>Student information:</legend>
Name:<br>
<input type="text" name="name"> <br>
Age:<br>
<input type="text" name="age"> <br>
Email:<br>
<input type="email" name="email"><br>
<br><br>
<input type="submit" name="submit" value="submit">
</fieldset>
</form>
</body>
</html>
<?php
include "dbconfig.php";
if (isset($_POST['submit'])) {
$name = $_POST['name'];
$age = $_POST['age'];
$email = $_POST['email'];
$sql = "INSERT INTO `students`(`name`, `age`, `email`) VALUES ('$name','$age','$email')";
$result = $conn->query($sql);
if ($result == TRUE) {
echo "New record created successfully.";
header('Location: view-student.php');
}else{
echo "Error:". $sql . "<br>". $conn->error;
}
$conn->close();
}
?>
How to View Records?
The name of the file is view-student.php.
<?php
include "dbconfig.php";
?>
<!DOCTYPE html>
<html>
<head>
<title>Student Database</title>
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.0/css/bootstrap.min.css">
</head>
<body>
<div class="container">
<h2>Student Details</h2>
<table class="table">
<thead>
<tr>
<th>ID</th>
<th>Name</th>
<th>Age</th>
<th>Email</th>
<th>Action</th>
</tr>
</thead>
<tbody>
<?php
$sql = "SELECT * FROM students";
$result = $conn->query($sql);
if ($result->num_rows > 0) {
while ($row = $result->fetch_assoc()) {
?>
<tr>
<td><?php echo $row['id']; ?></td>
<td><?php echo $row['name']; ?></td>
<td><?php echo $row['age']; ?></td>
<td><?php echo $row['email']; ?></td>
<td><a class="btn btn-info" href="update-student.php?id=<?php echo $row['id']; ?>">Edit</a>
<a class="btn btn-danger" href="delete-student.php?id=<?php echo $row['id']; ?>">Delete</a>
</td>
</tr>
<?php }
}
?>
</tbody>
</table>
</div>
</body>
</html>PHPCopy
How to Update Records?
The name of the file is update-student.php.
<?php
include "dbconfig.php";
if (isset($_POST['update'])) {
$stu_id = $_POST['stu_id'];
$name = $_POST['name'];
$age = $_POST['age'];
$email = $_POST['email'];
$sql = "UPDATE `students` SET `name`='$name',`age`='$age',`email`='$email' WHERE `id`='$stu_id'";
$result = $conn->query($sql);
if ($result == TRUE) {
echo "Record updated successfully.";
header('Location: view-student.php');
}else{
echo "Error:" . $sql . "<br>" . $conn->error;
}
}
if (isset($_GET['id'])) {
$stu_id = $_GET['id'];
$sql = "SELECT * FROM students WHERE id='$stu_id'";
$result = $conn->query($sql);
if ($result->num_rows > 0) {
while ($row = $result->fetch_assoc()) {
$id = $row['id'];
$name = $row['name'];
$age = $row['age'];
$email = $row['email'];
}
?>
<h2>Student details Update Form</h2>
<form action="" method="post">
<fieldset>
<legend>Personal information:</legend>
Name:<br>
<input type="text" name="name" value="<?php echo $name; ?>">
<input type="hidden" name="stu_id" value="<?php echo $id; ?>">
<br>
Age:<br>
<input type="text" name="age" value="<?php echo $age; ?>">
<br>
Email:<br>
<input type="email" name="email" value="<?php echo $email; ?>">
<br><br>
<input type="submit" value="Update" name="update">
</fieldset>
</form>
</body>
</html>
<?php
} else{
header('Location: view-student.php');
}
}
?>
How to Delete Records?
The name of the file is delete-student.php.
<?php
include "dbconfig.php";
if (isset($_GET['id'])) {
$stu_id = $_GET['id'];
$sql = "DELETE FROM students WHERE id ='$stu_id'";
$result = $conn->query($sql);
if ($result == TRUE) {
echo "Record deleted successfully.";
header('Location: view-student.php');
}else{
echo "Error:" . $sql . "<br>" . $conn->error;
}
}
?>