Database classes

[AdRock]

I am learning OOP and I have made a database connection class.

 

What seems stupid to me in a way is why i have to provide the host, username, password and database name everytime i want to prform a query.  Sometimes there may be 2 or more queries on one page so it's frustrating having to provide those details every time i want to run a query.

 

Is there a way i can have those variables in a seperate file and include it when i want to perform a query.  I have tried it but i get some errors

Notice: Could not connect to server in d:\Apache\htdocs\classes\database\MySQL.php on line 80

 

Warning: mysql_query(): supplied argument is not a valid MySQL-Link resource in d:\Apache\htdocs\classes\database\MySQL.php on line 111

 

Warning: mysql_error(): supplied argument is not a valid MySQL-Link resource in d:\Apache\htdocs\classes\database\MySQL.php on line 112

 

Notice: Query failed: SQL: SELECT * FROM articles ORDER BY title in d:\Apache\htdocs\classes\database\MySQL.php on line 113

 

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in d:\Apache\htdocs\classes\database\MySQL.php on line 155

 

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in d:\Apache\htdocs\classes\database\MySQL.php on line 171

 

here is my database class

<?php
/**
* @package SPLIB
* @version $Id: MySQL.php,v 1.1 2003/12/12 08:06:07 kevin Exp $
*/
/**
* MySQL Database Connection Class
* @access public
* @package SPLIB
*/
class MySQL {
    /**
    * MySQL server hostname
    * @access private
    * @var string
    */
    var $host;

    /**
    * MySQL username
    * @access private
    * @var string
    */
    var $dbUser;

    /**
    * MySQL user's password
    * @access private
    * @var string
    */
    var $dbPass;

    /**
    * Name of database to use
    * @access private
    * @var string
    */
    var $dbName;

    /**
    * MySQL Resource link identifier stored here
    * @access private
    * @var string
    */
    var $dbConn;

    /**
    * Stores error messages for connection errors
    * @access private
    * @var string
    */
    var $connectError;

    /**
    * MySQL constructor
    * @param string host (MySQL server hostname)
    * @param string dbUser (MySQL User Name)
    * @param string dbPass (MySQL User Password)
    * @param string dbName (Database to select)
    * @access public
    */
    function MySQL ($host,$dbUser,$dbPass,$dbName) {
        $this->host=$host;
        $this->dbUser=$dbUser;
        $this->dbPass=$dbPass;
        $this->dbName=$dbName;
        $this->connectToDb();
    }

    /**
    * Establishes connection to MySQL and selects a database
    * @return void
    * @access private
    */
    function connectToDb () {
        // Make connection to MySQL server
        if (!$this->dbConn = @mysql_connect($this->host,
                                      $this->dbUser,
                                      $this->dbPass)) {
            trigger_error('Could not connect to server');
            $this->connectError=true;
        // Select database
        } else if ( !@mysql_select_db($this->dbName,$this->dbConn) ) {
            trigger_error('Could not select database');
            $this->connectError=true;
        }
    }

    /**
    * Checks for MySQL errors
    * @return boolean
    * @access public
    */
    function isError () {
        if ( $this->connectError )
            return true;
        $error=mysql_error ($this->dbConn);
        if ( empty ($error) )
            return false;
        else
            return true;
    }

    /**
    * Returns an instance of MySQLResult to fetch rows with
    * @param $sql string the database query to run
    * @return MySQLResult
    * @access public
    */
    function & query($sql) {
        if (!$queryResource=mysql_query($sql,$this->dbConn))
            trigger_error ('Query failed: '.mysql_error($this->dbConn).
                           ' SQL: '.$sql);
        return new MySQLResult($this,$queryResource);
    }
}

/**
* MySQLResult Data Fetching Class
* @access public
* @package SPLIB
*/
class MySQLResult {
    /**
    * Instance of MySQL providing database connection
    * @access private
    * @var MySQL
    */
    var $mysql;

    /**
    * Query resource
    * @access private
    * @var resource
    */
    var $query;

    /**
    * MySQLResult constructor
    * @param object mysql   (instance of MySQL class)
    * @param resource query (MySQL query resource)
    * @access public
    */
    function MySQLResult(& $mysql,$query) {
        $this->mysql=& $mysql;
        $this->query=$query;
    }

    /**
    * Fetches a row from the result
    * @return array
    * @access public
    */
    function fetch () {
        if ( $row=mysql_fetch_array($this->query,MYSQL_ASSOC) ) {
            return $row;
        } else if ( $this->size() > 0 ) {
            mysql_data_seek($this->query,0);
            return false;
        } else {
            return false;
        }
    }

    /**
    * Returns the number of rows selected
    * @return int
    * @access public
    */
    function size () {
        return mysql_num_rows($this->query);
    }

    /**
    * Returns the ID of the last row inserted
    * @return int
    * @access public
    */
    function insertID () {
        return mysql_insert_id($this->mysql->dbConn);
    }
    
    /**
    * Checks for MySQL errors
    * @return boolean
    * @access public
    */
    function isError () {
        return $this->mysql->isError();
    }
}
?>

 

and an example of my query

<?php

include_once 'database/connection.php';
// Include the MySQL class
require_once('database/MySQL.php');


//$host='localhost';   // Hostname of MySQL server
//$dbUser='user';    // Username for MySQL
//$dbPass='password';    // Password for user
//$dbName='databasename'; // Database name

echo $host;
// Connect to MySQL
$db = & new MySQL($host,$dbUser,$dbPass,$dbName);

$sql="SELECT * FROM articles ORDER BY title";

// Perform a query getting back a MySQLResult object
$result = $db->query($sql);

// Iterate through the results
while ($row = $result->fetch()) {
    echo ( 'Title: '.$row['title'].'<br />' );
    echo ( 'Content: '.$row['content'].'<br />' );
    echo ( 'Time: '.$row['time'].'<br />' );    
}
?>

[KevinM1]

From the code provided, I don't see why you'd need to send the DB connection info more than once (creation of the DB object).  Is there something I'm just being dense about?  Because I don't see why you'd need that info for each individual query.

[AdRock]

I got the code out of a sitepoint book....php anthology volume 1.

 

In the connection.php i put the username etc and when i echo them out it displays so i don't know why i get the errors.

 

It just seems pointless everytime i want to perform a query that i have to pass the variable over and over again when i should be able to include them

[DarkWater]

Why are you using PHP 4 OOP code?

[KevinM1]

I got the code out of a sitepoint book....php anthology volume 1.

 

In the connection.php i put the username etc and when i echo them out it displays so i don't know why i get the errors.

 

It just seems pointless everytime i want to perform a query that i have to pass the variable over and over again when i should be able to include them

 

Mind showing what connection.php is, if you haven't already?  Because so far you've given two classes and one example, which didn't illustrate your problem.  At the very least, mind writing an example with two queries so I can see this duplication?

 

And DarkWater is right.  PHP 4 isn't nearly as good as PHP 5 for OOP.  It's an 8 year old version that's no longer supported by Zend.  If they're not supporting it, you shouldn't want to if you have a choice about it.

[DarkWater]

I think the simplest way to put is that PHP 4 failed miserably at OOP.  Yes?

[AdRock]

This is the connection.php

<?php
$host	= 'locahost';
$dbUser	= 'root';
$dbPass	= 'password';
$dbName = 'dbname';
?>

 

here are 2 examples in the book that do 2 different queries

<?php
// Include the MySQL class
require_once('Database/MySQL.php');

$host='localhost';   // Hostname of MySQL server
$dbUser='harryf';    // Username for MySQL
$dbPass='secret';    // Password for user
$dbName='sitepoint'; // Database name

// Connect to MySQL
$db = & new MySQL($host,$dbUser,$dbPass,$dbName);

$title='How to insert data';
$body='This is the body of the article';
$author='HarryF';

// A query to INSERT data
$sql="INSERT INTO
        articles
      SET
        title='".$title."',
        body='".$body."',
        author='".$author."'";

$db->query($sql);

if ( !$db->isError() )
    echo ( 'INSERT successful' );
else
    echo ( 'INSERT failed' );
?>

 

<?php
// Include the MySQL class
require_once('Database/MySQL.php');

$host='localhost';   // Hostname of MySQL server
$dbUser='harryf';    // Username for MySQL
$dbPass='secret';    // Password for user
$dbName='sitepoint'; // Database name

$db = & new MySQL($host,$dbUser,$dbPass,$dbName);

// A query to select an article
$sql="SELECT article_id FROM articles WHERE title='How to insert data'";

$result=$db->query($sql);

$row = $result->fetch();

// A new title
$title='How to update data';

$sql="UPDATE
        articles
      SET
        title='".$title."'
      WHERE
        article_id='".$row['article_id']."'";

$db->query($sql);

if ( !$db->isError() )
    echo ( 'UPDATE successful' );
else
    echo ( 'UPDATE failed' );
?>

 

On one page i might want to select something from the database such as a username before inserting into the database and it seems stupid to supply those same variables again.

 

Can you please recommend some good OOP php books?

[KevinM1]

Well, no matter what you do, you're going to have to send the DB connection info to the MySQL class' constructor.  There's really no getting around that as PHP has no real concept of state.

 

The reason why the book repeats the info is to illustrate its use.  In a real version, you'd probably see something like...

 

Connection.php:

$host = 'localhost';
$user = 'user';
$pass = 'pass';
$db = 'db';

 

Main code:

require_once('connection.php');

myDB =& new MySQL($host, $user, $pass, $db);

[AdRock]

That is what i tried but i get the errors

[KevinM1]

Hmm.... remove the '@' from your DB connect/select statements and show me the errors it reports, if any. 

[KevinM1]

I'm running into the same issues as you with a test script I've written.  I think it's some sort of hosting issue, as my version of the MySQL class is getting the login info from the included file (I verified this by echoing the values from inside the class itself), and I can access the db from phpMyAdmin just fine using the same info.  I'm waiting to hear back from my host on this.

 

In any event, you asked for some good OOP books, so here's a couple:

PHP 5 Objects, Patterns, and Practice by Matt Zandstra: http://www.amazon.com/PHP-Objects-Patterns-Practice-Second/dp/1590599098/ref=pd_bbs_sr_1?ie=UTF8&s=books&qid=1215888164&sr=1-1

 

Design Patterns: Elements of Reusable Object-Oriented Software by the Gang of Four: http://www.amazon.com/Design-Patterns-Object-Oriented-Addison-Wesley-Professional/dp/0201633612/ref=pd_bbs_sr_1?ie=UTF8&s=books&qid=1215888192&sr=1-1

[KevinM1]

Okay, I got mine to work:

 

Config.php:

function __autoload($className)
{
   $path = str_replace('_', DIRECTORY_SEPARATOR, $className);
   require_once("$path.php5");
}

DEFINE ('HOST', 'localhost');
DEFINE ('USER', 'user');
DEFINE ('PASSWORD', 'password');
DEFINE ('MYDB', 'hockey');

 

MySQL:

class MySQL
{
   private $host;
   private $user;
   private $pass;
   private $dbName;

   private $dbc;
   private $dbSelected;

   public function __construct($host, $user, $pass, $dbName)
   {
      $this->host = $host;
      $this->user = $user;
      $this->pass = $pass;
      $this->dbName = $dbName;

      $this->dbConnect();
   }

   private function dbConnect()
   {
      $this->dbc = @mysql_connect($this->host, $this->user, $this->pass);

      if(!$this->dbc)
      {
         echo "Cannot connect to the DB!<br />";
      }
      else
      {
         $this->dbSelected = @mysql_select_db($this->dbName, $this->dbc);

         if(!$this->dbSelected)
         {
           echo "Cannot select the correct DB!<br />";
         }
      }
   }

   public function isError()
   {
      if(!($this->dbc && $this->dbSelected))
      {
         return true;
      }
      else
      {
         return false;
      }
   }

   public function query($query)
   {
      if(!$this->isError())
      {
         $result = mysql_query($query, $this->dbc);
         return $result;
      }
      else
      {
         echo "Cannot return records from database due to connection failure.<br />";
      }
   }
}

 

Client code:

require_once('data/config.php5');

$myDB = new MySQL(HOST, USER, PASSWORD, MYDB);

$query = "SELECT * FROM goalies";
$result = $myDB->query($query);

if($result)
{
   while($row = mysql_fetch_assoc($result))
   {
      echo "{$row['first_name']} {$row['last_name']}<br />";
   }
}

 

Some things to keep in mind if you're not familiar with PHP 5:

 

Everything is implicitly pass-by-reference, which is why I don't put '&' when creating a MySQL object, among other places.

 

__autoload() is a function available in PHP 5 that automatically fires when code attempts to instantiate a new object.  It's typically used (like it is here) to automatically (and dynamically) load the needed class files when an object of that class is trying to be created.  It saves the coder from having to keep track of what class files have been included/required.

 

PHP 5 has real data access control (public, protected, private).

 

PHP 5 has other OOP benefits as well (interfaces, abstract classes/methods, special functions (__construct(), __destruct(), etc.)).

[AdRock]

Sorry for the delay Nightslyr........had to reinstall windows etc and MySQL was being a pain in the ass.

 

I got it to work...i had to include the connection after the MySQL class was included.....don't know why it didn't work the other way around.

 

I do have a similar question related to database classes.

 

At my placement for uni (doing web development), i've been using Joomla and I've had to edit some of the php.  I don't know how their database classes work but when i echo out a variable from the database i use something like $p->id

 

I've noticed that they use a for each loop and assign each field value to a variable.  Would it be possible to do somethin glike this instead of using a while loop and echoing out $row['id']?

[KevinM1]

Sorry for the delay Nightslyr........had to reinstall windows etc and MySQL was being a pain in the ass.

 

I got it to work...i had to include the connection after the MySQL class was included.....don't know why it didn't work the other way around.

 

I do have a similar question related to database classes.

 

At my placement for uni (doing web development), i've been using Joomla and I've had to edit some of the php.  I don't know how their database classes work but when i echo out a variable from the database i use something like $p->id

 

I've noticed that they use a for each loop and assign each field value to a variable.  Would it be possible to do somethin glike this instead of using a while loop and echoing out $row['id']?

 

Without seeing their code, I can't really comment one way or another.  I mean, I'm sure you could jury-rig some sort of foreach implementation, but it'd be beneficial to see their DB class(es) to see exactly how they did it.  Echoing something like $p->id looks a bit dangerous on the surface as it implies that their class grants the user direct access to its properties, which is a pretty big no-no in OOP.