☰ ☰

PHP - Database class

1. Configuration

The database configuration settings are defined in the 'dbconfig.php' file.
You can specify a 'user' and a 'password' to read or write.
In the database class a 'debugflag' constant is used to show specific error messages or not.
The 'dbconfig.php' file is required in the database class to set up a PDO connection.
In the example, the configuration file is placed in the same folder as the class file.
You can place the file outside the 'root' folder for security reasons. You can use the prefix '$_SERVER[DOCUMENT_ROOT]/../'

<?php
// dbconfig.php
define('DB_WRITER', "root");
define('DB_WRITER_PASSWORD', "root");
define('DB_READER', "root");
define('DB_READER_PASSWORD', "root");
define('DB_DATABASE', "snippets");
define('DB_SERVER', "localhost");
define('DEBUGFLAG', "true");
?>

2. Database class

The database class consists of a property '$conn' that contains the connection object.
In the constructor (__construct) the PDO connection object is created and placed in the connection property ($conn).
The parameters of the constructor method (or function) are by default filled with the values from the configuration file.
The select_query method executes a simple SQL statement.
In the query_prepared method, the SQL statement is first prepared (prepare) and during execution the values are filled in the provided placeholders.
In the examples you can see how to use the database class in your scripts.

<?php
// database.php
require "dbconfig.php";

class database {
	private $conn;
	
	function __construct($host = DB_SERVER, $dbname = DB_DATABASE, 
						 $user = DB_READER, $password = DB_READER_PASSWORD) {
		try {
			$conn = new PDO("mysql:host=$host;dbname=$dbname", $user, $password);
			$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
			$this->conn = $conn;
		}
		catch(PDOException $e)
		{
			if (DEBUGFLAG === "true") {
				echo "<div><b>Error: " . $e->getmessage() . "</b></div><br>\n";
			} else {
				echo "<div><b>Error: database error (1).</b></div><br>\n";
			}
			return -1;
		}	
	}
	public function select_query($sql) {
		try {
			return $this->conn->query($sql);			
		}
		catch(PDOException $e)
		{
			if (DEBUGFLAG === "true") {
				echo "<div><b>Error: " . $e->getmessage() . "</b></div><br>\n";
			} else {
				echo "<div><b>Error: database error (2).</b></div><br>\n";
			}
			return -1;			
		}
	}
	public function query_prepared($sql, $values) {
		try {
			$stmt = $this->conn->prepare($sql);
			$stmt->execute($values);
			//var_dump($stmt);
			return $stmt;
		}
		catch(PDOException $e)
		{
			if (DEBUGFLAG === "true") {
				echo "<div><b>Error: " . $e->getmessage() . "</b></div><br>\n";
			} else {
			    echo "<div><b>Error: database error (3).</b></div><br>\n";
			}
			return -1;			
		}
	}
}
?>

3. Create a list

This first example shows a simple (unformatted) list of the records from a table.
With a 'foreach' loop, each record from the result ($result) is retrieved and displayed.

// make a list using the database class
require("database.php");
if (!isset($db)) $db = new database();
$sql = "SELECT * FROM category LIMIT 10";	// the first 10 records
$result = $db->select_query($sql);
foreach($result->fetchAll() as $rec) {
	// form.php will use the record id for changing or removing the record
	echo "<a href='form.php?id=".$rec["id"]."&action=change'>change</a> ";
	echo "<a href='form.php?id=".$rec["id"]."&action=remove'>remove</a> ";
	echo $rec["id"] . " " . $rec["name"] . "<br>";
}

4. Find a record

In this second example, a specific record is retrieved from the database table.
The 'id' field was 'hardcoded' but usually comes from a url variable.

// search for a record using the database class and a record id
require("database.php");
if (!isset($db)) $db = new database();
$id = 4; 	// usually set as a url variable in a list (see above)
$sql = "SELECT * FROM category WHERE id = :id";
$values = array(':id'=>$id);
$result = $db->query_prepared($sql,$values);
$rec = $result->fetch();	// maximum one record possible
if (isset($rec)) echo "Record found: " . $rec["id"] . " " . $rec["name"];

5. Add a record

Third example: a record is added to an existing database table.
The result is then checked.

// insert a record using the database class
require("database.php");
if (!isset($db)) $db = new database();
$id = null;	// must be null when inserting (primary key - auto increment)
$name = "test category";
$active = 0;
$sql = "INSERT INTO category (id, name, active)";
$sql .= " VALUES (:id, :name, :active)";
$values = array (':id' => $id, ':name' => $name, ':active' => $active);
$result = $db->query_prepared($sql, $values);
if ($result->rowCount() > 0) {
	echo "Record with name = " . $name . " inserted.<br><br>";
} else {
	if ($result->rowCount() == 0) {
		echo "No data was changed.<br>";
	} else {
		echo "Error.<br>";
	}
}
echo $sql . "<br>";
var_dump($values);
echo "Number of records affected: " . $result->rowCount();

Pages

Paragraphs