How to develop Projects(AMS) Using PHP and Mysql
Part 9
3️⃣ Astrology Management System
📌 Folder Structure:
│── /astrologers/
│ ├── add_astrologer.php # Add astrologer
│ ├── edit_astrologer.php # Edit astrologer
│ ├── delete_astrologer.php # Delete astrologer
│ ├── astrologer_list.php # List all astrologers
Database Table:
CREATE TABLE astrologers (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255) NOT NULL,
email VARCHAR(255) UNIQUE NOT NULL,
specialization VARCHAR(255),
experience INT DEFAULT 0,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
File: astrologers/add_astrologer.php
<?php
// Include database
connection
require_once '../config/db.php';
// Adjust the path as needed
// Initialize variables
$name = $email = $specialization
= $experience = "";
$errors = [];
// Process form
submission
if ($_SERVER["REQUEST_METHOD"]
== "POST") {
// Sanitize input data
$name = trim($_POST['name']);
$email = trim($_POST['email']);
$specialization = trim($_POST['specialization']);
$experience = trim($_POST['experience']);
// Validate input
if (empty($name)) {
$errors[] = "Name is
required.";
}
if (empty($email)) {
$errors[] = "Email is
required.";
} elseif (!filter_var($email,
FILTER_VALIDATE_EMAIL)) {
$errors[] = "Invalid email
format.";
}
if (!empty($experience) && !ctype_digit($experience))
{
$errors[] = "Experience must be a
positive integer.";
}
// If no errors, proceed with database
insertion
if (empty($errors)) {
// Escape special characters to prevent
SQL injection
$name = mysqli_real_escape_string($conn,
$name);
$email = mysqli_real_escape_string($conn,
$email);
$specialization = mysqli_real_escape_string($conn,
$specialization);
$experience = mysqli_real_escape_string($conn,
$experience);
// SQL Query to insert data
$sql = "INSERT INTO astrologers
(name, email, specialization, experience)
VALUES ('$name', '$email', '$specialization',
'$experience')";
// Execute query
if (mysqli_query($conn, $sql)) {
echo "<p
style='color:green;'>Astrologer added successfully!</p>";
} else {
echo "<p
style='color:red;'>Error: " . mysqli_error($conn) . "</p>";
}
} else {
// Display validation errors
foreach ($errors as $error) {
echo "<p
style='color:red;'>$error</p>";
}
}
}
// Close database
connection
mysqli_close($conn);
?>
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width,
initial-scale=1.0">
<title>Add Astrologer</title>
</head>
<body>
<h2>Add Astrologer</h2>
<form method="post" action="">
<label for="name">Name:</label>
<input type="text" name="name"
required><br><br>
<label for="email">Email:</label>
<input type="email" name="email"
required><br><br>
<label for="specialization">Specialization:</label>
<input type="text" name="specialization"><br><br>
<label for="experience">Experience
(Years):</label>
<input type="number" name="experience"
min="0"><br><br>
<input type="submit"
value="Add Astrologer">
</form>
</body>
</html>
Explanation:
1.
Database Connection:
Includes db.php to connect to the database.
2.
Form Handling:
o Retrieves
user input and trims whitespace.
o Validates
input (empty fields, valid email, experience as a positive integer).
3.
SQL Query Execution:
Uses mysqli_query() (without prepared statements).
4.
SQL Injection Prevention:
Uses mysqli_real_escape_string() to escape user input.
5.
Error Handling:
Displays validation and database errors.
6.
HTML Form:
Provides a basic form for adding astrologers.
File: astrologers/edit_astrologer.php
<?php
// Include database
connection
require_once '../config/db.php';
// Adjust path as needed
// Initialize variables
$id = $name = $email = $specialization
= $experience = "";
$errors = [];
// Check if ID is
provided in the URL
if (isset($_GET['id'])
&& is_numeric($_GET['id'])) {
$id = $_GET['id'];
// Fetch astrologer's current data
$sql = "SELECT * FROM astrologers
WHERE id = $id";
$result = mysqli_query($conn, $sql);
if ($result && mysqli_num_rows($result)
== 1) {
$row = mysqli_fetch_assoc($result);
$name = $row['name'];
$email = $row['email'];
$specialization = $row['specialization'];
$experience = $row['experience'];
} else {
echo "<p
style='color:red;'>Astrologer not found.</p>";
exit;
}
} else {
echo "<p
style='color:red;'>Invalid request.</p>";
exit;
}
// Process form
submission
if ($_SERVER["REQUEST_METHOD"]
== "POST") {
// Sanitize input data
$name = trim($_POST['name']);
$email = trim($_POST['email']);
$specialization = trim($_POST['specialization']);
$experience = trim($_POST['experience']);
// Validate input
if (empty($name)) {
$errors[] = "Name is
required.";
}
if (empty($email)) {
$errors[] = "Email is
required.";
} elseif (!filter_var($email,
FILTER_VALIDATE_EMAIL)) {
$errors[] = "Invalid email
format.";
}
if (!empty($experience) && !ctype_digit($experience))
{
$errors[] = "Experience must be a
positive integer.";
}
// If no errors, update astrologer data
if (empty($errors)) {
// Escape special characters
$name = mysqli_real_escape_string($conn,
$name);
$email = mysqli_real_escape_string($conn,
$email);
$specialization = mysqli_real_escape_string($conn,
$specialization);
$experience = mysqli_real_escape_string($conn,
$experience);
// Update query
$sql = "UPDATE astrologers SET
name = '$name',
email = '$email',
specialization = '$specialization',
experience = '$experience'
WHERE id = $id";
// Execute query
if (mysqli_query($conn, $sql)) {
echo "<p
style='color:green;'>Astrologer updated successfully!</p>";
} else {
echo "<p
style='color:red;'>Error: " . mysqli_error($conn) . "</p>";
}
} else {
// Display validation errors
foreach ($errors as $error) {
echo "<p
style='color:red;'>$error</p>";
}
}
}
// Close database
connection
mysqli_close($conn);
?>
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width,
initial-scale=1.0">
<title>Edit Astrologer</title>
</head>
<body>
<h2>Edit Astrologer</h2>
<form method="post" action="">
<label for="name">Name:</label>
<input type="text" name="name"
value="<?php echo htmlspecialchars($name); ?>"
required><br><br>
<label for="email">Email:</label>
<input type="email" name="email"
value="<?php echo htmlspecialchars($email); ?>"
required><br><br>
<label for="specialization">Specialization:</label>
<input type="text" name="specialization"
value="<?php echo htmlspecialchars($specialization);
?>"><br><br>
<label for="experience">Experience
(Years):</label>
<input type="number" name="experience"
min="0" value="<?php echo htmlspecialchars($experience);
?>"><br><br>
<input type="submit"
value="Update Astrologer">
</form>
</body>
</html>
Explanation:
1.
Database Connection:
Includes db.php for the database connection.
2.
Fetching Existing Data:
o Retrieves
the astrologer’s details using $_GET['id'].
o Displays
an error message if the ID is invalid or not found.
3.
Form Handling:
o Retrieves
user input and sanitizes it.
o Validates
input fields:
§ Ensures
name and email are not empty.
§ Checks
for a valid email format.
§ Ensures
experience is a positive integer.
4.
SQL Query Execution:
Uses mysqli_query() (without prepared statements).
5.
SQL Injection Prevention:
Uses mysqli_real_escape_string() to escape user input.
6.
Error Handling:
Displays validation and database errors.
7.
HTML Form:
Pre-fills the form with existing astrologer data for editing.
File: astrologers/delete_astrologer.php
<?php
// Include database
connection
require_once '../config/db.php';
// Adjust path as needed
// Check if ID is
provided in the URL
if (isset($_GET['id'])
&& is_numeric($_GET['id'])) {
$id = $_GET['id'];
// Escape special characters to prevent SQL
injection
$id = mysqli_real_escape_string($conn, $id);
// Delete query
$sql = "DELETE FROM astrologers WHERE
id = $id";
// Execute query
if (mysqli_query($conn, $sql)) {
echo "<p
style='color:green;'>Astrologer deleted successfully!</p>";
} else {
echo "<p
style='color:red;'>Error: " . mysqli_error($conn) . "</p>";
}
} else {
echo "<p
style='color:red;'>Invalid request.</p>";
}
// Close database
connection
mysqli_close($conn);
?>
Explanation:
1.
Database Connection:
Includes db.php to establish a connection.
2.
Checking the ID:
o Verifies
if an id is passed in the URL and is numeric.
o Uses
mysqli_real_escape_string() to prevent SQL injection.
3.
SQL Query Execution:
o Uses
mysqli_query() to execute a DELETE SQL query.
o Displays
success or error messages accordingly.
4.
Closes the Database Connection
after execution.
How to Use:
- Call
this file with an astrologer's id in the URL:
http://yourwebsite.com/astrologers/delete_astrologer.php?id=3
- This
will delete the astrologer with id = 3 from the astrologers table.
File: astrologers/astrologer_list.php
<?php
// Include database
connection
require_once '../config/db.php';
// Adjust path as needed
// Fetch astrologers from
the database
$sql = "SELECT *
FROM astrologers ORDER BY created_at DESC";
$result = mysqli_query($conn,
$sql);
?>
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width,
initial-scale=1.0">
<title>Astrologer List</title>
<style>
table {
width: 100%;
border-collapse: collapse;
}
table, th, td {
border: 1px solid black;
}
th, td {
padding: 10px;
text-align: left;
}
th {
background-color: #f2f2f2;
}
</style>
</head>
<body>
<h2>Astrologer List</h2>
<!-- Add Astrologer Button -->
<a href="add_astrologer.php"><button>Add
New Astrologer</button></a>
<br><br>
<table>
<tr>
<th>ID</th>
<th>Name</th>
<th>Email</th>
<th>Specialization</th>
<th>Experience
(Years)</th>
<th>Created At</th>
<th>Actions</th>
</tr>
<?php
if ($result && mysqli_num_rows($result)
> 0) {
while ($row = mysqli_fetch_assoc($result))
{
echo "<tr>";
echo "<td>" . $row['id']
. "</td>";
echo "<td>" . htmlspecialchars($row['name'])
. "</td>";
echo "<td>" . htmlspecialchars($row['email'])
. "</td>";
echo "<td>" . htmlspecialchars($row['specialization'])
. "</td>";
echo "<td>" . htmlspecialchars($row['experience'])
. "</td>";
echo "<td>" . htmlspecialchars($row['created_at'])
. "</td>";
echo "<td>
<a
href='edit_astrologer.php?id=" . $row['id'] . "'>Edit</a> |
<a
href='delete_astrologer.php?id=" . $row['id'] . "' onclick='return
confirm(\"Are you sure you want to delete this
astrologer?\");'>Delete</a>
</td>";
echo "</tr>";
}
} else {
echo "<tr><td
colspan='7' style='text-align:center;'>No astrologers
found.</td></tr>";
}
// Close database connection
mysqli_close($conn);
?>
</table>
</body>
</html>
Explanation:
1.
Database Connection:
Includes db.php to establish a connection.
2.
Fetching Data:
o Runs
a SELECT * FROM astrologers query to get all astrologers.
o Orders
the list by created_at in descending order.
3.
Displaying Data in an HTML Table:
o Loops
through results using mysqli_fetch_assoc($result).
o Displays
astrologer details in a structured table format.
o Uses
htmlspecialchars() to prevent XSS attacks.
4.
Action Buttons:
o Edit:
Redirects to edit_astrologer.php?id=<astrologer_id>.
o Delete:
Redirects to delete_astrologer.php?id=<astrologer_id> with a confirmation
alert.
5.
Handles Empty Results:
Displays "No astrologers found" if no records exist.
6.
Closes the Database Connection
after execution.
How It Works
- Visit
the page:
http://yourwebsite.com/astrologers/astrologer_list.php
- Features:
- View
a list of astrologers.
- Add
a new astrologer using the "Add New Astrologer" button.
- Edit
an astrologer’s details using the "Edit" link.
- Delete
an astrologer with confirmation using the "Delete" link.