Help with a PHP database class...

[Hall of Famer]

Well I've designed this database class extending from mysqli, it has been working out nicely until I try to run a while loop to retrieve more than one row of data:

 

Class file:

<?php

class Database extends mysqli{
  protected $mysqli, $result, $table, $column, $where, $value, $limit, $order, $query;

  public function __construct($host, $user, $pass, $db){
    $this->mysqli = new mysqli($host, $user, $pass, $db) 
	   or die("Error connecting to the database {$db}");

  }

  public function __destruct(){
$this->mysqli->close();
  }

  protected function prepareQuery(){
if ($prepare = $this->mysqli->prepare($this->query)) {
  trigger_error("Problem preparing query ($this->query) ".$this->mysqli->error, E_USER_ERROR);
}
return $prepare;
  }
  
  protected function reset(){
unset($this->table);
unset($this->column);
unset($this->where);
unset($this->value);
unset($this->limit);
unset($this->order);
    unset($this->result);
unset($this->query);
  }
  
  public function table($table){
    $this->table = $table;
return $this;
  }

  public function addcolumn($column, $key = "false"){ 
    $i = 0;  
    foreach($column as $col => $type){
  $this->column .= "{$col} {$type}";
  $this->column .= ($i == 0 and $key == "true")?" NOT NULL AUTO_INCREMENT PRIMARY KEY ":"";
  $this->column .= ($i < count($column) - 1)?", ":"";
  $i++;
}
return $this;
  }
  
  public function managecolumn($column, $type="", $action = ""){
    if($action == "add") $this->column = "ADD column {$column} {$type}";
if($action == "unique") $this->column = "ADD UNIQUE ({$column})";
if($action == "drop") $this->column = "DROP column {$column}";
if($action == "modify") $this->column = "MODIFY {$column} {$type}";
if($action == "action" and is_array($column)) $this->column = "CHANGE {$column[0]} {$column[1]} {$type}";
return $this;
  }
  
  public function fromcolumn($column){  
    $this->column = (is_array($column))?implode(",", $column):$column;    
return $this;
  }

  public function setcolumn($column){  
    $i = 0;
    foreach($column as $col => $val){
      $this->column .= "{$col} = '{$val}'";
      $this->column .= ($i < count($column) - 1)?", ":"";
      $i++; 
    }  
return $this;
  }

  public function intocolumn($column){  
    $this->column = (is_array($column))?implode(", ", $column):$column;
    $this->column = "({$this->column})";    
return $this;
  }

  public function value($value){
    $i = 0;  
    foreach($value as $val){
      $valuestring .= "'{$val}'";
  $valuestring .= ($i < (count($value)-1))?", ":" "; 
      $i++;
    }
    $this->value = $valuestring;  
return $this;
  }
  
  public function where($where, $comparison = "", $logic = ""){
    $i = 0;
    $comparison = (empty($comparison))?"=":$comparison;
    $logic = (empty($logic))?array_fill(0,count($where)," AND"):$logic;
    foreach ($where as $col => $val){
  $wherestring .= (is_array($comparison))?" {$col} {$comparison[$i]} '{$val}'" : " {$col} {$comparison} '{$val}'";
  $wherestring .= ($i < (count($where)-1))?" {$logic[$i]}" :" ";
  $i++;
}
$this->where = $wherestring;
return $this;
  }
  
  public function limit($limit){
    $this->limit = $limit;
return $this;
  }
  
  public function order($order){
    $this->order = $order;
return $this;
  }

  public function fetch($action = ""){
    if($action == "row") $result = $this->result->fetch_row(); 
    if($action == "assoc") $result = $this->result->fetch_assoc();
    if($action == "array") $result = $this->result->fetch_array();
if($action == "field") $result = $this->result->fetch_field(); 
if($action == "fields") $result = $this->result->fetch_fields(); 
    if($action == "object")  $result = $this->result->fetch_object();
if($action == "all")  $result = $this->result->fetch_all();
    if($action == "num") $result = $this->result->num_rows();
    $this->reset();
    return $result;    
  }

  public function create(){
    $query = "CREATE TABLE {$this->table} ({$this->column})";	
$this->mysqli->query($query) or die("Mysql Error, cannot create table {$this->table}");
    $this->reset();
  }
  
  public function alter(){
    $query = "ALTER TABLE {$this->table} {$this->column}";	
$this->mysqli->query($query) or die("Mysql Error, cannot alter table {$this->table}");
    $this->reset();
  }
  
  public function drop(){
    $query = "DROP TABLE {$this->table}";
$this->mysqli->query($query) or die("Mysql Error, cannot drop table {$this->table}");
    $this->reset();
  }
  
  public function select(){
    if(empty($this->column)) $this->column = "*";
    $query = "SELECT {$this->column} FROM {$this->table}";
$query .= (!empty($this->where))?" WHERE {$this->where}":"";
$query .= (!empty($this->order))?" ORDER BY {$this->order}":"";
$query .= (!empty($this->limit))?" LIMIT {$this->limit}":"";
    $this->result = $this->mysqli->query($query);
return $this;
  }

  public function update(){
    $query = "UPDATE {$this->table} SET {$this->column} WHERE {$this->where}";
    $this->mysqli->query($query) or die("Mysql Error, cannot update table {$this->table}");
    $this->reset();
  }

  public function insert(){
    $query = "INSERT INTO {$this->table} {$this->column} VALUES ({$this->value})";
    $this->mysqli->query($query) or die("Mysql Error, cannot insert into table {$this->table}");
    $this->reset();
  }

  public function delete(){
    $query = "DELETE FROM {$this->table} WHERE {$this->where}";
    $this->mysqli->query($query) or die("Mysql Error, cannot delete from table {$this->table}");
    $this->reset();
  }

}
?>

 

Example File:

include("classes/class_database.php");
$db = new Database($dbhost, $dbuser, $dbpass, $dbname);
while($row = $db->table($prefix."adoptables")->fromcolumn("*")->order("id")->limit(2)->select()->fetch("array")){
  echo $row['type'];
  echo "<br>";
}

 

So what I am trying to accomplish is to write one single line of codes to accomplish what people normally have to do in three lines:

 

$query = "SELECT * FROM {$prefix}adoptables ORDER by id LIMIT 2";
$result = mysql_query($result);
while($row = mysql_fetch_array($result))

 

to:

 

while($row = $db->table($prefix."adoptables")->fromcolumn("*")->order("id")->limit(2)->select()->fetch("array"))

 

It appears to trigger an infinite loop, and I cant fix it myself. Can anyone of you please lemme know how I can make my database class codes to work with while loop? Thanks.

 

 

[KevinM1]

It's an infinite loop because each turn in your while-loop rebuilds the initial query result before fetching the data.  You won't be able to do what you want in one line of code.  You'll need to build and run your query outside of the loop, then fetch the data from the result inside the loop.

[xyph]

You could always check if the $result is equal to FALSE before you assign it.

 

  public function select(){
    if( !$this->result ) {
    	if(empty($this->column)) $this->column = "*";
    	$query = "SELECT {$this->column} FROM {$this->table}";
	$query .= (!empty($this->where))?" WHERE {$this->where}":"";
	$query .= (!empty($this->order))?" ORDER BY {$this->order}":"";
	$query .= (!empty($this->limit))?" LIMIT {$this->limit}":"";
    	$this->result = $this->mysqli->query($query);
    }
    return $this;
  }

 

You would then have to call $db->reset(); after each query though

 

Here's a cleaner fetch function as well

  public function fetch($action = ""){
    $num = $this->result->num_rows;
if( !method_exists($this->result,'fetch_'.$action) ) return FALSE;
return $this->result->{'fetch_'.$action}();
  }

[the182guy]

Write A function in the db class rather than putting a whole while loop on one line of code. A good example of this is Joomla's loadAssocList and loadObjectList functions, which are generic methods for grabbing a whole recordset from any query without having to keep wring the while loop.

[Hall of Famer]

I see, so the entire mysql query is reset whenever this while loop reaches the end of each run, thats why it continues as an infinite loop. Guess I will have to find a way to prevent this from happening. I know it is possible to retrieve multiple rows from database by assigning a two-dimensional array, but I personally do not like it since a two dimensional array is difficult to manage. It also makes the codes look quite complicated for my fellow coders to interpret.

 

You could always check if the $result is equal to FALSE before you assign it.

 

  public function select(){
    if( !$this->result ) {
    	if(empty($this->column)) $this->column = "*";
    	$query = "SELECT {$this->column} FROM {$this->table}";
	$query .= (!empty($this->where))?" WHERE {$this->where}":"";
	$query .= (!empty($this->order))?" ORDER BY {$this->order}":"";
	$query .= (!empty($this->limit))?" LIMIT {$this->limit}":"";
    	$this->result = $this->mysqli->query($query);
    }
    return $this;
  }

 

You would then have to call $db->reset(); after each query though

 

Here's a cleaner fetch function as well

  public function fetch($action = ""){
    $num = $this->result->num_rows;
if( !method_exists($this->result,'fetch_'.$action) ) return FALSE;
return $this->result->{'fetch_'.$action}();
  }

 

Thank you so so much for this kind and informative reply, I will give a try on my site later today to see whether it works and let you know if I encounter any issues.

[KevinM1]

One more thing - since you're extending MySQLi, you don't need a $mysqli data member.  Why?  Because your class is MySQLi, with extra bits added on top.  Anything that's public or protected in MySQLi is automatically available to you.  So, in your constructor, if you need to access the parent's (MySQLi's) constructor, you can simply do this:

 

parent::__construct(/* args */);

 

Before you go much further, you should really read up on the basics of OOP.  A good place to start is: http://php.net/manual/en/language.oop5.php

[jcbones]

If an extended class has no constructor, then the constructor of the parent is used automatically.  Removing the __construct() function from your extended class, you could still pass the arguments, and they would be used in the parent::__construct() automatically.

 

Example

<?php
class me {

function __construct($me) {
echo $me . '<br />';
}

}

class you extends me {

}

new you('Jcbones');

?>

 

Thereby, as Nightslyr posted, you don't have the extra datamember that just points back to itself.  Here is another great OOP tutorial.

[xyph]

Sorry, I saw that and totally forgot to mention it.

 

You want to do one or the other, not both

[Hall of Famer]

I see, thanks for bringing it up to me. In fact, the original plan was to add the script path and table prefix as new parameters initiated in the class constructor, but for some reason it was not carried out. Guess I will just use this code as mentioned by Nightslyr then:

 

parent::__construct($host, $user, $pass, $db);

[Hall of Famer]

Oh btw I've encountered a minor issue. Your script works perfectly for the while loop now, but it seems that I cannot execute another select or update query. I figured it is because the reset() method was not called, but I cant seem to find the appropriate location where the reset() method should be called. In fact, whenever the reset() method is called, the while loops stops working. Can you show me the way to reset the query after while loop reaches its end? Thanks.

[xyph]

Unsetting is a BAD idea for class properties. I changed your reset method to this

 

  public function reset(){
  	if( is_object($this->result) && method_exists($this->result, 'free') )
  		$this->result->free();
$this->table = FALSE; $this->column = FALSE;
$this->where = FALSE; $this->value = FALSE;
$this->limit = FALSE; $this->order = FALSE;
    $this->result = FALSE; $this->query = FALSE;
$this->table = FALSE;
  }

 

And I use this

 

while( $row = $db->table('users')->fromcolumn("*")->order("id")->limit(5)->select()->fetch('assoc') ) {
print_r( $row );
}
$db->reset();
echo "\n\n\n";
while( $row = $db->table('users')->fromcolumn("*")->order("name")->limit(2)->select()->fetch('assoc') ) {
print_r( $row );
}

[Hall of Famer]

I see, thanks for pointing it out for me.  I will give another try later tonight, it looks good so far.

[premiso]

Unsetting is a BAD idea for class properties.

 

I never heard of this, can you provide a link to the reasoning? I would love to give it a read.

[Hall of Famer]

Well is there a way to not have to write the $db->reset() in the script file? I mean, the method reset() is automatically called once the while loop fails?

[xyph]

Unsetting is a BAD idea for class properties.

 

I never heard of this, can you provide a link to the reasoning? I would love to give it a read.

 

If you want to unset properties from within the class, then they probably don't need to be properties, and instead arguments in the methods that may require them. You generally want to reserve properties to values that are REQUIRED by the class. If you want some form of dynamic properties, then use an array... protected $vars = array(); and use a method to add/remove keys from this array.

 

There's no concrete reason beyond it goes against what OOP is trying to accomplish... do you really want to have to call isset() before trying to access any local properties that may have been unset() somewhere in the class?

 

It's possible to use unset() on properties successfully just like one can use the global keyword, but for newer programmers that might not fully understand why it can lead to issues, sometimes a DON'T DO IT will suffice. Other times, a short explanation is needed, but that's generally something one quickly figures out during their own research and exploration.

 

If you disagree, let me know. I'd love to talk about this further, as pretty much everything is subjective and I'd like to hear the merits on unsetting properties.

 

Well is there a way to not have to write the $db->reset() in the script file? I mean, the method reset() is automatically called once the while loop fails?

 

Of course - though I really don't see an issue with having to call reset() after a fetch();

  public function fetch($action = ""){
if( !method_exists($this->result,'fetch_'.$action) ) return FALSE;
$return = $this->result->{'fetch_'.$action}();
// The fetch_fields and fetch_all aren't meant to be looped, so we'll allow
// manual reset. The rest return NULL when they've reached the end, so we check
// for that
if( $return === NULL ) {
	$this->reset();
	return FALSE;
} else
	return $return
  }

 

You may want to call reset() after fetch_field or fetch_all calls as well.

[premiso]

If you disagree, let me know. I'd love to talk about this further, as pretty much everything is subjective and I'd like to hear the merits on unsetting properties.

 

Nah, never disagreed. Just curious about it as I never heard of it being an issue. But the reasoning gave makes sense.

[Hall of Famer]

Thank you so much for the help and support so far, I really appreciate this. Looks like there is a missing semicolon in the script though, but I fixed this myself. I plan to work on multi-select and multi-query methods in recent future, hopefully I will be able to figure that out.