| Introduction to Web Development |
|---|
| Basic PHP Syntax |
|---|
| PHP Basics |
|---|
| Variables and Data Types |
|---|
| Functions in PHP |
|---|
| User-defined Functions |
|---|
| Built-in Functions |
|---|
| Variable Scope |
|---|
| Include and Require |
|---|
| Working with Forms |
|---|
| Forms in HTML |
|---|
| Form Handling in PHP |
|---|
| Working with Files |
|---|
| File Handling |
|---|
| File System Functions |
|---|
| Working with Databases |
|---|
| Introduction to Databases |
|---|
| Advanced PHP Techniques |
|---|
| Object-Oriented Programming (OOP) |
|---|
| Error and Exception Handling |
|---|
A database is a structured collection of data that is stored and managed electronically. Databases are used to store, retrieve, and manage data in a way that allows for efficient querying, updating, and administration. Databases are crucial in applications that require large amounts of data to be stored and accessed, such as websites, business applications, and software systems.
Types of Databases
1. Relational Databases (RDBMS):
- Use tables to store data.
- Tables are related to each other through keys.
- Examples: MySQL, PostgreSQL, SQLite, Microsoft SQL Server, Oracle Database.
2. NoSQL Databases:
- Do not use fixed table schemas.
- Suitable for unstructured or semi-structured data.
- Examples: MongoDB, Cassandra, CouchDB, Redis.
3. In-Memory Databases:
- Store data in RAM for faster access.
- Examples: Redis, Memcached.
4. Object-Oriented Databases:
- Store data in the form of objects.
- Examples: ObjectDB, db4o.
5. NewSQL Databases:
- Aim to provide the same scalable performance of NoSQL systems while maintaining the ACID (Atomicity, Consistency, Isolation, Durability) guarantees of traditional databases.
- Examples: Google Spanner, CockroachDB.
Using a Database with PHP
PHP (Hypertext Preprocessor) is a popular server-side scripting language used to develop dynamic web applications. PHP can interact with various databases, with MySQL being one of the most commonly used databases in PHP applications.
Steps to Use a Database with PHP
1. Install and Configure Database Server
- Install a database server like MySQL or MariaDB.
- Configure the database server, create a database, and set up user permissions.
2. Connect to the Database
Using MySQLi (MySQL Improved) extension:
<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "database_name";
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
echo "Connected successfully";
?>
Using PDO (PHP Data Objects):
<?php
$dsn = "mysql:host=localhost;dbname=database_name";
$username = "username";
$password = "password";
try {
$pdo = new PDO($dsn, $username, $password);
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
echo "Connected successfully";
} catch (PDOException $e) {
echo "Connection failed: " . $e->getMessage();
}
?>
3. Perform Database Operations
- Create a Table
<?php
$sql = "CREATE TABLE MyGuests (
id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
firstname VARCHAR(30) NOT NULL,
lastname VARCHAR(30) NOT NULL,
email VARCHAR(50),
reg_date TIMESTAMP
)";
if ($conn->query($sql) === TRUE) {
echo "Table MyGuests created successfully";
} else {
echo "Error creating table: " . $conn->error;
}
$conn->close();
?>
- Insert Data
<?php
$sql = "INSERT INTO MyGuests (firstname, lastname, email) VALUES ('John', 'Doe', 'john@example.com')";
if ($conn->query($sql) === TRUE) {
echo "New record created successfully";
} else {
echo "Error: " . $sql . "<br>" . $conn->error;
}
$conn->close();
?>
- Select Data
<?php
$sql = "SELECT id, firstname, lastname FROM MyGuests";
$result = $conn->query($sql);
if ($result->num_rows > 0) {
// Output data of each row
while($row = $result->fetch_assoc()) {
echo "id: " . $row["id"]. " - Name: " . $row["firstname"]. " " . $row["lastname"]. "<br>";
}
} else {
echo "0 results";
}
$conn->close();
?>
- Update Data
<?php
$sql = "UPDATE MyGuests SET lastname='Doe' WHERE id=2";
if ($conn->query($sql) === TRUE) {
echo "Record updated successfully";
} else {
echo "Error updating record: " . $conn->error;
}
$conn->close();
?>
- Delete Data
<?php
$sql = "DELETE FROM MyGuests WHERE id=3";
if ($conn->query($sql) === TRUE) {
echo "Record deleted successfully";
} else {
echo "Error deleting record: " . $conn->error;
}
$conn->close();
?>
4. Close the Database Connection
Always close the database connection when you’re done to free up resources.
<?php
$conn->close();
?>
Best Practices
- Use Prepared Statements: To prevent SQL injection attacks, use prepared statements when working with user inputs.
<?php
$stmt = $pdo->prepare("INSERT INTO MyGuests (firstname, lastname, email) VALUES (:firstname, :lastname, :email)");
$stmt->bindParam(':firstname', $firstname);
$stmt->bindParam(':lastname', $lastname);
$stmt->bindParam(':email', $email);
// Insert a row
$firstname = "John";
$lastname = "Doe";
$email = "john@example.com";
$stmt->execute();
?>
- Error Handling: Implement proper error handling to manage and log errors gracefully.
<?php
try {
// database operations
} catch (PDOException $e) {
error_log($e->getMessage());
exit('Something went wrong');
}
?>
- Secure Database Credentials: Never hard-code database credentials in your PHP files. Use environment variables or configuration files.
By following these steps and best practices, you can effectively use databases with PHP to create robust and secure web applications.
