MySQL Class

Small, short code snippets that other people may find useful. Do you have a good regex that you would like to share? Share it! Even better, the code can be commented on, and improved.

Moderator: General Moderators

Nay
Forum Regular
Posts: 951
Joined: Fri Jun 20, 2003 11:03 am
Location: Brisbane, Australia

MySQL Class

Post by Nay »

I came up with the following class that I could use for generally any site I plan to work on in the future. Well, since it's kind of a 'big commitment' using the code for all the sites, I wanted to ask if it is efficient or something of that sort.

Any comment is really welcome.....

Code: Select all

<?php

class FA_mysql {

   // define variables
   
   var $db_username = "foo";
   var $db_password = "bar";
   var $db_dbname = "2xfoo";
   
   // set connection variables to null
   
   var $connection = null;
   var $database = null;
   
   function connect() {
    
      $link = mysql_connect("localhost", $this->db_username, $this->db_password);
      
      if(empty($link) || !isSet($link)) {
         echo "Critical Error: Could not connect to the database: " . $this->db_dbname . "because of " . mysql_errno() . " : " . mysql_error();
         // returns the error and error #
         exit;
      }
      
      $this->connection = $link;
        
   }
   
   function select() {
    
      $db = null;
      $n_args = func_num_args(); // get number of arguments used when calling this function
      $args = func_get_args(); // get all the arguments
      
      if($n_args > 1) {
         echo "Error calling function select(), too many arguments";
         // well you can't select more than one db at a time can you? 
         exit;
      }
      
      if($n_args == 1) {
         $db = $args[0];
         // if there's an argument, set the select db
      } else {
         $db = $this->db_dbname;
         // else, the db is the default db
      }
        
      $select = mysql_select_db($db) or die(mysql_error());
      
      $this->database = $select;
      
   }
   
   function connectSelect() {
    
      $this->connect();
      $this->select();
        
   }
   
   function sql_error() {
    
      // FA custom error reporting. will return the error #, error statement and the SQL query
      
      echo "MySQL returned the following error:<br />";
      echo "<em><strong>" . mysql_errno() . "</strong>" . mysql_error() . "</em>";
      echo "<pre>" . $query . "</pre>";
      exit;      
        
   }
   
   function query($SQL) {
    
      // check for an empty query, saves the time of a bad query execution
      
      if(func_num_args() < 1) {
         echo "Unable to execute query(), SQL is empty";
         exit;  
      }
      
      $result = mysql_query($query, $this->connection) or die(sql_error());
      return $result;
        
   }
   
   function queryFetch($SQL) {

      if(func_num_args() < 1) {
         echo "Unable to execute queryFetch(), SQL is empty";
         exit;  
      }    

      $result = $this->query($SQL, $this->connection);
      $rows = mysql_fetch_array($result) or die(sql_error());
      return $rows;
      
   }
   
   function close() {
    
      mysql_close($this->connection);
        
   }
   
}

?>
Thanks,

-Nay
qads
DevNet Resident
Posts: 1199
Joined: Tue Apr 23, 2002 10:02 am
Location: Brisbane

Post by qads »

i made a class for mysql too :D, not saying mine is better then yours (or vice versa) but have a look :).

Code: Select all

<?php
class mysql
{
//
var $class_name = "cMysql";
var $class_author = "Qads";
var $class_build = "8 - 20 Dec 03";//last time it was edited
var $VERSION = "1.0";
var $regeisterd = "No";
var $rege_name = "N/a";
var $rege_org = "N/a";
//
var $record;
var $DEMO_MODE = false;
var $HOST = "localhost";
var $DB_USER = "***";
var $DB_PASS = "****";
var $DB_NAME = "dbname here";
//throw out the error;
function error($msg)
{
if($this->DEMO_MODE == true)
{
$demo = "<div class="error-title" align="center">Demo Mode</div>";
}
if(strstr($msg, "|"))
{
$msg = str_replace("[/font]","</font></b>", $msg);
$msg = str_replace("|","", $msg);
}
else
{
$msg = str_replace("[/font]","</font></b><br />", $msg);
}
$msg = str_replace("[", "<b><font color="", $msg);
$msg = str_replace("]", "">", $msg); 
$msg = '<style>
.error-title{
	font-family: "Courier New", Courier, monospace;
	font-size: medium;
	font-weight: bold;
	color: Red;
	padding: 4px;
}
.error-msg{
	background: #F4F4F4;
	border: 1px black solid;
	color: Black;
	padding: 10px;
	font-family: Verdana, Geneva, Arial, Helvetica, sans-serif;
	font-size: 9pt;
}
.version{
	color: black;
	font-family: "Courier New", Courier, monospace;
	font-size: 8pt;
	font-weight: bolder;
}
</style>
'.$demo.'
<table width="512" border="0" cellpadding="0" cellspacing="0" class="table">
  <tr> 
    <td width="512" height="21" valign="top" class="error-title">ERROR!</td>
  </tr>
  <tr> 
    <td height="85" valign="top" class="error-msg">'.$msg.'</td>
  </tr>  <tr> 
    <td valign="top" align="right" class="version">cMysql '.$this->VERSION.' &copy; Digitalcoder.co.uk</td>
  </tr>
</table>';
return $msg;
}
//content to database
function connect()
{
@mysql_connect($this->HOST, $this->DB_USER, $this->DB_PASS)or die($this->error("[red]Unable to connect to database host![/font]Please make sure that you are useing correct host name, database username and database password."));
@mysql_select_db($this->DB_NAME)or die($this->error("[red]unable to select to database![/font]Please make sure you are useing a correct database name."));
}

//mysql fields in sql format
function format_fields($fields)
{
$explode = explode(",", $fields);
for($x = 0; $x<=count($explode); $x++)
{
if(!empty($explode[$x]))
{
$explode[$x] = trim($explode[$x]);
if($x < 1)
{
$formated = "$explode[$x]";
}
else
{
$formated .= ", $explode[$x]";
}
}
}
return $formated;
}
//
function do_query($query)
{
  global $queries_done;
  $result=mysql_query($query);
  $queries_done++;
 return $result;
}
//
function query($fields, $table, $where)
{
$this->connect();
$fields = $this->format_fields($fields);
$query = $this->do_query("SELECT $fields FROM `$table` WHERE $where")or die($this->error("[red]Invild Query![/font][blue]SELECT $fields FROM `$table` WHERE $where [/font]".mysql_error()));
return $query;
}
//
/**
 * @return array
 * @param $query_id mysql_query as input
 * @desc takes mysql_query resourcse id
 */
function get_array($query_id)
{
$this->record = mysql_fetch_array($query_id);
return $this->record;
}
//
function num_rows($query_id)
{
$num_rows = mysql_num_rows($query_id);
return $num_rows;
}
//
function nextpage($table,$perpage,$start,$page, $query)
{
$start = (int)$start;
$query = "SELECT count(*) as count FROM $table $query"; 
$result = $this->do_query($query)or die($this->error("[red]Mysql Error: Invild Query![/font]SELECT count(*) as count FROM $table $query<br />".mysql_error()));
$row = $this->get_array($result);
$numrows = $row[0];
echo "<script language="JavaScript" type="text/JavaScript">
function MM_goToURL() {
  var i, args=MM_goToURL.arguments; document.MM_returnValue = false;
  for (i=0; i<(args.length-1); i+=2) eval(args[i]+".location='"+args[i+1]+"'");
}
</script>";
if($start > 0)
{
$r =  "<input name="nextbtn" type="button" id="previousbtn" onClick="MM_goToURL('parent','".$page."&start=".($start - $perpage)."');return document.MM_returnValue" value="<<< Previous">";
}
if($numrows > ($start + $perpage))
{
$r .= "&nbsp;<input name="nextbtn" type="button" id="nextbtn" onClick="MM_goToURL('parent','".$page."&start=".($start + $perpage)."');return document.MM_returnValue" value="Next >>>">";
}
return $r;
}
//
function insert($table, $fields, $values) 
{ 
$this->connect();
    $into = explode(",", $fields); 
    $value = explode(",", $values); 

    if(count($value) != count($into)) 
    { 
  die($this->error("[red]Insert Fields Count Mismatch![/font]Unable to insert record.<pre>Fields: ".print_r($into)."<pre/><pre>Values: ".print_r($value)."</pre>"));
    } 

    for($x = 0; $x<count($value); $x++) // CHANGED <= TO < 
    { 
        $value[$x] = addslashes(trim($value[$x])); 
        $into[$x] = trim($into[$x]); 

       if($x == 0) 
        { 
            $fields = "`$into[$x]`"; 
            $gen_query = "'$value[$x]'"; 
        } 
        else 
        { 
            $fields .= ", `$into[$x]`"; 
            $gen_query .= ", '$value[$x]'"; 
        } 
    } 
$query = $this->do_query("INSERT INTO `$table` ($fields) VALUES($gen_query)")or die($this->error("[red]Invild Query![/font]Unable to update record.<br />".mysql_error()));
}
//
function Delete($table, $where)
{
$this->connect();
$query = $this->do_query("DELETE FROM `$table` WHERE $where")or die($this->error("[red]Invild Query![/font]Unable to delete record.<br />[blue]DELETE FROM `$table` WHERE $where [/font]".mysql_error()));
}
//
function update($toupdate, $value, $table, $where)
{
$toupdate = explode(",", $toupdate);
$value = explode(",", $value);
if(count($value) != count($toupdate))
{
die($this->error("[red]Field count mismatch[/font]Unable to update record."));
}
$gen_query = "";
for($x = 0; $x<=count($value); $x++)
{
if(!empty($toupdate[$x]))
{
if($x == 0)
{
$gen_query .= "`$toupdate[$x]` = '$value[$x]'";
}
else
{
$gen_query .= ", `$toupdate[$x]` = '$value[$x]'";
}
}
}
$update = $this->do_query("UPDATE `$table` SET $gen_query WHERE $where")or die($this->error("[red]Invild Query![/font]Unable to update record.<br />[blue]UPDATE `$table` SET $gen_query WHERE $where [/font]".mysql_error()));
}
//
function unset_array($array)
{
if(is_array($array))
{
for($x = 0; $x<=count($array); $x++)
{
unset($array[$x]);
}
}
else
{
die($this->error("[red]Unable to unset array[/font]Passed value is not a array!"));
}
}
//
//
function about()
{
$html = '
<style type="text/css">
<!--
.header {
	font-family: Geneva, Arial, Helvetica, sans-serif;
	font-size: 10pt;
	font-weight: bold;
}
.txt
{
	font-family: Geneva, Arial, Helvetica, sans-serif;
	font-size: 10pt;
	background-color: #EEEEEE;
	padding: 4px;
	border: 1px black solid;
}
-->
</style>
<center>
<table width="60%" border="0" cellpadding="0" cellspacing="0">
<tr> 
<td width="100%" height="22" align="left" valign="bottom" class="header">'.$this->class_name.' Version Information</td>
  </tr>
  <tr> 
<td height="19" align="left" valign="top" class="txt">Version: '.$this->VERSION.'<br />
        Build: '.$this->class_build.'<br />
        Author: '.$this->class_author.'</td>
  </tr>
  <tr> 
    <td height="19" align="left" valign="middle" class="header">Registration Information</td>
  </tr>
  <tr> 
<td height="19" align="left" valign="top" class="txt">Registered: '.$this->regeisterd.'<br />
        Name: '.$this->rege_name.'<br />
        Organization: '.$this->rege_org.'</td>
  </tr>
</table>
<hr align="center" width="50%" size="1" noshade="noshade" />
</center>';
return $html;
}
//
}
$mysql = &NEW mysql();
$mysql->connect();
?>
it is still being worked on, i have the db, host information in the class beacuse each project has only one so i dont see a need to define it on every page :roll:....any suggestions?

exmaples...

Code: Select all

<?php
//query
$query = $mysql->query("ID,username,email", $login_table, "`username` = '$username' AND `password` = '$password' limit 1");
//num rows
$num_rows = mysql_num_rows($query);

//update
$update_session_time = $mysql->update("time", "$time", "session_data", "`session_id` = '$sess_id' AND `user_id` = '$user_id' AND `ip` = '$user_ip' LIMIT 1");

//insert
$mysql->insert("session_data", "session_id,user_id,ip,time", "$sid,$data[ID],$ip,$time");

//delete
$delete_old = $mysql->Delete("session_data", "`time` <= '$invild_sessions'");

//get array
$row = $mysql->get_array($query);

//give this a try!
echo $mysql->about();
?>
very easy to use and shows all the required errors in nice format.
User avatar
aquila125
Forum Commoner
Posts: 96
Joined: Tue Dec 09, 2003 10:39 am
Location: Belgium

Post by aquila125 »

Nay,

looks pretty nice :)

But perhaps instead of exitting or dieing on an error, you could output the error (as you are doing now) and return a false, if the query completes correctly you can return the result, or true for an update/insert query...
Like this, you can decide if you want the script to end or not from within the calling script..
Perhaps you can't get the data out of the database, but it's still nicer to complete the rest of the html code..
Nay
Forum Regular
Posts: 951
Joined: Fri Jun 20, 2003 11:03 am
Location: Brisbane, Australia

Post by Nay »

@qads,

why you little.......*crushes knuckles*.........OUTSIDE! :lol:....

@aquila,

Nice suggestion. I'm too used to exiting on errors O.o well, other errors I like to redirect to a http 500 8)...

:lol:, not on my clients work though...

-Nay
qads
DevNet Resident
Posts: 1199
Joined: Tue Apr 23, 2002 10:02 am
Location: Brisbane

Post by qads »

outside? lol :lol:.
User avatar
lazy_yogi
Forum Contributor
Posts: 243
Joined: Fri Jan 24, 2003 3:27 am

Post by lazy_yogi »

I wanted my class to handle iterating through the results also, and you can see the interface.

I did get it off the net somewhere and modified it since then. I'd put up where i got it but forget now =/

The main modification I made was to set up for a destructor when php fixes up it classes in v5 to disconnect before the object is deleted. php currently closes connections when script ends, but this is bad practice if you ask me.

And the reason I use die for a problem such as non connection is that again when php5 comes out with the dodgy OO fixed up, I can just throw an exception. so any workaround now will be wasted for then.

Anyway, here it is if anyone's interested

Code: Select all

<?php


# EXAMPLES:
# 
# SELECT
# include "class.mysql.php";
# $mysql = new MySql();
# $mysql->query("SELECT field FROM table");
# 
# if ($mysql->num_rows() > 0) {
#     while ($mysql->movenext())
#         echo $mysql->getfield("field");
# }
#  
# INSERT
# include "class.mysql.php";
# $mysql = new MySql();
# $mysql->query("INSERT INTO table (field) values ('value')");
# 
# UPDATE
# include "class.mysql.php";
# $mysql = new MySql();
# $mysql->query("UPDATE table SET field='newvalue' WHERE fieldID=1");
# 
# DELETE
# include "class.mysql.php";
# $mysql = new MySql();
# $mysql->query("DELETE FROM table WHERE fieldID=1");


class MySql {
 
    ### private: connection parameters 
    var $host;
    var $database;
    var $user;
    var $password;

    var $conn;
    var $result;
    var $row;

    var $debug=True;

    ### Constructor -  mysql::mysql()
    function MySql ($host='',$user='',$password='',$database='') {

        ### set the connection parameters
        $this->host     = "localhost";
        $this->database = "test";
        $this->user     = "root";
        $this->password = "";

        ### make the connection
        $this->connect();
        if ($this->debug)
            print '<Br>&nbsp;&nbsp;=> created mysql connection: '.$this->conn.'<br>';

        ### remove this and put '$this->close' in destructor when php5 comes out
        register_shutdown_function(array($this, 'close'));
    }


    ### Open connection with the server -  mysql::connect()
    ### N.B. pconnect uses a new connection resource for each connection!
    function connect () { 
            
        ### Open connection
        $this->conn = mysql_connect($this->host,$this->user,$this->password)
            OR die("Connection to $server failed <br>\n");
         
        ### Select to database
        mysql_select_db($this->database,$this->conn)
            OR die("Error:" . mysql_errno() . " : " . mysql_error() . "<br>\n");
    }


    ### Execute SQL -  mysql::query()
    function query($sql) {
        $this->result = mysql_query($sql,$this->conn)
            OR die("-Error:" . mysql_errno() . " : " . mysql_error() . "<br>\n");
    }


    ### return number of rows in current select -  mysql::num_rows()
    function num_rows() {
        return mysql_num_rows($this->result);        
    }


    ### fetch next row in result -  mysql::movenext()
    function movenext(){
        $this->row = mysql_fetch_array($this->result);
        $status = is_array($this->row);        
        return($status);
    }


    ### get field value from the current row -  mysql::getfield()
    function getfield($field){
        return($this->row[$field]);
    }    
 

    ### close the connection -  mysql::close()
    function close() {
        mysql_close($this->conn);
        if ($this->debug)
            print '<br>&nbsp;&nbsp;=> freed mysql connection: '.$this->conn.'<br>';
    }
     
}

?>
Nay
Forum Regular
Posts: 951
Joined: Fri Jun 20, 2003 11:03 am
Location: Brisbane, Australia

Post by Nay »

No bears allowed, only pandas!

-Nay
User avatar
infolock
DevNet Resident
Posts: 1708
Joined: Wed Sep 25, 2002 7:47 pm

Post by infolock »

very nice!

moving this to the code sniplet forum ;)
ghost007
Forum Commoner
Posts: 49
Joined: Sat Nov 22, 2003 10:10 am

Post by ghost007 »

hi,

thx for nice code in these examples. As I'm just starting to get some grip on OOP I was wondering if it would be good coding practice to group all my vars in a config class and than include this file where the vars are needed.

eg. for class mysql I would use:
cfgx = new config();
user = cfgx->user;
db = cfgx->db;
...

thx for any opinion on this.
siech
User avatar
infolock
DevNet Resident
Posts: 1708
Joined: Wed Sep 25, 2002 7:47 pm

Post by infolock »

i guess it really depends. if it's easier for you that way, sure. either way, it works just as effectively.
Nay
Forum Regular
Posts: 951
Joined: Fri Jun 20, 2003 11:03 am
Location: Brisbane, Australia

Post by Nay »

I just like OOP since it's fancier........;)

:lol:

-Nay
User avatar
lazy_yogi
Forum Contributor
Posts: 243
Joined: Fri Jan 24, 2003 3:27 am

Post by lazy_yogi »

ghost007 wrote:hi,

thx for nice code in these examples. As I'm just starting to get some grip on OOP I was wondering if it would be good coding practice to group all my vars in a config class and than include this file where the vars are needed.

eg. for class mysql I would use:
cfgx = new config();
user = cfgx->user;
db = cfgx->db;
...

thx for any opinion on this.
siech
Yea ... if it makes it simpler. Its called the Facade design pattern, where one class has attributes as instances of a number of other classes.
http://www.dofactory.com/Patterns/PatternFacade.aspx
If it suits your needs, go for it.

Eli
User avatar
lazy_yogi
Forum Contributor
Posts: 243
Joined: Fri Jan 24, 2003 3:27 am

Post by lazy_yogi »

I've changed the structure of the class I posted before because I was treating the mysql connection object and the result object as the same item. Anyway, here's the change. Lemme know if you think there's something not quite optimal about it

Eli

Code: Select all

<?php

# EXAMPLES:
# 
# SELECT
# include "class.mysql.php";
# $mysql = new MySql();
# $iter = $mysql->query("SELECT field FROM table");
# if ($iter->num_rows() > 0)
#     while ($iter->next())
#         echo $iter->row['field'];
#
# INSERT
# include "class.mysql.php";
# $mysql = new MySql();
# $mysql->query("INSERT INTO table (field) values ('value')");
# 
# UPDATE
# include "class.mysql.php";
# $mysql = new MySql();
# $mysql->query("UPDATE table SET field='newvalue' WHERE fieldID=1");
# 
# DELETE
# include "class.mysql.php";
# $mysql = new MySql();
# $mysql->query("DELETE FROM table WHERE fieldID=1");



### object to handle the query result iteration
class MySqlIterator {

    ### private: query result parameters
    var $result; # the query result handle
    var $row;    # the current row we are iterating over


    ### Constructor -  MySqlIterator::MySqlIterator()
    function MySqlIterator($result) {
        $this->result = $result;
    }


    ### fetch next row in result -  MySqlIterator::next()
    function next() {
        $this->row = mysql_fetch_array($this->result);
        $status = is_array($this->row);
        return $status;
    }


    ### return number of rows in current select -  MySqlIterator::num_rows()
    function num_rows() {
        return mysql_num_rows($this->result);        
    }


    ### return all (remaining) rows as dictionary items
    function fetchall() {
        $arr = array();
        while ($this->next()) 
            $arr[] = $this->row;
        return $arr;
    }
}



### object to handle connections and queries to the database
class MySql {
 
    ### private: connection parameters 
    var $host;
    var $database;
    var $user;
    var $password;

    ### the connection
    var $conn;

    ### debugging
    var $debug=False;


    ### Constructor -  mysql::mysql()
    function MySql () {

        # set the connection parameters
        $this->host     = "localhost";
        $this->database = "test";
        $this->user     = "root";
        $this->password = "";

        # make the connection
        $this->connect();
        if ($this->debug)
            print '<BR>&nbsp;&nbsp;=> created mysql connection: '.$this->conn.'<BR>';

        # remove this and put '$this->close' in destructor when php5 comes out
        register_shutdown_function(array($this, 'close'));
    }


    ### Open connection with the server -  mysql::connect()
    ### N.B. pconnect uses a new connection resource for each connection!
    function connect () { 
            
        # Open connection
        $this->conn = mysql_connect($this->host,$this->user,$this->password)
            OR die("Connection to $server failed <br>\n");
         
        # Select to database
        mysql_select_db($this->database,$this->conn)
            OR die("Error:" . mysql_errno() . " : " . mysql_error() . "<BR>\n");
    }


    ### Execute SQL -  mysql::query()
    function query($sql) {
        $result = mysql_query($sql,$this->conn)
            OR die("-Error:" . mysql_errno() . " : " . mysql_error() . "<BR>\n");

        return new MySqlIterator($result);
    }


    ### close the connection -  mysql::close()
    function close() {
        mysql_close($this->conn);
        if ($this->debug)
            print '<BR>&nbsp;&nbsp;=> freed mysql connection: '.$this->conn.'<BR>';
    }
}
?>
McGruff
DevNet Master
Posts: 2893
Joined: Thu Jan 30, 2003 8:26 pm
Location: Glasgow, Scotland

Post by McGruff »

ghost007 wrote:hi,

thx for nice code in these examples. As I'm just starting to get some grip on OOP I was wondering if it would be good coding practice to group all my vars in a config class and than include this file where the vars are needed.

eg. for class mysql I would use:
cfgx = new config();
user = cfgx->user;
db = cfgx->db;
...

thx for any opinion on this.
siech
Not sure if there is any point in encapsulating config vars in a class. Just define them as constants?

A config.php FILE would be useful though: provides an easy-to-find single location to edit these and other settings.
McGruff
DevNet Master
Posts: 2893
Joined: Thu Jan 30, 2003 8:26 pm
Location: Glasgow, Scotland

Post by McGruff »

PS: there's far too much going on in some of those classes. Responsibility driven design suggests that, as a rule, each object does just one thing well.

Sorry I don't have time right now for a detailed critique but take a look at how eclipse http://www.students.cs.uu.nl/people/voo ... /index.php does it.
Post Reply