Database Connection via PDO

PDO (PHP Data Objects) is an interface for accessing various databases. Its biggest advantage is that it enables secure work with prepared statements and easy switching between different database types (MySQL, PostgreSQL, SQLite).

EXAMPLE

<?php
$server = "localhost";
$username = "root";
$password = "";
$database = "pdo_database";

try {
    // DSN (Data Source Name) specifies the driver type (mysql), host, and database name.
    // NOTE: Always add charset=utf8mb4 directly to the DSN string, as this ensures proper handling 
    // of special characters (like non-English letters) as soon as the connection is established.
    $dsn = "mysql:host=$server;dbname=$database;charset=utf8mb4";
    
    // Creating a new PDO object automatically opens the connection to the database
    $connection = new PDO($dsn, $username, $password);
    
    // Set error reporting mode to Exceptions
    // This is crucial, as any failed SQL statement will immediately trigger an exception, 
    // which we catch in the catch block below, instead of the script silently continuing.
    $connection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    
    echo "PDO connection was successful!";
} catch (PDOException $e) {
    // If the connection fails, the code in the try block is interrupted and this security block is executed
    echo "Connection failed: " . $e->getMessage();
}

// Closing a PDO connection is done by simply assigning null to the object
$connection = null;
?>

RESULT

The result will be displayed here...

PDO uses an object-oriented approach.
Supports Exceptions for better error handling.
Prepared statements protect against SQL injection.

Overview of common PDO operations

Below is a list of key commands in the PDO library. You will notice that the logic of SQL commands is the same as in MySQLi, but the way PHP presents them to the database is more modern and object-oriented.

Description / Method	Operation Description	Examples
PDO CONNECT	Establishing a connection using DSN (Data Source Name).	▶ Code example
CREATE DATABASE	Create a new database using the exec() method.	▶ Code example
CREATE TABLE	Create a new table with specific columns.	▶ Code example
INSERT INTO	Insert a record using exec() or prepare().	▶ Code example
LAST INSERT ID	Retrieve the ID of the last record using lastInsertId().	▶ Code example
PREPARED STATEMENTS	Secure database work using named parameters (:name).	▶ Code example
SELECT / FETCH	Retrieve data and store it in an Array.	▶ Code example
WHERE	Filter data using conditions.	▶ Code example
ORDER BY	Sort results in a specific order.	▶ Code example
DELETE	Delete records from a table.	▶ Code example
UPDATE	Update existing rows.	▶ Code example
TRANSACTIONS	Advanced work with beginTransaction, commit, and rollback.	▶ Code example

PDO Library - Alphabetical list of methods

PDO (PHP Data Objects) offers a consistent interface for working with different databases. Below is a complete list of methods available in the PDO and PDOStatement classes. Use the search bar above the table for easier searching.

Method	Operation Description	Simulator Example
`__construct()`	Creates a new PDO object.	▶ Code example
`beginTransaction()`	Starts a transaction.	▶ Code example
`bindColumn()`	Binds a column to a PHP variable.	▶ Code example
`bindParam()`	Binds a parameter to the specified variable name.	▶ Code example
`bindValue()`	Binds a value to a parameter.	▶ Code example
`closeCursor()`	Closes the cursor, enabling the statement to be executed again.	▶ Code example
`columnCount()`	Returns the number of columns in the result set.	▶ Code example
`commit()`	Commits a transaction.	▶ Code example
`debugDumpParams()`	Dumps an SQL prepared statement for debugging purposes.	▶ Code example
`errorCode()`	Fetches the SQLSTATE associated with the last operation.	▶ Code example
`errorInfo()`	Fetch extended error information associated with the last operation.	▶ Code example
`exec()`	Execute an SQL statement and return the number of affected rows.	▶ Code example
`execute()`	Executes a prepared statement.	▶ Code example
`fetch()`	Fetches the next row from a result set.	▶ Code example
`fetchAll()`	Returns an array containing all of the result set rows.	▶ Code example
`fetchColumn()`	Returns a single column from the next row of a result set.	▶ Code example
`fetchObject()`	Fetches the next row and returns it as an object.	▶ Code example
`getAttribute()`	Retrieve a database connection attribute.	▶ Code example
`getAvailableDrivers()`	Returns an array of available PDO drivers.	▶ Code example
`inTransaction()`	Checks if inside a transaction.	▶ Code example
`lastInsertId()`	Returns the ID of the last inserted row.	▶ Code example
`nextRowset()`	Advances to the next rowset in a multi-result statement.	▶ Code example
`prepare()`	Prepares an SQL statement for secure execution.	▶ Code example
`query()`	Prepares and executes an SQL statement in one step.	▶ Code example
`quote()`	Quotes a string for use in a query.	▶ Code example
`rollBack()`	Rolls back a transaction.	▶ Code example
`rowCount()`	Returns the number of rows affected by the last SQL statement.	▶ Code example
`setAttribute()`	Set a connection attribute.	▶ Code example
`setFetchMode()`	Set the default fetch mode for this statement.	▶ Code example