PHP – Class MySQL Database Queries

This PHP class can execute MySQL database queries using arrays.

It can connect to a MySQL database and execute SELECT queries retrieving the results as
associative arrays.

The class can also execute INSERT or UPDATE queries taking arrays of field values. It can also
execute DELETE queries.

Code Example: Download Example
index.php
<?php
require('class.ArrayQry.php');

//Define DB Connection Info
$db_creds = array(
    'db'       => "test",
    'user'     => "uname",
    'pw'       => "mypass",
    'host'     => "localhost"
);

/*
////////////////////
//Example 1: Simple Select w optional debugging
//return results in 2-dim Assoc Array
////////////////////
$qry = new ArrayQry($db_creds, true);
$qry->debug();          //optional
$qry->set_qry("SELECT * FROM contacts");
$rows = $qry->execute('s');
print_r($rows);
*/

/*
////////////////////
//Example 2: Insert Record
//Inserts an Assoc Array into DB using the keys as column names and values as column data
////////////////////
$insert_info = array(
    'fname'       => "Tater",
    'lname'       => "Salad",
    'phone'       => "555-5555",
    'email'       => "me@mine.com"
);
$qry = new ArrayQry($db_creds, true);
$qry->debug();          	//optional
$qry->set_tbl("contacts"); //the table to insert into
$qry->set_update_array($insert_info);//the array of data
$qry->execute('i');
*/

/*
////////////////////
//Example 3: Update Record
//Inserts an Assoc Array into DB using the keys as column names and values as column data
////////////////////
$update_info = array(
    'phone'       => "222-2222",
);
$qry = new ArrayQry($db_creds, true);
$qry->set_tbl("contacts"); //the table to insert into
$qry->set_update_array($update_info);//the array of data
$qry->set_where("WHERE id='9'");
$qry->execute('u');
*/

/*
////////////////////
//Example 4: Delete Record
////////////////////
$qry = new ArrayQry($db_creds, true);
$qry->set_qry("DELETE FROM contacts WHERE id='12'");
$qry->execute('d');
*/

///*
////////////////////
//Example 5: Multiple Insert with single database connection
////////////////////
$insert_info = array(
    0 =>array(
        'fname'       => "Ham",
        'lname'       => "Burgler",
        'phone'       => "555-5555",
        'email'       => "me@mine.com"
    ),
    1 =>array(
        'fname'       => "Frank",
        'lname'       => "Enstein",
        'phone'       => "555-5555",
        'email'       => "me@mine.com"
    ),
    2 =>array(
        'fname'       => "Pete",
        'lname'       => "Repeat",
        'phone'       => "555-5555",
        'email'       => "me@mine.com"
    )
);

//This example shows how YOU CAN define your connection info outside the class, if desired. This is preferred when performing multiple querys for performance:
$conn_array = $db_creds;
$conn = mysql_connect($conn_array['host'], $conn_array['user'], $conn_array['pw'])or die("Unable to connect to MYSQL because: ".mysql_error());
$db_select = mysql_select_db($conn_array['db'],$conn) or die("Could not select '".$conn_array['db']."' database because: ".mysql_error());

$qry = new ArrayQry();//note that since db connection is already made, no arguments used in creating the object
$qry->set_tbl("contacts"); //the table to insert into
    foreach($insert_info as $arr){
        $qry->set_update_array($arr);//the array of data
        $qry->execute('i'); //which action to execute
    }

//If you open the db conection outside the class you should also close it  when finished, else it is not necessary if class is handling connection
mysql_close($conn);
//*/
?>
class.ArrayQry.php
<?php
/**
 * ArrayQry
 *
 * @package   MySQL Array Query
 * @author    Mark Berube
 * @license   Distributed under GNU/GPL
 * @version   0.1
 * @access    public
 */
class ArrayQry{
    var $result;
    var $result_val_type;
    var $tbl = false;
    var $qry = false;
    var $qry_type;
    var $update_arr = false;
    var $where_clause = false;
    var $conn_to_db;
    var $conn_array;
    var $error = false;
    var $debug = false;

    /**
     * ArrayQry::__construct()
     * @desc object constructor
     * @param array $conn_array
     * @param bool $conn_to_db
     * @return void
     */
    function __construct($conn_array=false,$conn_to_db=false){
        $this->conn_to_db = $conn_to_db;
        $this->conn_array = $conn_array;
    }

    /**
     * ArrayQry::set_qry()
     * @desc Sets the MySQL qry to be executed
     * @param string $qry
     * @return void
     */
    function set_qry($qry){
        $this->qry = $qry;
    }

    /**
     * ArrayQry::set_tbl()
     * @desc Sets the table name for certain function to use in the assembly of MySQL qry to be executed
     * @param string $tbl
     * @return void
     */
    function set_tbl($tbl){
        $this->tbl = $tbl;
    }

    /**
     * ArrayQry::set_where()
     * @desc Sets a 'where clause' for the update query. The where clause will be appended to the end of an update qry to define which record(s) to affect.
     * @param string $where
     * @return void
     */
    function set_where($where){
        $this->where_clause = $where;
    }

    /**
     * ArrayQry::set_update_array()
     * @desc Sets the assoc array of info to be used in the MySQL insert and update qry to be executed
     * @param array $update_arr
     * @return void
     */
    function set_update_array($update_arr){
        $this->update_arr = $update_arr;
    }

    /**
     * ArrayQry::debug()
     * @desc If function is called with no args it will echo errors if something is missing. Default is off
     * @param bool $val
     * @return void
     */
    function debug($val=true){
        $this->debug = $val;
    }

    /**
     * ArrayQry::execute()
     * @desc execute query object after all parameters set
     * @param string $action
     * @return array if action is select else true or false
     */
    function execute($action){
        if($this->conn_to_db){
            $conn_array = $this->conn_array;
            $conn = mysql_connect($conn_array['host'], $conn_array['user'], $conn_array['pw'])or die("Unable to connect to MYSQL because: ".mysql_error());
            $db_select = mysql_select_db($conn_array['db'],$conn) or die("Could not select '".$conn_array['db']."' database because: ".mysql_error());
        }
        switch($action){
            case 's':
                //select will return array, either empty or populated
                $this->result_val_type = 'array';
                return $this->db_qry_get_rows();
            break;
            case 'u':
                //update will return true or false
                $this->result_val_type = 'bool';
                $res = $this->db_update_record($this->tbl, $this->update_arr, $this->where_clause);
                $this->result = $res;
            break;
            case 'i':
                //insert will return true of false
                $this->result_val_type = 'bool';
                $res = $this->db_add_record($this->tbl, $this->update_arr);
                $this->result = $res;
            break;
            case 'd':
                //delete will return true of false
                $this->result_val_type = 'bool';
                $res = $this->db_rem_record();
                $this->result = $res;
            break;
        }
        if($this->conn_to_db){
            mysql_close($conn);
        }
        if($this->debug){
            if($this->error){
                echo $this->error;
                return false;
            }
        }
    }

    /**
     * ArrayQry::db_qry_get_rows()
     * @desc Assembles 'select' qry results to a 2-dim assoc array. This function is called with the 's' action
     * @return array if executed without error else false
     */
    function db_qry_get_rows(){
        $rows = array();
        		if($this->qry == false){
            	$this->error = "You must set a query (using set_qry()) before executing a 'select'";
            	return false;
        		}
        $res = mysql_query($this->qry);
            if($res){
                $cnt = 0;
                    while($row = mysql_fetch_assoc($res)){
                        foreach ($row as $key => $val){
                            $rows[$cnt][$key] = $val;
                        }
                    $cnt++;
                    }
                mysql_free_result($res);
            }
        return $rows;
    }

    /**
     * ArrayQry::db_add_record()
     * @desc Inserts an Assoc array of info into db using array keys as the column names and the array vals as the data. This function is called with the 'i' action
     * @param string $tbl
     * @param array $update_arr
     * @return true if executed without error
     */
    function db_add_record($tbl,$update_arr){
        if($this->tbl == false){
            $this->error = "You must set a table (using set_tbl()) before executing an 'insert'";
            return false;
        }
        if($this->update_arr == false){
            $this->error = "You must set an assoc array (using set_update_array()) before executing an 'insert'";
            return false;
        }
        $cols = $this->arrayKeys2str($update_arr);
        $vals = $this->array2str($update_arr,true);
        $qry = "INSERT INTO $tbl ";
        $qry .= "($cols) VALUES ($vals)";
            if(mysql_query($qry)){
                return true;
            }else{
                $this->error = "MySQL error ".mysql_errno().": ".mysql_error()."\n<br>When executing:<br>\n$qry\n<br>";
                //echo $error;
                return false;
            }
    }

    /**
     * ArrayQry::db_update_record()
     * @desc Updates a db record using info from assoc array, the array keys as the column names to update, and the array vals as the data. This function is called with the 'u' option
     * @param string $tbl
     * @param array $update_arr
     * @param string $where
     * @return true if executed without error
     */
    function db_update_record($tbl,$update_arr,$where){
        if($this->tbl == false){
            $this->error = "You must set a table (using set_tbl()) before executing an 'update'";
            return false;
        }
        if($this->update_arr == false){
            $this->error = "You must set an assoc array (using set_update_array()) before executing an 'update'";
            return false;
        }
        if($this->where_clause == false){
            $this->error = "You must set an sql-style 'where' clause (using set_where_clause()) before executing an 'update' ...ex: WHERE id='1'...";
            return false;
        }
        $qry = "UPDATE $tbl SET ";
        $qry .= $this->array2updateStr($update_arr);
        $qry .= $where;
            if(mysql_query($qry)){
                return true;
            }else{
                $this->error = "MySQL error ".mysql_errno().": ".mysql_error()."\n<br>When executing:<br>\n$qry\n<br>";
                //echo $error;
                return false;
            }
    }

    /**
     * ArrayQry::db_rem_record()
     * @desc Remove (or Delete) a db record by simply setting a 'delete' qry and executing with the 'd' action
     * @return true if executed without error
     */
    function db_rem_record(){
        if($this->qry == false){
            $this->error = "You must set a query (using set_qry()) before executing a 'delete'";
            return false;
        }

        if(mysql_query($this->qry)){
            return true;
        }else{
            $this->error = "MySQL error ".mysql_errno().": ".mysql_error()."\n<br>When executing:<br>\n$qry\n<br>";
            return false;
        }
    }

    /**
     * ArrayQry::array2str()
     * @desc Converts any array of values to a comma delimited string for display purposes. If quotes are desired around the values, set quotes arg to true, else default will be no quotes
     * @param array $arr
     * @param bool $quotes
     * @return string
     */
    function array2str($arr,$quotes=false){
        $str = '';
        $cnt = 0;
            foreach($arr as $x){
                $x = trim($x);
                if($quotes){
                	$str .= ($cnt < sizeof($arr)-1) ? "'$x', " : "'$x' ";
                }else{
                	$str .= ($cnt < sizeof($arr)-1) ? $x .', ' : $x;
                }
                $cnt++;
            }
        return $str;
    }

    /**
     * ArrayQry::arrayKeys2str()
     * @desc Converts the keys of an assoc array to a comma delimited string for display purposes. This func is called when assembling the column names for an insert action
     * @param array $arr
     * @return string
     */
    function arrayKeys2str($arr){
        $str = '';
        $cnt = 0;
            foreach($arr as $key => $val){
                $str .= ($cnt < sizeof($arr)-1) ? $key .', ' : $key;
                $cnt++;
            }
        return $str;
    }

    /**
     * ArrayQry::array2updateStr()
     * @desc Converts an assoc array to a comma delimited string of col_name='col_data' pairs for display purposes. This func is called when assembling the update string for the 'u' action
     * @param array $arr
     * @return string
     */
    function array2updateStr($arr){
        $str = '';
        $cnt = 0;
            foreach($arr as $key => $val){
                $val = trim($val);
                $str .= ($cnt < sizeof($arr)-1) ? "$key='$val', " : "$key='$val' ";
                $cnt++;
            }
        return $str;
    }
}

?>

Post to Twitter Post to Digg Post to Facebook Post to Google Buzz Send Gmail

Leave a Comment

Your email address will not be published. Required fields are marked *