jump to navigation

Connecting to Mysql with PHP for newbies May 22, 2011

Posted by Tournas Dimitrios in PHP.
trackback

This article will  outline 3 methods to connect a  PHP driven website with  a Mysql database . It would be great if the developer had the choice to select the method that he / she  preferred , but unfortunately , not all hosting providers supports all 3 methods . The developer must be familiar with all methods   and choose the one that is proper to the hosting environment .  A golden rule to discover your hosting environment is to run a script with the “phpinfo()” directive .

A developer has the following options :

  • Tthe Mysql extension : If the hosting server runs PHP 4  . Nowadays this method should not be used as it is replaced from the more secure  improved version (Mysqli) , but if you need to , you are free to do so .
  • The Mysqli  (improved) : If the hosting server runs PHP 5.xx  and Mysql 4.1 . This extension has two “ways”  of connecting  to a database : a) a procedural type , which helps new developers to make the transition from the earlier type (Mysql)  and b) an object oriented type of connection which has a OOP – style . This article presents the second type of connection (OOP )
  • The PDO  : If it’s available  on hosting server with PHP 5.xx and Mysql 4.1 . It supposed to be Database neutral , so if you plan in future to transfer your website to another Database vendor , this is the extension of choice . Caution : PDO displays your Database username , password onscreen when it can’t connect to the Database , so the code must be enclosed in an “try – catch ” statement to customize the returned error message

Regardless of which method is used to connect to Mysql  , the process always follows the following sequence :

  1. Connect to Mysql using the  hostname , username and password (this will just return a “dbadapter”
  2. Select the Database you want to work with  ( a Mysql server can implement endless Databases)
  3. Prepare a SQL query
  4. Use the “dbadapter” from step 1 to execute the SQL query
  5. Usually a loop is used to extract the data from the result returned from step 4

Connecting to a database is a routine task that can be performed on many pages , so a good practice would be to have the code on separate files and include them as needed .  Let’s go directly to practical examples Connecting with the Mysql extension :

<?php
// dbconn_mysql.ini.php
function dbConnect($type) {
  if ($type  == 'visitor') {
    $user = 'dbvisitor';
	$pwd = 'pass123';
	}
  elseif ($type == 'admin') {
    $user = 'root';
	$pwd = 'toor123';
	}
  else {
    exit('Unrecognized connection type');
	}
  $conn = mysql_connect('localhost', $user, $pwd) or die ('Cannot connect to server');
  mysql_select_db('testdb') or die ('Cannot open database');
  return $conn;
  }
?>

Connecting with the Mysqli  extension :

<?php
// dbconn_mysqli.ini.php
function dbConnect($type) {
  if ($type  == 'visitor') {
    $user = 'dbvisitor';
	$pwd = 'pass123';
	}
  elseif ($type == 'admin') {
    $user = 'root';
	$pwd = 'toor123';
	}
  else {
    exit('Unrecognized connection type');
	}
  $conn = new mysqli('localhost', $user, $pwd, 'testdb') or die ('Cannot open database');
  return $conn;
  }
?>

Connecting with PDO :

<?php
//dbconn_pdo.ini.php
function dbConnect($type) {
  if ($type  == 'visitor') {
    $user = 'dbvisitor';
	$pwd = 'pass123';
	}
  elseif ($type == 'admin') {
    $user = 'root';
	$pwd = 'toor123';
	}
  else {
    exit('Unrecognized connection type');
	}
  try {
    $conn = new PDO('mysql:host=localhost;dbname=testdb', $user, $pwd);
    return $conn;
	}
  catch (PDOException $e) {
    echo 'Cannot connect to database';
	exit;
	}
  }
?>

The previous scripts contain the proper code to connect on the 3 types of  Databases . Keep in mind , because this are include  files  , make sure there are no new lines or white-spaces before or after the PHP tags . The only think left is to include the proper file in the page that need a Database connection .  Each script will return a Db adapter that can be used to query the database . — >  $conn = dbConnect(‘root”) ;

.

Advertisements

Comments»

No comments yet — be the first.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s