FORM AND DATABASE CONNECTIVITY With CRUD Operation (Using PDO)

  

FORM AND DATABASE CONNECTIVITY

With CRUD Operation 

Using PDO 

With Query  method

Prerequisite:

Before you can test your PHP and MySQL application using XAMPP, you need to ensure that both Apache and MySQL services are running.

Here are the steps:

1.   Step 1. Start XAMPP:

• Open the XAMPP Control Panel. You can do this by navigating to the directory where XAMPP is installed and running xampp-control.exe.

2.   Step 2:   Start Apache:

• In the XAMPP Control Panel, find the "Apache" module.
• Click the "Start" button next to "Apache." This will start the Apache web server.

3.   Step 3:  Start MySQL:

• In the XAMPP Control Panel, find the "MySQL" module.
• Click the "Start" button next to "MySQL." This will start the MySQL database server.

4.   Step 4: Verify Running Services:

• After starting both Apache and MySQL, you should see "Running" next to their respective modules in the XAMPP Control Panel.
• Now that Apache and MySQL are running, you can open your web browser and access your PHP application by navigating to the appropriate URL.
• If your XAMPP installation is on your local machine, the URL might be http://localhost/your-directory/index.html,   where "your-directory" is the folder where you've placed your files.

How to create a form and connect it to the database table using php and mysql .

Step 1: Set Up Your Database

Step 2: Create a Connection Script

Step 3: Create HTML Form

Step 4: Create PHP Processing Script

Step 5: Test Your Form

 

Step 1: Set Up Your Database

 In Step 1, you'll be setting up your MySQL database, which includes creating a new database and defining a table within that database.

1.1 Create a Database:

1. Open your MySQL management tool (like phpMyAdmin, MySQL Workbench, or use command-line interface).
2. Once you're connected to your MySQL server, find an option to create a new database. In phpMyAdmin, there is typically a "New Database" button.
3. Choose a name for your database, for example, let's call it mydatabase.

 

Creating a Database Using phpMyAdmin

phpMyAdmin is a web-based tool that provides a graphical interface to manage MySQL databases.

Steps to create a database using phpMyAdmin:

1. Access phpMyAdmin: Open phpMyAdmin in your web browser (typically accessed via http://localhost/phpmyadmin).

2. Select the "Databases" Tab: Click on the "Databases" tab at the top of the page.

3. Create a Database:

o In the "Create database" field, enter the name of the database you want to create. suppose here your database name is my_DB

o Click the "Create" button.

Once you click "Create," phpMyAdmin will execute the necessary SQL command to create the database.

1.2 Create a Table:

Type 1 : Creating Tables Using phpMyAdmin

phpMyAdmin provides a user-friendly interface to create tables. The steps are :

1. Access phpMyAdmin: Open phpMyAdmin in your web browser (typically accessed via http://localhost/phpmyadmin). 

2. Select the Database: Click on the database in which you want to create the table.

3. Create a Table:

o Click on the "Structure" tab if you’re not already there.

o Enter the table name and the number of columns in the "Create table" section, then click "Go".

Define the columns: name, type, length, and other attributes for each column.

o Click "Save" to create the table.

With these steps, you've created a database named my_DB and a table named myGuests within that database.

The myGuests table is where you'll store information about guests who submit data through your form.

 

Step 2: Create a Connection Script

In Step 2, you will create a PHP script to establish a connection to the MySQL database. This script will be responsible for connecting your PHP application to the MySQL database, allowing you to perform operations like inserting data into the database or retrieving data from it.

Before save the connection script you have to create a folder according to name of your project ,i.e; here MyGuests .So i make a folder named as myGuests in C:\\XaMPP\Htdocs\ .Save all the file inside myGuests folder.

2.1 Create a Connection Script:

Open your preferred text editor or integrated development environment (IDE).

Create a new PHP file, for example, db.php.

In this file, you'll define the necessary variables for connecting to your MySQL database, such as the server name, username, password, and database name.

FileName: db_connect.php/database_connect.php/config.php

Save db_connect.php in  myGuests folder.

<?php $servername = "localhost"; $username = "username"; $password = "password"; try {   $conn = new PDO("mysql:host=$servername;dbname=myDB", $username, $password);   // set the PDO error mode to exception   $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);   echo "Connected successfully"; } catch(PDOException $e) {   echo "Connection failed: " . $e->getMessage(); } ?>

 Save the db_connect.php file.

Output:

Step-by-step explanation :

Step 1: Setting Up Variables

<?php

$servername = "localhost";  // Replace with your server name if different

$username = "root";         // Replace with your MySQL username

$password = "";             // Replace with your MySQL password

$dbname = "my_DB";          // Replace with your database name

Variable Initialization: The first part of the script initializes four variables with the database connection details:

$servername stores the server name, which is "localhost" in this case.

$username stores the MySQL username.

$password stores the MySQL password.

$dbname stores the name of the database you want to connect to.

Step 2: Create Connection:

try {

    // Create connection

    $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);

A try block is used to attempt to create a new PDO connection.

The new PDO statement creates a new PDO instance to connect to the database. 

The parameters passed are:

"mysql:host=$servername;dbname=$dbname" specifies the database type (MySQL), host (server name), and database name.

$username and $password are the MySQL username and password.

Set PDO Error Mode:

    // Set the PDO error mode to exception

    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION) sets the error mode of the PDO instance to exception. 

This means that if there is an error during the database operations, it will throw a PDOException.

Success Message:

    echo "Database connected";

If the connection is successful, it prints "Database connected".

Exception Handling:

} catch(PDOException $e) {

    echo "Connection failed: " . $e->getMessage();

}

?>

The catch block catches any PDOException that might be thrown if the connection fails.

If an exception is caught, it prints "Connection failed: " followed by the error message ($e->getMessage()) provided by the exception.

Summary

1. The script initializes the database connection parameters.
2. It attempts to create a new PDO instance to connect to the database.
3. It sets the error mode to exception.
4. If the connection is successful, it prints a success message.
5. If the connection fails, it catches the exception and prints an error message.

Step 3: Create HTML Form

In Step 3, you will create an HTML form to collect user input. This form will serve as the interface through which users can enter their information. The form will later be processed by a PHP script to insert the data into the MySQL database.

3.1 Create HTML Form:

Open your preferred text editor or integrated development environment (IDE).
Create a new HTML file, for example, index.html.
In this file, you'll define an HTML form with input fields for the user to enter their name, email, and password.
Filename: index.html
Save index.html in myGuests floder.

<!DOCTYPE html> <html lang="en"> <head>     <meta charset="UTF-8">     <meta name="viewport" content="width=device-width, initial-scale=1.0">     <title>User Registration</title> </head> <body>     <form action="process.php" method="post">         <label for="name">First Name:</label>         <input type="text" name="firstname" required><br>         <label for="name">Last Name:</label>         <input type="text" name="lastname" required><br>         <label for="email">Email:</label>         <input type="email" name="email" required><br>         <label for="password">Reg Date:</label>         <input type="date" name="reg_date" required><br>         <input type="submit" value="Register">     </form> </body> </html>

 Save the index.html file.

Output:

Step-by-step Explanation :

DOCTYPE Declaration and HTML Tag:

<!DOCTYPE html>

<html lang="en">

<!DOCTYPE html>: This declaration defines the document type and version of HTML being used, which is HTML5 in this case.

<html lang="en">: This tag signifies the beginning of the HTML document. The lang="en" attribute specifies that the language of the document is English.

Head Section:

<head>

    <meta charset="UTF-8">

    <meta name="viewport" content="width=device-width, initial-scale=1.0">

    <title>User Registration</title>

</head>

<head>: This tag contains meta-information about the document, such as its character set, viewport settings, and title.

<meta charset="UTF-8">: This tag sets the character encoding for the document to UTF-8, which supports almost all characters from all languages.

<meta name="viewport" content="width=device-width, initial-scale=1.0">: This tag ensures the webpage is responsive by setting the viewport to match the device's width and initial scale.

<title>User Registration</title>: This tag sets the title of the document, which is displayed in the browser tab.

Body Section:

<body>

<body>: This tag contains the content of the HTML document that is displayed in the web browser.

Form Tag:

    <form action="process.php" method="post">

<form action="process.php" method="post">: This tag creates an HTML form. The action="process.php" attribute specifies the URL of the server-side script (process.php) that will handle the form submission. The method="post" attribute specifies that the form data will be sent using the POST method.

First Name Field:

        <label for="name">First Name:</label>

        <input type="text" name="firstname" required><br>

<label for="name">First Name:</label>: This tag creates a label for the first name input field. The for attribute associates the label with the corresponding input field.

<input type="text" name="firstname" required>: This tag creates a text input field for the first name. The name="firstname" attribute assigns a name to the input field, which will be used to reference the data in the server-side script. The required attribute ensures that this field must be filled out before the form can be submitted.

<br>: This tag inserts a line break after the input field.

Last Name Field:

        <label for="name">Last Name:</label>

        <input type="text" name="lastname" required><br>

Similar to the first name field, this section creates a label and input field for the user's last name.

Email Field:

        <label for="email">Email:</label>

        <input type="email" name="email" required><br>

<label for="email">Email:</label>: This tag creates a label for the email input field.

<input type="email" name="email" required>: This tag creates an input field specifically for email addresses. The type="email" attribute ensures that the input is validated as an email address.

Registration Date Field:

        <label for="password">Reg Date:</label>

        <input type="date" name="reg_date" required><br>

<label for="password">Reg Date:</label>: This tag creates a label for the registration date input field.

<input type="date" name="reg_date" required>: This tag creates an input field specifically for selecting a date. The type="date" attribute ensures that the input is a valid date.

Submit Button:

        <input type="submit" value="Register">

<input type="submit" value="Register">: This tag creates a submit button. When clicked, the form data is sent to the URL specified in the action attribute of the form tag (process.php).

Closing Tags:

    </form>

</body>

</html>

</form>: This tag closes the form.

</body>: This tag closes the body section.

</html>: This tag closes the HTML document.

what this form do ?

This form is designed to collect user registration information, including the user's first name, last name, email address, and registration date. 

When the form is submitted, it sends this data to a server-side script (specified as process.php) using the POST method. 

Detailed explanation of the process:

User Input:

First Name: The user is required to enter their first name in the firstname input field.

Last Name: The user is required to enter their last name in the lastname input field.

Email: The user is required to enter a valid email address in the email input field.

Registration Date: The user is required to select a registration date using the reg_date input field.

Form Submission:

When the user clicks the "Register" button, the form data is submitted to process.php using the POST method. This means the data is included in the body of the HTTP request, making it more secure for sensitive information compared to the GET method, which appends data to the URL.

Server-Side Processing:

The process.php script (not provided in your example) is responsible for handling the form data. 

Typically, this script(process.php) might:

1. Validate the input data (e.g., check that all required fields are filled, the email format is correct, the registration date is valid, etc.).
2. Sanitize the data to prevent security vulnerabilities like SQL injection or cross-site scripting (XSS).
3. Save the data to a database.
4. Provide feedback to the user (e.g., confirmation message, redirect to another page, error messages if validation fails, etc.).

Step 4: Create PHP Processing Script

In Step 4, you will create a PHP script (process.php) to handle the form submissions and insert the data into the MySQL database.

4.1 Create PHP Processing Script:

Open your preferred text editor or integrated development environment (IDE).

Create a new PHP file, for example, process.php

In this file, you'll handle the form submission, retrieve data from the submitted form, and insert it into the MySQL database.

Filename: process.php

Save process.php in myGuests folder

<?php if ($_SERVER["REQUEST_METHOD"] == "POST") {     // Collect and sanitize input data     $firstname = htmlspecialchars(trim($_POST['firstname']));     $lastname = htmlspecialchars(trim($_POST['lastname']));     $email = htmlspecialchars(trim($_POST['email']));     $reg_date = htmlspecialchars(trim($_POST['reg_date']));     // Validate data (this is a basic example, real validation would be more comprehensive)     if (!empty($firstname) && !empty($lastname) && filter_var($email, FILTER_VALIDATE_EMAIL) && !empty($reg_date)) {         // Include the database connection file         include 'db_connect.php';         try {             // Construct the SQL query             $sql = "INSERT INTO users (firstname, lastname, email, reg_date) VALUES ('$firstname', '$lastname', '$email', '$reg_date')";             // Execute the query             $conn->query($sql);                          echo "Registration successful!";         } catch(PDOException $e) {             echo "Error: " . $e->getMessage();         }         $conn = null;     } else {         echo "Invalid input data.";     } } ?>

Step-by-Step Explanation:

Check Request Method:

<?php
if ($_SERVER["REQUEST_METHOD"] == "POST") {
The script starts by checking if the request method is POST using $_SERVER["REQUEST_METHOD"] == "POST". This ensures that the form data is being submitted through a POST request and not any other method (like GET).

Collect and Sanitize Input Data:

    // Collect and sanitize input data
    $firstname = htmlspecialchars(trim($_POST['firstname']));
    $lastname = htmlspecialchars(trim($_POST['lastname']));
    $email = htmlspecialchars(trim($_POST['email']));
    $reg_date = htmlspecialchars(trim($_POST['reg_date']));
trim($_POST['firstname']): Trims any whitespace from the beginning and end of the input.
htmlspecialchars($input): Converts special characters to HTML entities to prevent XSS (Cross-Site Scripting) attacks.
This process is repeated for lastname, email, and reg_date.

Validate Input Data:

    // Validate data (this is a basic example, real validation would be more comprehensive)
    if (!empty($firstname) && !empty($lastname) && filter_var($email, FILTER_VALIDATE_EMAIL) && !empty($reg_date)) {
!empty($firstname): Checks if the first name is not empty.
!empty($lastname): Checks if the last name is not empty.
filter_var($email, FILTER_VALIDATE_EMAIL): Validates the email format.
!empty($reg_date): Checks if the registration date is not empty.
If all these conditions are met, the script proceeds to the next steps; otherwise, it will output "Invalid input data."

Include Database Connection File:

        // Include the database connection file
        include 'db_connect.php';
The script includes the db_connect.php file, which contains the database connection logic using PDO. This makes the database connection available in the current script.

Construct the SQL Query:

        try {
            // Construct the SQL query
            $sql = "INSERT INTO users (firstname, lastname, email, reg_date) VALUES ('$firstname', '$lastname', '$email', '$reg_date')";
The SQL query string is constructed using the sanitized input data. This query will insert a new record into the users table with the fields firstname, lastname, email, and reg_date.

Execute the SQL Query:

            // Execute the query
            $conn->query($sql);
            
            echo "Registration successful!";
$conn->query($sql): Executes the SQL query using the PDO connection.
If the query executes successfully, it prints "Registration successful!"

Error Handling:

        } catch(PDOException $e) {
            echo "Error: " . $e->getMessage();
        }

The try block is used to catch any exceptions that occur during the query execution.
If a PDOException is caught, the script will print the error message.

Close the Database Connection:

        $conn = null;
$conn = null;: Closes the database connection by setting the PDO object to null.

Invalid Input Handling:

    } else {
        echo "Invalid input data.";
    }
}
?>
If the input data does not pass validation, it prints "Invalid input data."

Summary:

1. Form Handling: The script processes the form data submitted via POST request.
2. Data Sanitization: Input data is sanitized to prevent XSS attacks.
3. Data Validation: Ensures that required fields are not empty and the email format is valid.
4. Database Connection: The database connection is included from an external file (db_connect.php).
5. SQL Query Execution: An SQL query is constructed and executed to insert the user data into the database.
6. Error Handling: Exceptions during database operations are caught and displayed.
7. Connection Closure: The database connection is closed after the operation.

Step 5. Test Your Form:

In Step 5, you will test your form by placing all the files in the same directory on your server and filling out the form to ensure that the data is submitted to the MySQL database.

5.1 Test Your Form:

Make sure you have all three files (db_connect.php, index.html, and process.php) in the same directory/folder on your server.

Steps to run your project : 

1. Open your web browser and navigate to the location where you placed the files. For example, if you're running a local server, the URL might be something like http://localhost/your-directory/index.html.

2. Open the index.html file in your web browser.

3. Fill out the form with a sample name, email, and password.

4. Click the "Register" button to submit the form.

5.If everything is set up correctly, you should see a message indicating whether the registration was successful or if there was an error. Check your MySQL database to see if the user's information has been inserted into the users table.

Output:

Congratulations! You've successfully tested your form, and the data entered should now be stored in your MySQL database. If you encounter any errors, double-check your code and ensure that your server environment is configured correctly. Additionally, check the error messages displayed by the script for any clues on what might be going wrong.

Data Retrieve From Database

Retrieve data from a table in PHP using the query() method. The Steps are

Step 1: Establish Database Connection

Here already you created db_connect.php to handle the database connection.Just access that one.

Step 2: Create the Main PHP Script to Retrieve Data

Now, create a file retrieve_data.php to retrieve and display the data from the table . And save this file in same project folder.

retrieve_data.php

<?php // Step 1: Include the connection script file include 'db_connect.php'; // Include the database connection script try {     // Step 2: Prepare the SQL query     $sql = "SELECT id, firstname, lastname, email, reg_date FROM MyGuests";     // Step 3: Execute the query     $result = $conn->query($sql);     // Step 4: Check if there are results     if ($result->rowCount() > 0) {         // Step 5: Output data of each row         echo "<table border='1'>         <tr>         <th>ID</th>         <th>First Name</th>         <th>Last Name</th>         <th>Email</th>         <th>Registration Date</th>         </tr>";         while ($row = $result->fetch(PDO::FETCH_ASSOC)) {             echo "<tr>             <td>" . htmlspecialchars($row['id']) . "</td>             <td>" . htmlspecialchars($row['firstname']) . "</td>             <td>" . htmlspecialchars($row['lastname']) . "</td>             <td>" . htmlspecialchars($row['email']) . "</td>             <td>" . htmlspecialchars($row['reg_date']) . "</td>             </tr>";         }         echo "</table>";     } else {         echo "0 results";     } } catch (PDOException $e) {     echo "Error: " . $e->getMessage(); } // Step 6: Close the database connection $conn = null; ?>

Step-by-Step Explanation

Include the Database Connection Script:

include 'db_connect.php';
This line includes the db_connect.php file, which contains the code to establish the PDO connection to the database.

Prepare the SQL Query:

$sql = "SELECT id, firstname, lastname, email, reg_date FROM MyGuests";
The SQL query string is defined to select the id, firstname, lastname, email, and reg_date columns from the MyGuests table.

Execute the Query:

$result = $conn->query($sql);
The query method of the PDO object is used to execute the SQL query. The result is stored in the $result variable.

Check if There Are Results:

if ($result->rowCount() > 0) {
The rowCount method of the result object checks if there are any rows returned by the query.

Output Data of Each Row:

echo "<table border='1'>
<tr>
<th>ID</th>
<th>First Name</th>
<th>Last Name</th>
<th>Email</th>
<th>Registration Date</th>
</tr>";

If there is data:

If there are results, an HTML table is started with headers for each column.
while ($row = $result->fetch(PDO::FETCH_ASSOC)) {
    echo "<tr>
    <td>" . htmlspecialchars($row['id']) . "</td>
    <td>" . htmlspecialchars($row['firstname']) . "</td>
    <td>" . htmlspecialchars($row['lastname']) . "</td>
    <td>" . htmlspecialchars($row['email']) . "</td>
    <td>" . htmlspecialchars($row['reg_date']) . "</td>
    </tr>";
}
The fetch method of the result object retrieves each row as an associative array. Each column value is escaped with htmlspecialchars to prevent XSS attacks and is then inserted into a table row.

Close the HTML Table:

echo "</table>";

Handle No Results:

} else {
    echo "0 results";
}
If there are no rows returned, the script outputs "0 results".

Error Handling:

} catch (PDOException $e) {
    echo "Error: " . $e->getMessage();
}
Any exceptions thrown during the query execution are caught and the error message is printed.

Close the Database Connection:

$conn = null;
The database connection is closed by setting the PDO object to null.
This approach uses PDO's query method to execute the SQL query and fetch results, while also handling errors and ensuring safe output of data.

Step 3 : To view Output from Table

Open your web browser and navigate to the location where you placed the files. For example, if you're running a local server, the URL might be something like http://localhost/your-directory/retrive_data.php

Output:

Edit Data From Database

To edit data in a database using PHP and MySQL with the query() method, you'll follow these steps. Editing data involves updating existing records based on specific criteria, typically using SQL's UPDATE statement.

Step-by-Step Guide to Edit Data Using query() Method

Step 1: Establish Database Connection

First, ensure you have a db_connect.php file

Step 2: Create the HTML Form (Optional)

If you want to allow users to input data through an HTML form, you can create a form like this:

edit_data.html

<!DOCTYPE html>  <html>  <head>      <title>Edit Record</title>  </head>  <body>      <form action="edit_data.php" method="post">          ID: <input type="text" name="id"><br>          First Name: <input type="text" name="firstname"><br>          Last Name: <input type="text" name="lastname"><br>          Email: <input type="email" name="email"><br>          Registration Date: <input type="date" name="reg_date"><br>          <input type="submit" value="Update">      </form>  </body>  </html>

 Step 3: PHP Script to Handle Edit (edit_data.php)

Create a PHP script (edit_data.php) to handle the edit operation based on the submitted form data.

edit_data.php

<?php include 'db_connect.php'; // Include the database connection script if ($_SERVER["REQUEST_METHOD"] == "POST") {     // Retrieve form data and sanitize it     $id = htmlspecialchars(trim($_POST['id'])); // The ID of the record to edit     $firstname = htmlspecialchars(trim($_POST['firstname']));     $lastname = htmlspecialchars(trim($_POST['lastname']));     $email = htmlspecialchars(trim($_POST['email']));     $reg_date = htmlspecialchars(trim($_POST['reg_date']));     try {         // Create SQL update query         $sql = "UPDATE MyGuests SET firstname='$firstname', lastname='$lastname', email='$email', reg_date='$reg_date' WHERE id='$id'";         // Execute the query         $result = $conn->query($sql);         // Check if the record was updated         if ($result) {             echo "Record updated successfully";         } else {             echo "Error updating record";         }     } catch (PDOException $e) {         echo "Error: " . $e->getMessage();     } } // Close the database connection $conn = null; ?>

Step-by-Step Explanation:

Include the Database Connection Script:

include 'db_connect.php';
This line includes the db_connect.php file, which contains the code to establish the PDO connection to the database.

Check Request Method:

if ($_SERVER["REQUEST_METHOD"] == "POST") {
The script checks if the request method is POST to ensure that the form data is being submitted through a POST request.

Retrieve and Sanitize Form Data:

$id = htmlspecialchars(trim($_POST['id']));
$firstname = htmlspecialchars(trim($_POST['firstname']));
$lastname = htmlspecialchars(trim($_POST['lastname']));
$email = htmlspecialchars(trim($_POST['email']));
$reg_date = htmlspecialchars(trim($_POST['reg_date']));
Form data is retrieved using $_POST and sanitized using htmlspecialchars and trim to prevent XSS attacks and remove whitespace.

Try-Catch Block for Error Handling:

try {
A try-catch block is used to handle any exceptions that may occur during the execution of the SQL query.

Create SQL Update Query:

$sql = "UPDATE MyGuests SET firstname='$firstname', lastname='$lastname', email='$email', reg_date='$reg_date' WHERE id='$id'";
The SQL update query is constructed using the sanitized input data.

Execute the SQL Query:

$result = $conn->query($sql);
The query method of the PDO object is used to execute the SQL update query.

Check If the Record Was Updated:

if ($result) {
    echo "Record updated successfully";
} else {
    echo "Error updating record";
}
If the query execution returns a result, it means the record was updated successfully. Otherwise, it prints an error message.

Catch Block for Error Handling:

} catch (PDOException $e) {
    echo "Error: " . $e->getMessage();
}
If an exception is caught, the error message is printed.

Close the Database Connection:

$conn = null;
The database connection is closed by setting the PDO object to null.
Important Note:
This code directly incorporates user input into the SQL query, which can expose the application to SQL injection attacks. It is generally recommended to use prepared statements with bound parameters to mitigate this risk. However, since you requested the query method specifically, the code is written accordingly.

Step 3 : To Edit data in  Table

Open your web browser and navigate to the location where you placed the files. For example, if you're running a local server, the URL might be something like http://localhost/your-directory/edit_data.html

Output:

after Update button clicked

Delete Data from Database

Deleting data from a database table in PHP involves using an SQL DELETE statement. 

Step-by-Step Guide to Delete Data Using PHP

Step 1: Establish Database Connection

First, ensure you have a db_connect.php file or similar to establish a connection to your MySQL database.

Step 2: Create the HTML Form or Determine Deletion Criteria

Decide how you will determine which records to delete. You can either:

Use an HTML form to allow users to input criteria for deletion.Write the HTML form.

delete_data.html

<!DOCTYPE html> <html> <head>     <title>Delete Record</title> </head> <body>     <form action="delete_data.php" method="post">         ID: <input type="text" name="id"><br>         <input type="submit" value="Delete">     </form> </body> </html>

 Step 3: PHP Script to Handle Deletion (delete_data.php)

Create a PHP script (delete_data.php) to handle the deletion operation based on the submitted form data or predefined criteria.

delete_data.php

<?php include 'db_connect.php'; // Include the database connection script if ($_SERVER["REQUEST_METHOD"] == "POST") {     // Retrieve and sanitize form data     $id = htmlspecialchars(trim($_POST['id'])); // The ID of the record to delete     try {         // Create SQL delete query         $sql = "DELETE FROM MyGuests WHERE id = '$id'";         // Execute the query         $result = $conn->query($sql);         // Check if the query was successful         if ($result) {             echo "Record deleted successfully";         } else {             echo "Error deleting record";         }     } catch (PDOException $e) {         echo "Error: " . $e->getMessage();     } } // Close the database connection $conn = null; ?>

Step-by-Step Explanation:

Include the Database Connection Script:

include 'db_connect.php';
This includes the db_connect.php file, which establishes the PDO connection to the database.

Check Request Method:

if ($_SERVER["REQUEST_METHOD"] == "POST") {
The script verifies that the request method is POST to confirm form submission.

Retrieve and Sanitize Form Data:

$id = htmlspecialchars(trim($_POST['id']));
Retrieve the id from the POST request and sanitize it using htmlspecialchars and trim to remove any extra whitespace and prevent XSS attacks.

Try-Catch Block for Error Handling:

try {
Use a try-catch block to handle any potential exceptions during database operations.

Create SQL Delete Query:

$sql = "DELETE FROM MyGuests WHERE id = '$id'";
Construct the SQL delete query, embedding the sanitized id directly into the SQL string.

Execute the Query:

$result = $conn->query($sql);
Execute the query using the query method of the PDO object.

Check If the Query Was Successful:

if ($result) {
    echo "Record deleted successfully";
} else {
    echo "Error deleting record";
}
If the query execution returns a result (indicating success), print "Record deleted successfully." Otherwise, print an error message.

Catch Block for Error Handling:

} catch (PDOException $e) {
    echo "Error: " . $e->getMessage();
}
If an exception is thrown, the catch block captures it and prints the error message.

Close the Database Connection:

$conn = null;
Close the database connection by setting the PDO object to null.
Note:
Directly including user input in SQL queries can expose your application to SQL injection attacks. Always use prepared statements with parameter binding when handling user input in SQL queries to enhance security.

Step 3 : To delete data in  Table

Open your web browser and navigate to the location where you placed the files. For example, if you're running a local server, the URL might be something like http://localhost/your-directory/delete_data.html

Output:

Input the id number which is existing in ur table and submit ...

next

Display all records from Database 

with provided option to edit and delete

To display all records in a table and provide options to delete and edit each record, you need to:

1. Retrieve and display records from the database.
2. Provide options as  "Edit" and "Delete" buttons for each record.
3. Create separate scripts to handle the editing and deleting operations.

Here are the steps :

Step 1: Establish Database Connection

Ensure you have a db_connect.php file to establish a connection to your MySQL database.

Step 2: Display Records with Edit and Delete Options

Create a script (view_records.php) to retrieve and display all records with "Edit" and "Delete" buttons.

view_records.php

<?php include 'db_connect.php'; // Include the database connection script // Create SQL select query $sql = "SELECT id, firstname, lastname, email, reg_date FROM MyGuests"; try {     // Execute the query     $result = $conn->query($sql);     // Check if there are rows in the result set     if ($result->rowCount() > 0) {         echo "<table border='1'>";         echo "<tr><th>ID</th><th>First Name</th><th>Last Name</th><th>Email</th><th>Registration Date</th><th>Action</th></tr>";         // Fetch and display each row of the result set         while ($row = $result->fetch(PDO::FETCH_ASSOC)) {             $id = htmlspecialchars($row['id']);             $firstname = htmlspecialchars($row['firstname']);             $lastname = htmlspecialchars($row['lastname']);             $email = htmlspecialchars($row['email']);             $reg_date = htmlspecialchars($row['reg_date']);             echo "<tr>";             echo "<td>$id</td>";             echo "<td>$firstname</td>";             echo "<td>$lastname</td>";             echo "<td>$email</td>";             echo "<td>$reg_date</td>";             echo "<td>";             echo "<a href='edit_record.php?id=$id'>Edit</a> | ";             echo "<a href='delete_record.php?id=$id' onclick='return confirm(\"Are you sure you want to delete this record?\");'>Delete</a>";             echo "</td>";             echo "</tr>";         }         echo "</table>";     } else {         echo "0 results";     } } catch (PDOException $e) {     echo "Error: " . $e->getMessage(); } // Close the database connection $conn = null; ?>

 

Step-by-Step Explanation

Include the Database Connection Script:
php
Copy code
include 'db_connect.php';
This includes the db_connect.php file, which sets up the PDO connection to the database.
Create SQL Select Query:
php
Copy code
$sql = "SELECT id, firstname, lastname, email, reg_date FROM MyGuests";
Defines the SQL query to select records from the MyGuests table.
Execute the Query:
php
Copy code
$result = $conn->query($sql);
Executes the SQL query using PDO's query method.
Check Number of Rows:
php
Copy code
if ($result->rowCount() > 0) {
Checks if the result set contains any rows.
Display the Results in an HTML Table:
php
Copy code
echo "<table border='1'>";
echo "<tr><th>ID</th><th>First Name</th><th>Last Name</th><th>Email</th><th>Registration Date</th><th>Action</th></tr>";
Outputs the start of an HTML table and table headers.
Fetch and Display Each Row:
php
Copy code
while ($row = $result->fetch(PDO::FETCH_ASSOC)) {
    $id = htmlspecialchars($row['id']);
    $firstname = htmlspecialchars($row['firstname']);
    $lastname = htmlspecialchars($row['lastname']);
    $email = htmlspecialchars($row['email']);
    $reg_date = htmlspecialchars($row['reg_date']);
    echo "<tr>";
    echo "<td>$id</td>";
    echo "<td>$firstname</td>";
    echo "<td>$lastname</td>";
    echo "<td>$email</td>";
    echo "<td>$reg_date</td>";
    echo "<td>";
    echo "<a href='edit_record.php?id=$id'>Edit</a> | ";
    echo "<a href='delete_record.php?id=$id' onclick='return confirm(\"Are you sure you want to delete this record?\");'>Delete</a>";
    echo "</td>";
    echo "</tr>";
}
Fetches each row of the result set as an associative array and outputs the data in table rows.
Handle Exceptions:
php
Copy code
} catch (PDOException $e) {
    echo "Error: " . $e->getMessage();
}
Catches and displays any PDO exceptions that occur during query execution.
Close the Database Connection:
php
Copy code
$conn = null;
Closes the database connection by setting the PDO object to null.
Important Notes:
Using the query method with direct data embedding is not recommended due to the risk of SQL injection. In production code, use prepared statements to safeguard against such risks.
The fetch(PDO::FETCH_ASSOC) method fetches rows as associative arrays, where the keys correspond to column names

Step 3: Edit Record Script (edit_record.php)

Create a script (edit_record.php) to handle the editing of a record.

HTML Form for Editing:

edit_record.php

<?php include 'db_connect.php'; // Include the database connection script if ($_SERVER["REQUEST_METHOD"] == "GET" && isset($_GET['id'])) {     $id = htmlspecialchars(trim($_GET['id'])); // Sanitize input     // Create SQL select query     $sql = "SELECT id, firstname, lastname, email, reg_date FROM MyGuests WHERE id = '$id'";     try {         // Execute the query         $result = $conn->query($sql);         // Fetch the result         if ($row = $result->fetch(PDO::FETCH_ASSOC)) {             $id = htmlspecialchars($row['id']);             $firstname = htmlspecialchars($row['firstname']);             $lastname = htmlspecialchars($row['lastname']);             $email = htmlspecialchars($row['email']);             $reg_date = htmlspecialchars($row['reg_date']);         } else {             echo "Record not found.";             exit();         }     } catch (PDOException $e) {         echo "Error: " . $e->getMessage();         exit();     } } elseif ($_SERVER["REQUEST_METHOD"] == "POST") {     // Handle the update operation     $id = htmlspecialchars(trim($_POST['id']));     $firstname = htmlspecialchars(trim($_POST['firstname']));     $lastname = htmlspecialchars(trim($_POST['lastname']));     $email = htmlspecialchars(trim($_POST['email']));     $reg_date = htmlspecialchars(trim($_POST['reg_date']));     // Create SQL update query     $sql = "UPDATE MyGuests SET firstname = '$firstname', lastname = '$lastname', email = '$email', reg_date = '$reg_date' WHERE id = '$id'";     try {         // Execute the query         $result = $conn->query($sql);         if ($result) {             echo "Record updated successfully";             header("Location: view_records.php");             exit();         } else {             echo "Error updating record.";         }     } catch (PDOException $e) {         echo "Error: " . $e->getMessage();     } } // Close the database connection $conn = null; ?> <!DOCTYPE html> <html> <head>     <title>Edit Record</title> </head> <body>     <form action="edit_record.php" method="post">         <input type="hidden" name="id" value="<?php echo htmlspecialchars($id); ?>">         First Name: <input type="text" name="firstname" value="<?php echo htmlspecialchars($firstname); ?>"><br>         Last Name: <input type="text" name="lastname" value="<?php echo htmlspecialchars($lastname); ?>"><br>         Email: <input type="email" name="email" value="<?php echo htmlspecialchars($email); ?>"><br>         Registration Date: <input type="date" name="reg_date" value="<?php echo htmlspecialchars($reg_date); ?>"><br>         <input type="submit" value="Update">     </form> </body> </html>

 

Step-by-Step Explanation

Include the Database Connection Script:

include 'db_connect.php';
This line includes the db_connect.php file which contains the PDO connection setup.

Handle GET Request to Fetch Record:

if ($_SERVER["REQUEST_METHOD"] == "GET" && isset($_GET['id'])) {
    $id = htmlspecialchars(trim($_GET['id']));
Check if the request method is GET and id is set in the query parameters. Sanitize the id input.

Create and Execute SQL Select Query:

$sql = "SELECT id, firstname, lastname, email, reg_date FROM MyGuests WHERE id = '$id'";
$result = $conn->query($sql);
Construct the SQL query with the id directly included in the string and execute it using the query method.

Fetch the Result:

if ($row = $result->fetch(PDO::FETCH_ASSOC)) {
    $id = htmlspecialchars($row['id']);
    $firstname = htmlspecialchars($row['firstname']);
    $lastname = htmlspecialchars($row['lastname']);
    $email = htmlspecialchars($row['email']);
    $reg_date = htmlspecialchars($row['reg_date']);
} else {
    echo "Record not found.";
    exit();
}
Fetch the result as an associative array and sanitize the output. If no result is found, display an error message.

Handle POST Request to Update Record:

elseif ($_SERVER["REQUEST_METHOD"] == "POST") {
    $id = htmlspecialchars(trim($_POST['id']));
    $firstname = htmlspecialchars(trim($_POST['firstname']));
    $lastname = htmlspecialchars(trim($_POST['lastname']));
    $email = htmlspecialchars(trim($_POST['email']));
    $reg_date = htmlspecialchars(trim($_POST['reg_date']));
Check if the request method is POST and retrieve and sanitize the input data.

Create and Execute SQL Update Query:

$sql = "UPDATE MyGuests SET firstname = '$firstname', lastname = '$lastname', email = '$email', reg_date = '$reg_date' WHERE id = '$id'";
$result = $conn->query($sql);
Construct the SQL update query with the updated data and execute it using the query method.

Check if Update Was Successful:

if ($result) {
    echo "Record updated successfully";
    header("Location: view_records.php");
    exit();
} else {
    echo "Error updating record.";
}
If the query executes successfully, display a success message and redirect to view_records.php. Otherwise, show an error message.

Close the Database Connection:

$conn = null;
Close the database connection by setting the PDO object to null.

HTML Form for Editing Records:

<form action="edit_record.php" method="post">
    <input type="hidden" name="id" value="<?php echo htmlspecialchars($id); ?>">
    First Name: <input type="text" name="firstname" value="<?php echo htmlspecialchars($firstname); ?>"><br>
    Last Name: <input type="text" name="lastname" value="<?php echo htmlspecialchars($lastname); ?>"><br>
    Email: <input type="email" name="email" value="<?php echo htmlspecialchars($email); ?>"><br>
    Registration Date: <input type="date" name="reg_date" value="<?php echo htmlspecialchars($reg_date); ?>"><br>
    <input type="submit" value="Update">
</form>
Provides an HTML form pre-filled with the record's data for editing. The form sends updated data via POST to edit_record.php.
Security Note:
Directly embedding user input into SQL queries is vulnerable to SQL injection attacks. Always use prepared statements with bound parameters to enhance security in production applications.

Step 4: Delete Record Script (delete_record.php)

Create a script (delete_record.php) to handle the deletion of a record.

delete_record.php

<?php include 'db_connect.php'; // Include the database connection script if ($_SERVER["REQUEST_METHOD"] == "GET" && isset($_GET['id'])) {     $id = htmlspecialchars(trim($_GET['id'])); // Sanitize input     // Create SQL delete query     $sql = "DELETE FROM MyGuests WHERE id = '$id'";     try {         // Execute the query         $result = $conn->query($sql);         if ($result) {             echo "Record deleted successfully";         } else {             echo "Error deleting record.";         }     } catch (PDOException $e) {         echo "Error: " . $e->getMessage();     }     // Close the database connection     $conn = null;     // Redirect to view_records.php     header("Location: view_records.php");     exit(); } ?>

Step-by-Step Explanation

Include the Database Connection Script:

include 'db_connect.php';
This line includes the db_connect.php file, which sets up the PDO connection to the database.

Check GET Request and Sanitize Input:

if ($_SERVER["REQUEST_METHOD"] == "GET" && isset($_GET['id'])) {
    $id = htmlspecialchars(trim($_GET['id']));
Check if the request method is GET and if the id parameter is present in the URL. Sanitize the id input.

Create and Execute SQL Delete Query:

$sql = "DELETE FROM MyGuests WHERE id = '$id'";
$result = $conn->query($sql);
Construct the SQL query to delete the record with the specified id and execute it using the query method.

Check If Deletion Was Successful:

if ($result) {
    echo "Record deleted successfully";
} else {
    echo "Error deleting record.";
}
If the query executes successfully, display a success message. If not, display an error message.

Handle Exceptions:

} catch (PDOException $e) {
    echo "Error: " . $e->getMessage();
}
Catch and display any PDO exceptions that occur during the query execution.

Close the Database Connection:

$conn = null;
Close the database connection by setting the PDO object to null.

Redirect to view_records.php:

header("Location: view_records.php");
exit();
Redirect the user to the view_records.php page and exit the script to ensure no further code is executed.
Note:
Using the query method with directly embedded user inputs (like $id) is unsafe as it can lead to SQL injection attacks. Always use prepared statements with parameter binding to prevent such risks.

Step 3 : To view all the records from Table

Open your web browser and navigate to the location where you placed the files. For example, if you're running a local server, the URL might be something like http://localhost/your-directory/view_records.php

Output:

Now if you click on edit then...

Now if you click on Delete then

Conclusion