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

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

Post by lazy_yogi »

McGruff wrote: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.
globals defeat the modularisation which is one of OOP's biggest assets.
I personally prefer to go for OOP and put it in a class.
I find that as applications get bigger (over about 30 files) the proceedural approach of including runable code and having global variables becomes very messy and hard to work with. And as it becomes very large (over 1000 files) it becomes an absolute nightmare.
McGruff wrote: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.
Absolutely! Which is why I put the result iteration objects seperate to the mysql connection object.

Cheers
Eli
McGruff
DevNet Master
Posts: 2893
Joined: Thu Jan 30, 2003 8:26 pm
Location: Glasgow, Scotland

Post by McGruff »

lazy_yogi wrote:globals defeat the modularisation which is one of OOP's biggest assets
Global vars can be a problem since it may not be always be apparent where, if or how they are being changed in a complex script. Constants on the other are set once and can't be edited.

Are you saying that OOP precludes the use of constants or did I pick you up wrong?
User avatar
lazy_yogi
Forum Contributor
Posts: 243
Joined: Fri Jan 24, 2003 3:27 am

Post by lazy_yogi »

Ahh you're right. I didn't distinguish between globals and constants.

Cheers,
Eli
User avatar
devork
Forum Contributor
Posts: 213
Joined: Fri Aug 08, 2003 6:44 am
Location: p(h) developer's network

Post by devork »

hay nay
what about using heredocs being used in your calss instead of escaping quotes.?


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

Post by lazy_yogi »

There's heredocs in php ??
Nay
Forum Regular
Posts: 951
Joined: Fri Jun 20, 2003 11:03 am
Location: Brisbane, Australia

Post by Nay »

Ah, heredoc 8). I'm sure recognize this Yogi:

Code: Select all

<?php
echo <<< END
this is php  -> no escapes needed " ' ?> <? die()
END;
?>
Speaking about die, I was reading phpbuilder and it gave me the idea of having a constructer that has die() in it, making the class un-instantialble.

Meh, I got tons to learn about OO ways. I googled it, more Dutch sites came out than anything. Ah, Netherlands must be an OO land then ;).

-Nay
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post by Weirdan »

Nay wrote:Speaking about die, I was reading phpbuilder and it gave me the idea of having a constructer that has die() in it, making the class un-instantialble.
Hmm, using trigger_error("Attempt to instantiate pure virtual class",E_USER_ERROR) seems more clear to me in this particular case.
It... ehh... seems more ... Java'ish ;)
User avatar
lazy_yogi
Forum Contributor
Posts: 243
Joined: Fri Jan 24, 2003 3:27 am

Post by lazy_yogi »

Nay wrote:Speaking about die, I was reading phpbuilder and it gave me the idea of having a constructer that has die() in it, making the class un-instantialble.
Haha .. That kind of defeats the purpose =P

Btw ... OOP doesn't really use die(). It uses try/catch blocks for error handling. Unfortunately php never had this (tho its comming with php5 soon). It's a far far superior way to hadle errors. Tho I'm too lazy to explain just how powerful and useful it is (too much typing =P).

Weirdan wrote:It... ehh... seems more ... Java'ish ;)
Oh dear god ..... not java .... 8) <= I realise this emoticon has nothing to do with the conversaiton, it just looks cool tho =)

Cheers,
Eli
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post by Weirdan »

lazy_yogi wrote: Oh dear god ..... not java ....
Do you prefer SmallTalk? :?
User avatar
lazy_yogi
Forum Contributor
Posts: 243
Joined: Fri Jan 24, 2003 3:27 am

Post by lazy_yogi »

I've heard great things about smalltalk but never used it.

I use python alot and find it an incredible language.
- easy to use
- massive support
- makes it easy to write OO programs, unlike php which is an effort to wrote OO.
- awesome for applications, not just web apps
- insanely easy to use and learn and develop software
- much more readable code than any other language.

If you haven't used it. I highly recommend it!

Cheers,
Eli
Bora
Forum Newbie
Posts: 4
Joined: Sun Jan 04, 2004 12:27 pm

Post by Bora »

Andf how about this class ???

Code: Select all

<?php

class DB
{
    var $base;
    var $dane;
    var $group;
    var $host;
    var $order;
    var $pola;
    var $pass;
    var $sql;
    var $tabela;
    var $user;
    var $warunek;

    function DB($host,$user,$pass,$base){
        $this->connect($host,$user,$pass,$base);
    }

    function connect($host,$user,$pass,$base){
        //ob_start(); start
        $this->sql =mysql_connect($host,$user,$pass);
        mysql_select_db($base);
        return true;
    }

    function disconnect(){
        mysql_close($this->sql);
    }

    //  $db->add("tabela",array("pole"=>"wartosc"));
    //  np: $db->add('test',array("a"=>"b","c"=>"d"));
    function add($tabela,$dane){
        $key = array_keys($dane);
        $value = array_values($dane);
        $il = count($key);
        for ($i=0;$i<$il;$i++)
        {
            if (empty($ad)){
                $ad="(";
            }else{
                $ad=$ad.",";
            }
            if (empty($val)){
                $val="(";
            }else{
                $val=$val.",";
            }
            $ad=$ad.$key[$i];
            $val=$val."'".$value[$i]."'";
        }
        $ad=$ad.")";
        $val=$val.")";
        $sql="INSERT INTO ".$tabela." ".$ad." VALUES ".$val;
        if (mysql_query($sql)){
            return true;
        }else{
            $this->_error();
            return false;
        }
    }

    //  $db->update("tabela",array("pole"=>"wartosc"),"warunek");
    //  np: $db->update('test',array("a"=>"g","c"=>"h"),"a='b'");
    function update($tabela,$dane,$warunek=""){
        $key = array_keys($dane);
        $value = array_values($dane);
        $il = count($key);
        $set="";
        for ($i=0;$i<$il;$i++)
        {
            if (!empty($set)){
                $set=$set.",";
            }
            $set=$set.$key[$i]."='".$value[$i]."'";
        }
        $sql="UPDATE ".$tabela." SET ".$set." WHERE ".$warunek;
        if (mysql_query($sql)){
            return true;
        }else{
            $this->_error();
            return false;
        }
    }

    //  $db->del("tabela","warunek");
    //  np: $db->del('test',"a='g'");
    function del($tabela,$warunek=""){
        $sql="DELETE FROM ".$tabela." WHERE ".$warunek;
        if (mysql_query($sql)){
            return true;
        }else{
            $this->_error();
            return false;
        }
    }

    //  $db->num_rows("tabela","co zliczac","warunek");
    //  np: $db->num_rows('test','a',null);
    function num_rows($tabela,$pole,$warunek="") {
        if ($warunek=="") {
            $where = "";
        } else {
            $where = " WHERE ".$warunek;
        }
        $sql = "SELECT COUNT(".$pole.") FROM ".$tabela.$where;
        if($res = mysql_query($sql)){
            return mysql_num_rows($res);
        }else{
            $this->_error();
            return false;
        }
    }

    //  $res = $db->select('test',array('a','c'),null,null,null);
    //  while ($arr = $db->fetch($res)) {
    //          echo $arr['a']." - ".$arr['c']."<br>\n";
    //  }
    function fetch($sql){
		return mysql_fetch_assoc($sql);
    }

    //  $res = $db->select_zap('SELECT pola FROM tabela WHERE warunek');
    function select_zap($sql){
        if ($res = mysql_query($sql)){
            return $res;
        }else{
            $this->_error();
            return false;
        }
    }

    //  $db->select('tabela',array("pole","pole"),"warunek","order by","group by");
    //  np: $db->select('test',array("a","c"),null,null,null);
    function select($tabela,$pola,$warunek="",$order="",$group=""){
        foreach ($pola as $pole)
        {
            if (!empty($ob)){
                $ob=$ob.",";
            }else{
                $ob="";
            }
            $ob=$ob.$pole;
        }
        if (isset($warunek)){
            $war=" WHERE ".$warunek;
        }else{
            $war="";
        }
        if (isset($order)){
            $ord=" ORDER BY ".$order;
        }else{
            $ord="";
        }
        if (isset($group)){
            $gr=" GROUP BY ".$group;
        }else{
            $gr="";
        }
        $sql="SELECT ".$ob." from ".$tabela.$war.$ord.$gr;
        if ($res = mysql_query($sql)){
            return $res;
        }else{
            $this->_error();
            return false;
        }

    }

    function _error(){
        $this->error[]=mysql_errno();
    }

}


    $db = new DB('localhost','***','***','***');
    $db->del('test',"a='g'");
    $db->add('test',array("a"=>"b","c"=>"d"));
    $db->update('test',array("a"=>"g","c"=>"h"),"a='b'");
    $db->num_rows('test','a',null);
    $db->select('test',array("a","c"),null,null,null);


    $res = $db->select('test',array('a','c'),null,null,null);
    while ($arr = $db->fetch($res)) {
            echo $arr['a']." - ".$arr['c']."<br>\n";
    }
    $db->disconnect();

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

Post by lazy_yogi »

umm .. no error handling for no connection to the mysql and similarly for no connection to the particular db ?

also, I like the way you've created a wrapper for sql. But anyone using a db needs to learnt it so they can do more in depth queries if needed such as outer joins,etc. So I would think native sql would be important.

'select_zap' <== hmm ... good function names in general, but this is not very self explainitory

However, well written.
And a nice idea!

Cheers,
Eli
Bora
Forum Newbie
Posts: 4
Joined: Sun Jan 04, 2004 12:27 pm

Post by Bora »

Code: Select all

<?php
class DB
{
    var $base;
    var $dane;
    var $group;
    var $host;
    var $join;
    var $order;
    var $pola;
    var $pass;
    var $sql;
    var $tabela;
    var $user;
    var $warunek;

    function DB($host,$user,$pass,$base){
        $this->connect($host,$user,$pass,$base);
    }

    function connect($host,$user,$pass,$base){
        //ob_start(); start
        $this->sql = mysql_connect($host,$user,$pass) or die("Access denied for user: '".$user."'@'".$host."' ");

        mysql_select_db($base) or die("Base error");
        return true;
    }

    function disconnect(){
        mysql_close($this->sql);
    }

    //  $db->add("tabela",array("pole"=>"wartosc"));
    //  np: $db->add('test',array("a"=>"b","c"=>"d"));
    function add($tabela,$dane){
        $key = array_keys($dane);
        $value = array_values($dane);
        $il = count($key);
        for ($i=0;$i<$il;$i++)
        {
            if (empty($ad)){
                $ad="(";
            }else{
                $ad=$ad.",";
            }
            if (empty($val)){
                $val="(";
            }else{
                $val=$val.",";
            }
            $ad=$ad.$key[$i];
            $val=$val."'".$value[$i]."'";
        }
        $ad=$ad.")";
        $val=$val.")";
        $sql="INSERT INTO ".$tabela." ".$ad." VALUES ".$val;
        if (mysql_query($sql)){
            return true;
        }else{
            $this->_error();
            return false;
        }
    }

    //  $db->update("tabela",array("pole"=>"wartosc"),"warunek");
    //  np: $db->update('test',array("a"=>"g","c"=>"h"),"a='b'");
    function update($tabela,$dane,$warunek=""){
        $key = array_keys($dane);
        $value = array_values($dane);
        $il = count($key);
        $set="";
        for ($i=0;$i<$il;$i++)
        {
            if (!empty($set)){
                $set=$set.",";
            }
            $set=$set.$key[$i]."='".$value[$i]."'";
        }
        $sql="UPDATE ".$tabela." SET ".$set." WHERE ".$warunek;
        if (mysql_query($sql)){
            return true;
        }else{
            $this->_error();
            return false;
        }
    }

    //  $db->del("tabela","warunek");
    //  np: $db->del('test',"a='g'");
    function del($tabela,$warunek=""){
        $sql="DELETE FROM ".$tabela." WHERE ".$warunek;
        if (mysql_query($sql)){
            return true;
        }else{
            $this->_error();
            return false;
        }
    }

    //  $db->num_rows("tabela","co zliczac","warunek");
    //  np: $db->num_rows('test','a',null);
    function num_rows($tabela,$pole,$warunek="") {
        if ($warunek=="") {
            $where = "";
        } else {
            $where = " WHERE ".$warunek;
        }
        $sql = "SELECT COUNT(".$pole.") FROM ".$tabela.$where;
        if($res = mysql_query($sql)){
            return mysql_num_rows($res);
        }else{
            $this->_error();
            return false;
        }
    }

    //  $res = $db->select('test',array('a','c'),null,null,null);
    //  while ($arr = $db->fetch($res)) {
    //          echo $arr['a']." - ".$arr['c']."<br>\n";
    //  }
    function fetch($sql){
		//return mysql_fetch_assoc($sql);
		if ($res = mysql_fetch_assoc($sql)){
            return $res;
        }else{
            $this->_error();
            return false;
        }
    }

    //  $res = $db->select_query('SELECT pola FROM tabela WHERE warunek');
    function select_zap($sql){
        if ($res = mysql_query($sql)){
            return $res;
        }else{
            $this->_error();
            return false;
        }
    }

    //  $db->select('tabela',array("pole","pole"),"warunek","join","order by","group by");
    //  np: $db->select('test',array("a","c"),null,null,null,null);
    function select($tabela,$pola,$warunek="",$join="",$order="",$group=""){
        foreach ($pola as $pole)
        {
            if (!empty($ob)){
                $ob=$ob.",";
            }else{
                $ob="";
            }
            $ob=$ob.$pole;
        }
        if (isset($join)){
            $jo=" ".$join." ";
        }else{
            $jo=" ";
        }
        if (isset($warunek)){
            $war=" WHERE ".$warunek;
        }else{
            $war="";
        }
        if (isset($warunek)){
            $war=" WHERE ".$warunek;
        }else{
            $war="";
        }
        if (isset($order)){
            $ord=" ORDER BY ".$order;
        }else{
            $ord="";
        }
        if (isset($group)){
            $gr=" GROUP BY ".$group;
        }else{
            $gr="";
        }
        $sql="SELECT ".$ob." from ".$tabela.$war.$jo.$ord.$gr;
        if ($res = mysql_query($sql)){
            return $res;
        }else{
            $this->_error();
            return false;
        }

    }

    function _error(){
        $this->error[]=mysql_errno();
    }

}


    $db = new DB('localhost','***','***','***');
    $db->del('test',"a='g'");
    $db->add('test',array("a"=>"b","c"=>"d"));
    $db->update('test',array("a"=>"g","c"=>"h"),"a='b'");
    $db->num_rows('test','a',null);
    $db->select('test',array("a","c"),null,null,null,null);
    $db->select_query("SELECT * from test");


    $res = $db->select('test',array('a','c'),null,null,null,null);
    while ($arr = $db->fetch($res)) {
            echo $arr['a']." - ".$arr['c']."<br>\n";
    }
    $db->disconnect();

?>
And select_zap -> "select" - eng select :)
"zap" - polish "zapytanie" == eng "query"

but right it should call select_query.
Post Reply