Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 8 of 8
  1. #1
    Regular Coder funnymoney's Avatar
    Join Date
    Aug 2007
    Posts
    364
    Thanks
    17
    Thanked 24 Times in 24 Posts

    MySQL Database PHP Class

    Custom PHP Class for manipulating MySQL. Simple to follow, and to use.

    PHP Code:
    <?php

    class Database {
        public function 
    connect($host$name$pass) {
            
    $connection mysql_connect($host$name$pass) or die(mysql_error());
            return 
    $connection;
        }
        public function 
    usedatabase($databasename) {
            return 
    mysql_select_db($databasename) or die(mysql_error()."method usedatabase");
        }
        
        public function 
    selectdata($tablename$fields) {
            
    $sql = ("SELECT $fields FROM $tablename");
            
    $query mysql_query($sql) or die(mysql_error()."method selectdata 1");
            
            if (!
    $query) {
                return 
    "Could not successfully run query ($sql) from DB: " mysql_error()."method selectdata";
                exit;
            }

            if (
    mysql_num_rows($query) == 0) {
                return 
    0;
                exit;
            }
            if(
    preg_match("/,/"$fields)) {
            
    $fields explode(","$fields);
            
    #print_r($fields);
            
    }
            while (
    $row mysql_fetch_assoc($query)) {
                if (
    is_array($fields)) {
                    foreach (
    $fields as $field) {
                        
    $rezultati[$field][] = $row[$field];
                    }
                }
                else {
                        
    $rezultati[$fields][] = $row[$fields];
                }
            
            }
            
    mysql_free_result($query);
            return 
    $rezultati;
        }
        
        public function 
    selectwhere($tablename$fields$where) {
            
    $sql = ("SELECT $fields FROM $tablename $where");
            
    $query mysql_query($sql) or die(mysql_error()."select where 1");
            
            if (!
    $query) {
                return 
    "Could not successfully run query ($sql) from DB: " mysql_error()."select where 2";
                exit;
            }

            if (
    mysql_num_rows($query) == 0) {
                return 
    0;
                exit;
            }
            if(
    preg_match("/,/"$fields)) {
            
    $fields explode(","$fields);
            
    #print_r($fields);
            
    }
            while (
    $row mysql_fetch_assoc($query)) {
                if (
    is_array($fields)) {
                    foreach (
    $fields as $field) {
                        
    $rezultati[$field] = $row[$field];
                    }
                }
                else {
                        
    $rezultati[$fields] = $row[$fields];
                }
            
            }
            
    mysql_free_result($query);
            return 
    $rezultati;
        }
        
        public function 
    selectallwhere ($tablename$fields$where) {
            
    $sql = ("SELECT $fields FROM $tablename $where");
            
    $query mysql_query($sql) or die(mysql_error()."select where 1");
            
            if (!
    $query) {
                return 
    "Could not successfully run query ($sql) from DB: " mysql_error()."select where 2";
                exit;
            }

            if (
    mysql_num_rows($query) == 0) {
                return 
    0;
                exit;
            }
            if(
    preg_match("/,/"$fields)) {
            
    $fields explode(","$fields);
            
    #print_r($fields);
            
    }
            while (
    $row mysql_fetch_assoc($query)) {
                if (
    is_array($fields)) {
                    foreach (
    $fields as $field) {
                        
    $rezultati[$field][] = $row[$field];
                    }
                }
                else {
                        
    $rezultati[$fields][] = $row[$fields];
                }
            
            }
            
    mysql_free_result($query);
            return 
    $rezultati;
        }
        
        public function 
    leftjoin($lefttable$righttable$matchedrow$fields$order) {
            
    $sql = ("SELECT $fields FROM $lefttable LEFT JOIN $righttable ON $lefttable.$matchedrow = $righttable.$matchedrow $order");
            
    $query mysql_query($sql) or die(mysql_error()."method selectdata left join");

            if (!
    $query) {
                return 
    "Could not successfully run query ($sql) from DB: " mysql_error()."method selectdata";
                exit;
            }

            if (
    mysql_num_rows($query) == 0) {
                return 
    0;
                exit;
            }
            
            if(
    preg_match("/,/"$fields)) {
            
    $fields explode(","$fields);
            
    #print_r($fields);
            
    }
            while (
    $row mysql_fetch_assoc($query)) {
                if (
    is_array($fields)) {
                    foreach (
    $fields as $field) {
                        
    $rezultati[$field][] = $row[$field];
                    }
                }
                else {
                        
    $rezultati[$fields][] = $row[$fields];
                }
            
            }
            
            
            
    mysql_free_result($query);
            return 
    $rezultati;
            
        }
        
        public function 
    insertdata($tablename$fields$values) {
            return 
    mysql_query(
            
    "INSERT INTO $tablename ($fields) VALUES ($values)"
            

                or die(
    mysql_error());
        }
        public function 
    update($tablename$field$value$where) {
            return 
    mysql_query("UPDATE $tablename SET $field = $value $where") or die(mysql_error());
        }
        public function 
    newupdate($tablename$values$where) {
            return 
    mysql_query("UPDATE $tablename SET $values $where") or die(mysql_error());
        }
        public function 
    delete($tablename$where) {
            return 
    mysql_query("DELETE FROM $tablename $where");
        }
    }

    #usage
    $mysql = new Database();
    $mysql->connect("localhost""username""password");
    $mysql->usedatabase("test");

    $selectdata $mysql->selectdata("tablename""tablefield1,tablefield2");
    $selectallwhere $mysql->selectallwhere("tablename""tablefield1,tablefield2,tablefield3""where status='1'");

    #class outputs multidimenstional array so you can access values like this

    print_r($selectdata['tablefield1']);
    print_r($selectallwhere);

    #try and see the difference

    ?>

  • #2
    Supreme Master coder! _Aerospace_Eng_'s Avatar
    Join Date
    Dec 2004
    Location
    In a place far, far away...
    Posts
    19,291
    Thanks
    2
    Thanked 1,043 Times in 1,019 Posts
    What if you want all of the fields and you have like 20 of them? Using a wildcard would be easiest but there would be no comma in your $fields variable. Would that break things?
    ||||If you are getting paid to do a job, don't ask for help on it!||||

  • #3
    Regular Coder funnymoney's Avatar
    Join Date
    Aug 2007
    Posts
    364
    Thanks
    17
    Thanked 24 Times in 24 Posts
    if by wildcard you mean about *, well, yeah, i thought that this code of mine covers that stuff, at least selectdata method, but i guess, now and there while i came further with this class for some reason i forgot about * wildcard.

    i remember i solved it with preg_replace, but i am really really not sure were results disposed as they are now. anyway in 16kb of code, i never ever had to use wildcard *

  • #4
    New to the CF scene
    Join Date
    Sep 2009
    Location
    Lisse, Zuid-Holland, the Netherlands
    Posts
    7
    Thanks
    0
    Thanked 2 Times in 2 Posts
    I don't want to be all too critical in my very first post on this board, but still; there's some things I don't really understand looking at your code.

    * First of all; why don't you use MySQLi? It supports prepared statements to begin with, and is OOP. It has numerous other advantages, to be read here: http://stackoverflow.com/questions/1...qli-over-mysql
    * Why do you echo in a class (die(mysql_error())? Saving the error and returning it at some point would be way more convenient.
    * Why would you want to kill your script? Not very convenient in a production environment. Returning the error back to the class/file that's asked to execute a query would be better, in my opinion. Then you grant yourself the possibility of logging the error and give the user a normal message back without the page being screwed up by die().
    * Your if(!$query) will never ever do a thing, as the script has died before.
    * In case you need to execute a slightly more complicated query, you still have to do it apart from this class, as it doesn't support subquery's (for example).
    * Last but not least; why don't you sanitize your input? It's very - no, VERY - easy to destroy your entire database with the code you've written. (SELECT DELETE FROM x WHERE y = z... etc.)

    Just my 2 cents.

  • Users who have thanked Robin_v_G for this post:

    funnymoney (09-24-2009)

  • #5
    Regular Coder funnymoney's Avatar
    Join Date
    Aug 2007
    Posts
    364
    Thanks
    17
    Thanked 24 Times in 24 Posts
    i have a new approach to database class now, but i will consider your 2 cents, and see what i can do about them.

    thanks

    if you have any suggestions, please, feel free to post them

  • #6
    Supreme Master coder! abduraooft's Avatar
    Join Date
    Mar 2007
    Location
    N/A
    Posts
    14,859
    Thanks
    160
    Thanked 2,223 Times in 2,210 Posts
    Blog Entries
    1
    Take a look at the code provided in the book at http://www.wrox.com/WileyCDA/WroxTit...-DOWNLOAD.html

    Unzip the downloaded file and open files class.Database.php and DB_test.php ch08. Not a complete one, as there's no support to use joins in queries other than SELECT, though you may get a different way of passing fields and their values.
    The Dream is not what you see in sleep; Dream is the thing which doesn't let you sleep. --(Dr. APJ. Abdul Kalam)

  • Users who have thanked abduraooft for this post:

    funnymoney (09-27-2009)

  • #7
    teh Moderatorinator
    Join Date
    Sep 2004
    Location
    USA
    Posts
    2,472
    Thanks
    4
    Thanked 40 Times in 40 Posts
    Does it handle stored procedures? I can't imagine using inline sql everywhere in an application.

  • #8
    Regular Coder funnymoney's Avatar
    Join Date
    Aug 2007
    Posts
    364
    Thanks
    17
    Thanked 24 Times in 24 Posts
    thank you for the posted code abduraooft. i can see already now, almost maybe a month after writing that class that it can be done quite a bit different. now for this CMS, of some sort, i'm using new database approach with less similar queries, and i do get some results. but database class for me, is still pretty hard coded so i'm satisfied even if all i need to do is add a mathod...
    dunno, maybe i have to much time on my hand, since i did read a lot about classes, and stuff, but still, even if you do something bad, when you are using classes it's much much more simpler to change it to better code then by using procedures

    xml + database + some php is new approach


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •