Unit 3: Database Interaction and CodeIgniter Framework
Database Interaction and CodeIgniter Framework
NOTES
3.1 PHP with MySQL/MongoDB
Introduction to PHP
PHP (Hypertext Preprocessor) is a server-side scripting language used to create dynamic and interactive web pages. It is widely used with databases to store, retrieve, and manage data.
Server-side: Code is executed on the server.
Open-source: Free to use and supported by a large community.
Used for: Form handling, CRUD operations, authentication systems, etc.
Connecting PHP with Databases
PHP can be connected with different types of databases to perform operations like inserting, updating, deleting, and retrieving data.
Popular Databases used with PHP
MySQL (Relational Database)
MongoDB (NoSQL Database)
1. PHP with MySQL
What is MySQL?
MySQL is an open-source Relational Database Management System (RDBMS) that uses tables to store data.
Connecting PHP with MySQL
PHP uses the mysqli or PDO extension to connect with MySQL.
Steps to Connect:
Basic MySQL Operations in PHP
1. Inserting Data
2. Retrieving Data
3. Updating Data
4. Deleting Data
2. PHP with MongoDB
What is MongoDB?
MongoDB is a NoSQL database that stores data in JSON-like documents (called BSON format). It is schema-less and flexible, ideal for unstructured data.
Requirements:
Install MongoDB server
Enable MongoDB driver/extension in PHP
Connecting PHP with MongoDB
Basic MongoDB Operations in PHP
1. Inserting Data
2. Retrieving Data
3. Updating Data
4. Deleting Data
Comparison: MySQL vs MongoDB
| Feature | MySQL (Relational) | MongoDB (NoSQL) |
|---|---|---|
| Data Format | Tables (rows and columns) | Documents (BSON/JSON format) |
| Schema | Fixed schema | Schema-less |
| Relationships | Supports JOINs | Embeds documents instead |
| Use Case | Structured data | Flexible or unstructured data |
| Query Language | SQL | MongoDB Query Language |
3.1.1 Connecting to Databases using mysqli or PDO
Introduction
When building dynamic web applications, we often need to store, retrieve, update, or delete data. PHP provides two main ways to connect to MySQL databases:
mysqli(MySQL Improved) ExtensionPDO(PHP Data Objects)
Both can be used to interact with MySQL databases, but they have some key differences.
1. Connecting with mysqli
✅ What is mysqli?
mysqlistands for MySQL Improved.It is an extension specifically designed for MySQL.
Supports procedural and object-oriented styles.
Allows prepared statements, which help prevent SQL injection.
🧪 Syntax (Procedural style):
🧪 Syntax (Object-Oriented style):
2. Connecting with PDO
✅ What is PDO?
PDOstands for PHP Data Objects.It provides a universal interface for multiple databases (MySQL, PostgreSQL, SQLite, etc.).
Only supports object-oriented style.
Supports prepared statements and exception handling.
🧪 Syntax:
Key Differences Between mysqli and PDO
| Feature | mysqli | PDO |
|---|---|---|
| Full Form | MySQL Improved | PHP Data Objects |
| Database Support | Only MySQL | Multiple (MySQL, PostgreSQL, SQLite…) |
| Style Supported | Procedural & Object-Oriented | Only Object-Oriented |
| Prepared Statements | Yes | Yes |
| Error Handling | Limited | Uses try-catch (Exception handling) |
| Portability | Less portable | More portable |
Which One to Use?
Use
mysqliif you’re only working with MySQL and prefer a simpler syntax.Use
PDOif you want flexibility and may switch between different types of databases in the future.Both are secure and support prepared statements, but PDO is generally considered more robust and modern.
✅ Tip: Always close the connection when you’re done.
mysqli
3.1.2 Creating Databases and Tables
Introduction
In any database-driven application, the first step is to create a database and the necessary tables to store data. In PHP, we can perform these operations using SQL queries and PHP functions such as mysqli or PDO.
1. What is a Database?
A database is a structured collection of data that can be accessed, managed, and updated. In MySQL, a database consists of one or more tables, and each table holds data in rows and columns.
2. What is a Table?
A table is a structure inside a database where data is stored.
Each row represents a single record.
Each column represents a field (e.g., name, age, email).
3. Creating a Database using PHP
✅ Using mysqli (Procedural Style)
4. Creating a Table in a Database
Before creating a table, we need to select a database.