FORM AND DATABASE CONNECTIVITY

With CRUD Operation

Using PDO

With Prepared Statements

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:

Open your MySQL management tool (like phpMyAdmin, MySQL Workbench, or use command-line interface).
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.
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

The script initializes the database connection parameters.
It attempts to create a new PDO instance to connect to the database.
It sets the error mode to exception.
If the connection is successful, it prints a success message.
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>

<head>

<title>User Registration</title>

</head>

<body>

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

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

</form>

</body>

</html>

Save the index.html file.

Output:

Step-by-step Explanation :

DOCTYPE Declaration and HTML Tag:

<!DOCTYPE html>

<!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>

<title>User Registration</title>

</head>

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

<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">: 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>

<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:

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>

<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>: 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">: 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:

Validate the input data (e.g., check that all required fields are filled, the email format is correct, the registration date is valid, etc.).
Sanitize the data to prevent security vulnerabilities like SQL injection or cross-site scripting (XSS).
Save the data to a database.
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") {

// Check if the request method is POST

// This ensures that the code only runs when the form is submitted via POST method

// 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']));

// htmlspecialchars() converts special characters to HTML entities to prevent XSS attacks

// trim() removes whitespace from the beginning and end of a string

// 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)) {

// Check if all required fields are not empty and if the email is valid

// Include the database connection file

include 'db_connect.php';

// This file should contain the code to connect to the database

try {

// Construct the SQL query with prepared statements

$sql = "INSERT INTO users (firstname, lastname, email, reg_date) VALUES (:firstname, :lastname, :email, :reg_date)";

// Prepare the statement

$stmt = $conn->prepare($sql);

// $conn is the PDO connection object created in 'db_connect.php'

// prepare() prepares the SQL statement for execution

// Bind parameters

$stmt->bindParam(':firstname', $firstname);

$stmt->bindParam(':lastname', $lastname);

$stmt->bindParam(':email', $email);

$stmt->bindParam(':reg_date', $reg_date);

// bindParam() binds the input data to the placeholders in the SQL query

// This prevents SQL injection by treating the input as data rather than part of the SQL query

// Execute the statement

$stmt->execute();

// execute() runs the prepared statement with the bound parameters

echo "Registration successful!";

} catch (PDOException $e) {

// If there is an error during the execution, it is caught here

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

}

// Close the connection

$conn = null;

// This ensures the connection is properly closed after the operation is done

} else {

// If the input data is invalid, this message is shown

echo "Invalid input data.";

}

Step-by-Step Explanation:

Check the Request Method:

if ($_SERVER["REQUEST_METHOD"] == "POST") {
This condition ensures that the code inside the block only runs when the form is submitted via the POST method.

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() removes any whitespace from the beginning and end of the input.
htmlspecialchars() converts special characters to HTML entities to prevent XSS attacks.

Validate Data:

if (!empty($firstname) && !empty($lastname) && filter_var($email, FILTER_VALIDATE_EMAIL) && !empty($reg_date)) {
This checks if all required fields are not empty and if the email address is valid.

Include Database Connection:

php
Copy code
include 'db_connect.php';
This includes the database connection file, which should contain the code to connect to the database using PDO.

Construct and Prepare SQL Query:

$sql = "INSERT INTO users (firstname, lastname, email, reg_date) VALUES (:firstname, :lastname, :email, :reg_date)";
$stmt = $conn->prepare($sql);
A SQL query with placeholders is created.
The prepare() method prepares the SQL statement for execution.

Bind Parameters:

$stmt->bindParam(':firstname', $firstname);
$stmt->bindParam(':lastname', $lastname);
$stmt->bindParam(':email', $email);
$stmt->bindParam(':reg_date', $reg_date);
The bindParam() method binds the input data to the placeholders in the SQL query.
This step ensures that the input data is treated as data and not part of the SQL query, which helps prevent SQL injection.

Execute the Statement:

$stmt->execute();
The execute() method runs the prepared statement with the bound parameters.

Success Message:

echo "Registration successful!";
This message is displayed if the data is inserted successfully.

Error Handling:

} catch (PDOException $e) {
echo "Error: " . $e->getMessage();
}
If an error occurs during the execution of the SQL query, it is caught and the error message is displayed.

Close the Connection:

$conn = null;
This ensures that the database connection is properly closed after the operation is done.

Invalid Input Data Message:

} else {
echo "Invalid input data.";
}
If the input data validation fails, this message is shown to indicate invalid input data.
This approach ensures secure data handling, proper error management, and prevents common web security issues like SQL injection and XSS attacks.

Summary:

Form Handling: The script processes the form data submitted via POST request.
Data Sanitization: Input data is sanitized to prevent XSS attacks.
Data Validation: Ensures that required fields are not empty and the email format is valid.
Database Connection: The database connection is included from an external file (db_connect.php).
SQL Query Execution: An SQL query is constructed and executed to insert the user data into the database.
Error Handling: Exceptions during database operations are caught and displayed.
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: Prepare the statement

$stmt = $conn->prepare($sql);

// The prepare() method is used to prepare the SQL statement for execution.

// This is done to protect against SQL injection, as it allows the database to properly parse and escape input data.

// Step 4: Execute the query

$stmt->execute();

// The execute() method runs the prepared statement.

// Since this is a SELECT statement, there are no parameters to bind, so execute() is called directly.

// Step 5: Check if there are results

if ($stmt->rowCount() > 0) {

// The rowCount() method returns the number of rows affected by the last SQL statement.

// For a SELECT statement, it returns the number of rows in the result set.

// Step 6: Output data of each row

echo "<table border='1'>

<tr>

<th>First Name</th>

<th>Email</th>

<th>Registration Date</th>

</tr>";

// This creates an HTML table and sets up the headers.

while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {

// The fetch() method fetches the next row from the result set.

// The PDO::FETCH_ASSOC mode returns the row as an associative array, where the column names are the keys.

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>";

// This creates a new table row for each record in the result set.

// htmlspecialchars() is used to convert special characters to HTML entities, preventing XSS attacks.

}

echo "</table>";

// This closes the HTML table.

} else {

// If no rows are returned, this message is displayed.

echo "0 results";

}

} catch (PDOException $e) {

// If an error occurs during the execution of the SQL query, it is caught here.

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

// The error message is displayed.

}

// Step 7: Close the database connection

$conn = null;

// This ensures that the database connection is properly closed after the operation is done.

Step-by-Step Explanation

Include the connection script file:

include 'db_connect.php';

This includes the database connection file, which should contain the code to connect to the database using PDO.

Prepare the SQL query:

$sql = "SELECT id, firstname, lastname, email, reg_date FROM MyGuests";

This SQL query selects the id, firstname, lastname, email, and reg_date columns from the MyGuests table.

Prepare the statement:

$stmt = $conn->prepare($sql);

prepare() prepares the SQL statement for execution. It returns a PDOStatement object.

Execute the query:

$stmt->execute();

execute() runs the prepared statement.

Check if there are results:

if ($stmt->rowCount() > 0) {

rowCount() returns the number of rows in the result set. If it's greater than 0, it means there are results to display.

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>";

This creates an HTML table with headers for each column.

while ($row = $stmt->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>";

}

fetch(PDO::FETCH_ASSOC) retrieves the next row from the result set as an associative array.

htmlspecialchars() is used to convert special characters to HTML entities to prevent XSS attacks.

Each row is output as a new row in the HTML table.

echo "</table>";

This closes the HTML table.

Handle no results case:

} else {

echo "0 results";

}

If no rows are returned, this message is displayed.

Error handling:

} catch (PDOException $e) {

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

}

If an error occurs during the execution of the SQL query, it is caught here, and the error message is displayed.

Close the database connection:

$conn = null;

This ensures that the database connection is properly closed after the operation is done.

This approach ensures secure data handling, proper error management, and prevents common web security issues like SQL injection and XSS attacks.

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>

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>

</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";

// Prepare the statement

$stmt = $conn->prepare($sql);

// Bind parameters

$stmt->bindParam(':id', $id);

$stmt->bindParam(':firstname', $firstname);

$stmt->bindParam(':lastname', $lastname);

$stmt->bindParam(':email', $email);

$stmt->bindParam(':reg_date', $reg_date);

// Execute the query

$stmt->execute();

// Check if the record was updated

if ($stmt->rowCount() > 0) {

echo "Record updated successfully";

} else {

echo "No changes made to the record";

}

} catch (PDOException $e) {

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

}

// Close the database connection

$conn = null;

Step-by-Step Explanation:

Include the connection script file:

include 'db_connect.php';
This includes the database connection file, which should contain the code to connect to the database using PDO.

Check the request method:

if ($_SERVER["REQUEST_METHOD"] == "POST") {
This ensures the code inside the block only runs when the form is submitted via the POST method.

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']));
trim() removes any whitespace from the beginning and end of the input.
htmlspecialchars() converts special characters to HTML entities to prevent XSS attacks.

Create SQL update query:

$sql = "UPDATE MyGuests SET firstname = :firstname, lastname = :lastname, email = :email, reg_date = :reg_date WHERE id = :id";
This SQL query updates the specified columns in the MyGuests table where the id matches.

Prepare the statement:

$stmt = $conn->prepare($sql);
prepare() prepares the SQL statement for execution.

Bind parameters:

$stmt->bindParam(':id', $id);
$stmt->bindParam(':firstname', $firstname);
$stmt->bindParam(':lastname', $lastname);
$stmt->bindParam(':email', $email);
$stmt->bindParam(':reg_date', $reg_date);
bindParam() binds the input data to the placeholders in the SQL query.

Execute the query:

$stmt->execute();
execute() runs the prepared statement.

Check if the record was updated:

if ($stmt->rowCount() > 0) {
echo "Record updated successfully";
} else {
echo "No changes made to the record";
}
rowCount() returns the number of rows affected by the last SQL statement.
If rows were updated, a success message is displayed; otherwise, a message indicating no changes were made is displayed.

Error handling:

} catch (PDOException $e) {
echo "Error: " . $e->getMessage();
}
If an error occurs during the execution of the SQL query, it is caught here, and the error message is displayed.

Close the database connection:

$conn = null;
This ensures that the database connection is properly closed after the operation is done.
This approach ensures secure data handling, proper error management, and prevents common web security issues like SQL injection and XSS attacks.

Step 3 : To Edit data in Table

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>

ID: <input type="text" name="id"><br>

</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";

// Prepare the statement

$stmt = $conn->prepare($sql);

// Bind the parameter

$stmt->bindParam(':id', $id);

// Execute the query

$stmt->execute();

// Check if the query was successful

if ($stmt->rowCount() > 0) {

echo "Record deleted successfully";

} else {

echo "No record found with the given ID";

}

} catch (PDOException $e) {

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

}

// Close the database connection

$conn = null;

Step-by-Step Explanation:

Step 3 : To delete data in Table

Output:

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

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:

Retrieve and display records from the database.
Provide options as "Edit" and "Delete" buttons for each record.
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 {

// Prepare the statement

$stmt = $conn->prepare($sql);

// Execute the query

$stmt->execute();

// Check if there are rows in the result set

if ($stmt->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 = $stmt->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

Detailed Explanation:

Include the connection script file:

php

Copy code

include 'db_connect.php';

This includes the database connection file, which should contain the code to connect to the database using PDO.

Create SQL select query:

php

Copy code

$sql = "SELECT id, firstname, lastname, email, reg_date FROM MyGuests";

This SQL query selects the id, firstname, lastname, email, and reg_date columns from the MyGuests table.

Prepare the statement:

php

Copy code

$stmt = $conn->prepare($sql);

prepare() prepares the SQL statement for execution.

Execute the query:

php

Copy code

$stmt->execute();

execute() runs the prepared statement.

Check if there are rows in the result set:

php

Copy code

if ($stmt->rowCount() > 0) {

rowCount() returns the number of rows in the result set. If it's greater than 0, it means there are results to display.

Display 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>";

This creates an HTML table and sets up the headers.

Fetch and display each row of the result set:

php

Copy code

while ($row = $stmt->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>";

}

fetch(PDO::FETCH_ASSOC) retrieves the next row from the result set as an associative array.

htmlspecialchars() is used to convert special characters to HTML entities to prevent XSS attacks.

Each row is output as a new row in the HTML table with "Edit" and "Delete" action links.

Handle no results case:

php

Copy code

} else {

echo "0 results";

}

If no rows are returned, this message is displayed.

Error handling:

php

Copy code

} catch (PDOException $e) {

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

}

If an error occurs during the execution of the SQL query, it is caught here, and the error message is displayed.

Close the database connection:

php

Copy code

$conn = null;

This ensures that the database connection is properly closed after the operation is done.

This approach ensures secure data handling, proper error management, and prevents common web security issues like SQL injection and XSS attacks.

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

Here's the provided PHP code rewritten to use PDO with prepared statements:

php

Copy code

<?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 {

// Prepare the statement

$stmt = $conn->prepare($sql);

// Bind the parameter

$stmt->bindParam(':id', $id);

// Execute the query

$stmt->execute();

// Fetch the result

if ($row = $stmt->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 {

// Prepare the statement

$stmt = $conn->prepare($sql);

// Bind the parameters

$stmt->bindParam(':id', $id);

$stmt->bindParam(':firstname', $firstname);

$stmt->bindParam(':lastname', $lastname);

$stmt->bindParam(':email', $email);

$stmt->bindParam(':reg_date', $reg_date);

// Execute the query

$stmt->execute();

if ($stmt->rowCount() > 0) {

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>

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>

</form>

</body>

</html>

Step-by-Step Explanation

Detailed Explanation:
Include the connection script file:
php
Copy code
include 'db_connect.php';
This includes the database connection file, which should contain the code to connect to the database using PDO.
Check the request method and if id is set in the GET request:
php
Copy code
if ($_SERVER["REQUEST_METHOD"] == "GET" && isset($_GET['id'])) {
This ensures the code inside the block only runs when the page is accessed via a GET request and id is set.
Retrieve and sanitize the id from the GET request:
php
Copy code
$id = htmlspecialchars(trim($_GET['id']));
Create and prepare the SQL select query:
php
Copy code
$sql = "SELECT id, firstname, lastname, email, reg_date FROM MyGuests WHERE id = :id";
$stmt = $conn->prepare($sql);
$stmt->bindParam(':id', $id);
$stmt->execute();
Fetch the result and check if a record was found:
php
Copy code
if ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
// Sanitize and store the retrieved data
$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();
}
Check if the request method is POST for the update operation:
php
Copy code
} elseif ($_SERVER["REQUEST_METHOD"] == "POST") {
Retrieve and sanitize form data from the POST request:
php
Copy code
$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 and prepare the SQL update query:
php
Copy code
$sql = "UPDATE MyGuests SET firstname = :firstname, lastname = :lastname, email = :email, reg_date = :reg_date WHERE id = :id";
$stmt = $conn->prepare($sql);
$stmt->bindParam(':id', $id);
$stmt->bindParam(':firstname', $firstname);
$stmt->bindParam(':lastname', $lastname);
$stmt->bindParam(':email', $email);
$stmt->bindParam(':reg_date', $reg_date);
$stmt->execute();
Check if the update was successful:
php
Copy code
if ($stmt->rowCount() > 0) {
echo "Record updated successfully";
header("Location: view_records.php");
exit();
} else {
echo "Error updating record.";
}
Handle any exceptions:
php
Copy code
} catch (PDOException $e) {
echo "Error: " . $e->getMessage();
}
Close the database connection:
php
Copy code
$conn = null;
HTML Form for updating the record:
html
Copy code
<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>
This approach ensures secure data handling, proper error management, and prevents common web security issues like SQL injection and XSS attacks.

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

Here's how to rewrite the given PHP code using PDO with prepared statements:

php

Copy code

<?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 {

// Prepare the statement

$stmt = $conn->prepare($sql);

// Bind the parameter

$stmt->bindParam(':id', $id, PDO::PARAM_INT);

// Execute the query

$stmt->execute();

if ($stmt->rowCount() > 0) {

echo "Record deleted successfully";

} else {

echo "No record deleted. It might not exist.";

}

} catch (PDOException $e) {

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

}

// Close the database connection

$conn = null;

// Redirect to view_records.php

header("Location: view_records.php");

exit();

}

PHP 101: Learn the Basics and Beyond

Monday, July 22, 2024

FORM AND DATABASE CONNECTIVITY With CRUD Operation(PDO With Prepared Statements)

FORM AND DATABASE CONNECTIVITY

Prerequisite:

Here are the steps:

1. Step 1. Start XAMPP:

2. Step 2: Start Apache:

3. Step 3: Start MySQL:

4. Step 4: Verify Running Services:

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

Step 1: Set Up Your Database

1.1 Create a Database:

Creating a Database Using phpMyAdmin

1.2 Create a Table:

Type 1 : Creating Tables Using phpMyAdmin

Step 2: Create a Connection Script

2.1 Create a Connection Script:

Step-by-step explanation :

Step 1: Setting Up Variables

Step 2: Create Connection:

Set PDO Error Mode:

Success Message:

Exception Handling:

Step 3: Create HTML Form

3.1 Create HTML Form:

Step-by-step Explanation :

DOCTYPE Declaration and HTML Tag:

Head Section:

Body Section:

Form Tag:

First Name Field:

Last Name Field:

Email Field:

Registration Date Field:

Submit Button:

Closing Tags:

what this form do ?

4.1 Create PHP Processing Script:

Step-by-Step Explanation:

Check the Request Method:

Collect and Sanitize Input Data:

Validate Data:

Include Database Connection:

Construct and Prepare SQL Query:

Bind Parameters:

Execute the Statement:

Success Message:

Error Handling:

Close the Connection:

Invalid Input Data Message:

Summary:

Step 5. Test Your Form:

5.1 Test Your Form:

Steps to run your project :

Data Retrieve From Database

Step 1: Establish Database Connection

Step 2: Create the Main PHP Script to Retrieve Data

Step-by-Step Explanation

Include the connection script file:

include 'db_connect.php';

This includes the database connection file, which should contain the code to connect to the database using PDO.

Prepare the SQL query:

$sql = "SELECT id, firstname, lastname, email, reg_date FROM MyGuests";

This SQL query selects the id, firstname, lastname, email, and reg_date columns from the MyGuests table.

Prepare the statement:

$stmt = $conn->prepare($sql);

prepare() prepares the SQL statement for execution. It returns a PDOStatement object.

Execute the query:

$stmt->execute();

execute() runs the prepared statement.

Check if there are results:

if ($stmt->rowCount() > 0) {

rowCount() returns the number of rows in the result set. If it's greater than 0, it means there are results to display.

Output data of each row:

echo "<table border='1'>

<tr>

<th>ID</th>

<th>First Name</th>

<th>Last Name</th>