Class de connexion mysql

Télécharger le projet
Commenter

Description

Cette classe permet d'ouvrir et de fermer une connexion à un serveur MySQL.
Elle permet aussi d'envoyer des requêtes en stipulant uniquement les tables et les champs désirés.
Les requêtes SELECT renvoient un tableau indexé.

Source / Exemple : 


<?php
// +----------------------------------------------------------------------+
// | MySQL database connection class                                      |
// +----------------------------------------------------------------------+
// | September 5th, 2004                                                  |
// +----------------------------------------------------------------------+
// | This class makes you able to create a connection to a MySQL Database,|
// | send all the SQL command trough it and get the results back of a     |
// | SQL SELECT command in an indexed array.                              |
// +----------------------------------------------------------------------+
// | Author: Nottet Thomas                                                |
// +----------------------------------------------------------------------+

/**


     
  •  Function that creates a string list from an array

    •  
  • /

function createList($tab,$tab2)
    {
    if(!is_array($tab))
        {
        $string=$tab;
        }
    else
        {
        for($i=0;$i<count($tab);$i++)
            {
            $string.=$tab[$i];
            $string.=", ";
            }
        $string=substr($string,0,strlen($string)-2);
        }
    }

class MyDB
    {
    /**


         
  •  Basic Constructor

    •      
  •  @param $srv : (STRING) The Server Name

    •      
  •  @param $usr : (STRING) A valid user allowed to connect to the server

    •      
  •  @parma $pwd : (STRING) The user password

    •      
  •  @parma $db  : (STRING) The database you want to connect to.

    •      
  • /

    function MyDB($srv,$usr,$pwd,$db)
        {
        $this->srv=$srv;
        $this->usr=$srv;
        $this->db=$srv;
        $this->pwd=$srv;
        $this->id_connect=false;
        }

    /**


         
  •  Change the database you are connected to. It actually close the connection before reopening it with the database

    •      
  •  @parma $db  : (STRING) The database you want to connect to.

    •      
  •  @return : (BOOLEAN) TRUE if the connection has succeeded and the database exists

    •      
  •                      FALSE if the server is not available, the database doesn't exist or if you use a wrong user|password

    •      
  • /

    function setDB($db)
        {
        if($this->id_connect!=false)
            {
            $this->deconnect();
            }
        $this->db=$db;
        return $this->connect();
        }

    /**


         
  •  Change the username you are connected with. It actually close the connection before reopening it with the new user

    •      
  •  @parma $usr  : (STRING) The username you want to connect with.

    •      
  •  @parma $pwd  : (STRING) The password of the user you want to connect with.

    •      
  •  @return : (BOOLEAN) TRUE if the connection has succeeded and the database exists

    •      
  •                      FALSE if the server is not available, the database doesn't exist or if you use a wrong user|password

    •      
  • /

    function setUser($usr,$pwd)
        {
        if($this->id_connect!=false)
            {
            $this->deconnect();
            }
        $this->usr=$usr;
        $this->usr=$pwd;
        return $this->connect();
        }

    /**


         
  •  Open a MySQL Server Connection

    •      
  •  @return : (BOOLEAN) TRUE if the connection has succeeded and the database exists

    •      
  •                      FALSE if the server is not available, the database doesn't exist or if you use a wrong user|password

    •      
  • /

    function connect()
        {
        $this->id_connect=@mysql_connect($this->srv,$this->usr,$this->pwd);
        if(!$this->id_connect)
            {
            return false;
            }
        $this->db=$db;
        $dbselect=@mysql_select_db($this->db, $this->id_connect);
        if(!$dbselect)
            {
            $this->deconnect();
            $this->id_connect=false;
            return false;
            }
        return true;
        }

    /**


         
  •  Close the MySQL Server Connection

    •      
  • /

    function deconnect()
        {
        if($this->id_connect)
            {
            @mysql_close($this->id_connect);
            }
        $this->id_connect=false;
        }

    /**


         
  •  Check if the connection to the server is open

    •      
  •  @return : (BOOLEAN) TRUE if the connection is opened

    •      
  •                      FALSE if not

    •      
  • /

    function connected()
        {
        if(!$this->id_connect)
            {
            return false;
            }
        else
            {
            return @mysql_ping($this->id_connect);
            }
        }

    /**


         
  •  Function that get a records set from a database

    •      
  •  @param $fields     : (STRING ARRAY) Array(fields number x 1) of fields you want the values back

    •      
  •                       Don't forget to use alias if you have multiple fields with the same name from different tables

    •      
  •                       (format : TABLE_ALIAS.FIELD_NAME)

     *


         
  •  @param $tables     : (STRING ARRAY) Array(tables numbers x 1) of tables you want the results from

    •      
  •                       Don't forget to use alias if you have multiple fields with the same name from different tables

    •      
  •                       (format : TABLE_NAME TABLE_ALIAS)

     *


         
  •  @param $conditions : (STRING) Conditions list like you find behind a WHERE claus in a SQL statement. OPTIONAL

    •      
  •                       format : TABLE_ALIAS.FIELD_NAME operator VALUE [ logical_operator TABLE_ALIAS.FIELD_NAME operator VALUE ...]

    •      
  •                       operator could be : =, <, >, <>, <=, >=, IS NULL, IS NOT NULL

    •      
  •                       logical_operator could be : AND or OR

    •      
  •                       e.g. t.champs1 = 4 AND champs3 IS NOT NULL OR t2.champs4 = "foo"

    •      
  •                       Don't forget to put \ before a single quote in a string value.

     *


         
  •  @param $groupBy    : (STRING ARRAY) Array(fields number X 1) of fields following the GROUP BY clause. OPTIONAL

    •      
  •                       (sometimes needed if you use more than one table)

    •      
  •                       Don't forget to use alias if you have multiple fields with the same name from different tables

    •      
  •                       (format : $groubBy=array("TABLE_ALIAS.FIELD_NAME") )

     *


         
  •  @param $orderBy    : (STRING ARRAY) Array(fields number X 2) of fields following which you want to order the set. OPTIONAL

    •      
  •                       The first element should be the field you the result set ordered by.

    •      
  •                       The second should be TRUE if you want ASCENDING order or FALSE if you want DESCENDING order

    •      
  •                       Don't forget to use alias if you have multiple fields with the same name from different tables

    •      
  •                       (format : $orderBy=array(array("TABLE_ALIAS.FIELD_NAME",true)) )

     *


         
  •  @param $limits     : (INTEGER) The maximum number of records you want in the results set. OPTIONAL

    •      
  •                    of (INTEGER ARRAY) A 2-dimension array to only have a part of the results set.

    •      
  •                       The first element should be the index number of the first record you want back

    •      
  •                       The second should be the number of record you want back

     *


         
  •  @return : ARRAY(records number X fields number) An array containing a set of results or (BOOLEAN) FALSE if error or no records returned

    •      
  • /

    function select($fields,$tables,$conditions,$groupBy,$orderBy,$limits)
        {
        //Check you have values for fields and tables
        if(empty($fields) || empty($tables)) return false;

        //Fields List Creation
        $fieldsList=createList($fields);
        $query="SELECT $fieldsList";

        //Tables List Creation
        $tablesList=createList($tables);
        $query.=" FROM $tablesList";

        //Conditions List Creation
        if(!empty($conditions))
            {
            $query.=" WHERE $conditions";
            }

        //Group By List creation
        if(!empty($groupBy))
            {
            $tablesList=createList($tables);
            $query.=" GROUP BY $groupByList";
            }

        // Order By List Creation
        if(!empty($orderBy))
            {
            if(!is_array($orderBy))
                {
                $orderByList=$orderBy;
                }
            else
                {
                if(!is_array($orderBy[0]))
                    {
                    for($i=0;$i<count($orderBy);$i++)
                        {
                        $orderByList.=$orderBy[$i];
                        $orderByList.=" ASC, ";
                        }
                    $orderByList=substr($orderByList,0,strlen($orderByList)-2);
                    }
                else
                    {
                    for($i=0;$i<count($orderBy);$i++)
                        {
                        $orderByList.=$orderBy[$i][0];
                        if($orderBy[$i][1])
                            {
                            $orderByList.=" ASC, ";
                            }
                        else
                            {
                            $orderByList.=" DESC, ";
                            }
                        }
                    $orderByList=substr($orderByList,0,strlen($orderByList)-2);
                    }
                 }
            $query.=" ORDER BY $orderByList";
            }

        //Limit clause creation
        if(!empty($limits))
            {
            if(is_array($limits))
                {
                $query.=" LIMIT $limits[0], $limits[1]";
                }
            else
                {
                $query=$query." LIMIT $limits[0]";
                }
            }

        //Execute the SQL Statement
        if($this->id_connect)
            {
            $res=@mysql_query($query, $this->id_connect);
            if (empty($res))
                {
                return false;
                }
            else
                {
                $nbRows=@mysql_num_rows($res);
                if ($nbRows==0)
                    {
                    return false;
                    }
                else
                    {
                    for($i=0;$i<$nbRows;$i++)
                        {
                        $enregs[$i]=@mysql_fetch_array($res,MYSQL_NUM);
                        }
                    return $enregs;
                    }
                }
            }
        else
            {
            return false;
            }
        }
    /**


         
  •  Function that send a SQL INSERT command to the server to insert a new record in a table

    •      
  •  @param $fields : (STRING ARRAY) Array(fields number X 1) of the fields you want to give a value for the new record.

    •      
  •  @param $values : (STRING ARRAY) Array(fields number X 1) of values you want to give to the different fields

    •      
  •  @param $table  : (STRING) Table name you want to add a record to

    •      
  •  @return : (BOOLEAN) TRUE if the new record has correctly been added - FALSE if not

    •      
  • /

    function insert($fields,$values,$table)
        {
        if(empty($table) || empty($fields) ) return false;

        //SQL Statement Creation
        $fieldsList=createList($fields);
        $valuesList=createList($fields);
        $query="INSERT INTO $table ($fieldsList) VALUES ($valuesList)";

        //Execute the SQL statement
        if($this->id_connect)
            {
            $res=@mysql_query($query, $this->id_connect);
            if ($res)
                {
                return false;
                }
            else
                {
                return true;
                }
            }
        else
            {
            return false;
            }
        }

    /**


         
  •  Function that send a SQL DELETE command to the server to delete specific records in a table

    •      
  •  @param $table : (STRING) Table name you want to remove records from

    •      
  •  @param $conditions : (STRING) Conditions list like you find behind a WHERE claus in a SQL statement. OPTIONAL

    •      
  •                       format : TABLE_ALIAS.FIELD_NAME operator VALUE [ logical_operator TABLE_ALIAS.FIELD_NAME operator VALUE ...]

    •      
  •                       operator could be : =, <, >, <>, <=, >=, IS NULL, IS NOT NULL

    •      
  •                       logical_operator could be : AND or OR

    •      
  •                       e.g. t.champs1 = 4 AND champs3 IS NOT NULL OR t2.champs4 = "foo"

    •      
  •                       Don't forget to put \ before a single quote in a string value.

    •      
  •  @return : (INTEGER) the number of records that were succesfully removed - FALSE no records deleted or error

    •      
  • /

    function delete($table,$conditions)
        {
        //SQL Statement Creation
        $query="DELETE FROM $table";
        if(!empty($conditions))
            {
            $query=$query." WHERE $conditions";
            }
        //Execute SQL statement
        if($this->id_connect)
            {
            $res=@mysql_query($query, $this->id_connect);
            if (empty($res))
                {
                return false;
                }
            else
                {
                $nb=mysql_affected_rows($this->id_connect);
                if($nb==0)
                    {
                    return false;
                    }
                else
                    {
                    return $nb;
                    }
                }
            }
        }

    /**


         
  •  Function that send a SQL UPDATE command to the server to update specific records in a table

    •      
  •  @param $table : (STRING) Table name where you want to update records

    •      
  •  @param $fields     : (STRING ARRAY) Array(fields number x 1) of fields you want to update

    •      
  •  @param $values     : (STRING ARRAY) Array(fields number x 1) of values for the fields you want to update

    •      
  •  @param $conditions : (STRING) Conditions list like you find behind a WHERE claus in a SQL statement. OPTIONAL

    •      
  •                       format : TABLE_ALIAS.FIELD_NAME operator VALUE [ logical_operator TABLE_ALIAS.FIELD_NAME operator VALUE ...]

    •      
  •                       operator could be : =, <, >, <>, <=, >=, IS NULL, IS NOT NULL

    •      
  •                       logical_operator could be : AND or OR

    •      
  •                       e.g. t.champs1 = 4 AND champs3 IS NOT NULL OR t2.champs4 = "foo"

    •      
  •                       Don't forget to put \ before a single quote in a string value.

    •      
  •  @return : (INTEGER) the number of records that were succesfully updated - FALSE no records deleted or error

    •      
  • /

    function update($table,$fields,$values,$conditions)
        {
        //SQL Statement creation
        if(empty($fields)||empty($table)||empty($values)) return false;
        $query="UPDATE $table ";
        if (!is_array($fields))
            {
            $query.="SET $champs=$valeurs ";
            }
        else
            {
            $query.="SET ".$fields[0]."=".$values[0];
            for($i=1;$i<$nbchamps;$i++)
                {
                $query.=", ".$champs[$i]."=".$valeurs[$i];
                }
            }

        if(!empty($conditions))
            {
            $query.=" WHERE $conditions";
            }

        //SQL Statement Execution
        if($this->id_connect != false)
            {
            $res=@mysql_query($query, $this->id_connect);
            if (empty($res))
                {
                return false;
                }
            else
                {
                $nb=@mysql_affected_rows($this->id_connect);
                if($nb==0)
                    {
                    return false;
                    }
                else
                    {
                    return $nb;
                    }
                }
            }
        }

    var $db;         // (STRING) Database name
    var $id_connect; // (RESOURCE) ID of the Database Connection
    var $srv;        // (STRING) Server name you have to connect to
    var $usr;        // (STRING) Username you have to use for connection
    var $pwd;        // (STRING) Password of this user
    var $db;         // (STRING) Name of the current database you are connected to
}

?>
Télécharger le projet

Codes Sources

A voir également

S'inscrire maintenant

Vous n'êtes pas encore membre ?

inscrivez-vous, c'est gratuit et ça prend moins d'une minute !

Les membres obtiennent plus de réponses que les utilisateurs anonymes.

Le fait d'être membre vous permet d'avoir un suivi détaillé de vos demandes et codes sources.

Le fait d'être membre vous permet d'avoir des options supplémentaires.

Du même auteur (JeanPoldeux)