help in db wrapper class ??

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
User avatar
PHPycho
Forum Contributor
Posts: 336
Joined: Fri Jan 06, 2006 12:37 pm

help in db wrapper class ??

Post 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
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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.)
User avatar
onion2k
Jedi Mod
Posts: 5263
Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com

Post by onion2k »

Just use ADODB Lite. It's ace. There's no point reinventing the wheel.
User avatar
maliskoleather
Forum Contributor
Posts: 155
Joined: Tue May 15, 2007 2:19 am
Contact:

Post 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
User avatar
AKA Panama Jack
Forum Regular
Posts: 878
Joined: Mon Nov 14, 2005 4:21 pm

Re: help in db wrapper class ??

Post 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.
Post Reply