PHP: reusing database class

Question

Hi,

I built a class that allows me to do:

$db->query($query);

and it works perfectly, although if I want to do:

$db->query($query);
while($row = $db->fetch_assoc()){
    $db->query($anotherquery);
    echo $db->result();
}

it "breaks" the class. I don't want to constantly have to redeclare my class (eg: $seconddb = new database()), is there a way to get around this? I want to be able to reuse $db within $db, without overwriting the "outside" db. currently I'm create an array of data (from db->fetch_assoc() then doing a foreach and then doing the db call inside that:

$db->query('SELECT * FROM table');
while($row = $db->fetch_assoc()){
    $arr[] = $row;
}

foreach($arr as $a){
    $db->query(); // query and processing here
}

Is this the best method or am I missing the obvious? Should I consider passing a connection link ID with the database connection?

Answer 1

Have $db->query() return a "query" object which you can then iterate for the query results.

Answer 2

You should consider having the $db->query() method return a result so you don't need to save the states in the result.

Ex.

// psaudo
class DB{

  function query($sql){
    $db->query('SELECT * FROM table');
    $arr = array();
    while($row = $db->fetch_assoc()){
      $arr[] = $row;
    }
    return $arr;
  }

}

But the way, having a nested query loop like your example may be very slow and not scale up.

Answer 3

You should use

$query = $this->db->query("YOUR QUERY");

foreach ($query->result_array() as $row)
{
   echo $row['title'];
   echo $row['name'];
   echo $row['body'];
}

Answer 4

Have a look at my simple abstraction