CLASS DE CONNEXION MYSQL

<?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 zip

25 février 2005 00:55:46 :

* Modifications des paramètres des méthodes de requêtes SELECT, UPDATE, INSERT et DELETE. Passage de tableaux de string et non plus un paramètre sous forme de liste. * Ajout de méthodes : - Connexion et deconnexion sans destruction de l'objet - Vérifier que la connexion est toujours active - Modification de l'utilisateur ou de la base de données avec deconnexion/connexion sans destruction de l'objet.