Page 1 of 1

help in db wrapper class ??

Posted: Sun May 20, 2007 4:43 am
by PHPycho
I had the following db wrapper class:
db.class.php

Code: Select all

<?php
class Db{
	var $linkID;
	var $queryID;		
	/* Open Database linkID */
	function Db(){	
		$this->linkID = mysql_connect(DB_HOST, DB_USER, DB_PASS);
		mysql_select_db(DB_NAME);
	}
			
	}	
	function doQuery($sql){			
		$this->queryID = @mysql_query($sql,$this->linkID);						  
		return $this->queryID;
	}	
		
	/* Fetch Data as Array */
	function doFetchArr(){
		return mysql_fetch_array($this->queryID);
	}
	
	
	
	function doFetchNum(){
		return mysql_fetch_row($this->queryID);
	}		
	
	/* Return the number of fields in the most recently retrieved result */
    function getNumCols(){
        return mysql_num_fields($this->queryID);
    }
	
	/* Free the Result */
	function doFreeResult(){
		return mysql_free_result($this->queryID);
	}	
	
	/* Close Database linkID */
	function doClose(){
		mysql_close($this->linkID);
	}		

}
?>
when i tried to run another query in the loop of one query , it only shows the results of one. For example:

Code: Select all

require_once("db.class.php");
$Db = new Db();
$sql = "SHOW TABLES FROM ".DB_NAME;
$result = $Db->doQuery($sql);

while ($row = $Db->doFetchNum()) {
	//$Db1 = new Db();
    echo "Table: {$row[0]}<br />";
	$sql2 = "SELECT * FROM {$row[0]}";
	$result2 = $Db->doQuery($sql2);
	$num_fields = $Db->getNumCols();
	//$Db->doFreeResult();
	for($i = 0; $i < $num_fields; $i++){
		echo mysql_field_name($result2, $i)."<br />";
	}
	
	
	
}
In above it returns the field names of only one table.
but if we create another instance of Db class inside the loop and use it , it works.

What i want ?
- i want to modify the db wrapper class to avoid such case ie no need to create another object inside the loop for multilevel queries.

thanks in advance to all of you

Posted: Sun May 20, 2007 7:16 am
by feyd
You will need to either return the query reference, or create another object that does query specific operations (such as fetching, row counts, etc.)

Posted: Sun May 20, 2007 7:52 am
by onion2k
Just use ADODB Lite. It's ace. There's no point reinventing the wheel.

Posted: Sun May 20, 2007 12:04 pm
by maliskoleather
i use something simmilar to this on most of my projects:

Code: Select all

class _MySQL{

  function __construct(){
      $this->user = 'username';
      $this->pass = 'password';
      $this->db = 'database';
      $this->host = 'host';
  }
  
  function connect(){
      $connection = @mysql_connect($this->host, $this->user, $this->pass)
          or $this->error('DB Connect Error[ln:'.__LINE__.']');
      mysql_select_db($this->db)
          or $this->error('DB Select Error[ln:'.__LINE__.']');
      return $connection;
  }
  
  function doQuery($sql){
      
      $res = mysql_query( $sql, $this->connect() ) or die( $this->error('DB Query Error[ln:'.__LINE__.']') );
      
      $rows = mysql_num_rows($res);
      
      //dont cache if nothing returned from db
      if( $rows==0 ){
          return FALSE;
      }elseif( $rows==1 ){
          //just return a singe array... no need for multi-dimensional crap
          $results = mysql_fetch_assoc($res);
      }else{
          //return multi-dimensional array
          $results = array();
          while($row = mysql_fetch_assoc($res) ){
              $results[] = $row;
          }
      }
      
      //return the new results
      return $results;
      
  }
}
though ADODB Lite or MDB2 would probably be easier to implement and use

Re: help in db wrapper class ??

Posted: Sun May 20, 2007 3:16 pm
by AKA Panama Jack
Your code needs to pass the queryID each time use this instead.

Code: Select all

<?php
class Db{
	var $linkID;
	var $queryID;		
	/* Open Database linkID */
	function Db(){	
		$this->linkID = mysql_connect(DB_HOST, DB_USER, DB_PASS);
		mysql_select_db(DB_NAME);
	}
			
	}	
	function doQuery($sql){			
		$this->queryID = @mysql_query($sql, $this->linkID);						  
		return $this->queryID;
	}	
		
	/* Fetch Data as Array */
	function doFetchArr($queryid){
		return mysql_fetch_array($queryid);
	}
	
	function doFetchNum($queryid){
		return mysql_fetch_row($queryid);
	}		
	
	/* Return the number of fields in the most recently retrieved result */
	function getNumCols($queryid){
		return mysql_num_fields($queryid);
	}
	
	/* Free the Result */
	function doFreeResult($queryid){
		return mysql_free_result($queryid);
	}	
	
	/* Close Database linkID */
	function doClose(){
		mysql_close($this->linkID);
	}		

}
?>

Now use the cahnged code below to access your class.

Code: Select all

require_once("db.class.php");
$Db = new Db();
$sql = "SHOW TABLES FROM ".DB_NAME;
$result = $Db->doQuery($sql);

while ($row = $Db->doFetchNum($result)) {
	echo "Table: {$row[0]}<br />";
	$sql2 = "SELECT * FROM {$row[0]}";
	$result2 = $Db->doQuery($sql2);
	$num_fields = $Db->getNumCols($result2);
	for($i = 0; $i < $num_fields; $i++){
		echo mysql_field_name($result2, $i)."<br />";
	}	
	$Db->doFreeResult($result2);
}
$Db->doFreeResult($result);
$Db->doClose();
You need to pass the resultID for the proper query. Your code was using the resultID of your FIRST query all of the time.