PHP MVC, PDO: loop through returned mysql data

Question

In my DB layer class I have a method that takes a select sql query as param:

public function select($sqlQuery) {             
  $stmt = $this->pdo->prepare($sqlQuery);
  $stmt->execute();
  $ret = $stmt->fetch(PDO::FETCH_ASSOC);
  return $ret;
}

I want to be able to return the fetched data to my model, and then loop through it in my view class, without using any PDO in the view.

How should this be done the right way?

I want to be able to reach the table rows as $row['testCol'] etc.

score 2 · Accepted Answer · answered Sep 27 '12 at 19:31

Call fetchAll() instead of fetch(), which will return a full multidimensional array.

public function select($sqlQuery) {             
  $stmt = $this->pdo->prepare($sqlQuery);
  $stmt->execute();
  $ret = $stmt->fetchAll(PDO::FETCH_ASSOC);
  return $ret;
}

$results = $your_object->select("SELECT * FROM yourtable");
// It's a 2D array!
var_dump($results);

score 2 · Answer 2 · edited May 23 '17 at 11:56

Model is a layer. Not a class or object.

And, if you write your code by adhering to SRP, the model layer would contain several different types of structures. As minimum you would have separate classes for domain business logic and storage abstraction.

What you have here is complete mess. Not only you lumping both domain logic and storage mechanism in same class (and pretend that it is "model"), but you are also exposing SQL directly to presentation layer.

If you were trying to build a proper model layer, the current view would request information from service instance (which would be the type of structures, that one would use to separate domain business logic from views and controller) and acquire the data it need.

The service would instantiate the proper domain object(s) and, based on state, previously set by controller, retrieve information into them using some form of storage abstraction (either data mapper, repository, unit of work, dao or some similar structure). Then, after performing required data manipulations on the filled domain object(s), it would return data to the view.

View, based on received information and previous signals from controller, would decide what sort of response should be generated. If the indicated for of response is HTML, it would use one or (usually) multiple templates to compose the output. Thought it might as well send just a HTTP location header, to trigger a redirect.

For further reading I would recommend this answer.

The point is: you currently have simplified the concepts of MVC to the point where even separation between presentation and model layer has been broken.

score 2 · Answer 3 · answered Sep 29 '12 at 15:03

You could easily return $stmt. It allows you to iterate over it and is accessible like PDO::FETCH_ASSOC:

$rows = $model->select($sqlQuery);

...

foreach($rows as $row)
{
    echo $row['testCol'], "\n";
}

Inside your function it's like:

public function select($sqlQuery) {             
  $stmt = $this->pdo->prepare($sqlQuery);
  $stmt->execute();
  return $stmt;
}

Keep it simple, that will help you in the long run. You only want something in your view that is accessible like an array and PDO offers you that out of the box. You can easily replace it later with - let's say - an array if you need to. Maybe not the best example with the array, but I'm sure you get the idea.

And just a note in the margin: Don't call it model, just call it database or similar. It better names the thingy which is always helpful.

PHP MVC, PDO: loop through returned mysql data

3 Answers3