Location: PHPKode > scripts > ArrayQry > 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;
    }
}

?>
Return current item: ArrayQry