Unit 5: Working with Web Form

By Notes Vandar

5.1 Creating simple web form

Creating a simple web form in HTML is straightforward. Web forms are essential for collecting user input, such as contact information, feedback, or any other data. This section will guide you through the steps to create a simple web form, including its components and attributes.

Basic Structure of a Web Form

  1. HTML Document Structure: Start with a basic HTML structure.
  2. Form Element: Use the <form> tag to create the form.
  3. Input Fields: Include various input fields using <input>, <textarea>, and <select> elements.
  4. Submit Button: Add a button to submit the form.

Example of a Simple Web Form

<!DOCTYPE html>
<html lang=”en”>
<head>
<meta charset=”UTF-8″>
<meta name=”viewport” content=”width=device-width, initial-scale=1.0″>
<title>Simple Web Form</title>
</head>
<body>
<h1>Contact Us</h1>
<form action=”process_form.php” method=”post”>
<label for=”name”>Name:</label><br>
<input type=”text” id=”name” name=”name” required><br><br>

<label for=”email”>Email:</label><br>
<input type=”email” id=”email” name=”email” required><br><br>

<label for=”message”>Message:</label><br>
<textarea id=”message” name=”message” rows=”4″ cols=”50″ required></textarea><br><br>

<label for=”contact_method”>Preferred Contact Method:</label><br>
<select id=”contact_method” name=”contact_method”>
<option value=”email”>Email</option>
<option value=”phone”>Phone</option>
<option value=”chat”>Chat</option>
</select><br><br>

<input type=”submit” value=”Submit”>
</form>
</body>
</html>

Breakdown of the Form Elements

  1. Form Tag:
    • <form action="process_form.php" method="post">:
      • action: Specifies the URL to which the form data will be sent upon submission (e.g., process_form.php).
      • method: Defines how data will be sent. Common methods are GET (appends data to the URL) and POST (sends data in the request body).
  2. Input Fields:
    • Name Field:
      <input type=”text” id=”name” name=”name” required>
      • type="text": Creates a text input field.
      • id and name: Used to identify the field.
      • required: Indicates that this field must be filled out.
    • Email Field:
      <input type=”email” id=”email” name=”email” required>
      • type="email": Ensures that the input follows the email format.
    • Message Field:
      <textarea id=”message” name=”message” rows=”4″ cols=”50″ required></textarea>
      • Creates a multi-line text input area.
  3. Dropdown Menu:<select id=”contact_method” name=”contact_method”> <option value=”email”>Email</option> <option value=”phone”>Phone</option> <option value=”chat”>Chat</option> </select> “` – Provides a selection of options for users to choose their preferred contact method.
  4. Submit Button:
    <input type=”submit” value=”Submit”>
    • A button that submits the form data to the server.

 

5.2 Creating a Multipage Web Form

A multipage web form is useful for breaking a long form into smaller, manageable sections. This approach enhances user experience by reducing the feeling of overwhelm and allows users to focus on one section at a time. In this section, we’ll explore how to create a simple multipage web form using HTML and PHP.

Basic Structure of a Multipage Web Form

  1. HTML Document Structure: Create separate HTML pages for each section of the form.
  2. Form Elements: Each page should contain a portion of the form and pass data to the next page.
  3. Navigation: Include “Next” and “Previous” buttons to navigate between the pages.

Example of a Multipage Web Form

Step 1: Create Page 1 (personal_info.php)

This page collects the user’s personal information.

<!– personal_info.php –>
<!DOCTYPE html>
<html lang=”en”>
<head>
<meta charset=”UTF-8″>
<meta name=”viewport” content=”width=device-width, initial-scale=1.0″>
<title>Personal Information</title>
</head>
<body>
<h1>Step 1: Personal Information</h1>
<form action=”contact_info.php” method=”post”>
<label for=”name”>Name:</label><br>
<input type=”text” id=”name” name=”name” required><br><br>

<label for=”email”>Email:</label><br>
<input type=”email” id=”email” name=”email” required><br><br>

<input type=”submit” value=”Next”>
</form>
</body>
</html>

Step 2: Create Page 2 (contact_info.php)

This page collects the user’s contact information.

<!– contact_info.php –>
<!DOCTYPE html>
<html lang=”en”>
<head>
<meta charset=”UTF-8″>
<meta name=”viewport” content=”width=device-width, initial-scale=1.0″>
<title>Contact Information</title>
</head>
<body>
<h1>Step 2: Contact Information</h1>
<form action=”review.php” method=”post”>
<input type=”hidden” name=”name” value=”<?php echo htmlspecialchars($_POST[‘name’]); ?>”>
<input type=”hidden” name=”email” value=”<?php echo htmlspecialchars($_POST[’email’]); ?>”>

<label for=”phone”>Phone Number:</label><br>
<input type=”tel” id=”phone” name=”phone” required><br><br>

<label for=”address”>Address:</label><br>
<textarea id=”address” name=”address” required></textarea><br><br>

<input type=”submit” value=”Next”>
<a href=”personal_info.php”>Previous</a>
</form>
</body>
</html>

Step 3: Create Page 3 (review.php)

This page allows users to review their input before submitting the entire form.

<!– review.php –>
<!DOCTYPE html>
<html lang=”en”>
<head>
<meta charset=”UTF-8″>
<meta name=”viewport” content=”width=device-width, initial-scale=1.0″>
<title>Review Your Information</title>
</head>
<body>
<h1>Review Your Information</h1>
<form action=”thank_you.php” method=”post”>
<input type=”hidden” name=”name” value=”<?php echo htmlspecialchars($_POST[‘name’]); ?>”>
<input type=”hidden” name=”email” value=”<?php echo htmlspecialchars($_POST[’email’]); ?>”>
<input type=”hidden” name=”phone” value=”<?php echo htmlspecialchars($_POST[‘phone’]); ?>”>
<input type=”hidden” name=”address” value=”<?php echo htmlspecialchars($_POST[‘address’]); ?>”>

<p><strong>Name:</strong> <?php echo htmlspecialchars($_POST[‘name’]); ?></p>
<p><strong>Email:</strong> <?php echo htmlspecialchars($_POST[’email’]); ?></p>
<p><strong>Phone Number:</strong> <?php echo htmlspecialchars($_POST[‘phone’]); ?></p>
<p><strong>Address:</strong> <?php echo nl2br(htmlspecialchars($_POST[‘address’])); ?></p>

<input type=”submit” value=”Submit”>
<a href=”contact_info.php”>Previous</a>
</form>
</body>
</html>

Step 4: Create Thank You Page (thank_you.php)

This page confirms the submission.

<!– thank_you.php –>
<!DOCTYPE html>
<html lang=”en”>
<head>
<meta charset=”UTF-8″>
<meta name=”viewport” content=”width=device-width, initial-scale=1.0″>
<title>Thank You</title>
</head>
<body>
<h1>Thank You!</h1>
<p>Your information has been submitted successfully.</p>
</body>
</html>

Breakdown of the Multipage Web Form

  1. Form Data Handling:
    • Each page of the form captures specific information and sends it to the next page using the POST method.
    • The hidden inputs in the subsequent pages store previously submitted data, making it available for review and final submission.
  2. Navigation:
    • Each page includes a “Next” button to proceed to the next step and a “Previous” link to return to the previous page.
  3. Data Validation:
    • Use the required attribute to ensure users fill out necessary fields.
    • Use htmlspecialchars() to prevent XSS attacks by escaping special characters in user inputs.

5.3 Retrieving Form Data Using POST and GET Methods

When creating web forms, you can send data to the server using either the POST or GET method. Each method has its specific use cases and characteristics. This section will explain how to retrieve form data using both methods, with examples.

5.3.1 The GET Method

The GET method appends the form data to the URL as query parameters. This method is generally used for requests where data is not sensitive, such as search queries or filters.

Characteristics of the GET Method:

  • Data is visible in the URL.
  • Limited data size (around 2048 characters).
  • Suitable for non-sensitive data.
  • Data can be bookmarked.

Example of a GET Form

<!DOCTYPE html>
<html lang=”en”>
<head>
<meta charset=”UTF-8″>
<meta name=”viewport” content=”width=device-width, initial-scale=1.0″>
<title>GET Form Example</title>
</head>
<body>
<h1>Search Form</h1>
<form action=”get_results.php” method=”get”>
<label for=”query”>Search:</label>
<input type=”text” id=”query” name=”query” required>
<input type=”submit” value=”Search”>
</form>
</body>
</html>

Retrieving Data in PHP (get_results.php)

<!– get_results.php –>
<!DOCTYPE html>
<html lang=”en”>
<head>
<meta charset=”UTF-8″>
<meta name=”viewport” content=”width=device-width, initial-scale=1.0″>
<title>Search Results</title>
</head>
<body>
<h1>Search Results</h1>
<?php
if (isset($_GET[‘query’])) {
$query = htmlspecialchars($_GET[‘query’]);
echo “You searched for: ” . $query;
} else {
echo “No search query provided.”;
}
?>
</body>
</html>

5.3.2 The POST Method

The POST method sends data in the body of the HTTP request, making it suitable for sensitive information or larger amounts of data. This method is commonly used for form submissions where data should not be displayed in the URL.

Characteristics of the POST Method:

  • Data is not visible in the URL.
  • No data size limit (but there may be server limits).
  • Suitable for sensitive data (e.g., passwords).
  • Data cannot be bookmarked.

Example of a POST Form

<!DOCTYPE html>
<html lang=”en”>
<head>
<meta charset=”UTF-8″>
<meta name=”viewport” content=”width=device-width, initial-scale=1.0″>
<title>POST Form Example</title>
</head>
<body>
<h1>Feedback Form</h1>
<form action=”post_feedback.php” method=”post”>
<label for=”name”>Name:</label>
<input type=”text” id=”name” name=”name” required><br><br>

<label for=”feedback”>Feedback:</label><br>
<textarea id=”feedback” name=”feedback” required></textarea><br><br>

<input type=”submit” value=”Submit”>
</form>
</body>
</html>

Retrieving Data in PHP (post_feedback.php)

<!– post_feedback.php –>
<!DOCTYPE html>
<html lang=”en”>
<head>
<meta charset=”UTF-8″>
<meta name=”viewport” content=”width=device-width, initial-scale=1.0″>
<title>Feedback Received</title>
</head>
<body>
<h1>Feedback Received</h1>
<?php
if ($_SERVER[“REQUEST_METHOD”] == “POST”) {
$name = htmlspecialchars($_POST[‘name’]);
$feedback = htmlspecialchars($_POST[‘feedback’]);
echo “Thank you, ” . $name . “! Your feedback: ” . nl2br($feedback);
} else {
echo “No feedback received.”;
}
?>
</body>
</html>

Summary of Differences Between GET and POST

Feature GET POST
Data Visibility Visible in URL Not visible in URL
Data Length Limited (around 2048 characters) No specific limit
Use Case Non-sensitive data Sensitive data, file uploads
Bookmarking Yes No
Suitable for Search queries, filters User registration, feedback forms

 

5.4 Storing Form Data to a CSV File

Storing form data in a CSV (Comma-Separated Values) file is a common practice for data management. This allows you to easily collect, view, and analyze submitted data in a structured format. In this section, we’ll go through how to create a form and then store the submitted data in a CSV file using PHP.

Example Form to Collect Data

We’ll create a simple feedback form that collects a user’s name, email, and feedback message. Upon submission, the data will be stored in a CSV file.

Step 1: Create the Form (feedback_form.php)

<!DOCTYPE html>
<html lang=”en”>
<head>
<meta charset=”UTF-8″>
<meta name=”viewport” content=”width=device-width, initial-scale=1.0″>
<title>Feedback Form</title>
</head>
<body>
<h1>Feedback Form</h1>
<form action=”store_feedback.php” method=”post”>
<label for=”name”>Name:</label>
<input type=”text” id=”name” name=”name” required><br><br>

<label for=”email”>Email:</label>
<input type=”email” id=”email” name=”email” required><br><br>

<label for=”feedback”>Feedback:</label><br>
<textarea id=”feedback” name=”feedback” required></textarea><br><br>

<input type=”submit” value=”Submit”>
</form>
</body>
</html>

Step 2: Store Data in CSV (store_feedback.php)

Now we need to create the PHP script that will handle the form submission and store the data in a CSV file.

<?php
// store_feedback.php

if ($_SERVER[“REQUEST_METHOD”] == “POST”) {
// Get the form data
$name = htmlspecialchars($_POST[‘name’]);
$email = htmlspecialchars($_POST[’email’]);
$feedback = htmlspecialchars($_POST[‘feedback’]);

// Open the CSV file in append mode
$file = fopen(‘feedback.csv’, ‘a’);

// Check if the file opened successfully
if ($file) {
// Write the data to the CSV file
fputcsv($file, array($name, $email, $feedback));

// Close the file
fclose($file);

echo “Thank you for your feedback!”;
} else {
echo “Error opening the file.”;
}
} else {
echo “Invalid request.”;
}
?>

Explanation of the PHP Code

  1. Form Data Retrieval:
    • We first check if the form was submitted using the POST method. Then, we retrieve the form data and sanitize it using htmlspecialchars() to prevent XSS attacks.
  2. Open the CSV File:
    • fopen('feedback.csv', 'a'): Opens the feedback.csv file in append mode. If the file doesn’t exist, it will be created.
  3. Writing to the CSV File:
    • fputcsv($file, array($name, $email, $feedback)): Writes the array of form data as a new line in the CSV file. Each value will be separated by a comma.
  4. Closing the File:
    • Always close the file after writing to free up system resources.
  5. Feedback to User:
    • After successfully writing to the CSV file, a message is displayed to the user confirming the receipt of their feedback.

Viewing the CSV File

The resulting feedback.csv file will have data stored in the following format:

Name,Email,Feedback
John Doe,johndoe@example.com,This is a great website!
Jane Smith,janesmith@example.com,Very helpful information.

 

 

5.5 Reading CSV file and displaying content as html table

Displaying data from a CSV file as an HTML table is a straightforward way to visualize the information collected through forms. In this section, we’ll cover how to read data from a CSV file and present it in a structured HTML table using PHP.

Step 1: Sample CSV File

Assuming you have a CSV file named feedback.csv with the following content:

Name,Email,Feedback
John Doe,johndoe@example.com,This is a great website!
Jane Smith,janesmith@example.com,Very helpful information.
Emily Johnson,emilyj@example.com,I found what I was looking for.

Step 2: Create a PHP Script to Read and Display CSV Data (display_feedback.php)

Here’s how to read the CSV file and display its content in an HTML table format:

<!DOCTYPE html>
<html lang=”en”>
<head>
<meta charset=”UTF-8″>
<meta name=”viewport” content=”width=device-width, initial-scale=1.0″>
<title>Feedback Display</title>
<style>
table {
width: 80%;
border-collapse: collapse;
margin: 20px auto;
}
th, td {
padding: 10px;
border: 1px solid #ddd;
text-align: left;
}
th {
background-color: #f2f2f2;
}
</style>
</head>
<body>
<h1 style=”text-align: center;”>Feedback Table</h1>
<table>
<thead>
<tr>
<th>Name</th>
<th>Email</th>
<th>Feedback</th>
</tr>
</thead>
<tbody>
<?php
// Open the CSV file for reading
if (($handle = fopen(‘feedback.csv’, ‘r’)) !== FALSE) {
// Skip the first row (header)
fgetcsv($handle);

// Loop through each row of the CSV file
while (($data = fgetcsv($handle)) !== FALSE) {
echo ‘<tr>’;
echo ‘<td>’ . htmlspecialchars($data[0]) . ‘</td>’; // Name
echo ‘<td>’ . htmlspecialchars($data[1]) . ‘</td>’; // Email
echo ‘<td>’ . htmlspecialchars($data[2]) . ‘</td>’; // Feedback
echo ‘</tr>’;
}

// Close the file
fclose($handle);
} else {
echo ‘<tr><td colspan=”3″>Error opening the file.</td></tr>’;
}
?>
</tbody>
</table>
</body>
</html>

Explanation of the Code

  1. Opening the CSV File:
    • fopen('feedback.csv', 'r'): Opens the feedback.csv file for reading.
    • We check if the file was opened successfully; if not, an error message is displayed.
  2. Reading the CSV Data:
    • fgetcsv($handle): This reads the first row of the CSV file (the header) and skips it, as we only want to display the data.
    • A while loop is used to read each subsequent row of the CSV file until all rows have been processed.
  3. Displaying Data in HTML Table:
    • For each row, a new table row (<tr>) is created, and individual cells (<td>) are populated with data.
    • The htmlspecialchars() function is used to ensure that any special characters in the data are properly escaped to prevent XSS attacks.
  4. Closing the File:
    • The fclose($handle) function is called to close the file after reading all the data.
  5. Error Handling:
    • If the file cannot be opened, a message indicating an error is displayed in the table.

Resulting HTML Table

When you run the display_feedback.php script, it will generate an HTML table that looks like this:

Name Email Feedback
John Doe johndoe@example.com This is a great website!
Jane Smith janesmith@example.com Very helpful information.
Emily Johnson emilyj@example.com I found what I was looking for.

 

Unit 6: Database and PHP

6.1 Introduction to database

Databases are crucial components of modern applications, enabling structured data storage and efficient data management. In this section, we’ll explore what databases are, their types, and their importance in web development.

What is a Database?

A database is an organized collection of data that allows for efficient retrieval, management, and updating. Databases are used to store various types of information, such as user profiles, transaction records, and content for websites.

Key Characteristics of Databases:

  • Structured Data: Data is organized in a structured format, often in tables, which makes it easy to query and analyze.
  • Data Integrity: Databases enforce rules to maintain accuracy and consistency in data.
  • Scalability: Databases can handle increasing amounts of data and users as an application grows.
  • Security: Databases implement user access controls to protect sensitive information.

Importance of Databases in Web Development

Databases play a critical role in web development by allowing developers to:

  • Store User Data: Manage user registrations, profiles, and preferences.
  • Handle Content Management: Support dynamic content generation, such as blog posts and product listings.
  • Process Transactions: Store and manage financial transactions in e-commerce applications.
  • Facilitate Data Analysis: Enable reporting and analytics on user behavior and application performance.

Types of Databases

Databases can be categorized into several types, primarily based on their data models:

  1. Relational Databases:
    • Definition: Use a table-based structure to store data, with relationships defined between tables.
    • Examples: MySQL, PostgreSQL, Oracle, Microsoft SQL Server.
    • Key Features:
      • Data is stored in rows and columns.
      • Supports SQL (Structured Query Language) for querying and manipulating data.
      • Enforces ACID (Atomicity, Consistency, Isolation, Durability) properties for transactions.
  2. NoSQL Databases:
    • Definition: Use various data models (document, key-value, graph, column-family) to store unstructured or semi-structured data.
    • Examples: MongoDB, Redis, Cassandra, CouchDB.
    • Key Features:
      • Designed for scalability and flexibility.
      • Handle large volumes of diverse data types.
      • Often schema-less, allowing for more agile development.
  3. In-Memory Databases:
    • Definition: Store data in the main memory (RAM) for faster access and performance.
    • Examples: Redis, Memcached.
    • Key Features:
      • Extremely fast data retrieval and processing.
      • Often used for caching and real-time analytics.
  4. NewSQL Databases:
    • Definition: Combine the benefits of relational databases with the scalability of NoSQL.
    • Examples: Google Spanner, CockroachDB.
    • Key Features:
      • Use SQL for querying but are designed to scale horizontally.
      • Provide ACID guarantees while handling large datasets.

 

6.2 Create, Retrieve, Update and Delete operation in database

CRUD operations are the fundamental operations that can be performed on a database. They consist of Create, Read, Update, and Delete actions. In this section, we will explore each of these operations using PHP and MySQL.

Setting Up the Database

Before we dive into the CRUD operations, let’s assume you have a database named my_database with a table called feedback structured as follows:

CREATE TABLE feedback (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
email VARCHAR(100) NOT NULL,
feedback TEXT NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

1. Create Operation

The Create operation allows you to insert new records into the database.

Example: Inserting Data into the Database

// Database connection
$servername = “localhost”;
$username = “username”;
$password = “password”;
$dbname = “my_database”;

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

// Check connection
if ($conn->connect_error) {
die(“Connection failed: ” . $conn->connect_error);
}

// Prepare and bind
$stmt = $conn->prepare(“INSERT INTO feedback (name, email, feedback) VALUES (?, ?, ?)”);
$stmt->bind_param(“sss”, $name, $email, $feedback);

// Set parameters and execute
$name = “John Doe”;
$email = “johndoe@example.com”;
$feedback = “This is a great website!”;
$stmt->execute();

echo “New record created successfully”;

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

2. Retrieve Operation

The Retrieve operation allows you to read data from the database.

Example: Fetching Data from the Database

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

// Check connection
if ($conn->connect_error) {
die(“Connection failed: ” . $conn->connect_error);
}

// Retrieve data
$sql = “SELECT * FROM feedback”;
$result = $conn->query($sql);

if ($result->num_rows > 0) {
// Output data of each row
while($row = $result->fetch_assoc()) {
echo “Name: ” . htmlspecialchars($row[“name”]) . ” – Email: ” . htmlspecialchars($row[“email”]) . ” – Feedback: ” . htmlspecialchars($row[“feedback”]) . “<br>”;
}
} else {
echo “0 results”;
}

$conn->close();

3. Update Operation

The Update operation allows you to modify existing records in the database.

Example: Updating Data in the Database

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

// Check connection
if ($conn->connect_error) {
die(“Connection failed: ” . $conn->connect_error);
}

// Prepare and bind
$stmt = $conn->prepare(“UPDATE feedback SET feedback = ? WHERE id = ?”);
$stmt->bind_param(“si”, $feedback, $id);

// Set parameters and execute
$feedback = “This website is awesome!”;
$id = 1; // Assuming we want to update the feedback with ID 1
$stmt->execute();

echo “Record updated successfully”;

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

4. Delete Operation

The Delete operation allows you to remove records from the database.

Example: Deleting Data from the Database

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

// Check connection
if ($conn->connect_error) {
die(“Connection failed: ” . $conn->connect_error);
}

// Prepare and bind
$stmt = $conn->prepare(“DELETE FROM feedback WHERE id = ?”);
$stmt->bind_param(“i”, $id);

// Set parameters and execute
$id = 1; // Assuming we want to delete the feedback with ID 1
$stmt->execute();

echo “Record deleted successfully”;

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

 

6.3 Connecting to a Database through PHP with mysqli_connect()

To interact with a MySQL database in PHP, you need to establish a connection using the mysqli_connect() function. This function provides an easy way to connect to the MySQL database server.

Syntax of mysqli_connect()

mysqli_connect(host, username, password, database);
  • host: The hostname or IP address of the MySQL server (usually localhost for local development).
  • username: The MySQL username you want to connect with.
  • password: The password associated with the MySQL username.
  • database: The name of the database you want to work with (optional for connection).

Example: Connecting to a MySQL Database Using mysqli_connect()

Here’s a step-by-step example of how to connect to a MySQL database using mysqli_connect():

<?php
// Database connection parameters
$servername = “localhost”; // Hostname
$username = “your_username”; // MySQL username
$password = “your_password”; // MySQL password
$dbname = “my_database”; // Database name

// Create connection
$conn = mysqli_connect($servername, $username, $password, $dbname);

// Check connection
if (!$conn) {
die(“Connection failed: ” . mysqli_connect_error());
}
echo “Connected successfully”;

// Close connection
mysqli_close($conn);
?>

Explanation of the Example

  1. Define Connection Parameters:
    • Set the server name, username, password, and database name. Make sure to replace your_username and your_password with your actual MySQL credentials.
  2. Create the Connection:
    • The mysqli_connect() function is called with the specified parameters. This function returns a connection object if successful or false on failure.
  3. Check Connection:
    • After attempting to connect, you should check if the connection was successful using an if statement. If the connection fails, mysqli_connect_error() will return the error message.
  4. Use the Connection:
    • If the connection is successful, you can proceed to perform database operations (e.g., executing queries).
  5. Close the Connection:
    • Once you’re done with the database operations, it’s good practice to close the connection using mysqli_close() to free up resources.

Error Handling

When establishing a connection, always handle errors gracefully. Instead of simply dying with an error message, you could log the error or display a user-friendly message.

if (!$conn) {
// Log the error
error_log(“Database connection failed: ” . mysqli_connect_error());
// Display a user-friendly message
echo “Sorry, we are experiencing technical difficulties.”;
}

6.4 Executing Queries with mysqli_query()

Once you have established a connection to a MySQL database using mysqli_connect(), you can execute SQL queries using the mysqli_query() function. This function is essential for performing operations like inserting, updating, deleting, and selecting data from your database.

Syntax of mysqli_query()

mysqli_query(connection, query);
  • connection: The connection object returned by mysqli_connect().
  • query: The SQL query string that you want to execute.

Return Value

  • On success, mysqli_query() returns:
    • TRUE for INSERT, UPDATE, and DELETE queries.
    • A result set (object) for SELECT queries.
  • On failure, it returns FALSE.

Example: Executing Queries Using mysqli_query()

1. Inserting Data

Here’s how to insert data into a table using mysqli_query():

<?php

// Database connection parameters
$servername = “localhost”;
$username = “your_username”;
$password = “your_password”;
$dbname = “my_database”;

// Create connection
$conn = mysqli_connect($servername, $username, $password, $dbname);

// Check connection
if (!$conn) {
die(“Connection failed: ” . mysqli_connect_error());
}

// SQL query to insert data
$sql = “INSERT INTO feedback (name, email, feedback) VALUES (‘Alice’, ‘alice@example.com’, ‘Great service!’)”;

// Execute the query
if (mysqli_query($conn, $sql)) {
echo “New record created successfully”;
} else {
echo “Error: ” . $sql . “<br>” . mysqli_error($conn);
}

// Close connection
mysqli_close($conn);
?>

2. Selecting Data

To retrieve data from a database, you can execute a SELECT query:

<?php
// Database connection
$conn = mysqli_connect($servername, $username, $password, $dbname);

// Check connection
if (!$conn) {
die(“Connection failed: ” . mysqli_connect_error());
}

// SQL query to select data
$sql = “SELECT * FROM feedback”;
$result = mysqli_query($conn, $sql);

// Check if there are results and output data
if (mysqli_num_rows($result) > 0) {
while($row = mysqli_fetch_assoc($result)) {
echo “Name: ” . htmlspecialchars($row[“name”]) . ” – Email: ” . htmlspecialchars($row[“email”]) . ” – Feedback: ” . htmlspecialchars($row[“feedback”]) . “<br>”;
}
} else {
echo “0 results”;
}

// Close connection
mysqli_close($conn);
?>

3. Updating Data

To update existing records, you can use an UPDATE query:

<?php
// Database connection
$conn = mysqli_connect($servername, $username, $password, $dbname);

// Check connection
if (!$conn) {
die(“Connection failed: ” . mysqli_connect_error());
}

// SQL query to update data
$sql = “UPDATE feedback SET feedback = ‘Excellent service!’ WHERE id = 1”;

// Execute the query
if (mysqli_query($conn, $sql)) {
echo “Record updated successfully”;
} else {
echo “Error: ” . mysqli_error($conn);
}

// Close connection
mysqli_close($conn);
?>

4. Deleting Data

To delete records from a table, use a DELETE query:

<?php
// Database connection
$conn = mysqli_connect($servername, $username, $password, $dbname);

// Check connection
if (!$conn) {
die(“Connection failed: ” . mysqli_connect_error());
}

// SQL query to delete data
$sql = “DELETE FROM feedback WHERE id = 1”;

// Execute the query
if (mysqli_query($conn, $sql)) {
echo “Record deleted successfully”;
} else {
echo “Error: ” . mysqli_error($conn);
}

// Close connection
mysqli_close($conn);
?>

Error Handling

When executing queries, it’s essential to handle errors effectively. You can use mysqli_error($conn) to get the error message if a query fails. This information can be useful for debugging.

if (!$result) {
echo “Error executing query: ” . mysqli_error($conn);
}

6.5 Fetching Data with mysqli_fetch_assoc() and mysqli_fetch_array()

After executing a SELECT query using mysqli_query(), you will often need to retrieve the results. PHP provides several functions to fetch rows from a result set, including mysqli_fetch_assoc() and mysqli_fetch_array(). These functions allow you to access the data returned from the database.

1. Using mysqli_fetch_assoc()

mysqli_fetch_assoc() retrieves a result row as an associative array. Each key in the array corresponds to a column name in the result set.

Syntax:

mysqli_fetch_assoc(result);
  • result: The result set returned from a SELECT query.

Example: Fetching Data with mysqli_fetch_assoc()

<?php
// Database connection parameters
$servername = “localhost”;
$username = “your_username”;
$password = “your_password”;
$dbname = “my_database”;

// Create connection
$conn = mysqli_connect($servername, $username, $password, $dbname);

// Check connection
if (!$conn) {
die(“Connection failed: ” . mysqli_connect_error());
}

// SQL query to select data
$sql = “SELECT * FROM feedback”;
$result = mysqli_query($conn, $sql);

// Fetch and display data
if (mysqli_num_rows($result) > 0) {
while ($row = mysqli_fetch_assoc($result)) {
echo “Name: ” . htmlspecialchars($row[“name”]) . ” – Email: ” . htmlspecialchars($row[“email”]) . ” – Feedback: ” . htmlspecialchars($row[“feedback”]) . “<br>”;
}
} else {
echo “0 results”;
}

// Close connection
mysqli_close($conn);
?>

In this example, mysqli_fetch_assoc() retrieves each row as an associative array, allowing you to access data using column names.

2. Using mysqli_fetch_array()

mysqli_fetch_array() retrieves a result row as an associative array, a numeric array, or both. You can specify the type of array you want to retrieve using the second parameter.

Syntax:

mysqli_fetch_array(result, resulttype);
  • result: The result set returned from a SELECT query.
  • resulttype: (optional) The type of array to return:
    • MYSQLI_ASSOC – associative array (default)
    • MYSQLI_NUM – numeric array
    • MYSQLI_BOTH – both associative and numeric (default behavior if the parameter is omitted)

Example: Fetching Data with mysqli_fetch_array()

<?php
// Database connection
$conn = mysqli_connect($servername, $username, $password, $dbname);

// Check connection
if (!$conn) {
die(“Connection failed: ” . mysqli_connect_error());
}

// SQL query to select data
$sql = “SELECT * FROM feedback”;
$result = mysqli_query($conn, $sql);

// Fetch and display data
if (mysqli_num_rows($result) > 0) {
while ($row = mysqli_fetch_array($result, MYSQLI_BOTH)) {
echo “Name: ” . htmlspecialchars($row[0]) . ” – Email: ” . htmlspecialchars($row[1]) . ” – Feedback: ” . htmlspecialchars($row[2]) . “<br>”;
// Alternatively, using associative keys:
echo “Name: ” . htmlspecialchars($row[“name”]) . ” – Email: ” . htmlspecialchars($row[“email”]) . ” – Feedback: ” . htmlspecialchars($row[“feedback”]) . “<br>”;
}
} else {
echo “0 results”;
}

// Close connection
mysqli_close($conn);
?>

In this example, mysqli_fetch_array() retrieves each row as both an associative array and a numeric array, allowing access to data via both column names and numerical indices.

 

6.6 Creating User Registration and Login Feature

Implementing a user registration and login feature is a common requirement for web applications. This section will cover how to create a simple registration and login system using PHP and MySQL.

Overview of the Steps

  1. Set Up the Database: Create a MySQL database and a table for user information.
  2. User Registration: Create a form for users to register and process the data to store in the database.
  3. User Login: Create a form for users to log in and validate their credentials.
  4. Session Management: Use PHP sessions to maintain user login state.

1. Set Up the Database

First, create a MySQL database and a users table to store user information.

SQL Query to Create the Table:

CREATE TABLE users (
id INT(11) AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(50) NOT NULL,
password VARCHAR(255) NOT NULL,
email VARCHAR(100) NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Make sure to hash passwords before storing them in the database for security.

2. User Registration

HTML Registration Form (register.php):

<!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>
<h2>User Registration</h2>
<form action=”register.php” method=”post”>
<label for=”username”>Username:</label>
<input type=”text” name=”username” required><br><br>

<label for=”email”>Email:</label>
<input type=”email” name=”email” required><br><br>

<label for=”password”>Password:</label>
<input type=”password” name=”password” required><br><br>

<button type=”submit”>Register</button>
</form>
</body>
</html>

Processing Registration (register.php):

<?php
// Database connection parameters
$servername = “localhost”;
$username = “your_username”;
$password = “your_password”;
$dbname = “my_database”;

// Create connection
$conn = mysqli_connect($servername, $username, $password, $dbname);

// Check connection
if (!$conn) {
die(“Connection failed: ” . mysqli_connect_error());
}

// Check if form is submitted
if ($_SERVER[“REQUEST_METHOD”] == “POST”) {
$username = mysqli_real_escape_string($conn, $_POST[‘username’]);
$email = mysqli_real_escape_string($conn, $_POST[’email’]);
$password = password_hash($_POST[‘password’], PASSWORD_DEFAULT); // Hash the password

// SQL query to insert user
$sql = “INSERT INTO users (username, email, password) VALUES (‘$username’, ‘$email’, ‘$password’)”;

if (mysqli_query($conn, $sql)) {
echo “Registration successful!”;
} else {
echo “Error: ” . mysqli_error($conn);
}
}

// Close connection
mysqli_close($conn);
?>

3. User Login

HTML Login Form (login.php):

<!DOCTYPE html>
<html lang=”en”>
<head>
<meta charset=”UTF-8″>
<meta name=”viewport” content=”width=device-width, initial-scale=1.0″>
<title>User Login</title>
</head>
<body>
<h2>User Login</h2>
<form action=”login.php” method=”post”>
<label for=”username”>Username:</label>
<input type=”text” name=”username” required><br><br>

<label for=”password”>Password:</label>
<input type=”password” name=”password” required><br><br>

<button type=”submit”>Login</button>
</form>
</body>
</html>

Processing Login (login.php):

<?php
session_start(); // Start the session

// Database connection parameters
$servername = “localhost”;
$username = “your_username”;
$password = “your_password”;
$dbname = “my_database”;

// Create connection
$conn = mysqli_connect($servername, $username, $password, $dbname);

// Check connection
if (!$conn) {
die(“Connection failed: ” . mysqli_connect_error());
}

// Check if form is submitted
if ($_SERVER[“REQUEST_METHOD”] == “POST”) {
$username = mysqli_real_escape_string($conn, $_POST[‘username’]);
$password = $_POST[‘password’];

// SQL query to retrieve user
$sql = “SELECT * FROM users WHERE username=’$username'”;
$result = mysqli_query($conn, $sql);

if (mysqli_num_rows($result) > 0) {
$row = mysqli_fetch_assoc($result);
// Verify the password
if (password_verify($password, $row[‘password’])) {
// Set session variables
$_SESSION[‘username’] = $username;
echo “Login successful! Welcome, ” . htmlspecialchars($username) . “.”;
} else {
echo “Invalid password.”;
}
} else {
echo “User not found.”;
}
}

// Close connection
mysqli_close($conn);
?>

4. Session Management

To manage user sessions, you use the PHP session functions. After a user logs in successfully, you can store their information in a session variable.

  • Starting a session: Use session_start() at the beginning of your PHP scripts that require session management.
  • Storing session data: Assign values to the $_SESSION array, e.g., $_SESSION['username'] = $username;.
  • Destroying a session: Use session_destroy() to log the user out.

Example: Logout (logout.php)

<?php
session_start(); // Start the session
session_unset(); // Unset session variables
session_destroy(); // Destroy the session

echo “You have been logged out.”;
?>

 

6.7 Remembering users with cookies and session

In web development, managing user sessions and remembering users across different visits is crucial for providing a seamless user experience. This can be achieved using cookies and sessions in PHP. Here’s a detailed explanation of both concepts and how to implement them.

1. Understanding Sessions

Sessions are a way to store information on the server side, allowing you to maintain user state across multiple pages. When a user logs in, their session can hold their user ID or username, enabling personalized experiences.

How Sessions Work:

  1. Start a Session: Use session_start() at the beginning of your script.
  2. Store Session Data: Assign values to the $_SESSION superglobal array.
  3. Access Session Data: Retrieve stored values from the $_SESSION array on any page.
  4. End a Session: Use session_destroy() to clear the session.

Example: Using Sessions

<?php
session_start(); // Start the session

// Set session variables
$_SESSION[‘username’] = ‘Alice’;
$_SESSION[‘loggedin’] = true;

// Accessing session data
if (isset($_SESSION[‘loggedin’]) && $_SESSION[‘loggedin’] === true) {
echo “Welcome, ” . htmlspecialchars($_SESSION[‘username’]);
} else {
echo “Please log in.”;
}

// Logging out
session_unset(); // Unset session variables
session_destroy(); // Destroy the session
?>

2. Understanding Cookies

Cookies are small pieces of data stored on the client’s browser. They can be used to remember user preferences, login details, or any other relevant information across sessions.

How Cookies Work:

  1. Set a Cookie: Use the setcookie() function to create a cookie.
  2. Access a Cookie: Retrieve cookie values from the $_COOKIE superglobal array.
  3. Expire a Cookie: Set the expiration time to delete the cookie.

Example: Using Cookies

<?php
// Set a cookie to remember the user for 30 days
setcookie(“username”, “Alice”, time() + (86400 * 30), “/”); // 86400 = 1 day

// Accessing cookie data
if (isset($_COOKIE[‘username’])) {
echo “Welcome back, ” . htmlspecialchars($_COOKIE[‘username’]);
} else {
echo “Hello, guest!”;
}

// Deleting a cookie
setcookie(“username”, “”, time() – 3600, “/”); // Set expiration time to past
?>

3. Combining Sessions and Cookies

You can combine sessions and cookies to enhance user experience. For example, you might use cookies to remember a user’s login status or username, allowing for auto-login or personalized greetings.

Example: Remembering Login with Cookies and Sessions

  1. Login Form: Modify the login form to include a “Remember Me” checkbox.

HTML Login Form (login.php):

<form action=”login.php” method=”post”>
<label for=”username”>Username:</label>
<input type=”text” name=”username” required><br><br>

<label for=”password”>Password:</label>
<input type=”password” name=”password” required><br><br>

<input type=”checkbox” name=”remember” value=”1″> Remember Me<br><br>

<button type=”submit”>Login</button>
</form>

  1. Processing Login: Modify the login logic to set a cookie if “Remember Me” is checked.

Processing Login (login.php):

<?php
session_start(); // Start the session

// Database connection parameters
$servername = “localhost”;
$username = “your_username”;
$password = “your_password”;
$dbname = “my_database”;

// Create connection
$conn = mysqli_connect($servername, $username, $password, $dbname);

// Check connection
if (!$conn) {
die(“Connection failed: ” . mysqli_connect_error());
}

// Check if form is submitted
if ($_SERVER[“REQUEST_METHOD”] == “POST”) {
$username = mysqli_real_escape_string($conn, $_POST[‘username’]);
$password = $_POST[‘password’];

// SQL query to retrieve user
$sql = “SELECT * FROM users WHERE username=’$username'”;
$result = mysqli_query($conn, $sql);

if (mysqli_num_rows($result) > 0) {
$row = mysqli_fetch_assoc($result);
// Verify the password
if (password_verify($password, $row[‘password’])) {
// Set session variables
$_SESSION[‘username’] = $username;
$_SESSION[‘loggedin’] = true;

// Check if ‘Remember Me’ is checked
if (isset($_POST[‘remember’])) {
// Set a cookie that lasts 30 days
setcookie(“username”, $username, time() + (86400 * 30), “/”);
}

echo “Login successful! Welcome, ” . htmlspecialchars($username) . “.”;
} else {
echo “Invalid password.”;
}
} else {
echo “User not found.”;
}
}

// Close connection
mysqli_close($conn);
?>

  1. Checking for Remember Me on Login: When the user visits the site again, check if the cookie is set and log them in automatically.

Example Check for Cookie on Page Load (index.php):

<?php
session_start();

// Check if the user is logged in
if (!isset($_SESSION[‘loggedin’])) {
// Check for the cookie
if (isset($_COOKIE[‘username’])) {
$_SESSION[‘username’] = $_COOKIE[‘username’];
$_SESSION[‘loggedin’] = true;
echo “Welcome back, ” . htmlspecialchars($_COOKIE[‘username’]) . “!”;
} else {
echo “Please log in.”;
}
} else {
echo “Welcome, ” . htmlspecialchars($_SESSION[‘username’]) . “.”;
}
?>

 

6.8 Converting a Database Table to CSV File Using fputcsv()

Exporting data from a database table to a CSV (Comma-Separated Values) file is a common task in web development. The fputcsv() function in PHP makes it easy to write data to a CSV file. Below are the steps to convert a database table to a CSV file using this function.

Steps to Convert Database Table to CSV

  1. Connect to the Database: Establish a connection to your MySQL database.
  2. Retrieve Data: Execute a query to fetch the data from the desired table.
  3. Open a File for Writing: Use fopen() to create or open a CSV file.
  4. Write Data to CSV: Use fputcsv() to write each row of data to the CSV file.
  5. Close the File: Ensure to close the file after writing.

Example Code

Here’s a complete example demonstrating how to convert a database table (e.g., users) to a CSV file.

1. Database Structure

Assuming we have a users table with the following structure:

CREATE TABLE users (
id INT(11) AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(50) NOT NULL,
email VARCHAR(100) NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

2. PHP Code to Export to CSV

export_to_csv.php:

<?php
// Database connection parameters
$servername = “localhost”;
$username = “your_username”;
$password = “your_password”;
$dbname = “my_database”;

// Create connection
$conn = mysqli_connect($servername, $username, $password, $dbname);

// Check connection
if (!$conn) {
die(“Connection failed: ” . mysqli_connect_error());
}

// SQL query to select all users
$sql = “SELECT * FROM users”;
$result = mysqli_query($conn, $sql);

// Check if any records found
if (mysqli_num_rows($result) > 0) {
// Open a file in write mode (‘w’)
$filename = ‘users.csv’;
$file = fopen($filename, ‘w’);

// Optional: Set CSV column headers
$headers = array(‘ID’, ‘Username’, ‘Email’, ‘Created At’);
fputcsv($file, $headers);

// Fetch each row and write it to the CSV file
while ($row = mysqli_fetch_assoc($result)) {
fputcsv($file, $row);
}

// Close the file
fclose($file);
echo “Data successfully exported to $filename”;
} else {
echo “No records found.”;
}

// Close the database connection
mysqli_close($conn);
?>

Explanation of the Code

  1. Database Connection: The code establishes a connection to the MySQL database using mysqli_connect().
  2. SQL Query: It executes a SQL query to select all records from the users table.
  3. File Handling:
    • A new CSV file (users.csv) is created using fopen() in write mode ('w').
    • Column headers are optionally added to the CSV file to indicate what each column represents.
  4. Writing to CSV: Each row from the database result is fetched using mysqli_fetch_assoc() and written to the CSV file with fputcsv().
  5. Closing the File: Finally, the file is closed with fclose(), and a success message is displayed.

Running the Script

To run this script, simply access export_to_csv.php via your web browser. If successful, you will have a users.csv file in the same directory as your PHP script.

 

6.9 Reading CSV file and reflecting the contents in database

Importing data from a CSV file into a database is a common task in web applications. This process typically involves reading the CSV file, parsing its contents, and then inserting or updating records in the database. Below, you’ll find a step-by-step guide and example code on how to achieve this in PHP.

Steps to Read a CSV File and Insert Data into a Database

  1. Open the CSV File: Use fopen() to open the CSV file for reading.
  2. Read the Data: Use fgetcsv() to read each row from the CSV file.
  3. Connect to the Database: Establish a connection to your MySQL database.
  4. Insert Data into the Database: Prepare and execute SQL insert statements for each row.
  5. Close the Database Connection and the CSV File: Always close your resources to avoid memory leaks.

Example Code

Here’s a complete example demonstrating how to read a CSV file and insert its contents into a database table (e.g., users).

1. Database Structure

Assuming we have a users table with the following structure:

CREATE TABLE users (
id INT(11) AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(50) NOT NULL,
email VARCHAR(100) NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

2. PHP Code to Import CSV

import_from_csv.php:

<?php
// Database connection parameters
$servername = “localhost”;
$username = “your_username”;
$password = “your_password”;
$dbname = “my_database”;

// Create connection
$conn = mysqli_connect($servername, $username, $password, $dbname);

// Check connection
if (!$conn) {
die(“Connection failed: ” . mysqli_connect_error());
}

// Path to the CSV file
$csvFile = ‘users.csv’;

// Open the CSV file for reading
if (($handle = fopen($csvFile, ‘r’)) !== FALSE) {
// Skip the first row (header) if necessary
fgetcsv($handle); // Uncomment this line if your CSV has headers

// Prepare SQL insert statement
$stmt = $conn->prepare(“INSERT INTO users (username, email) VALUES (?, ?)”);
$stmt->bind_param(“ss”, $username, $email);

// Read each row from the CSV file
while (($data = fgetcsv($handle)) !== FALSE) {
// Assign values from the CSV to variables
$username = $data[0]; // Assuming username is in the first column
$email = $data[1]; // Assuming email is in the second column

// Execute the insert statement
if (!$stmt->execute()) {
echo “Error inserting data: ” . $stmt->error . “<br>”;
}
}

// Close the prepared statement
$stmt->close();
// Close the CSV file
fclose($handle);
echo “Data successfully imported from $csvFile”;
} else {
echo “Error opening the file.”;
}

// Close the database connection
mysqli_close($conn);
?>

Explanation of the Code

  1. Database Connection: The code establishes a connection to the MySQL database using mysqli_connect().
  2. Open CSV File: The CSV file (users.csv) is opened for reading using fopen().
  3. Skip Header Row: The first row is skipped if it contains column headers using fgetcsv(). Uncomment this line if your CSV file has headers.
  4. Prepare Insert Statement: A prepared statement is created to safely insert data into the database.
  5. Reading and Inserting Data: Each row is read from the CSV file using fgetcsv(). The values are assigned to variables and inserted into the database using the prepared statement. Any errors during insertion are displayed.
  6. Close Resources: The prepared statement and CSV file are closed after processing. Finally, the database connection is also closed.

Running the Script

To run this script, place your users.csv file in the same directory as your import_from_csv.php script, and then access the script via your web browser. If successful, you will see a success message, and the data from the CSV will be reflected in the database.

Example CSV Format

Your users.csv should look something like this:

username,email
alice@example.com,alice@example.com
bob@example.com,bob@example.com
charlie@example.com,charlie@example.com
Previous Chapter
Important Questions
Comments
Discussion
0 Comments
  Loading . . .