Here is a simple class as described above, that will work for code containing a single database connection -
<?php
// simple db class using PDO
class db
{
public static $db;
public static function connect($dsn,$username=null,$password=null,$driver_options=null)
{
$pdo = new pdo($dsn, $username, $password, $driver_options);
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); // set the error mode to exceptions
$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES,false); // turn off emulated prepared queries, run real prepared queries
$pdo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE,PDO::FETCH_ASSOC); // set default fetch mode to assoc
self::$db = $pdo;
}
// general purpose query method. accepts an optional array of input parameters and returns a pdostatement object
public static function query($sql,$params=[])
{
if($params)
{
// prepared query
$stmt = self::$db->prepare($sql);
$stmt->execute($params);
// note: to execute a prepared query more than once, you would call the ->execute() method on the returned stmt object, supplying an array of new input values for each new execution
} else {
// non-prepared query
$stmt = self::$db->query($sql);
}
// return the pdo statement object
return $stmt;
}
public static function fetch_one($sql,$params=[])
{
$stmt = self::query($sql,$params);
$row = $stmt->fetch();
return $row;
}
public static function fetch_all($sql,$params=[])
{
$stmt = self::query($sql,$params);
$rows = $stmt->fetchAll();
return $rows;
}
}
// example usage
// define connection credentials
$db_servername = ""; // hostname or ip address
$db_username = ""; // connection username
$db_password = ""; // connection password
$db_name = ""; // database name
$db_charset = "utf8mb4"; // character set for connection/your database tables - typical value shown
//define PDO dsn
$dsn = "mysql:host=$db_servername;dbname=$db_name;charset=$db_charset";
//create connection
db::connect($dsn,$db_username,$db_password);
// form a prepared sql query, execute, and fetch one row
$sql = "SELECT * FROM your_table WHERE id = ?";
$row = db::fetch_one($sql,[1]);
// examine data
echo '<pre>';
print_r($row);
// form a non-prepared sql query, execute, and fetch all rows
$sql = "SELECT * FROM your_table";
$rows = db::fetch_all($sql);
// examine data
echo '<pre>';
print_r($rows);
If you need to do something different with the result from a query, either add method(s) to the class, or just use the query() method to execute the query. Since the query() method returns the PDOStatement object, you can use any of the PDOStatement methods to access the data.
The connection property is publicly available, so you can reference it when you need to get the last insert id from an INSERT/UPDATE query, reference/modify any of the connection attributes, or any of the other PDO object methods.