Anyone Know Of A Really Good Mysql Class? Looking for something easy but full featured.

[vujsa 0]

Generally speaking, when I write a script, it either utilizes the MySQL class of the parent system (like Mambo or Joomla) or I use basic functions and snippets to perform the database queries I need. I really like the Joomla database class as it allows you to simply pass a regular query string to it and the data is returned without the need for extra work!

 

The Invision Power Board (IPB) database class which is what is used for this forum is kind of a pain to use since it wants the query string in a non-MySQL standard format. Nonetheless, it does work and I could use it if I needed but it isn't open source and I don't like to barrow in this manner since I don't like for people to barrow some of my stuff in that way.

 

The SMF (Simple Machines Forum) database function is a joke for the older version I checked. Maybe I should check a newer version but I get the feeling that I won't be much happier.

 

I have found a few open source classes written but they tend to require more than the database query to be sent. For example:

$sql = "SELECT * FROM `users`"$result = $db -> Select($sql);

-OR-

$sql = "INSERT INTO players (`f_name`, `l_name`,`position`) VALUES ('Peyton','Manning','Quarterback')"$result = $db -> Insert($sql);

See how each MySQL query type has it's own function in the class.

Another script I found uses a slightly different method to specify the query type:

$result = $database->query('SELECT',$sql);

What I really want is just this:

$sql = "SELECT * FROM `users`"$result = $database->query($sql);

-OR-

$sql = "INSERT INTO players (`f_name`, `l_name`,`position`) VALUES ('Peyton','Manning','Quarterback')"$result = $database->query($sql);

I don't mind if there are optional arguments in the function call but I want to be able to use a single function call for all of my queries like in Joomla.

 

I considered, since it is open source, using the core of the Joomla database class but it has so much specific Joomla code in it and a large chunk of the code is not related to anything I need. I think the biggest plus to using the Joomla database class is that it is relatively secure and has ALL of the error checking built in which is something I tend to get too lazy to do.

 

So, if anyone has a really good open source MySQL class that they use that would work for me, let me know. Otherwise I'll have to go ahead and write one this weekend. I'm pretty sure I can hammer something out pretty quickly but like I said, I'm lazy and would rather use something already made.

 

Thanks,

 

vujsa

[vizskywalker 0]

I don't know of any such class, but would be willing to help write one if I have time and you need or want some help. It does sound like a good idea.~Viz

[vujsa 0]

Well, I started modifying one of the scripts I found to do the job.

The first thing I did was strip out all of the excess code. Much of the code seemed to be for features that would only be used if you wanted to create an system like phpMyAdmin! I didn't see any reason to leave the code to do SHOW, DESCRIBE, and EXPLAIN. Also, I didn't need a database create option nor a database select option.

I removed about 50 lines of code and added error checking for DELETE, INSERT, and UPDATE. It isn't the best possible class I could come up with I'm sure but it looks like it will work for now.

I thought about doing the class for PHP 5 using the mysqli functions but it seems that support for that is somewhat limited and I can't say for sure what MySQL and PHP versions the script may end up on. The mysqli extensions seem to be a lot easier to manage but that'll have to wait until I get more time. I guess it would be best if I wrote both then had the master script choose the best class for the server settings like Joomla does.

Incidentally, I got the base code from here:
http://www.jemts.com/?pl=scripts&pg=dbclass
Note the following statement:

Fell free to edit or enhance this class in anyway, I would love to see any improvements anyone can make.

Here is what it looks like now:

<?php///////////////////////////////////////////////////////////////////////////////////////* DBClass v1.3 written by Matthew Manela   Script orginally written for http://www.jemts.com/   Copyright (C) 2003 Matthew Manela. All rights reserved.   See readme file for instructions on implementing and using this class.   If you have any question about this script please email me at jemts@jemts.com.   Updates to this class will come regularly.	 */////////////////////////////////////////////////////////////////////////////////////////Start of classclass Database{	var $DBname, $DBuser, $DBpass, $DBhost;	var $DBlink, $Result;	var $Connection;	###########################################	# Function:	Database - constructor	# Parameters:  database name, database username, database password, database host	# Return Type: boolean	# Description: connect to database, and select database, if database doesn't exist create it and selects it	###########################################	function Database($name, $user, $pass, $host){		$this->DBname=$name;		$this->DBuser=$user;		$this->DBpass=$pass;		$this->DBhost=$host;		if(!($this->DBlink = mysql_connect($this->DBhost, $this->DBuser, $this->DBpass))){			echo mysql_errno() . ": " . mysql_error() . "\n";			trigger_error ("Cannot connect to database", E_USER_ERROR); 			return FALSE;		}else{			if(!mysql_select_db($this->DBname,$this->DBlink)){				echo mysql_errno() . ": " . mysql_error() . "\n";						trigger_error ("Cannot connect to database", E_USER_ERROR);				return FALSE;			}else{			return TRUE;			}		return TRUE;		}	}#end of database constructor	//////////////////////////////////////////////////////////////////////////////////////	###########################################	# Function:	Disconnect	# Parameters:  none	# Return Type: boolean	# Description: disconnects from database	###########################################	function Disconnect(){		if(mysql_close($this->DBlink)){  		return TRUE;		}else{		echo mysql_errno() . ": " . mysql_error() . "\n";		trigger_error ("Cannot close the database", E_USER_ERROR);		return FALSE;		}	}#end of disconnect	//////////////////////////////////////////////////////////////////////////////////////	###########################################	# Function:	Query	# Parameters:  sqlstring , type	# Return Type: Either boolean or array depending on type of query	#			   If it is a delete query returns number of rows affected	# Description: executes any SQL Query statement	###########################################	function Query($Query){		$Query = trim($Query);		if(eregi("^((SELECT))",$Query)){			if($this->Result = mysql_query($Query,$this->DBlink)) {				while ($row = mysql_fetch_array($this->Result)) {					$data[] = $row;				}				mysql_free_result($this->Result);//probably not needed				return $data;			}else{				//no entry exists in database				return FALSE;			}		}else{			$result = mysql_query($Query,$this->DBlink);			if(!isset($result) || is_null($result)){				echo mysql_errno() . ": " . mysql_error() . "\n";				trigger_error ("Query did not succeed", E_USER_ERROR);				return FALSE;			}			elseif(eregi("^((DELETE)|(INSERT)|(UPDATE))",$Query)){				if(@mysql_affected_rows() < 1){					echo mysql_errno() . ": " . mysql_error() . "\n";					trigger_error ("Query did not succeed", E_USER_ERROR);					return FALSE;				}else{					return @mysql_affected_rows();				}			}else{				 return true;			}		}	}#end of query function}#End of class?><?php$database= new Database("DatabaseName","DatabaseUser","DatabasePass","DatabaseHost");//$rows = $database->Query("INSERT INTO jos_jstats_iptocountry (IP_FROM,IP_TO,COUNTRY_CODE2,COUNTRY_NAME) VALUES ('321321','321321','RR','Whatever')");//$rows = $database->Query("DELETE FROM jos_jstats_iptocountry WHERE IP_FROM = '321321'");$rows = $database->Query("SELECT * FROM jos_jstats_iptocountry");echo "<pre>";print_r($rows);echo "</pre>";$database->Disconnect();		 ?>

Actually, there is a large number of classes written for this subject but I don't have the time to download, review, test, and play around with each one.

For the quick list:
http://forums.xisto.com/no_longer_exists/

Do you have any suggestions?

Thanks for the reply.

vujsa

[Quatrux 4]

Some time ago I went through mediawiki source and saw their database class, thought that I need something like that and wrote it by taking things/ideas out of them, I changed lots of stuff to fit my purpose, but I never completely used it so it can be with bugs and etc. So you can see how it works by looking into mediawiki too, moreover the new versions might have better class, I mean they should have updated it to work even better, but most of mediawiki people use objects rather than arrays. ;]

[vujsa 0]

Thanks for the information. I'll check it out and see if I can clean it up enough for a general purpose MySQL class.The best part about using such a class is even if I find a better one later, I should be able to replace the one I'm using now with little trouble. I mean, since I only want the class to return an array, TRUE, or FALSE with an error; the method by which the class gets it's out put isn't as important.So while I investigate better class options, I think I'll begin work on the project that will use the class I am looking for. Since the project is meant to only be a demo and I'll have to make a pitch to "sell" it, I don't want to get too wrapped up with one little class that could be replaced later.The project is a data manipulation system for maintaining a number of records on a daily basis. Due to a number of factors, it will mostly be proprietary but the database class can be very general as long as it works exceptionally well!So, I'll look into the MediaWiKi database class. I guess that there are a number of really good open source projects out there that would use such a class but It'll take time to find the most suitable one.vujsa