PHP Database Integration with MySQL

Integrating PHP with MySQL is crucial for building dynamic, data-driven web applications. MySQL is a popular open-source relational database management system that PHP can interact with to store, retrieve, update, and delete data. This integration allows for persistent data storage beyond the lifespan of a single PHP session.

1. Setting Up MySQL for PHP Integration

Requirements

XAMPP or WAMP: These tools include Apache (web server), MySQL (database), and PHP, allowing local testing of PHP scripts with MySQL databases.

phpMyAdmin: A web-based tool included in XAMPP and WAMP, making it easy to manage MySQL databases.

2.Create DATABASE and Table in MYSQL

There are two ways to create the DATABASE and tables in MYSQL.

Type 1: Using the Stand alone Mysql:

Here are the steps to create a database and a table in MySQL:

Step 1: Open MySQL Command Line Interface

1. Open the MySQL Command Line Client, or connect to MySQL via a terminal or command prompt (you’ll need to have MySQL installed).

2. Log in with your username and password:

mysql -u username -p

3. Enter your password when prompted.

Step 2: Create a New Database

1. To create a new database, use the following SQL command:

CREATE DATABASE database_name;

Replace database_name with your desired database name (e.g., student).

2. To confirm it was created, you can list all databases:

SHOW DATABASES;

Step 3: Select the Database

1. Use the USE command to select the database you want to work with:

USE database_name;

2. Now, all the tables and operations will be created within this selected database.

Step 4: Create a Table in the Database

1. Use the CREATE TABLE command to create a table. Define the table name, column names, and data types. Here’s an example for a students table:

CREATE TABLE students (

id INT AUTO_INCREMENT PRIMARY KEY,

name VARCHAR(100),

age INT,

gender VARCHAR(100)

);

o id: an integer that auto-increments, making it a unique identifier for each row (set as the primary key).

o name: stores the student’s name, up to 100 characters.

o age: stores the student’s age.

2. To confirm the table creation, list the tables in the database:

SHOW TABLES;

Step 5: Verify the Table Structure

1. To view the structure of the students table, use the DESCRIBE command:

DESCRIBE students;

This shows each column, data type, and any constraints (e.g., primary key).

Example Workflow

mysql> CREATE DATABASE student_management;

mysql> USE student_management;

mysql> CREATE TABLE students (

id INT AUTO_INCREMENT PRIMARY KEY,

name VARCHAR(100),

age INT,

gender VARCHAR(100)

);

mysql> SHOW TABLES;

mysql> DESCRIBE students;

Type II: Using PHPMyadmin

Step 1: Access phpMyAdmin

1. Open your web browser.

2. Go to http://localhost/phpmyadmin (or the URL where phpMyAdmin is installed on your server).

3. Log in with your MySQL username and password.

Step 2: Create a New Database

1. In the left sidebar, click on New to start creating a new database.

2. In the Database name field, enter your desired database name (e.g., student_management).

3. Select a Collation (usually utf8_general_ci is a good choice for general purposes).

4. Click Create.

Step 3: Create a Table in the Database

1. After creating the database, it will appear in the left sidebar. Click on your new database name to open it.

2. You’ll see an option to Create table. In the Table name field, enter the table name (e.g., students).

3. In the Number of columns field, specify the number of columns you want (e.g., 5 for id, name, age, gender, and enrollment_date).

4. Click Go to proceed.

Step 4: Define Table Columns

1. For each column, enter the Column Name, Type, and Length/Values as needed:

o id: Type: INT, Length: leave empty, A_I (Auto Increment): check this box (this will automatically increment id for each new entry).

o name: Type: VARCHAR, Length: 100.

o age: Type: INT.

o enrollment_date: Type: DATE.

2. Set id as the Primary Key:

o Scroll to the id row and check the Primary box (or choose it in the Index dropdown). This makes id the unique identifier for each row.

3. After defining all columns, click Save at the bottom right.

Step 5: Verify the Table Structure

1. To confirm the table structure, click on the Structure tab at the top of the phpMyAdmin interface. This shows each column, data type, and constraints like primary keys.

Structure Of Table:

Column Name	Type	Length/Values	Index	A_I (Auto Increment)
id	INT		PRIMARY KEY	Yes
name	VARCHAR	100
age	INT
grade	VARCHAR 100

Viewing the Database and Table

1. In the left sidebar, you’ll now see the database (student) and the table (students) listed.

2. You can now proceed to insert, view, and manage data within this table!

2. Connecting PHP to MySQL

PHP offers two main methods to connect to MySQL:

Type 1: Connecting Using MySQLi

Example:

db/config/db_connect

db.php

<?php
// Database credentials

$servername = "localhost";

$username = "root";

$password = "";

$database = "student";

// Create connection

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

// Check connection

if ($conn->connect_error) {

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

}

echo "Connected successfully";

In this example:

localhost: Refers to the server where MySQL is running.

root: Default username for MySQL (use with caution; it's better to create specific users for each application).

password: Left blank by default on local installations like XAMPP, but it should be secure on production servers.

Type 2: Connecting Using PDO

PDO is a more flexible choice, supporting multiple databases beyond MySQL. It uses try-catch blocks for error handling.

Example:

<?php

try {

$conn = new PDO("mysql:host=localhost;dbname=student", "root", "");

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

echo "Connected successfully";

} catch(PDOException $e) {

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

}

Comparison of MySQLi and PDO

Feature	MySQLi	PDO
Database Support	MySQL only	Multiple databases
Named Parameters	No	Yes
Error Handling	Limited	Exception handling
Object-Oriented	Yes	Yes

3. Basic CRUD Operations with PHP and MySQL

CRUD stands for Create, Read, Update, and Delete — the four basic operations for manipulating data in a database.

A. Create Operation (INSERT)

The INSERT statement adds new records to a table.

Example:

insert.php

<?php

$sql = "INSERT INTO students (name, age, grade) VALUES ('Alice', 20, 'A')";

if ($conn->query($sql) === TRUE) {

echo "New record created successfully";

} else {

echo "Error: " . $sql . "<br>" . $conn->error;

}

This query inserts a new student with name "Alice," age 20, and grade "A" into the students table.

Prepared Statements (for security):

<?php

$stmt = $conn->prepare("INSERT INTO students (name, age, grade) VALUES (?, ?, ?)");

$stmt->bind_param("sis", $name, $age, $grade);

$stmt->execute();

B. Read Operation (SELECT)

The SELECT statement retrieves data from one or more tables.

Example:

read.php

<?php

$sql = "SELECT id, name, age, grade FROM students";

$result = $conn->query($sql);

if ($result->num_rows > 0) {

while($row = $result->fetch_assoc()) {

echo "ID: " . $row["id"]. " - Name: " . $row["name"]. " - Age: " . $row["age"]. " - Grade: " . $row["grade"]. "<br>";

}

} else {

echo "0 results";

}

Here, data is retrieved and displayed row-by-row using a loop. This approach allows us to process large datasets efficiently.

C. Update Operation (UPDATE)

The UPDATE statement modifies existing records.

Example:

update.php

<?php

$sql = "UPDATE students SET grade='B' WHERE name='Alice'";

if ($conn->query($sql) === TRUE) {

echo "Record updated successfully";

} else {

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

}

In this example, we change Alice’s grade to "B". Always use prepared statements for user-generated input to avoid SQL injection.

D. Delete Operation (DELETE)

The DELETE statement removes records from the table.

Example:

delete.php

<?php

$sql = "DELETE FROM students WHERE name='Alice'";

if ($conn->query($sql) === TRUE) {

echo "Record deleted successfully";

} else {

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

}

This command removes Alice’s record from the table.

4. Database Security: Preventing SQL Injection

SQL Injection is a security vulnerability that can allow an attacker to manipulate SQL queries by injecting code into form inputs or URL parameters.

Prepared statements are a key defense mechanism against SQL injection.

Example of Using Prepared Statements with MySQLi:

<?php

$stmt = $conn->prepare("SELECT * FROM students WHERE name = ?");

$stmt->bind_param("s", $name);

$stmt->execute();

$result = $stmt->get_result();

while($row = $result->fetch_assoc()) {

echo $row["name"];

}

5. Error Handling

Error Checking: Always check the connection and query results.

Exception Handling (PDO): The use of try-catch blocks to catch exceptions can make debugging and error management more effective.

Example:

<?php

try {

$stmt = $conn->prepare("INSERT INTO students (name, age) VALUES (:name, :age)");

$stmt->execute(array(':name' => 'Bob', ':age' => 22));

} catch(PDOException $e) {

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

}

Assignments(Using the Form Concept)

SOLUTIONS

Assignment 1. Create a Database Connection

This script connects PHP to a MySQL database and displays a success or error message based on the connection status.

<?php

// Database credentials

$servername = "localhost";

$username = "root";

$password = "";

$database = "student";

// Create connection

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

// Check connection

if ($conn->connect_error) {

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

}

echo "Connected successfully";

Explanation:

This code establishes a connection to MySQL using MySQLi.

$servername, $username, $password, and $database are connection details.

If the connection fails, die() stops execution and displays an error.

Assignment 2. CRUD Operations

Using Mysqli Query():

A. Insert Operation(Using Form )

This form and PHP script allow users to insert data into the students table.

HTML Form (insert_form.html):

<!DOCTYPE html>

<html>

<head>

<title>Insert Student</title>

</head>

<body>

Name: <input type="text" name="name" required><br>

Age: <input type="number" name="age" required><br>

Grade: <input type="text" name="grade" required><br>

</form>

</body>

</html>

PHP Script (insert_student.php):

<?php

include 'db_connection.php';

$name = $_POST['name'];

$age = $_POST['age'];

$grade = $_POST['grade'];

$sql = "INSERT INTO students (name, age, grade) VALUES ('$name', $age, '$grade')";

if ($conn->query($sql) === TRUE) {

echo "New record created successfully";

} else {

echo "Error: " . $sql . "<br>" . $conn->error;

}

$conn->close();

Explanation:

$_POST captures form input, and INSERT adds a new student.

db_connection.php should include the connection script from step 1.

$conn->close() closes the connection.

B. Retrieve Operation(Using Form)

This script retrieves and displays all records from the students table.

<?php

include 'db_connection.php';

$sql = "SELECT id, name, age, grade FROM students";

$result = $conn->query($sql);

if ($result->num_rows > 0) {

while($row = $result->fetch_assoc()) {

echo "ID: " . $row["id"]. " - Name: " . $row["name"]. " - Age: " . $row["age"]. " - Grade: " . $row["grade"]. "<br>";

}

} else {

echo "0 results";

}

$conn->close();

Explanation:

This code selects all student records, iterates over them with while(), and prints each one.

C. Update Operation

This form and script update a student’s grade based on their name.

HTML Form (update_form.html):

<!DOCTYPE html>

<html>

<head>

<title>Update Student Grade</title>

</head>

<body>

Name: <input type="text" name="name" required><br>

New Grade: <input type="text" name="new_grade" required><br>

</form>

</body>

</html>

PHP Script (update_student.php):

<?php

include 'db_connection.php';

$name = $_POST['name'];

$new_grade = $_POST['new_grade'];

$sql = "UPDATE students SET grade='$new_grade' WHERE name='$name'";

if ($conn->query($sql) === TRUE) {

echo "Record updated successfully";

} else {

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

}

$conn->close();

Explanation:

This updates the grade where name matches the form input.

D. Delete Operation

This script deletes a student record based on their ID.

HTML Form (delete_form.html):

<!DOCTYPE html>

<html>

<head>

<title>Delete Student</title>

</head>

<body>

Student ID: <input type="number" name="id" required><br>

</form>

</body>

</html>

PHP Script (delete_student.php):

<?php

include 'db_connection.php';

$id = $_POST['id'];

$sql = "DELETE FROM students WHERE id=$id";

if ($conn->query($sql) === TRUE) {

echo "Record deleted successfully";

} else {

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

}

$conn->close();

Explanation:

This code deletes a record where the id matches user input.

3. Prevent SQL Injection

To secure CRUD operations, use prepared statements.

Example (Insert with Prepared Statements):

<?php

include 'db_connection.php';

$stmt = $conn->prepare("INSERT INTO students (name, age, grade) VALUES (?, ?, ?)");

$stmt->bind_param("sis", $name, $age, $grade);

$name = $_POST['name'];

$age = $_POST['age'];

$grade = $_POST['grade'];

$stmt->execute();

echo "New record created successfully";

$stmt->close();

$conn->close();

Explanation:

bind_param binds data, preventing SQL injection by separating SQL commands from data.

4. Mini Project: Student Management System

Combine all CRUD operations above into a single project folder(student ):

index.php

<?php

// Database connection details

include "db_connect.php";

// Fetch all students from the database

$sql = "SELECT * FROM students ORDER BY id DESC";

$result = $conn->query($sql);

<!DOCTYPE html>

<head>

<title>Student Management System</title>

<style>

body { font-family: Arial, sans-serif; }

.container { width: 80%; margin: auto; }

table { width: 100%; border-collapse: collapse; margin-top: 20px; }

table, th, td { border: 1px solid black; padding: 8px; text-align: left; }

th { background-color: #f2f2f2; }

.add-student { margin-top: 20px; }

</style>

</head>

<body>

<h1>Student Management System</h1>

<h2>Student List</h2>

<?php if ($result->num_rows > 0): ?>

<table>

<tr>

<th>Gender</th>

<th>Enrollment Date</th>

</tr>

<?php while ($student = $result->fetch_assoc()): ?>

<tr>

</tr>

<?php endwhile; ?>

</table>

<?php else: ?>

<p>No students found.</p>

<?php endif; ?>

<button type="button">Add Student</button>

</a>

</div>

</body>

</html>

<?php

// Close the database connection

$conn->close();

Add navigation links or buttons to switch between pages, creating a seamless user interface.

5. Database Query Assignment

Here are queries to retrieve specific information from students:

SELECT * FROM students WHERE age > 18;

SELECT * FROM students ORDER BY grade DESC;

SELECT * FROM students ORDER BY grade DESC LIMIT 3;

These queries can be run in PHP by using $conn->query() or prepared statements for added security.

PHP Database Integration with MySQL

PHP Database Integration with MySQL

insert.php

read.php

update.php

delete.php

Assignments(Using the Form Concept)

Assignment 1. Create a Database Connection

Assignment 2. CRUD Operations

A. Insert Operation(Using Form )

B. Retrieve Operation(Using Form)

C. Update Operation

D. Delete Operation

3. Prevent SQL Injection

4. Mini Project: Student Management System

5. Database Query Assignment

Post a Comment

Practical Test Of MCA 2023-2025(A)

Contact form

PHP Database Integration with MySQL

PHP Database Integration with MySQL

insert.php

read.php

update.php

delete.php

Assignments(Using the Form Concept)

Assignment 1. Create a Database Connection

Assignment 2. CRUD Operations

A. Insert Operation(Using Form )

B. Retrieve Operation(Using Form)

C. Update Operation

D. Delete Operation

3. Prevent SQL Injection

4. Mini Project: Student Management System

5. Database Query Assignment

You Might Like

Post a Comment

Contact form