Top

Follow me and receive all the latest free scripts:

By Email:

Categories
Most Popular Posts

MySQL, PHP, OOP database connection

MySQL, PHP, OOP database connection

Published September 23, 2014 by , category Database

mysqldatabaseoopphp

How to connect database using Object-Oriented, PHP and MySQL

Introduction

In this tutorial I explain how to set up a database connection, using Object-Oriented Programming (OOP), PHP and MySQL. This can be adapted with PDO or MySQLi of course.

I use this database connection system for this website.

It's an easy 3 steps tutorial, with code example to get and insert data in the database.

  1. Create a db.class.php file at the root and write a class with functions (connect, getOne, getAll etc.),
  2. Call this file and create a new object on every pages of your website (in the header for example),
  3. Use the functions getOne, getAll or execute to select, insert or update data in the database.

The db.class.php file

Update: first I had published the db.class.php file implementing MySQL which is deprecated. So I still leave the first db.class.php/mysql version and I give you the db.class.php/PDO version (the one I recommand).

PDO version

<?php
class db {
	private $conn;
	private $host;
	private $user;
	private $password;
	private $baseName;
	private $port;
	private $Debug;

    function __construct($params=array()) {
		$this->conn = false;
		$this->host = 'localhost'; //hostname
		$this->user = 'user'; //username
		$this->password = 'pass'; //password
		$this->baseName = 'dbname'; //name of your database
		$this->port = '3306';
		$this->debug = true;
		$this->connect();
	}

	function __destruct() {
		$this->disconnect();
	}
	
	function connect() {
		if (!$this->conn) {
			try {
				$this->conn = new PDO('mysql:host='.$this->host.';dbname='.$this->baseName.'', $this->user, $this->password, array(PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8'));  
			}
			catch (Exception $e) {
				die('Erreur : ' . $e->getMessage());
			}

			if (!$this->conn) {
				$this->status_fatal = true;
				echo 'Connection BDD failed';
				die();
			} 
			else {
				$this->status_fatal = false;
			}
		}

		return $this->conn;
	}

	function disconnect() {
		if ($this->conn) {
			$this->conn = null;
		}
	}
	
	function getOne($query) {
		$result = $this->conn->prepare($query);
		$ret = $result->execute();
		if (!$ret) {
		   echo 'PDO::errorInfo():';
		   echo '<br />';
		   echo 'error SQL: '.$query;
		   die();
		}
		$result->setFetchMode(PDO::FETCH_ASSOC);
		$reponse = $result->fetch();
		
		return $reponse;
	}
	
	function getAll($query) {
		$result = $this->conn->prepare($query);
		$ret = $result->execute();
		if (!$ret) {
		   echo 'PDO::errorInfo():';
		   echo '<br />';
		   echo 'error SQL: '.$query;
		   die();
		}
		$result->setFetchMode(PDO::FETCH_ASSOC);
		$reponse = $result->fetchAll();
		
		return $reponse;
	}
	
	function execute($query) {
		if (!$response = $this->conn->exec($query)) {
			echo 'PDO::errorInfo():';
		   echo '<br />';
		   echo 'error SQL: '.$query;
		   die();
		}
		return $response;
	}
}

MySQL version

<?php
class db {
	private $conn;
	private $host;
	private $user;
	private $password;
	private $baseName;
	private $port;
	private $Debug;
	
	function __construct($params=array()) {
		$this->conn = false;
		$this->host = 'localhost'; //hostname
		$this->user = 'user'; //username
		$this->password = 'pass'; //password
		$this->baseName = 'dbname'; //name of your database
		$this->port = '3306';
		$this->debug = true;
		$this->connect();
	}

	function __destruct() {
		$this->disconnect();
	}
	
	function connect() {
		if (!$this->conn) {
			$this->conn = mysql_connect($this->host, $this->user, $this->password);	
			mysql_select_db($this->baseName, $this->conn); 
			mysql_set_charset('utf8',$this->conn);
			
			if (!$this->conn) {
				$this->status_fatal = true;
				echo 'Connection BDD failed';
				die();
			} 
			else {
				$this->status_fatal = false;
			}
		}

		return $this->conn;
	}

	function disconnect() {
		if ($this->conn) {
			@pg_close($this->conn);
		}
	}
	
	function getOne($query) { // getOne function: when you need to select only 1 line in the database
		$cnx = $this->conn;
		if (!$cnx || $this->status_fatal) {
			echo 'GetOne -> Connection BDD failed';
			die();
		}

		$cur = @mysql_query($query, $cnx);

		if ($cur == FALSE) {		
			$errorMessage = @pg_last_error($cnx);
			$this->handleError($query, $errorMessage);
		} 
		else {
			$this->Error=FALSE;
			$this->BadQuery="";
			$tmp = mysql_fetch_array($cur, MYSQL_ASSOC);
			
			$return = $tmp;
		}

		@mysql_free_result($cur);
		return $return;
	}
	
	function getAll($query) { // getAll function: when you need to select more than 1 line in the database
		$cnx = $this->conn;
		if (!$cnx || $this->status_fatal) {
			echo 'GetAll -> Connection BDD failed';
			die();
		}
		
		mysql_query("SET NAMES 'utf8'");
		$cur = mysql_query($query);
		$return = array();
		
		while($data = mysql_fetch_assoc($cur)) { 
			array_push($return, $data);
		} 

		return $return;
	}
	
	function execute($query,$use_slave=false) { // execute function: to use INSERT or UPDATE
		$cnx = $this->conn;
		if (!$cnx||$this->status_fatal) {
			return null;
		}

		$cur = @mysql_query($query, $cnx);

		if ($cur == FALSE) {
			$ErrorMessage = @mysql_last_error($cnx);
			$this->handleError($query, $ErrorMessage);
		}
		else {
			$this->Error=FALSE;
			$this->BadQuery="";
			$this->NumRows = mysql_affected_rows();
			return;
		}
		@mysql_free_result($cur);
	}
	
	function handleError($query, $str_erreur) {
		$this->Error = TRUE;
		$this->BadQuery = $query;
		if ($this->Debug) {
			echo "Query : ".$query."<br>";
			echo "Error : ".$str_erreur."<br>";
		}
	}
}

Call the file

Add those 2 lines on every pages of your website

<?php
include('db.class.php'); // call db.class.php
$bdd = new db(); // create a new object, class db()

The SELECT Statement

The SELECT statement is used to select data from a database.

With this Object-Oriented connection, we have 2 ways to do it: 1 line selection or multi-line selection

The 1 line selection

Let say you have a table users listing website registered users, and you want to select ONE specific user, the one named "Smith".

<?php
$User = $bdd->getOne('SELECT id, firstname, lastname FROM users WHERE lastname = "Smith"'); // 1 line selection, return 1 line
echo $User['id'].'<br>'; // display the id
echo $User['firstname'].'<br>'; // display the first name
echo $User['lastname']; // display the last name

The multi-line selection

Now, still with your table users, you want to select the full users list.

<?php
$Users = $bdd->getAll('SELECT id, firstname, lastname FROM users'); // select ALL from users
		
$nbrUsers = count($Users); // return the number of lines

echo $nbrUsers.' users in the database<br />';
	
foreach($Users as $user) { // display the list
echo $user['id'].' - '.$user['firstname'].' - '.$user['lastname'];	
}

The INSERT INTO Statement

The INSERT INTO statement is used to insert new records in a table.

Now you want to insert a new member in your table users. This new member's name is: first name: firstname1, last name: lastname1.

You have to use the execute function

<?php
$query = $bdd->execute('INSERT INTO users (firstname, lastname) VALUES ("firstname1", "lastname1")');

The UPDATE Statement

The UPDATE statement is used to update existing records in a table.

You want to update the last member inserted and change his first name to firstname2 and his last name to lastname2.

You have to use the execute function

<?php
$query = $bdd->execute('UPDATE users SET firstname="firstname2", lastname="lastname2" WHERE id=20');

Conclusion

Now you are able to manage your database in a simple way.

About Simon Laroche
Simon Laroche on Google+
Simon Laroche on Twitter
Simon Laroche on Facebook
Simon Laroche on Pinterest
Simon Laroche on LinkedIn
: I am a Coder, Designer, Webmaster and Expert SEO Consulting, I'm also a wise traveller and an avid amateur photographer. I created the website TipoCode and many others such as Landolia: a World of Photos...

If you need help about this script, please leave a comment below. I reply as much as I can depending of my time, you may also get help from others.
I also offer a paid support, if you are in the need to adapt or create a script...

Leave a comment

Comments (12 comments)

Ron Posted on November 07, 2016
Great. Thanks. I can learn oops while using code.
rratin Posted on September 03, 2016
it helped me a lot, thank you.
Simon Laroche
Simon Laroche Posted on January 30, 2016
@Filsjust: yes you can sanitize the statements if you need to. Example:

$stmt = $dbh->prepare("SELECT * FROM animals WHERE animal_id = :animal_id AND animal_name = :animal_name");

/*** bind the paramaters ***/
$stmt->bindParam(':animal_id', $animal_id, PDO::PARAM_INT);
$stmt->bindParam(':animal_name', $animal_name, PDO::PARAM_STR, 5);

/*** execute the prepared statement ***/
$stmt->execute();
Filsjust Posted on January 29, 2016
Hi!
This tuttorial was very helpfull to me.
I would like to know if i should sanitize the statements for the query, or if that's not necessary in pdo.
Simon Laroche
Simon Laroche Posted on November 19, 2015
@Suraj shrestha
- and got an error "Cannot redeclare class db". -

Your class called db is declared twice... You have to check in your code page where.
Suraj shrestha Posted on November 17, 2015
And one thing I want to include that I called both the pages in a single page index.php using require as

require 'class.show.php';
require 'class.add.php';
Suraj shrestha Posted on November 17, 2015
Thanks for your time and this useful tutorial.
I am trying to use this scripts and little bit in confusion. I tried to put both the select and insert codes on two different pages and included the database connection object on both pages like

require 'class.database.php';
$bdd = new db();

and got an error "Cannot redeclare class db".

Simon Laroche
Simon Laroche Posted on October 28, 2015
@Marlon : yes you're correct. Right now it will return "status_fatal". You can add a
private $status_fatal

to setup a proper message. I didn't check because normally my queries are correct :)
Marlon Posted on October 28, 2015
Hi Simon,

THX so much! Very useful for me.

I see that there is a $this->status_fatal in the code
But I can not find the status_fatal defined (like:
private $status_fatal
).
Or isn't it necessary?

With kind regards,
Marlon
Simon Laroche
Simon Laroche Posted on September 05, 2015
@timinwaedi: I have added the PDO version of the db.class.php file, just change the file and everything will work the same way :)
Simon Laroche
Simon Laroche Posted on June 19, 2015
I recommend you to use PDO
timinwaedi Posted on June 18, 2015
Hi Simon
Thanks for publishing the db.class.php tutorial which I found very helpful. I noticed you are still implementing mysql which is deprecated. Could you rewrite this tutorial using mysqli?
Thanks