FORM AND DATABASE CONNECTIVITY

With CRUD Operations

Using Procedural approach

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 for connection script should be : db_connect.php/database_connect.php/config.php

Here I 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 = mysqli_connect($servername, $username, $password,$dbname);

// Check connection

if (!$conn) {

die("Connection failed: " . mysqli_connect_error());

}

echo "Connected successfully";

Save the db_connect.php file.

Step by Step Explanation:

Step 1: Setting Up Variables

$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

$servername: This variable holds the name of the server where your MySQL database is located. localhost means your database is on the same machine as the script.

$username: This variable stores the username you use to log in to the MySQL server. The default username for many MySQL installations is root.

$password: This variable holds the password for your MySQL user. It's currently empty, which is common for local development setups, but should be set to a secure password in a production environment.

$dbname: This variable contains the name of the database you want to connect to. Here, it's set to my_DB.

Step 2: Creating the Connection to the Database

$conn = mysqli_connect($servername, $username, $password, $dbname);
mysqli_connect($servername, $username, $password, $dbname); attempts to establish a connection to the MySQL database using the provided server name, username, password, and database name.
$conn: The function returns a connection object ($conn) if the connection is successful or false if it fails.
Step 3: Checking the Connection

if (!$conn) {
die("Connection failed: " . mysqli_connect_error());
}
if (!$conn) checks if the connection failed by testing if $conn is false.
If the connection failed, die("Connection failed: " . mysqli_connect_error()); terminates the script and outputs an error message. mysqli_connect_error() returns a string describing the error.

Step 4: Outputting a Success Message

echo "Connected successfully";
If the connection is successful (i.e., $conn is not false), this line outputs "Connected successfully" to indicate that the connection to the database was established successfully.
This PHP script connects to a MySQL database using the MySQLi extension.
Summary
The script defines the necessary parameters to connect to a MySQL database using the MySQLi extension ($servername, $username, $password, $dbname).
It attempts to establish a connection to the database using mysqli_connect().
It checks if the connection was successful.
If not, it outputs an error message and terminates the script.
If successful, it outputs a success message.
Output:

Run this file to check database connectivity.

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/db_connect.php

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.

Step-by-Step Explanation

Document Type Declaration

<!DOCTYPE html>

This tells the web browser that the document is an HTML5 document.

HTML Tag and Language Attribute

The <html> tag is the root element of the HTML document.

The lang="en" attribute specifies the language of the document as English.

Head Section

<head>

<title>User Registration</title>

</head>

<head>: This section contains meta-information about the document.

<title>User Registration</title>: Sets the title of the document, which appears in the browser tab.

Body Section

<body>

...

</body>

The <body> tag contains the content of the HTML document that is visible to the user.

Form Tag

...

</form>

<form>: This tag creates a form for user input.

action="process.php": Specifies the server-side script (process.php) that will handle the form data.

method="post": Specifies the HTTP method to use when sending the form data (POST method is used here).

Label and Input for First Name

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

<label for="firstname">First Name:</label>: A label for the first name input field. The for attribute connects the label to the input field with the id firstname.

<input type="text" id="firstname" name="firstname" required>: A text input field for the user's first name. The id and name attributes are both set to "firstname". The required attribute means the user must fill out this field before submitting the form.

<br>: A line break to move the next element to a new line.

Label and Input for Last Name

Similar to the first name field, but for the last name. The id and name attributes are set to "lastname".

Label and Input for Email

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

<label for="email">Email:</label>: A label for the email input field.

<input type="email" id="email" name="email" required>: An email input field. The type="email" ensures that the input must be a valid email address.

Label and Input for Registration Date

<label for="reg_date">Reg Date:</label>: A label for the registration date input field.

<input type="date" id="reg_date" name="reg_date" required>: A date input field. The type="date" allows the user to select a date from a date picker.

Submit Button

<input type="submit" value="Register">: A submit button that submits the form. The value="Register" sets the text displayed on the button.

Summary:

This HTML code creates a user registration form that collects the user's first name, last name, email, and registration date.
When the form is submitted, the data is sent to the process.php script using the POST method.
Each input field is labeled and required, ensuring that the user must fill out all fields before submitting the form.

Output:

Run this file to check the form .

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'];
$sql = "INSERT INTO MyGuests(firstname,lastname, email, reg_date) VALUES ('$firstname', '$lastname','$email', '$reg_date')";
if (mysqli_query($conn, $sql)) {
echo "Registration successful";
} else {
echo "Error: " . $sql . "<br>" . mysqli_error($conn);
}

mysqli_close($conn);
}
?>

Save the process.php file.
Step by Step Code Explanation:

Step 1:Include Database Connection Script

include 'db_connect.php';

This line includes the db_connect.php script, which is assumed to contain the code to establish a connection to the database.

Step 2: Check Request Method

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

This condition checks if the request method is POST. This means the following code block will only run if the form was submitted using the POST method.

Step 3: Retrieve Form Data

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

These lines retrieve the values from the POST request and store them in PHP variables:

$firstname = $_POST['firstname']; assigns the submitted value for the first name to the $firstname variable.

$lastname = $_POST['lastname']; assigns the submitted value for the last name to the $lastname variable.

$email = $_POST['email']; assigns the submitted value for the email to the $email variable.

$reg_date = $_POST['reg_date']; assigns the submitted value for the registration date to the $reg_date variable.

Step 4: Insert Data into Database/Construct SQL query

$sql = "INSERT INTO MyGuests (firstname, lastname, email, reg_date) VALUES ('$firstname', '$lastname', '$email', '$reg_date')";

This line constructs an SQL query to insert a new record into the MyGuests table. The INSERT INTO statement specifies the table (MyGuests) and the columns (firstname, lastname, email, reg_date) to which the values should be inserted.

The values are taken from the PHP variables ($firstname, $lastname, $email, $reg_date) and inserted into the query.

Execute the SQL query and handle the result:

if (mysqli_query($conn, $sql)) {

echo "Registration successful";

} else {

echo "Error: " . $sql . "<br>" . mysqli_error($conn);

}

if (mysqli_query($conn, $sql)) { ... } attempts to execute the SQL query using the mysqli_query() function.

If the query is successful, it outputs "Registration successful".

If the query fails, it outputs an error message that includes the SQL query and the error returned by mysqli_error($conn). This helps in diagnosing the problem by providing details of the SQL query and the specific error message.

Close the database connection:

mysqli_close($conn);
This line closes the database connection using mysqli_close($conn), ensuring that the connection is properly terminated and resources are freed.
Summary

The script starts by including a database connection script to establish a connection to the database.
It then checks if the request method is POST, indicating that the form has been submitted.
It retrieves the form data from the POST request.
It constructs an SQL INSERT INTO query to add the new record to the MyGuests table.
It executes the SQL query and outputs a success or error message based on the result.
Finally, it closes the database connection to free up resources.
By following these steps, the script ensures that data submitted via a form is safely inserted into the database and handles any errors that may occur during the process.

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 connection script file

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

// Step 2: Write the SQL query

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

// Step 3: Execute the query

$result = mysqli_query($conn, $sql);

// Step 4: Check if the query was successful and there are results

if (mysqli_num_rows($result) > 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 = mysqli_fetch_assoc($result)) {

echo "<tr>";

echo "<td>" . $row["id"] . "</td>";

echo "<td>" . $row["firstname"] . "</td>";

echo "<td>" . $row["lastname"] . "</td>";

echo "<td>" . $row["email"] . "</td>";

echo "<td>" . $row["reg_date"] . "</td>";

echo "</tr>";

}

echo "</table>";

} else {

echo "0 results";

}

// Step 6: Close the database connection

mysqli_close($conn);

?>

Step-by-Step Explanation:

Step 1: Include Database Connection Script

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

This line includes the db_connect.php script, which establishes a connection to the database.

Step 2: Write the SQL Query

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

This line defines an SQL query that selects the id, firstname, lastname, email, and reg_date columns from the MyGuests table in the database.

Step 3: Execute the SQL query:

$result = mysqli_query($conn, $sql);

This line executes the SQL query using the mysqli_query() function and stores the result in the $result variable. This variable will contain the data returned by the query or false if the query failed.

Step 4: Check if the query was successful and there are results:

if (mysqli_num_rows($result) > 0) {

if (mysqli_num_rows($result) > 0) checks if the query returned any rows by counting the number of rows in the result set. If there are more than 0 rows, it proceeds to process the results.

Step 5: Output table headers:

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

These lines start an HTML table with a border and define the table headers: ID, First Name, Last Name, Email, and Registration Date.

Step 6: Output data of each row:

while ($row = mysqli_fetch_assoc($result)) {
echo "<tr>";
echo "<td>" . $row["id"] . "</td>";
echo "<td>" . $row["firstname"] . "</td>";
echo "<td>" . $row["lastname"] . "</td>";
echo "<td>" . $row["email"] . "</td>";
echo "<td>" . $row["reg_date"] . "</td>";
echo "</tr>";
}

while ($row = mysqli_fetch_assoc($result)) fetches each row of the result set as an associative array and stores it in the $row variable.
Inside the loop, it outputs each row's data in a table row (<tr>), with each cell (<td>) containing the corresponding value from the $row array.
For example, echo "<td>" . $row["id"] . "</td>"; outputs the id value in a table cell.

Step 7: Close the table and handle no results:

echo "</table>";
} else {
echo "0 results";

}
After the loop, echo "</table>"; closes the HTML table.
If there are no results, else { echo "0 results"; } outputs "0 results".

Step 8: Close the database connection:

mysqli_close($conn);
?>
This line closes the database connection using mysqli_close($conn), ensuring that the connection is properly terminated and resources are freed.
Summary
Step 1: Includes a file that establishes the database connection.
Step 2: Defines an SQL query to select specific columns from the MyGuests table.
Step 3: Executes the SQL query and stores the result.
Step 4: Checks if there are any results.
If there are results, it proceeds to output them.
If there are no results, it outputs "0 results".
Step 5: Outputs the data in an HTML table format.
Step 6: Closes the database connection to free up resources.

Step 3 : To view Output from Table

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.