PHP Database Connectivity: Access a Database using PHP

What are Relational Databases?

• Relational databases are the backbone of many web applications.
• They are structured in tables with rows and columns, allowing data to be organized efficiently.
• These databases establish relationships between different tables, enabling complex data retrieval and manipulation.

SQL

• SQL, or Structured Query Language, is the universal language for managing relational databases.
• With SQL, you can create, retrieve, update, and delete data.
• It's essential for any developer to grasp the basics of SQL to interact effectively with databases.

PHP Data Objects (PDO)

• PHP Data Objects (PDO) is a database abstraction layer that provides a consistent interface for connecting to various database systems.
• It simplifies database connectivity, making it easier to switch between different databases without changing your code.

Database Agnostic

• PDO is designed to work with multiple database management systems (DBMS) such as MySQL, PostgreSQL, SQLite, etc.
• It provides a consistent API regardless of the underlying database,
• making it easier to switch between different databases without changing much code.

Prepared Statements

• PDO supports prepared statements, which are pre-compiled SQL statements with placeholders for variables.
• Prepared statements help prevent SQL injection attacks by separating SQL code from user input and automatically sanitizing input values.

Parameter Binding

• PDO allows you to bind parameters to prepared statements, where the actual values are passed separately from the SQL query.
• This improves performance and security by reducing the need for repetitive parsing and compilation of SQL statements.

Error Handling

What is SQLite?

• SQLite is a serverless, self-contained, and file-based relational database engine.
• It's perfect for small to medium-sized applications.
• We'll guide you through setting up an SQLite database and performing basic operations using PHP.
• Create a SQLite Database: If you haven't already created a SQLite database file,
• you can do so using a tool like DB Browser for SQLite or executing commands in the SQLite command-line interface.
• Enable SQLite Extension in PHP: Ensure that the SQLite extension is enabled in your PHP configuration file (php.ini).
• Look for the following line and remove the semicolon (;) if present to enable the extension.

1<?php
2try {
3    // Specify the SQLite database file path
4    $databaseFile = 'path/to/your/database.db';
5
6    // Create a new PDO connection to the SQLite database
7    $pdo = new PDO('sqlite:' . $databaseFile);
8
9    // Set PDO error mode to exception for error handling
10    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
11
12    // Display success message if connection is established
13    echo "Connected to SQLite database successfully!";
14} catch (PDOException $e) {
15    // Display error message if connection fails
16    echo "Connection failed: " . $e->getMessage();
17}
18?>

• Replace 'path/to/your/database.db' with the actual file path to your SQLite database file.
• The try block attempts to create a new PDO connection to the SQLite database using the specified database file path.
• We set the PDO error mode to PDO::ERRMODE_EXCEPTION to enable exception-based error handling.
• If the connection is successful, the code displays a success message.
• If an exception occurs during the connection attempt, it catches the PDOException and displays an error message.

What is MongoDB?

• MongoDB is a popular NoSQL database management system that stores data in a flexible, JSON-like format called BSON (Binary JSON).
• Document-Oriented: MongoDB is a document-oriented database, meaning it stores data in documents rather than tables.
• Scalability: MongoDB is designed for horizontal scalability, allowing you to distribute data across multiple servers or clusters.
• Query Language: MongoDB uses a powerful query language based on JSON-like syntax.
• It supports a wide range of queries, including CRUD operations (Create, Read, Update, Delete), text search , etc.
• Discover how PHP can be used to connect to MongoDB and perform operations such as inserting, retrieving, and updating documents.

What is MySQL ?

• MySQL is one of the most widely or extensively used open-source relational database management systems.
• Scalability and Performance: MySQL is designed for scalability and can handle large amounts of data efficiently
• Cross-Platform Compatibility: MySQL is compatible with various operating systems, including Windows, Linux and macOS.
• Community and Ecosystem: MySQL has a large and active community of developers, database administrators and users who contribute to its development.

MySQLi Object Interface

• MySQLi is a PHP extension specifically designed for MySQL database interactions.
• It offers both a procedural and an object-oriented approach to database operations.

Object-Oriented Approach

Database Connection

• The MySQLi object-oriented interface allows you to establish a connection to a MySQL database using the mysqli class.
• for example:

1$mysqli = new mysqli('localhost', 'username', 'password', 'database_name');
2if ($mysqli->connect_error) {
3    die('Connection failed: ' . $mysqli->connect_error);
4}
5echo 'Connected successfully!';

1$stmt = $mysqli->prepare('SELECT * FROM users WHERE id = ?');
2$id = 1; // Example user ID
3$stmt->bind_param('i', $id); // 'i' indicates integer type
4$stmt->execute();
5$result = $stmt->get_result();
6$user = $result->fetch_assoc();
7echo 'User ID: ' . $user['id'] . '<br>';
8echo 'Name: ' . $user['name'] . '<br>';
9echo 'Email: ' . $user['email'] . '<br>';

Checking Data Errors

Conclusion

• You now have a basic understanding of relational databases, SQL, PDO, MySQLi, SQLite, MongoDB, and MySQL.
• You've also learned how to perform database operations with PHP and handle data errors effectively.