Lecture Notes for Class 15:
Working
with Databases - Part 1
Objective:
- Introduction to
Databases: Understand what a database is and its importance in web
applications.
- Connecting to
MySQL using PHP: Learn how to connect a PHP script to a MySQL database.
- Basic Database
Operations: Get familiar with fundamental SQL operations such as
creating, reading, updating, and deleting (CRUD) data.
1. What is a Database?
- Definition: A
database is an organized collection of data that can be easily accessed,
managed, and updated.
- Types of Databases:
- Relational
Databases: Data is stored in tables (e.g., MySQL, PostgreSQL).
- NoSQL Databases:
Data is stored in formats like JSON, key-value pairs, etc. (e.g.,
MongoDB, Redis).
Importance of Databases:
- Data persistence:
Store data for long-term use.
- Data integrity:
Maintain accuracy and consistency of data.
- Multi-user access:
Allow multiple users to access and manipulate data simultaneously.
2. MySQL Database
- Overview:
MySQL is a popular open-source relational database management system
(RDBMS).
- Key Features:
- High performance and
reliability.
- Flexibility in
managing large volumes of data.
- Support for multiple
data types.
3. Setting Up MySQL
- Installation:
- Ensure you have a
web server (like XAMPP) that includes MySQL.
- Start the MySQL
server through XAMPP Control Panel.
- Creating a
Database:
- Open phpMyAdmin
(usually available at http://localhost/phpmyadmin).
- Click on the
"Databases" tab.
- Enter a name for
your new database and click "Create".
Example: Create a database
called school_db.
4. Connecting to MySQL using PHP
- Using the MySQLi
Extension:
- MySQLi (MySQL
Improved) is a PHP extension that allows you to interact with MySQL
databases.
- Basic Connection
Code:
php
Copy code
<?php
// Database configuration
$host = 'localhost'; // Host name
$username = 'root'; // MySQL username
$password = ''; // MySQL password
$database = 'school_db'; // Database
name
// Create connection
$conn = new mysqli($host, $username,
$password, $database);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
echo "Connected
successfully";
?>
- Explanation:
- new mysqli(...):
Creates a new MySQLi object and establishes a connection.
- $conn->connect_error:
Checks for connection errors.
5. Basic Database Operations
- Creating Tables:
Define the structure of your data.
sql
Copy code
CREATE TABLE students (
id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(30) NOT NULL,
age INT(3) NOT NULL,
class VARCHAR(50),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
- Inserting Data:
sql
Copy code
INSERT INTO students (name, age,
class) VALUES ('John Doe', 20, '10th Grade');
- Reading Data:
sql
Copy code
SELECT * FROM students;
- Updating Data:
sql
Copy code
UPDATE students SET age = 21 WHERE id
= 1;
- Deleting Data:
sql
Copy code
DELETE FROM students WHERE id = 1;
6. Executing SQL Queries in PHP
- Inserting Data:
php
Copy code
$sql = "INSERT INTO students
(name, age, class) VALUES ('Jane Doe', 19, '9th Grade')";
if ($conn->query($sql) === TRUE) {
echo "New record created successfully";
} else {
echo "Error: " . $sql . "<br>" .
$conn->error;
}
- Reading Data:
php
Copy code
$sql = "SELECT * 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"].
"<br>";
}
} else {
echo "0 results";
}
7. Conclusion
- By the end of this
class, students should be able to:
- Establish a
connection to a MySQL database using PHP.
- Execute basic SQL
queries to interact with the database.
- Next Steps: In
the next class, we will explore more advanced SQL operations and error
handling in PHP.
8. Practice Assignment
- Create a new
database called library_db.
- Create a table
called books with fields: id, title, author, and published_year.
- Write PHP scripts to
insert a few records into the books table and retrieve all records.
Additional Resources:
- PHP
MySQLi Documentation
- W3Schools SQL
Tutorial
- phpMyAdmin Documentation