Web Hosting Talk : Web Hosting Main Forums : Programming Discussion : Programming Tutorials : MySQL Class

[Dismounted]

In just about every site I code, I use PHP & MySQL.

Here is the MySQL PHP Class I always use when I code those sites. I have coded it myself. I call mine mysql.php and use require_once() when it is required.

PHP Code:

<?php
/*================================*\
|| MySQL PHP Class By Hanson Wong ||
||       Copyright (c) 2006       ||
\*================================*/

/*=========================*\
||      Usage Example      ||
\*=========================*/

# require_once('./mysql.php');
# $db = new MySQL;
#
# $db->connect(
#        localhost,
#        test_user,
#        test_pass,
#        test_db
# );
#
# $result = $db->query("SELECT * FROM test_table");
# echo $db->num_rows($result);

class MySQL
{
    // Define Variables
    var $errno;
    var $error;
    var $error_msg;
    var $link;

    /*==========================*\
    || Error Handling Functions ||
    \*==========================*/

    // Get Errors
    function getError()
    {
        if(empty($this->error))
        {
            $this->errno = mysql_errno();
            $this->error = mysql_error();
        }
        return $this->errno . ": " . $this->error;
    }
 
    // Print Error Message
    function printError($msg)
    {
        printf("<b>Error:</b> %s", $msg);
        exit;
    }

    /*==========================*\
    ||      Main Functions      ||
    \*==========================*/

    // PHP Equivalent: mysql_connect
    function connect($host, $user, $pass, $db)
    {
        $this->link = mysql_connect($host, $user, $pass);
         if(!$this->link)
        {
            $this->errno = 0;
            $this->error = "Connection failed to " . $host . ".";
            $this->error_msg = $this->errno . ": " . $this->error;
            return $this->printError($this->error_msg);
        }
        elseif(!mysql_select_db($db, $this->link))
        {
            $this->errno = mysql_errno();
            $this->error = mysql_error();
            $this->error_msg = $this->printError($this->getError());
            return $this->error_msg;
        }
        else 
        {
            return $this->link;
        }
    }
    
    // PHP Equivalent: mysql_close
    function close()
    {
        mysql_close($this->link);
    }
    
    // PHP Equivalent: mysql_query
    function query($query)
    {
        $query = mysql_query($query, $this->link);
        if(!$query)
        {
            $this->error_msg = $this->printError($this->getError());
            return $this->error_msg;
        }
        else
        {
            return $query;
        }
    }

    // PHP Equivalent: mysql_affected_rows
    function affected_rows()
    {
        $query = mysql_affected_rows($this->link);
        return $query;
    }

    // PHP Equivalent: mysql_escape_string
    function escape_string($string)
    {
        $query = mysql_escape_string($string);
        return $query;
    }

    // PHP Equivalent: mysql_fetch_array
    function fetch_array($query, $type)
    {
        $query = mysql_fetch_array($query, $type);
        return $query;
    }

    // PHP Equivalent: mysql_fetch_field
    function fetch_field($query, $offset)
    {
        $query = mysql_fetch_field($query, $offset);
        if(!$query)
        {
            $this->errno = 0;
            $this->error = "No information available!";
            $this->error_msg = $this->errno . ": " . $this->error;
            return $this->printError($this->error_msg);
        }
        else
        {
            return $query;
        }
    }

    // PHP Equivalent: mysql_fetch_row
    function fetch_row($query)
    {
        $query = mysql_fetch_row($query);
        return $query;
    }

    // PHP Equivalent: mysql_field_name
    function field_name($query, $offset)
    {
        $query = mysql_field_name($query, $offset);
        return $query;
    }

    // PHP Equivalent: mysql_free_result
    function free_result($query)
    {
        mysql_free_result($query);
    }

    // PHP Equivalent: mysql_insert_id
    function insert_id()
    {
        $query = mysql_insert_id($this->link);
        return $query;
    }

    // PHP Equivalent: mysql_num_fields
    function num_fields($query)
    {
        $query = mysql_num_fields($query);
        return $query;
    }

    // PHP Equivalent: mysql_num_rows
    function num_rows($query)
    {
        $query = mysql_num_rows($query);
        return $query;
    }

    // PHP Equivalent: mysql_real_escape_string
    function real_escape_string($string)
    {
        $query = mysql_real_escape_string($string, $this->link);
        return $query;
    }
}
?>

I give permission for you to change/use it on your own sites, the only thing I ask from you is that you keep the copyright intact. The only thing you are NOT allowed to do is use it comercially or sell it for your own profit.

[auveeb]

Cool This will be nifty for some upcoming websites of mine

[horizon]

PHP Code:

# $db->connect(
#        localhost,
#        test_user,
#        test_pass,
#        test_db
# ); 


The SQL server configuration could rather be added on one configuration file (like: config.php file). Then, the:

PHP Code:

 $db->connect(localhost, test_user, test_pass, test_db); 


could rather be used under the connect method name under the class routine you posted above - by using: this->connect($host, $user, $pass, $db);.

This way, users won't have to call a connection each times they would create an SQL statement.

[nik0las]

Nice class. The only thing I would suggest is that you take a look at the singleton design pattern. This is a perfect canidate.

[Marble]

nik0las has a solid point. Using this object can create a problem on a very active site.

http://us3.php.net/manual/en/language.oop5.patterns.php
http://www.imarc.net/blog/118/implem...php_singleton/
http://www.patternsforphp.com/wiki/Singleton

[Senrax]

Thanks this helped me out

[thesecret]

thanks alot

[dungho]

Thanks in advanced

[TrySparta]

Very useful, thank you.

[abhishekkaushik]

good script man...
i suggest than you keep the dbbase user/pass etc into config file and include this file in constructor of this function where you can directly connect to database

[HighRevHosting]

Very nice script. I got one pretty much the exact same. Anyone try using this while connected to multiple databases?

[kerber]

Originally Posted by Marble

Using this object can create a problem on a very active site.

Can you please elaborate why? (cause, its not small thing to play with..)

[petteyg359]

Originally Posted by kerber

Can you please elaborate why? (cause, its not small thing to play with..)

Stated in a previous post.

Originally Posted by nik0las

Nice class. The only thing I would suggest is that you take a look at the singleton design pattern. This is a perfect canidate.

[tim2718281]

Originally Posted by kerber

Can you please elaborate why? (cause, its not small thing to play with..)

I think they are assuming that a programmer using the class will issue multiple connects and closes within a web page.

And they are also assuming there is a way to modify the class to only issue a single connect and close to the SQL server, even if the programmer were to invoke connect and close multiple times with a page.

I can't work out how to do that, myself. Maybe there is an explanation somewhere.

I can see you could have a counter, incremented and decremented whenever connect and close is invoked. If a connect is issued when there is already a connection, the existing connection is used and the counter incremented. And the connection is only closed when the counter goes from 1 to 0.

But won't most web pages with multiple connects issue them in the series connect/disconnect/connect/disconect etc?

Maybe I'm simply failing to see the idea.

[tim2718281]

Originally Posted by tim2718281

I think they are assuming that a programmer using the class will issue multiple connects and closes within a web page.

And they are also assuming there is a way to modify the class to only issue a single connect and close to the SQL server, even if the programmer were to invoke connect and close multiple times with a page.

Ah; maybe I've got the wrong idea.

Maybe the idea is not to prevent multiple connects and disconnects within a web page, but to ensure the web page has at most one connection at once.