Geek Answers Handbook

MySQL + PHP: implementing a good model

In the MVC-Model View Controller design, you implement the model to work separately and contain buisness logic by pulling information from the database.

I struggle so much with the design of implementing a good model. I know what information needs to be pulled from the database, I just do not know how to implement it. I think of the model as the program’s API, and I overload myself with issues like

  • What if I need to sort the fields?
  • What if I need to select a specific username / id?
  • What if I need to group by a specific field?
  • How badly will performance impact if I select * just so that the calling function may need any information that is pulled out?

My API / model becomes extremely bloated, having separate functions and requests (only configured / changed) for each function

for instance

$cart->getShoppingCart() $cart->getShoppingCartSortByTitle() $cart->getShoppingCartGroupByItemType()

I feel this makes the model extremely bloated and very attached, creating a lot of duplicate code. Maybe it’s better to have such a model,

Best idea

 $cart->getItems('title, price')->order_by('title');

Where 'title, price' are the mySQL fields that you can select, obviously checked by the getItems() function. Thus, it is not only limited to returning certain fields.

  • How could I achieve this?
  • Is this a really good model?
  • Are there any other things you guys could offer?
+4
source share
6 answers

Use Object Relational Mapping (ORM) ...

Try the Doctrine ORM project .

Another solution is CodeIgniter , it has the best library of active records. Very helpful.

If you still decide to write your own class, use the PHP5 method chain . The syntax will be prettier ...

+1
source

Code Management The Igniter framework allows you to perform such a query in the database, limiting the copy / paste code.
See Active Records Module in your documentation.

And I think that their model is not bad.

0
source

"Best idea" is definitely the best idea. You might think about how Django implements this because this is the approach that is used there. Django is written in Python, which simplifies some things a little, but you can also use the concepts there in PHP (only with a little less accuracy). Thus, when creating a request, a request object is created with methods such as order_by. Using these methods will change the state of the query, and only when the query is actually running, it needs to generate SQL and execute it in the database.

If you stick to the former, you can use “magic methods” with dynamic names, as many existing frameworks do. For instance,

 getShoppingCart_groupby getShoppingCart_orderby

You will have one catch-all method with a dynamic argument list that reads the name of the called function and executes the required behavior if it is valid (and throws a standard error "method not found" if it is not). This is basically the same as what you are doing now, but it would greatly simplify the code and untie your model. You will need PHP5 for this and you are looking for the __call magic method .

Julien mentioned the code igniter in his answer - writing good models is very difficult, so it's usually best for you to use the existing infrastructure. (but it is interesting to try!)

0
source

First of all, make sure that all the “questions” you ask yourself relate to the features that your application really needs right now. One of the biggest problems that I saw when developing new projects was speculative design. Add only what you need. Write unit tests for what you add. When you get to the point where you need additional features, if necessary, reconstruct the best design.

If you need all the features ahead, I still recommend a refactoring approach. Introduce a few features that you can see similar or contributing to "bloat". When you are done, take a step back and see if you can reorganize something more elegant or something that distributes responsibility more evenly between different objects and / or methods. Then continue. The various books "templates" and "refactoring" will help you here.

0
source

First of all, you should consider:

  • There is no good general model. Each project needs its own model.
  • easy to read, manageable code
  • do not repeat the same code (or requests), so if you have a function for a specific task and you want it to be ordered in another way, change it and do not clone it
  • Use complex data structures such as arrays or objects to send data to a function, so you don’t always have to change the parameters necessary for the function
  • resource usage. The more you want everything around, the overall solution, the more resources it will use.

How badly will performance impact if I select * just so that the calling function might need some information hidden down?

It depends on the loading of your site. Most of the time (unless you pull large drops and text) * is fine, but when resources are scarce, you need to specify columns. This way you can save I / O time.

I feel this makes the model extremely bloated and very attached, creating a lot of duplicate code. Maybe it’s better to have such a model,

Perhaps try the following:

First of all, for complex queries, I use this class, which I did a long time ago for MySQL. This helps to solve the problem.

 class sqlAssembler { private $data = array(); var $S = array(); var $F = array(); var $W = array(); var $G = array(); var $H = array(); var $O = array(); var $L = array(); //Clause abbreviations var $clauselist = array ( 'S' => 'SELECT', 'F' => 'FROM', 'W' => 'WHERE', 'G' => 'GROUP BY', 'H' => 'HAVING', 'O' => 'ORDER BY', 'L' => 'LIMIT' ); //Default clause separators var $clausesep = array ( 'S' => ',', 'F' => ',', 'W' => ' AND ', 'G' => ',', 'H' => ' AND ', 'O' => ',', 'L' => '' ); function gen() { $tmp = ''; foreach ( $this->clauselist as $area => $clause ) { if ( count($this->{$area}) ) { $tmp .= ($clause != 'S' ? ' ' : '') . $clause . ' '; for ($i=0; $i < count($this->{$area}); $i++) { //echo $area = (string)$area; $tmp .= $this->{$area}[$i]; } //for } //if } //foreach return $tmp; } //function function genSection($area, $showsection = 0) { $tmp = ''; if ( count($this->{$area}) ) { for ($i=0; $i < count($this->{$area}); $i++) { $tmp .= $this->{$area}[$i]; } //for } //if return $tmp; } //function function clear() { foreach ($this as $area => $v) { //We only care about uppercase variables... do not declare any else variable with ALL UPPERCASE since it will be purged if (ctype_upper($area)) { if ($area == 'L') $this->$area = ''; else $this->$area = array(); } //if } //foreach } //function public function add($area, $str, $criteria = 1, $sep = '#') { if ($criteria) { if ($sep == '#') $sep = $this->clausesep[$area]; //Postgres' OFFSET should be set like: $str = '25 OFFSET 0' //Not very neat I know, but fuck it if ($area == 'L') { $this->{$area} = array(); } //if //$ref = $this->$area; $this->{$area}[] = (count($this->$area) ? $sep : '').$str; return count($this->$area)-1; } //if } //function public function del($area,$index) { if ( isset($this->{$area}[$index]) ) unset($this->{$area}[$index]); else trigger_error("Index nr. {$index} not found in {$area}!",E_USER_ERROR); } //function //-*-* MAGIC CHAIN FUNCTIONS public function S($str,$criteria = 1,$sep = '#') { $this->add(__FUNCTION__,$str,$criteria,$sep); return $this; } //function public function F($str,$criteria = 1,$sep = '#') { $this->add(__FUNCTION__,$str,$criteria,$sep); return $this; } //function public function W($str,$criteria = 1,$sep = '#') { $this->add(__FUNCTION__,$str,$criteria,$sep); return $this; } //function public function G($str,$criteria = 1,$sep = '#') { $this->add(__FUNCTION__,$str,$criteria,$sep); return $this; } //function public function H($str,$criteria = 1,$sep = '#') { $this->add(__FUNCTION__,$str,$criteria,$sep); return $this; } //function public function O($str,$criteria = 1,$sep = '#') { $this->add(__FUNCTION__,$str,$criteria,$sep); return $this; } //function public function L($str,$criteria = 1,$sep = '#') { $this->add(__FUNCTION__,$str,$criteria,$sep); return $this; } //function } //_sql

Perhaps try the following:

 function getShoppingCart($d) { $xx = new sqlAssembler(); $xx->S('*')-> F('items')-> //Notice, that we specified a criteria... if $d['id_item'] exists it will be joined to the WHERE clause, if not it will be left out W("(id_item > '{$d[id_item]}')",$d['id_item'])-> //Same here O("dt DESC",$d['date']) $sql = echo $xx->gen(); //id_item = 11, date = 2009-11-12 //$sql = "SELECT * FROM items WHERE (id_item > '11') ORDER BY dt DESC"; //id_item = null, date = null //$sql = "SELECT * FROM items"; $data = sqlArray($sql); //... handle data }
0
source

More articles:


All Articles