FORM AND DATABASE CONNECTIVITY

With CRUD Operation

Using Object-Orinted type

Using prepared statement

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

// Create connection

$conn = new mysqli($servername, $username, $password, $dbname);

// Check connection

if ($conn->connect_error) {

die("Connection failed: " . $conn->connect_error);

}

echo "database connected";

Save the db_connect.php file.

Output:

This script establishes a connection to your MySQL database using the provided credentials.

If the connection is successful, the $conn variable will hold the connection object, and if there is an error, it will display an error message and terminate the script.

This connection script will be included in other PHP files where you need to interact with the database, ensuring that you have a consistent and centralized way of connecting to your database throughout your application.

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:

This HTML file contains a simple form with fields for the user's name, email, and password.

Here the form's action attribute is set to "process.php," indicating that when the user submits the form, the data will be sent to the process.php file for further handling.

When users fill out this form and click "Register," the data will be sent to the process.php script for processing and insertion into the MySQL database.

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

include 'db_connect.php'; // Include the database connection script

if ($_SERVER["REQUEST_METHOD"] == "POST") {

$firstname = $_POST['firstname'];

$lastname = $_POST['lastname'];

$email = $_POST['email'];

$reg_date = $_POST['reg_date'];

// Prepare and bind

$stmt = $conn->prepare("INSERT INTO MyGuests (firstname, lastname, email, reg_date) VALUES (?, ?, ?, ?)");

if ($stmt === false) {

die("Error preparing statement: " . $conn->error);

}

$stmt->bind_param("ssss", $firstname, $lastname, $email, $reg_date);

// Execute the statement

if ($stmt->execute() === true) {

echo "Registration successful!";

} else {

echo "Error: " . $stmt->error;

}

// Close the statement

$stmt->close();

}

// Close the database connection

$conn->close();

Step-by-step explanation :

Include the Database Connection Script:

<?php

include 'db_connect.php'; // Include the database connection script

This line includes the db_connect.php script, which typically contains the code to establish a connection to the MySQL database. This ensures that $conn is available for database operations.

Check the Request Method:

if ($_SERVER["REQUEST_METHOD"] == "POST") {

This block checks if the request method is POST. This ensures that the script only processes form submissions (which typically use the POST method).

Retrieve Form Data:

$firstname = $_POST['firstname'];
$lastname = $_POST['lastname'];
$email = $_POST['email'];
$reg_date = $_POST['reg_date'];

These lines retrieve the form data sent via the POST request and store them in PHP variables: $firstname, $lastname, $email, and $reg_date.

Prepare the SQL Statement:

$stmt = $conn->prepare("INSERT INTO MyGuests (firstname, lastname, email, reg_date) VALUES (?, ?, ?, ?)");

This line prepares an SQL statement for execution using the prepare() method.

The SQL statement inserts values into the firstname, lastname, email, and reg_date columns of the MyGuests table. The question marks (?) are placeholders for the actual values that will be bound to the statement later.

Check for Preparation Errors:

if ($stmt === false) {
die("Error preparing statement: " . $conn->error);
}

This block checks if the statement preparation was successful. If $stmt is false, it means there was an error in preparing the statement, and the script terminates with an error message.

Bind Parameters to the Prepared Statement:

$stmt->bind_param("ssss", $firstname, $lastname, $email, $reg_date);

This line binds the variables to the prepared statement using the bind_param() method.

The "ssss" string specifies that all four variables are strings. The values of $firstname, $lastname, $email, and $reg_date are bound to the corresponding placeholders in the SQL statement.

Execute the Statement and Check for Errors:

if ($stmt->execute() === true) {
echo "Registration successful!";
} else {
echo "Error: " . $stmt->error;

}

This block executes the prepared statement using the execute() method. If the execution is successful, a success message is displayed.

If there is an error during execution, an error message is displayed with the specific error.

Close the Statement:

$stmt->close();

This line closes the prepared statement using the close() method. This is good practice to free up resources associated with the statement.

Close the Database Connection:

}
// Close the database connection
$conn->close();
?>

Finally, this line closes the database connection using the close() method on the $conn object, ensuring that all resources are properly released.

This script handles the insertion of new records into the MyGuests table using prepared statements, which provide a secure and efficient way to interact with the database. By using prepared statements and parameter binding, the code avoids SQL injection vulnerabilities and ensures that the input data is handled safely.

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

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

// Step 4: Execute the prepared statement

$stmt->execute();

// Step 5: Bind the result variables

$stmt->bind_result($id, $firstname, $lastname, $email, $reg_date);

// Step 6: Check if there are results

if ($stmt->num_rows > 0) {

// Step 7: Output data of each row

echo "<table border='1'>

<tr>

<th>First Name</th>

<th>Email</th>

<th>Registration Date</th>

</tr>";

while ($stmt->fetch()) {

echo "<tr>

<td>" . $firstname . "</td>

<td>" . $lastname . "</td>

<td>" . $email . "</td>

</tr>";

}

echo "</table>";

} else {

echo "0 results";

}

// Step 8: Close the statement and the database connection

$stmt->close();

$conn->close();

Step-by-Step Explanation

Step 1 : Include the connection script file:

include 'db_connect.php';

This line includes the database connection script, which typically contains the code to establish a connection to the MySQL database.

Step 2: Prepare the SQL query:

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

The SQL query to retrieve the required columns (id, firstname, lastname, email, reg_date) from the MyGuests table is stored in the $sql variable.

Step 3: Prepare the statement:

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

The prepare() method is called on the database connection object $conn to prepare the SQL statement. This helps in preventing SQL injection attacks and improves performance.

Step 4: Execute the prepared statement:

$stmt->execute();

The execute() method is called on the prepared statement object $stmt to execute the SQL query.

step 5: Bind the result variables:

$stmt->bind_result($id, $firstname, $lastname, $email, $reg_date);

The bind_result() method is used to bind the columns from the result set to PHP variables. This allows us to fetch the values from each row one by one.

Step 6: Check if there are results:

if ($stmt->num_rows > 0) {

This line checks if the result set contains any rows. If there are rows, it proceeds to output the data; otherwise, it displays "0 results".

Step 7: 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 ($stmt->fetch()) {
echo "<tr>

<td>" . $id . "</td>
<td>" . $firstname . "</td>

<td>" . $lastname . "</td>
<td>" . $email . "</td>

<td>" . $reg_date . "</td>
</tr>";

}
echo "</table>";

If there are rows in the result set, this block of code generates an HTML table to display the data. It fetches each row using the fetch() method and outputs the data in table rows.

Step 8: Close the statement and the database connection:

$stmt->close();
$conn->close();

Finally, the close() method is called on both the statement object $stmt and the connection object $conn to free up the resources.

This approach ensures secure and efficient handling of SQL queries using prepared statements, which is a best practice for interacting with databases in PHP.

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

$id = $_POST['id']; // The ID of the record to edit

$firstname = $_POST['firstname'];

$lastname = $_POST['lastname'];

$email = $_POST['email'];

$reg_date = $_POST['reg_date'];

// Create SQL update query using prepared statements

$sql = "UPDATE MyGuests SET firstname=?, lastname=?, email=?, reg_date=? WHERE id=?";

// Prepare the statement

if ($stmt = $conn->prepare($sql)) {

// Bind parameters to the prepared statement

$stmt->bind_param("ssssi", $firstname, $lastname, $email, $reg_date, $id);

// Execute the statement

if ($stmt->execute()) {

echo "Record updated successfully";

} else {

echo "Error updating record: " . $stmt->error;

}

// Close the statement

$stmt->close();

} else {

echo "Error preparing statement: " . $conn->error;

}

// Close the database connection

$conn->close();

Step-by-Step Explanation

Step 1:Include the connection script file:

include 'db_connect.php';

This line includes the database connection script, which typically contains the code to establish a connection to the MySQL database.

Step 2:Check the request method:

if ($_SERVER["REQUEST_METHOD"] == "POST") {

This block checks if the request method is POST, indicating that form data has been submitted.

Step 3: Retrieve form data:

$id = $_POST['id'];
$firstname = $_POST['firstname'];
$lastname = $_POST['lastname'];
$email = $_POST['email'];
$reg_date = $_POST['reg_date'];
The form data is retrieved from the POST request and stored in PHP variables.

Step 4: Create SQL update query using prepared statements:

$sql = "UPDATE MyGuests SET firstname=?, lastname=?, email=?, reg_date=? WHERE id=?";

The SQL query is defined with placeholders (?) for the parameters that will be bound later.

Step 5: Prepare the statement:

if ($stmt = $conn->prepare($sql)) {

The prepare() method is called on the database connection object $conn to prepare the SQL statement. If the statement is prepared successfully, it proceeds to the next steps.

Step 6:Bind parameters to the prepared statement:

$stmt->bind_param("ssssi", $firstname, $lastname, $email, $reg_date, $id);

The bind_param() method is used to bind the variables to the prepared statement. The "ssssi" string indicates the types of the variables: s for string and i for integer.

Step 6: Execute the statement:

if ($stmt->execute()) {
echo "Record updated successfully";

} else {
echo "Error updating record: " . $stmt->error;

}
The execute() method is called on the prepared statement object $stmt to execute the SQL query. If the execution is successful, a success message is displayed; otherwise, an error message is displayed.

Step 7:Close the statement:

$stmt->close();

The close() method is called on the statement object $stmt to free up the resources.

Step 8: Close the database connection:

$conn->close();

Finally, the close() method is called on the database connection object $conn to close the connection.

This approach ensures secure and efficient handling of SQL queries using prepared statements, which is a best practice for interacting with databases in PHP.

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 form data

$id = $_POST['id']; // The ID of the record to delete

// Create SQL delete query using prepared statements

$sql = "DELETE FROM MyGuests WHERE id = ?";

// Prepare the statement

if ($stmt = $conn->prepare($sql)) {

// Bind parameters to the prepared statement

$stmt->bind_param("i", $id);

// Execute the statement

if ($stmt->execute()) {

echo "Record deleted successfully";

} else {

echo "Error deleting record: " . $stmt->error;

}

// Close the statement

$stmt->close();

} else {

echo "Error preparing statement: " . $conn->error;

}

// Close the database connection

$conn->close();

Step-by-Step Explanation

Step 1:Include the connection script file:

include 'db_connect.php';
This line includes the database connection script, which typically contains the code to establish a connection to the MySQL database.

Step 2: Check the request method:

if ($_SERVER["REQUEST_METHOD"] == "POST") {
This block checks if the request method is POST, indicating that form data has been submitted.

Step 3: Retrieve form data:

$id = $_POST['id'];
The form data is retrieved from the POST request and stored in the PHP variable $id.

Step 4: Create SQL delete query using prepared statements:

$sql = "DELETE FROM MyGuests WHERE id = ?";
The SQL query is defined with a placeholder (?) for the parameter that will be bound later.

Step 5: Prepare the statement:

if ($stmt = $conn->prepare($sql)) {
The prepare() method is called on the database connection object $conn to prepare the SQL statement. If the statement is prepared successfully, it proceeds to the next steps.

Step 6: Bind parameters to the prepared statement:

$stmt->bind_param("i", $id);
The bind_param() method is used to bind the variable $id to the prepared statement. The "i" string indicates that the variable is of integer type.

Step 7: Execute the statement:

if ($stmt->execute()) {
echo "Record deleted successfully";

} else {
echo "Error deleting record: " . $stmt->error;

Step 8: Close the statement:

$stmt->close();
The close() method is called on the statement object $stmt to free up the resources.

Step 9: Close the database connection:

$conn->close();
Finally, the close() method is called on the database connection object $conn to close the connection.

This approach ensures secure and efficient handling of SQL queries using prepared statements, which is a best practice for interacting with databases in PHP.

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 using prepared statements

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

// Prepare the statement

if ($stmt = $conn->prepare($sql)) {

// Execute the statement

$stmt->execute();

// Bind the result variables

$stmt->bind_result($id, $firstname, $lastname, $email, $reg_date);

// Store result to get number of rows

$stmt->store_result();

if ($stmt->num_rows > 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 ($stmt->fetch()) {

echo "<tr>";

echo "<td>" . htmlspecialchars($id) . "</td>";

echo "<td>" . htmlspecialchars($firstname) . "</td>";

echo "<td>" . htmlspecialchars($lastname) . "</td>";

echo "<td>" . htmlspecialchars($email) . "</td>";

echo "<td>" . htmlspecialchars($reg_date) . "</td>";

echo "<td>";

echo "<a href='edit_record.php?id=" . htmlspecialchars($id) . "'>Edit</a> | ";

echo "<a href='delete_record.php?id=" . htmlspecialchars($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";

}

// Close the statement

$stmt->close();

} else {

echo "Error preparing statement: " . $conn->error;

}

// Close the database connection

$conn->close();

Step-by-Step Explanation

Step 1:Include the connection script file:

include 'db_connect.php';

This line includes the database connection script, which typically contains the code to establish a connection to the MySQL database.

Step 2: Create SQL select query using prepared statements:

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

The SQL query is defined and stored in the $sql variable.

Step 3: Prepare the statement:

if ($stmt = $conn->prepare($sql)) {

The prepare() method is called on the database connection object $conn to prepare the SQL statement. If the statement is prepared successfully, it proceeds to the next steps.

Step 3: Execute the statement:

$stmt->execute();

The execute() method is called on the prepared statement object $stmt to execute the SQL query.

Step 4: Bind the result variables:

$stmt->bind_result($id, $firstname, $lastname, $email, $reg_date);

The bind_result() method is used to bind the columns from the result set to PHP variables.

Step 5:Store result to get the number of rows:

$stmt->store_result();

The store_result() method is called to buffer the entire result set in the statement handle, allowing the number of rows to be retrieved.

Step 6: Check if there are results:

if ($stmt->num_rows > 0) {

This line checks ig the result set contains any rows. If there are rows, it proceeds to output the data; otherwise, it displays "0 results".

Step 7: Fetch and display each row of the result set:

while ($stmt->fetch()) {
echo "<tr>";
echo "<td>" . htmlspecialchars($id) . "</td>";
echo "<td>" . htmlspecialchars($firstname) . "</td>";
echo "<td>" . htmlspecialchars($lastname) . "</td>";
echo "<td>" . htmlspecialchars($email) . "</td>";
echo "<td>" . htmlspecialchars($reg_date) . "</td>";

echo "<td>";
echo "<a href='edit_record.php?id=" . htmlspecialchars($id) . "'>Edit</a> | ";
echo "<a href='delete_record.php?id=" . htmlspecialchars($id) . "' onclick='return confirm(\"Are you sure you want to delete this record?\");'>Delete</a>";
echo "</td>";
echo "</tr>";

}
If there are rows in the result set, this block of code generates an HTML table to display the data. It fetches each row using the fetch() method and outputs the data in table rows. The htmlspecialchars() function is used to prevent XSS attacks by converting special characters to HTML entities.

Step 8: Close the statement:

$stmt->close();
The close() method is called on the statement object $stmt to free up the resources.

Step 9: Close the database connection:

$conn->close();

Finally, the close() method is called on the database connection object $conn to close the connection.

This approach ensures secure and efficient handling of SQL queries using prepared statements, which is a best practice for interacting with databases in PHP.

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 = $_GET['id'];

// Create SQL select query using prepared statements

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

// Prepare the statement

if ($stmt = $conn->prepare($sql)) {

// Bind parameters to the prepared statement

$stmt->bind_param("i", $id);

// Execute the statement

$stmt->execute();

// Bind the result variables

$stmt->bind_result($id, $firstname, $lastname, $email, $reg_date);

// Fetch the result

if ($stmt->fetch()) {

$row = [

'id' => $id,

'firstname' => $firstname,

'lastname' => $lastname,

'email' => $email,

'reg_date' => $reg_date

];

} else {

echo "Record not found.";

exit();

}

// Close the statement

$stmt->close();

} else {

echo "Error preparing statement: " . $conn->error;

exit();

}

} elseif ($_SERVER["REQUEST_METHOD"] == "POST") {

// Handle the update operation

$id = $_POST['id'];

$firstname = $_POST['firstname'];

$lastname = $_POST['lastname'];

$email = $_POST['email'];

$reg_date = $_POST['reg_date'];

// Create SQL update query using prepared statements

$sql = "UPDATE MyGuests SET firstname=?, lastname=?, email=?, reg_date=? WHERE id=?";

// Prepare the statement

if ($stmt = $conn->prepare($sql)) {

// Bind parameters to the prepared statement

$stmt->bind_param("sssii", $firstname, $lastname, $email, $reg_date, $id);

// Execute the statement

if ($stmt->execute()) {

echo "Record updated successfully";

} else {

echo "Error updating record: " . $stmt->error;

}

// Close the statement

$stmt->close();

} else {

echo "Error preparing statement: " . $conn->error;

}

// Close the database connection

$conn->close();

header("Location: view_records.php");

exit();

}

<!DOCTYPE html>

<html>

<head>

<title>Edit Record</title>

</head>

<body>

First Name: <input type="text" name="firstname" value="<?php echo htmlspecialchars($row['firstname']); ?>"><br>

Last Name: <input type="text" name="lastname" value="<?php echo htmlspecialchars($row['lastname']); ?>"><br>

Email: <input type="email" name="email" value="<?php echo htmlspecialchars($row['email']); ?>"><br>

Registration Date: <input type="date" name="reg_date" value="<?php echo htmlspecialchars($row['reg_date']); ?>"><br>

</form>

</body>

</html>

Step-by-Step Explanation

Include the connection script file:

include 'db_connect.php';

This line includes the database connection script, which typically contains the code to establish a connection to the MySQL database.

Check if the request method is GET and the 'id' parameter is set:

if ($_SERVER["REQUEST_METHOD"] == "GET" && isset($_GET['id'])) {

$id = $_GET['id'];

This block checks if the request method is GET and if the id parameter is set. If true, it proceeds to retrieve the record with the specified id.

Create SQL select query using prepared statements:

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

The SQL query is defined with a placeholder (?) for the id parameter.

Prepare the statement:

if ($stmt = $conn->prepare($sql)) {

The prepare() method is called on the database connection object $conn to prepare the SQL statement. If the statement is prepared successfully, it proceeds to the next steps.

Bind parameters to the prepared statement:

$stmt->bind_param("i", $id);

The bind_param() method is used to bind the variable $id to the prepared statement. The "i" string indicates that the variable is of integer type.

Execute the statement:

$stmt->execute();

The execute() method is called on the prepared statement object $stmt to execute the SQL query.

Bind the result variables:

$stmt->bind_result($id, $firstname, $lastname, $email, $reg_date);

The bind_result() method is used to bind the columns from the result set to PHP variables.

Fetch the result:

if ($stmt->fetch()) {
$row = [

'id' => $id,
'firstname' => $firstname,

'lastname' => $lastname,
'email' => $email,

'reg_date' => $reg_date
];

} else {
echo "Record not found.";

exit();
}

The fetch() method is used to retrieve the result. If a record is found, it is stored in the $row array; otherwise, a message is displayed, and the script exits.

Handle the POST request for updating the record:

} elseif ($_SERVER["REQUEST_METHOD"] == "POST") {
$id = $_POST['id'];

$firstname = $_POST['firstname'];
$lastname = $_POST['lastname'];

$email = $_POST['email'];
$reg_date = $_POST['reg_date'];

Create SQL update query using prepared statements:

$sql = "UPDATE MyGuests SET firstname=?, lastname=?, email=?, reg_date=? WHERE id=?";

Prepare the statement:

if ($stmt = $conn->prepare($sql)) {

Bind parameters to the prepared statement:

$stmt->bind_param("sssii", $firstname, $lastname, $email, $reg_date, $id);

Execute the statement:

if ($stmt->execute()) {

echo "Record updated successfully";

} else {
echo "Error updating record: " . $stmt->error;

}

Close the statement and the database connection:

$stmt->close();
$conn->close();
header("Location: view_records.php");
exit();

HTML Form:

First Name: <input type="text" name="firstname" value="<?php echo htmlspecialchars($row['firstname']); ?>"><br>
Last Name: <input type="text" name="lastname" value="<?php echo htmlspecialchars($row['lastname']); ?>"><br>
Email: <input type="email" name="email" value="<?php echo htmlspecialchars($row['email']); ?>"><br>
Registration Date: <input type="date" name="reg_date" value="<?php echo htmlspecialchars($row['reg_date']); ?>"><br>

</form>

This approach ensures secure and efficient handling of SQL queries using prepared statements, which is a best practice for interacting with databases in PHP.

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 = $_GET['id'];

// Create SQL delete query using prepared statements

$sql = "DELETE FROM MyGuests WHERE id = ?";

// Prepare the statement

if ($stmt = $conn->prepare($sql)) {

// Bind parameters to the prepared statement

$stmt->bind_param("i", $id);

// Execute the statement

if ($stmt->execute()) {

echo "Record deleted successfully";

} else {

echo "Error deleting record: " . $stmt->error;

}

// Close the statement

$stmt->close();

} else {

echo "Error preparing statement: " . $conn->error;

}

// Close the database connection

$conn->close();

header("Location: view_records.php");

exit();

}

Step-by-Step Explanation

Step 1:Include the connection script file:

include 'db_connect.php';

This line includes the database connection script, which typically contains the code to establish a connection to the MySQL database.

Step 2: if the request method is GET and the 'id' parameter is set:

if ($_SERVER["REQUEST_METHOD"] == "GET" && isset($_GET['id'])) {

$id = $_GET['id'];

This block checks if the request method is GET and if the id parameter is set. If true, it proceeds to delete the record with the specified id.

Step 3:Create SQL delete query using prepared statements:

$sql = "DELETE FROM MyGuests WHERE id = ?";

The SQL query is defined with a placeholder (?) for the id parameter.

Step 4: Prepare the statement:

if ($stmt = $conn->prepare($sql)) {

The prepare() method is called on the database connection object $conn to prepare the SQL statement. If the statement is prepared successfully, it proceeds to the next steps.

Step 5: Bind parameters to the prepared statement:

$stmt->bind_param("i", $id);

The bind_param() method is used to bind the variable $id to the prepared statement. The "i" string indicates that the variable is of integer type.

Step 6:Execute the statement:

if ($stmt->execute()) {

echo "Record deleted successfully";

} else {

echo "Error deleting record: " . $stmt->error;

}

The execute() method is called on the prepared statement object $stmt to execute the SQL query. If the execution is successful, a success message is displayed; otherwise, an error message is displayed.

Step 7: Close the statement:

$stmt->close();

The close() method is called on the statement object $stmt to free up the resources.

Step 8:Close the database connection:

$conn->close();

Finally, the close() method is called on the database connection object $conn to close the connection.

Step 9: Redirect to view_records.php:

header("Location: view_records.php");

exit();

This line redirects the user to the view_records.php page after the record is deleted and ensures that no further code is executed by calling exit().

This approach ensures secure and efficient handling of SQL queries using prepared statements, which is a best practice for interacting with databases in PHP.

PHP 101: Learn the Basics and Beyond

Wednesday, July 17, 2024

FORM AND DATABASE CONNECTIVITY WITH CRUD Operation(Using Prepared Statement)

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 3: Create HTML Form

3.1 Create HTML Form:

Step 4: Create PHP Processing Script

4.1 Create PHP Processing Script:

Step-by-step explanation :

Include the Database Connection Script:

This line includes the db_connect.php script, which typically contains the code to establish a connection to the MySQL database. This ensures that $conn is available for database operations.

Check the Request Method:

Retrieve Form Data:

Prepare the SQL Statement:

Check for Preparation Errors:

Bind Parameters to the Prepared Statement:

Execute the Statement and Check for Errors:

Close the Statement:

Close the Database Connection:

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

Step 1 : Include the connection script file:

Step 2: Prepare the SQL query:

Step 3: Prepare the statement:

Step 4: Execute the prepared statement:

step 5: Bind the result variables:

Step 6: Check if there are results:

Step 7: Output data of each row:

Step 8: Close the statement and the database connection:

Step 3 : To view Output from Table

Edit Data From Database

Step 1: Establish Database Connection

Step 2: Create the HTML Form (Optional)

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

Step-by-Step Explanation

Step 1:Include the connection script file:

Step 2:Check the request method:

Step 3: Retrieve form data:

Step 4: Create SQL update query using prepared statements:

Step 5: Prepare the statement:

Step 6:Bind parameters to the prepared statement:

Step 6: Execute the statement:

Step 7:Close the statement:

Step 8: Close the database connection:

Delete Data from Database

Step-by-Step Guide to Delete Data Using PHP

Step 1: Establish Database Connection

Step 2: Create the HTML Form or Determine Deletion Criteria

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

Step-by-Step Explanation

Step 1:Include the connection script file:

Step 2: Check the request method:

Step 3: Retrieve form data:

Step 4: Create SQL delete query using prepared statements:

Step 5: Prepare the statement:

Step 6: Bind parameters to the prepared statement:

Step 7: Execute the statement:

Step 8: Close the statement:

Step 9: Close the database connection:

Step 3 : To delete data in Table

next