PHP/MySQL isolating database access in a class - how to handle multiple row result set in an OOP manner

Question

PHP/MySQLisolating database access in class - how to handle multiple row Selects

Here’s a coding question. I isolated all DB access functions in a class

<?php
class DB {
    var $conn;         
    function DBClass () {
                @$this-> conn = mysqli_connect (DB_SERVER, DB_USER, DB_PASS, DB_NAME);
    }
    function validateUser ($aUserid, $aPassword) {
                …  validation code – sql injection code etc..
                $sql = "Select userid, name, level From users where userid = '$aUserid' and password = '$aPassword'";
                $result = mysqli_query ( $this->conn, $sql );
                if (!$result || (mysqli_num_rows ($result) < 1)) {
                            return false;
                }
                $dbarray = mysqli_fetch_assoc ($result); // get a row
                return $dbarray;
    }

    function getProduct ($aProductid) {

                return $dbarray;
    }

    function getProductList () {  
                // <----------- this would be the problem function
    }
}

$DB = new DBClass();
?>

My calling routine:

<?php
        $dbarray = $DB->validateUser ($_POST['userid'], $_POST['password']); 
?>

No problem it works fine. I run into a problem with a result set of more than one row. Now I have to get back to the class object for each row. It’s no problem if I include the MySQL code in the calling routine, but I’d like to keep it isolated in my class and I’m not sure how to code it.

Any thoughts? Any examples?

Answer 1

you could save the result in an array and return it:

function getProductList () {
   $sql = "SELECT ...";
   $result = mysqli_query ( $this->conn, $sql );

   $myProducts = array();

   while ($row = mysqli_fetch_assoc($result))
      $myProducts[] = $row; // or array_push($myProducts, $row)
   }

   return $myProducts
}

As a result you'll have an array of arrays and each element of it will contain one row of the result.

Answer 2

You have a SQL injection right in your login page.

What happens if someone inputs that as password: xxx' OR 'yyy' <> 'x

Answer 3

If you use PHP 5.3.0 and mysqlnd, you can use the new function mysqli_fetch_all(). This returns an array of associative arrays.

If you use an earlier version of PHP, you could switch to using PDO, and use the function PDOStatement::fetchAll().

You ask in a comment what about a very large result set. It's true that an unbounded result set could cause the array to exceed your PHP memory limit and that would cause a fatal error and halt the script. But is this really a problem? How many products do you have? You could use LIMIT to make sure the query isn't unbounded.

---

Re the other part of your questions regarding going back to a class, I'd suggest making an Iterator class:

class DB implements IteratorAggregate
{
  protected $_data = array();

  public function getProductList() {
    // fetch all results from SQL query, stuff them into $this->_data
    return $this->getIterator();
  }

  public function getIterator() {
    return new ArrayIterator($this->_data);
  }
}

Now you can use the class in a foreach loop:

$db = new DB();
foreach ($db->getProductList() as $product) {
  // do something with each product
}

The IteratorAggregate interface means you can even do this:

$db = new DB();
$db->getProductList();
// ...other steps...
foreach ($db as $product) {
  // do something with each product
}

Of course you could only store one result set at a time with this method. If you used your DB class for any other queries in the meantime, it would complicate things. For this reason, most people don't try to write a single class to encapsulate all database operations. They write individual classes for each type of Domain Model they need to work with, decoupled from the database connection.