Mysqli Select Operation

Description

Select records from the single table. You can provide columns to be selected and where clause with associative array of conditions with keys as columns and value as column value. Along with these function parameters, you can set group by columnname, order by columnname, limit, like, in , not in, between clause etc. This function will automatically creates query for you and select data.

You may need to set $parameter_types as data type of where condition columns before using this function. For examples if you have five columns then you need to pass data type of each column like "sssis" where s denotes the string data type, i denotes numeric data type.


Function Reference :
 
       			mysqli_result/array dbSelect(string $table_name [, array $columns=array()] [, assoc. array $select_where_condition=array()]) 
             

Parameters

Name Type Required Parameter Info Example
table_name string Yes The name of the table to select records. any table name like employee_table,user_table
columns array Optional Array of columns to be selected array("username","gender")
select_where_condition associative array Optional Associative array with key as column name and values as column value for where clause. array("gender"=>"male")

Examples

  
          <?php 
         $sdb= new SimplifiedDB(); 
         $sdb->dbConnect("localhost","username","password","dbuser");
         $sdb->parameter_types="s";
         $sdb->dbSelect("users",array("firstName","email"),array("gender"=>"male"));
		 ?> 
         

Result

Returns result object by default. You can set $sdb->output_array=true, to return array as output.
If you use print_r($row) in
while ($rows = $result->fetch_array()) {
print_r($row);
}
it will show output as in following format.

Array ( [0] => Ram [firstName] => Ram [1] => y.ram11@gmail.com [email] => y.ram11@gmail.com ) Array ( [0] => Sachine [firstName] => Sachine [1] => y.ram11@gmail.com [email] => y.ram11@gmail.com )

Debuging

Debug Operation Result/Output
$sdb->query SELECT `firstName`,`email` FROM `users` WHERE `gender`=?
$sdb->last_insert_id
$sdb->rows_affected
$sdb->message_info Connected to database
$sdb->error_info

More Examples

 
           // Most basic example, selecting all columns and rows of table 'users'
            $result=$sdb->dbSelect("users"); 
           
           //selecting all columns of table 'users' where gender='male'
            $sdb->parameter_types="s";
            $result=$sdb->dbSelect("users","",array("gender"=>"male")); 
            
           // Applying group by, order by and limit conditions
            $sdb->group_by_column="role_id";
            $sdb->order_by_column="firstName";
            $sdb->limit_val="0,10";
            $sdb->parameter_types="s";
            $result=$sdb->dbSelect("users",array("firstName","email"),array("gender"=>"male"));
            
           // Example of using != operator, similary you can use any valid operator like '>','<','>=','<=',
            $sdb->parameter_types="s";
            $result=$sdb->dbSelect("users",array("firstName","email"),array("gender !="=>"male"));
            
           // Example of using 'like'
            $sdb->parameter_types="ss";
            $sdb->like_cols=array("firstname"=>"r%");
            $result=$sdb->dbSelect("users",array("firstName","email"),array("gender"=>"Male")); 
            
           // Example of using 'or' instead of 'and' in where condition
            $sdb->parameter_types="si"; 
            $sdb->and_or_condition="or";
            $result=$sdb->dbSelect("users",array("firstName","email"),array("gender"=>"male","role_id"=>10));
           
           // Example of using count(*), you needs to make backticks(`)="" off to use that.
            $sdb->backticks="";
            $sdb->parameter_types="i";
            $result=$sdb->dbSelect("users",array("Count(*)","email"),array("role_id"=>10));
           
           // Example of using Max, you needs to make backticks(`)="" off to use that.
            $sdb->backticks="";
            $result=$sdb->dbSelect("users",array("Max(userid)"));
            
            // Example of using between keyword
            $sdb->between_columns=array("dob"=>"1980-01-01","dob1"=>"1986-01-01");
            $sdb->parameter_types="ss"; 
            $result=$sdb->dbSelect("users",array("userid"));
           // Example of using 'in' claues
            $sdb->in=array("role_id"=>"'10','8','12'");
            $result=$sdb->dbSelect("users",array("userid"));
            
           // Example of using 'not in' clause
            $sdb->not_in=array("role_id"=>"'10','8','12'");
            $result=$sdb->dbSelect("users",array("userid"));
          
           // Example of using 'group by ' and 'having'
            $sdb->group_by_column="role_id";
            $sdb->having="sum(visits)>10 ";
            $result=$sdb->dbSelect("users",array("userid"));