Jump to content

use mysqli for accessing mysql

jkkenzie

By jkkenzie
in PHP Coding Help

 Share
Go to solution Solved by jkkenzie,

Recommended Posts

jkkenzie Advanced Member

jkkenzie

Posted
Posted

Hi,

I need help converting this class to use mysqli instead of using the deprecated functions like mysql_db_query e.t.c. I tried a couple of time but ended up with alot of errors.....

 

Here is the class:

<?php 
//This file contains depreceated methods and a complete revision is to be done 
//This class builds three kinds of queries. INSERT, SELECT, DELETE, and UPDATE  
class q_build 
{
 var $input, //This is an array of the input data for the UPDATE and INSERT queries.
 
     $info, //This is an array of the keys to be used for all the queries.
 
     $query, //This is where the output query is stored.
	 
	 $table, //The table to be queried.
	 
	 $id, //A store for the primary field or the key to be used in the query.

	 $countRow,
	 
	 $auto,
	 
	 $funcs,
	 	 
	 $extras; //A for aditional information currently used to store callback functions for data to be processed before it is inserted.

  ////////////////////////////////////////////////////////////////////////
  //                                                                    //
  //   insert_q()					                                   //
  //   This Method builds an insert query. It only requires:            //
  //     1. Fields to be affected, stored in the info property          //
  //     2. Data, stored in the input property                          //
  //     3. Callback functions(Optional), stored in the                 //
  //        extras property                                             //
  //                                                                    //
  ////////////////////////////////////////////////////////////////////////
	 
 function insert_q($table='')
 {
   $info=$this->info;
   $fields='';
   $values='';
   $input=$this->input;
   $d=0;
   if(is_array($info) && is_array($input)){
   foreach($info as $key=>$val)
   {
    if($d==2) $coma=',';
	else $coma='';
	$fields.=$coma.'`'.$key.'`';
	if(isset($this->extras[$key]) && function_exists($this->extras[$key])) { $nval=call_user_func($this->extras[$key], @$input[$val]); $values.=$coma."'".$nval."'"; }
	else $values.=$coma."'".@$input[$val]."'";
	$d=2;
   }
   $this->query='INSERT INTO `'.(!empty($table)?$table:$this->table).'`('.$fields.')'."\n";
   $this->query.='VALUES('.$values.');'."\n"; return true;}
   else{ $this->query='' ;return false;}
 }

  ////////////////////////////////////////////////////////////////////////
  //                                                                    //
  //   update_q()                                                       //
  //   This Method builds an update query. It only requires:            //
  //     1. Fields to be affected, stored in the info property          //
  //     2. Data, stored in the input property                          //
  //     3. Callback functions(Optional), stored in the                 //
  //        extras property                                             //
  //                                                                    //
  ////////////////////////////////////////////////////////////////////////
 
 function update_q($table='')   
 {
   $info=$this->info;
   $sq='';
   $input=$this->input;
   $d=0;
   if(is_array($info) && is_array($input) && !empty($this->id['field']) && (!empty($this->id['val']) || @$this->id['val']=='0')){
   foreach($info as $key=>$val)
   {
    if($d==2) $coma=',';
	else $coma='';
	if(isset($this->extras[$key]) && function_exists($this->extras[$key])) $nval=call_user_func($this->extras[$key], @$input[$val]); elseif(isset($input[$val])) $nval=$input[$val]; else $nval=false;
	if($nval!==false) $sq.=$coma.'`'.$key."`='".$nval."' ";
	$d=2;
   }
   $cndVar=(isset($this->id['state']))?"`".$this->id['field']."`".$this->id['state']['b4'].$this->id['val'].$this->id['state']['aft']:"`".$this->id['field']."` ='".$this->id['val']."'";
   $this->query="UPDATE `".(!empty($table)?$table:$this->table)."` SET ".$sq."WHERE $cndVar;\n"; unset($this->id['val'], $this->id['field']); return true;} 
   
   else{ unset($this->id['val'], $this->id['field']) ;$this->query='' ;return false;}
 }	 	 

  //////////////////////////////////////////////////////////////////////////////////////////////////
  //                                                                                              //
  //   select_q($start, $length, $all, $cnd, $cndv, $addtx, $conc, $scount                        //
  //   This Method builds an select query. It only requires:                                      //
  //                                                                                              //
  //////////////////////////////////////////////////////////////////////////////////////////////////
 
 function select_q
 (
		  $start=0,                 // The start position of the results i.e from first result(integer)

		  $length='',               //The length of the data to be selected(integer)

		  $all=false,               //Whether to selectt all fields or only those in the info property(bool)

		  $cnd=false,               //Whether to make a condition for the query(bool)

		  $cndv=array(),            //The array used to make the condition(array)

		  $addtx='',                //Text to be added before the end of the query(string)

		  $conc=false,              //Whether the added text requires an and or not(bool)

		  $scount=false ,           //Whether to select count as `rows` or not

		  $orderby=array(),         //

		  $myCount='count',         //Store The count query;

		  $table=''                 //
		  
 )   
 {   
  $funcs=$this->funcs;
  $info=$this->info;
  $this->funcs=NULL;
  if(is_array($info) && $all==false){
   $d=0;
   $fields='';
   $coma='';
   foreach($info as $key=>$val)
   {
	$distinct=($val=='dist')?'DISTINCT':'';
	if(!empty($funcs[$key])){
	 if(is_array($funcs[$key])){
	  foreach($funcs[$key] as $func)
	  {
	   $fields.="{$coma}{$distinct} {$func}( `{$key}` ) AS `{$key}_{$func}`";
	   $coma=','; $distinct="";
	  }
	 }
	 else $fields.="{$coma}{$distinct} {$funcs[$key]}( `{$key}` ) AS `{$key}`";
	}
	else $fields.=$coma.' '.$distinct.' `'.$key.'`';
    $coma=',';
   }}
  else $fields='*';
  $and='';
  if(is_array($cndv) && $cnd==true){
   $d=0;
   $condition='';
   foreach($cndv as $sval)
   {
	$and=($d==2)?(isset($sval['or'])?' OR ':' AND '):'';
	$opBrac=(isset($sval['opB'])?'( ':'');
	$clBrac=(isset($sval['clB'])?' )':'');
    if(!isset($sval['state']) && isset($sval['name'], $sval['val'])) { $condition.=$and.$opBrac.(!empty($sval['func'])?$sval['func'].'(':'').'`'.$sval['name']."`".(!empty($sval['func'])?')':'')."='".$sval['val']."'".$clBrac; $d=2;}
	elseif(isset($sval['state'], $sval['val']) && is_array($sval['state'])) { $condition.=$and.$opBrac.'`'.$sval['name'].'`'.@$sval['state']['b4'].$sval['val'].@$sval['state']['aft'].$clBrac; $d=2; }
   }}
  else $condition='1'; $chk=trim($condition);

  empty($chk)?$saver=' 1 ':$saver='';
  (empty($addtx) || empty($condition) || !$conc)?$and='':$and=' AND ';
  settype($start, 'integer');
  settype($length, 'integer');
  if(empty($length) || $length==0) $limit=''; else $limit=' LIMIT '.$start.', '.$length;
  if($orderby=='RAND()'){$orderStr=' ORDER BY RAND()';
   $limit=$orderStr.' LIMIT '.$length;
  }
  if(is_array($orderby)){
   $orderStr='';
   foreach($orderby as $order){
    if(!empty($order['field']) && !empty($order['order'])){
     if(empty($orderStr)){ $orderStr=" ORDER BY `".$order['field']."` ".$order['order'];} else $orderStr.=", `".$order['field']."` ".$order['order'];}} $limit=$orderStr.$limit;}
  $coStr=$myCount=='max'?'MAX':($myCount=='min'?'MIN':($myCount=='sum'?'SUM':'COUNT'));
  if($scount==true) $this->myCount="SELECT $coStr( ".(!empty($this->countRow)?'`'.$this->countRow.'`':'*')." ) AS `rows` FROM `".(!empty($table)?$table:$this->table).'` WHERE '.$saver.$condition.$and.$addtx.';';
  $this->query='SELECT '.$fields.' FROM `'.(!empty($table)?$table:$this->table).'` WHERE '.$saver.$condition.$and.$addtx.$limit;
  return true;
 }

  //////////////////////////////////////////////////////////////////////////////////////////////////
  //                                                                                              //
  //   delete_q($start, $length, $all, $cnd, $cndv, $addtx, $conc, $scount) 					  //
  //   This Method builds a delete query. It only requires:                                       //
  //                                                                                              //
  //////////////////////////////////////////////////////////////////////////////////////////////////
 
 function delete_q
 (
		  $cndv=array(),     //The array used to make the condition(array)
		  
		  $addtx='',         //Text to be added before the end of the query(string)
		  
		  $conc=false,       //Whether the added text requires an and or not(bool),
		  
		  $table=''          //the table being used
 )    
 {   
  $and='';
  if(is_array($cndv)){
   $d=0;
   $condition='';
   foreach($cndv as $sval)
   {
	$and=($d==2)?(isset($sval['or'])?'OR':'AND'):'';
	$opBrac=(isset($sval['opB'])?'( ':'');
	$clBrac=(isset($sval['clB'])?' )':'');
    if(!isset($sval['state']) && isset($sval['name'], $sval['val'])) { $condition.=$and.$opBrac.'`'.$sval['name']."`='".$sval['val']."'".$clBrac; $d=2;}
	elseif(isset($sval['state'], $sval['val']) && is_array($sval['state'])) { $condition.=$and.$opBrac.'`'.$sval['name'].'`'.@$sval['state']['b4'].$sval['val'].@$sval['state']['aft'].$clBrac; $d=2; }
   }}
  else $condition='1';
  $chk=trim($condition);
  empty($chk)?$saver=' 1 ':$saver='';
  (empty($addtx) || empty($condition) || !$conc)?$and='':$and=' AND '; 
  
  $this->query='DELETE FROM `'.(!empty($table)?$table:$this->table).'` WHERE '.$saver.$condition.$and.$addtx;
  return true;
 }
}

//This class adds a database connection and query execution functions to the q_build class
class DbQuery extends q_build
{

var $result,     //Store the result of a query

    $link,       //Stores the database connection link
	
    $password,   //Input the user password
	
    $host,      //Input the database host name
	
    $database,  //Input the database name
	
    $login;     //Input the user login

 //creates database connectin returning true on success and false on failure
 function connect()  
  {
   if($link = mysql_connect($this->host, $this->login, $this->password)) { $this->link=$link; return true;}
   else return false;
  }

 //Checks Whether the query qiven returns any results
 //returns false if it returns results true if no results are returned
 //if the query has an error it returns 'not determined'
 //It stores the number of results if false in the result property
 function dup_check($query) 
  {
   $database=$this->database;
    if($result=mysql_db_query($database, $query, $this->link))
      {
        if($resultlen=mysql_num_rows($result))
         {
        if($resultlen>=1) return false;
        elseif($resultlen<1) return true;
         }
         else return true;
      }
    else return 'not determined';
  }
 
 //Executes a query and returns the results in an array.
 //They are stored in the result property
 function return_db($select)
 {
   $database=$this->database;
  if($result=mysql_db_query($database, $select, $this->link))
   {
     if($confirm=@mysql_num_rows($result)){ if($confirm>0){
     $n=1;
     while($row=mysql_fetch_array($result, MYSQL_ASSOC)){
      $return[$n]=$row; $n++;
     }
	 mysql_free_result($result);return $return;} else return 'no results'; }
      else{ $result ;return 'query exequted';}
   }
  else return false;
 }
 
 //closes the database connection
 function cl(){mysql_close($this->link);}
 
 //select the last autoincrement
 function autoInc()
 {
	$result=mysql_db_query($this->database, "SELECT LAST_INSERT_ID() AS `LAST_ID`", $this->link);
	if(!$result) return false;
	$result=mysql_fetch_array($result, MYSQL_ASSOC);
	if(!$result) return false;
	//var_dump($result);
	$this->auto=$result['LAST_ID'];
	return $result['LAST_ID'];
 }
}
?>
Link to comment
Share on other sites
jkkenzie Advanced Member

jkkenzie

Posted
  • Author
Posted

The code is huge, sorry for posting the whole class: The only functions that am trying to use mysqli are the last four as below:

<?php 
//creates database connectin returning true on success and false on failure
 function connect()//(v0.01) 27/05/06  
  {
   if($link = mysql_connect($this->host, $this->login, $this->password)) { $this->link=$link; return true;}
   else return false;
  }

 //Checks Whether the query qiven returns any results
 //returns false if it returns results true if no results are returned
 //if the query has an error it returns 'not determined'
 //It stores the number of results if false in the result property
 function dup_check($query)//(v0.01) 27/05/06 
  {
   $database=$this->database;
    if($result=mysql_db_query($database, $query, $this->link))
      {
        if($resultlen=mysql_num_rows($result))
         {
        if($resultlen>=1) return false;
        elseif($resultlen<1) return true;
         }
         else return true;
      }
    else return 'not determined';
  }
 
 //Executes a query and returns the results in an array.
 //They are stored in the result property
 function return_db($select)
 {
   $database=$this->database;
  if($result=mysql_db_query($database, $select, $this->link))
   {
     if($confirm=@mysql_num_rows($result)){ if($confirm>0){
     $n=1;
     while($row=mysql_fetch_array($result, MYSQL_ASSOC)){
      $return[$n]=$row; $n++;
     }
	 mysql_free_result($result);return $return;} else return 'no results'; }
      else{ $result ;return 'query exequted';}
   }
  else return false;
 }
 
 //closes the database connection
 function cl(){mysql_close($this->link);}
 
 //select the last autoincrement
 function autoInc()
 {
	$result=mysql_db_query($this->database, "SELECT LAST_INSERT_ID() AS `LAST_ID`", $this->link);
	if(!$result) return false;
	$result=mysql_fetch_array($result, MYSQL_ASSOC);
	if(!$result) return false;
	//var_dump($result);
	$this->auto=$result['LAST_ID'];
	return $result['LAST_ID'];
 }
?>
Link to comment
Share on other sites
  • Solution
jkkenzie Advanced Member

jkkenzie

Posted
  • Author
  • Solution
Posted

I got it working by converting the last four functions to use mysqli as follows:

<?php
 //creates database connectin returning true on success and false on failure
 function connect()//(v0.01) 27/05/06  
  {
   if($link = mysqli_connect($this->host, $this->login, $this->password, $this->database)) { $this->link=$link; return true;}
   else return false;
  }

 //Checks Whether the query qiven returns any results
 //returns false if it returns results true if no results are returned
 //if the query has an error it returns 'not determined'
 //It stores the number of results if false in the result property
 function dup_check($query)//(v0.01) 27/05/06 
  {
   //$database=$this->database;
    if($result=mysqli_query($this->link, $query))
      {
        if($resultlen=mysqli_num_rows($result))
         {
        if($resultlen>=1) return false;
        elseif($resultlen<1) return true;
         }
         else return true;
      }
    else return 'not determined';
  }
 
 //Executes a query and returns the results in an array.
 //They are stored in the result property
 function return_db($select)
 {
   //$database=$this->database;
  if($result=mysqli_query($this->link, $select))
   {
     if($confirm=@mysqli_num_rows($result)){ if($confirm>0){
     $n=1;
     while($row=mysqli_fetch_array($result, MYSQLI_ASSOC)){
      $return[$n]=$row; $n++;
     }
	 mysqli_free_result($result);return $return;} else return 'no results'; }
      else{ $result ;return 'query exequted';}
   }
  else return false;
 }
 
 //closes the database connection
 function cl(){mysqli_close($this->link);}
 
 //select the last autoincrement
 function autoInc()
 {
	$result=mysqli_query($this->database, "SELECT LAST_INSERT_ID() AS `LAST_ID`", $this->link);
	if(!$result) return false;
	$result=mysqli_fetch_array($result, MYSQLI_ASSOC);
	if(!$result) return false;
	//var_dump($result);
	$this->auto=$result['LAST_ID'];
	return $result['LAST_ID'];
 }
?>
Link to comment
Share on other sites
This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
 Share
Go to topic listing
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.