Welcome Guest ( Log In | Register )



 
Reply to this topicStart new topic
> Anyone Know Of A Really Good Mysql Class?, Looking for something easy but full featured.
vujsa
post Oct 9 2007, 05:25 PM
Post #1


Absolute Newbie
Group Icon

Group: Admin
Posts: 888
Joined: 20-February 05
From: Indianapolis, Indiana, USA (Midwest)
Member No.: 2,714



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:
CODE
$sql = "SELECT * FROM `users`"
$result = $db -> Select($sql);

-OR-
CODE
$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:
CODE
$result = $database->query('SELECT',$sql);


What I really want is just this:
CODE
$sql = "SELECT * FROM `users`"
$result = $database->query($sql);

-OR-
CODE
$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
Go to the top of the page
 
+Quote Post
vizskywalker
post Oct 10 2007, 02:15 AM
Post #2


Techno-Necromancer
Group Icon

Group: Members
Posts: 1,018
Joined: 13-January 05
From: The Net
Member No.: 2,127



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
Go to the top of the page
 
+Quote Post
vujsa
post Oct 10 2007, 06:07 AM
Post #3


Absolute Newbie
Group Icon

Group: Admin
Posts: 888
Joined: 20-February 05
From: Indianapolis, Indiana, USA (Midwest)
Member No.: 2,714



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/index.php?pl=scripts&pg=dbclass
Note the following statement:
QUOTE
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:
CODE
<?php
//////////////////////////////////////////////////////////////////////////////////////
/* DBClass v1.3 written by Matthew Manela
   Script orginally written for 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 class
class 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://php.resourceindex.com/Functions_and...ase_Management/

Do you have any suggestions?

Thanks for the reply.

vujsa
Go to the top of the page
 
+Quote Post
Quatrux
post Oct 10 2007, 06:49 AM
Post #4


the Q
Group Icon

Group: [HOSTED]
Posts: 1,051
Joined: 13-July 05
From: Lithuania, Vilnius
Member No.: 7,059



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. ;]
Go to the top of the page
 
+Quote Post
vujsa
post Oct 10 2007, 05:10 PM
Post #5


Absolute Newbie
Group Icon

Group: Admin
Posts: 888
Joined: 20-February 05
From: Indianapolis, Indiana, USA (Midwest)
Member No.: 2,714



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
Go to the top of the page
 
+Quote Post

Reply to this topicStart new topic

Collapse

> Similar Topics

Topics Topics
  1. MySQL & PHP coding(9)
  2. Displaying Data From Mysql?(2)
  3. Php/mysql Data Display(3)
  4. Need Help With A PHP - MySQL Registration Script(13)
  5. Need For PHP/MySQL Creator(1)
  6. Printing Out A Table(6)
  7. Need Some Help Using PHP & MySQL(4)
  8. [PHP + MySQL] Separating The Results By Pages(0)
  9. [PHP + MySQL] Encrypting Data(11)
  10. [php] Index.php?section=xx&pag=yy(6)
  11. How Do You Create A Secure Loging?(4)
  12. Important: Basics Of Using PHP And MySQL(10)
  13. Need Help With Php/mysql And Web Servers Such As Asta's.(4)
  14. Need MySQL Alternative To The Syntax "or die()"(8)
  15. Re-order MySQL Table(11)
  1. PHP & MySQL: Displaying Content From A Given ID(6)
  2. How To Show Serial Nums In PHP Table For Contents Of MySQL DB(4)
  3. Php Mysql Errors(2)
  4. Sql Injection Prevention (passing Numerical Data Across Pages).(9)
  5. Php/mysql And Manual Page Caching?(4)
  6. Too Many Connections?(4)
  7. Extracting Mysql Maths Using Php(2)
  8. Warning: Mysql_num_rows()(1)
  9. Warning: Mysql_result(): Supplied Argument Is Not A Valid Mysql Result Resource In ...(4)
  10. Making A Link = Mysql_query(8)
  11. Making Something In Mysql Happen Only Once(10)
  12. Mysql Question(inserting Number From A Textfield)(3)
  13. Letting Users Add Mysql Data With Php(1)


 



- Lo-Fi Version Time is now: 11th October 2008 - 08:33 PM