UNIT IV DATABASES AND GRAPHICS USING PHP
Using PHP to access Database – Relational Databases and SQL – MySQLi Object interface – SQLite-
Direct file level manipulation – mongoDB.Embedding an image in a page – Basic Graphic concepts –
Creating and drawing images.
Using PHP to access Database:
Accessing Databases with PHP
PHP provides several ways to interact with databases. Here are the most common methods:
1. MySQLi (MySQL Improved)
<?php
// Database configuration
$servername = "localhost";
$username = "your_username";
$password = "your_password";
$dbname = "your_database";
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
// SQL query
$sql = "SELECT id, name, email FROM users";
$result = $conn->query($sql);
// Process results
if ($result->num_rows > 0) {
while($row = $result->fetch_assoc()) {
echo "ID: " . $row["id"]. " - Name: " . $row["name"]. " - Email: " . $row["email"]. "<br>";
}
} else {
echo "0 results";
}
// Close connection
$conn->close();
?>
2. PDO (PHP Data Objects)
<?php
try {
// Database configuration
$servername = "localhost";
$username = "your_username";
$password = "your_password";
$dbname = "your_database";
// Create connection
$conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
// Set PDO error mode to exception
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
// SQL query
$stmt = $conn->prepare("SELECT id, name, email FROM users");
$stmt->execute();
// Set the resulting array to associative
$result = $stmt->setFetchMode(PDO::FETCH_ASSOC);
// Process results
foreach($stmt->fetchAll() as $row) {
echo "ID: " . $row["id"]. " - Name: " . $row["name"]. " - Email: " . $row["email"]. "<br>";
}
} catch(PDOException $e) {
echo "Error: " . $e->getMessage();
}
// Close connection
$conn = null;
?>
3. Prepared Statements (Security Best Practice)
<?php
// Using MySQLi prepared statements
$stmt = $conn->prepare("INSERT INTO users (name, email) VALUES (?, ?)");
$stmt->bind_param("ss", $name, $email); // "ss" indicates two string parameters
// Set parameters and execute
$name = "John Doe";
$email = "
[email protected]";
$stmt->execute();
echo "New records created successfully";
$stmt->close();
?>
4. Connecting to Other Databases
PHP can connect to various databases including PostgreSQL, SQLite, Oracle, etc. Here's a
PostgreSQL example:
<?php
$conn = pg_connect("host=localhost dbname=your_database user=your_username
password=your_password");
if (!$conn) {
echo "An error occurred.\n";
exit;
}
$result = pg_query($conn, "SELECT * FROM users");
while ($row = pg_fetch_assoc($result)) {
echo $row['username'] . "\n";
}
?>
Relational Databases and SQL:
Relational databases organize data into tables with rows and columns, using structured relationships
between tables. SQL (Structured Query Language) is the standard language for interacting with these
databases.
Core Concepts
1. Tables
Store data in rows (records) and columns (fields)
Each table represents an entity (e.g., Users, Orders, Products)
2. Primary Keys
Unique identifier for each record in a table
Ensures no duplicate records
Example: user_id in a Users table
3. Foreign Keys
Creates relationships between tables
References the primary key of another table
Example: order.user_id references user.user_id
4. Relationships
One-to-One: One record in Table A relates to one record in Table B
One-to-Many: One record in Table A relates to many records in Table B
Many-to-Many: Requires a junction table (e.g., Students and Courses)
Basic SQL Commands
Data Definition Language (DDL)
-- Create a table
CREATE TABLE users (
user_id INT PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(50) NOT NULL UNIQUE,
email VARCHAR(100) NOT NULL UNIQUE,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- Alter a table
ALTER TABLE users ADD COLUMN last_login TIMESTAMP;
-- Drop a table
DROP TABLE users;
Data Manipulation Language (DML)
-- Insert data
INSERT INTO users (username, email) VALUES ('johndoe', '[email protected]');
-- Update data
UPDATE users SET email = '[email protected]' WHERE user_id = 1;
-- Delete data
DELETE FROM users WHERE user_id = 1;
-- Select data
SELECT * FROM users;
SELECT username, email FROM users WHERE user_id > 10 ORDER BY created_at DESC;
Joins (Combining Tables)
-- Inner Join (only matching records)
SELECT orders.order_id, users.username
FROM orders
INNER JOIN users ON orders.user_id = users.user_id;
-- Left Join (all from left table + matches from right)
SELECT users.username, orders.order_id
FROM users
LEFT JOIN orders ON users.user_id = orders.user_id;
-- Right Join (all from right table + matches from left)
SELECT users.username, orders.order_id
FROM users
RIGHT JOIN orders ON users.user_id = orders.user_id;
Aggregation and Grouping
-- Count records
SELECT COUNT(*) FROM users;
-- Group with aggregation
SELECT department, COUNT(*) as employee_count
FROM employees
GROUP BY department
HAVING COUNT(*) > 5;
Database Normalization
1. First Normal Form (1NF)
o Eliminate repeating groups
o All attributes contain atomic values
2. Second Normal Form (2NF)
o Must be in 1NF
o No partial dependency on primary key
3. Third Normal Form (3NF)
o Must be in 2NF
o No transitive dependencies
Indexes for Performance
-- Create an index
CREATE INDEX idx_email ON users(email);
-- Composite index
CREATE INDEX idx_name_department ON employees(last_name, department);
-- Unique index
CREATE UNIQUE INDEX idx_unique_username ON users(username);
Transactions (ACID Properties)
START TRANSACTION;
INSERT INTO orders (user_id, total) VALUES (1, 99.99);
UPDATE inventory SET quantity = quantity - 1 WHERE product_id = 101;
COMMIT; -- or ROLLBACK if errors occur
Stored Procedures
DELIMITER //
CREATE PROCEDURE GetUserOrders(IN userId INT)
BEGIN
SELECT * FROM orders WHERE user_id = userId;
END //
DELIMITER ;
-- Call the procedure
CALL GetUserOrders(1);
Views
CREATE VIEW active_users AS
SELECT user_id, username, email
FROM users
WHERE last_login > DATE_SUB(NOW(), INTERVAL 30 DAY);
-- Use the view
SELECT * FROM active_users;
MySQLi Object interface
MySQLi Object-Oriented Interface in PHP
The MySQLi extension provides an object-oriented interface for working with MySQL databases.
Here's a comprehensive guide to using the MySQLi object interface:
Basic Connection
```php
<?php
// Create connection
$mysqli = new mysqli("localhost", "username", "password", "database");
// Check connection
if ($mysqli->connect_error) {
die("Connection failed: " . $mysqli->connect_error);
}
echo "Connected successfully";
?>
```
Key Methods and Properties
1. Executing Queries
```php
// For SELECT queries (returns result object)
$result = $mysqli->query("SELECT * FROM users");
// For INSERT, UPDATE, DELETE (returns boolean)
$success = $mysqli->query("INSERT INTO users (name, email) VALUES ('John',
'
[email protected]')");
```
2. Prepared Statements
```php
// Prepare statement
$stmt = $mysqli->prepare("INSERT INTO users (name, email) VALUES (?, ?)");
// Bind parameters
$name = "John Doe";
$email = "[email protected]";
$stmt->bind_param("ss", $name, $email); // "ss" = two strings
// Execute
$stmt->execute();
// Close statement
$stmt->close();
```
3. Fetching Results
```php
$result = $mysqli->query("SELECT id, name, email FROM users");
// Fetch all rows as associative array
$rows = $result->fetch_all(MYSQLI_ASSOC);
// Fetch row by row
while ($row = $result->fetch_assoc()) {
echo "ID: {$row['id']}, Name: {$row['name']}";
}
// Get number of rows
echo $result->num_rows;
// Free result
$result->free();
```
4. Transaction Handling
```php
// Start transaction
$mysqli->begin_transaction();
try {
$mysqli->query("UPDATE accounts SET balance = balance - 100 WHERE user_id = 1");
$mysqli->query("UPDATE accounts SET balance = balance + 100 WHERE user_id = 2");
// Commit if both queries succeed
$mysqli->commit();
} catch (Exception $e) {
// Rollback on error
$mysqli->rollback();
echo "Transaction failed: " . $e->getMessage();
}
```
Error Handling
```php
// Check for errors in query execution
if (!$mysqli->query("INSERT INTO users (name) VALUES ('John')")) {
echo "Error: " . $mysqli->error;
}
// Or use exceptions
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
try {
$mysqli = new mysqli("localhost", "username", "password", "database");
$mysqli->query("INVALID SQL");
} catch (mysqli_sql_exception $e) {
echo "MySQLi Error: " . $e->getMessage();
}
```
Practical Example
```php
<?php
class Database {
private $mysqli;
public function __construct($host, $user, $pass, $db) {
$this->mysqli = new mysqli($host, $user, $pass, $db);
if ($this->mysqli->connect_error) {
throw new Exception("Connection failed: " . $this->mysqli->connect_error);
}
}
public function getUser($id) {
$stmt = $this->mysqli->prepare("SELECT id, name, email FROM users WHERE id = ?");
$stmt->bind_param("i", $id);
$stmt->execute();
$result = $stmt->get_result();
$user = $result->fetch_assoc();
$stmt->close();
return $user;
}
public function close() {
$this->mysqli->close();
}
}
// Usage
try {
$db = new Database("localhost", "user", "password", "my_db");
$user = $db->getUser(1);
print_r($user);
$db->close();
} catch (Exception $e) {
echo $e->getMessage();
}
?>
```
