V, I've got. Let's do M and maybe I'll end up with a C.

September 13, 2011 2:08 PM

I'm learning PHP and MySQL, trying to work from scratch before I ramping up to a framework. I've installed a templating system, just to play with it, so my templating/view logic feels well-organized. But how do I organize the other simple DB query code so it's more MVC-ish?

I'm using a template engine, and I end up with this in my index.php:


//Begin index.php

require_once('config.php');

// Include template (view) class for TinyButStrong

include_once('lib/tbs_class.php');
$TBS = new clsTinyButStrong;

LoadTemplate('templates/'.basename($_SERVER['SCRIPT_NAME'], '.php').'.html');

$site_title = 'Weights and Measures!';

// This would be considered model logic, I think -- (how) do I spin this off into another class file(s) or something?

$sql = 'SELECT * FROM billings';

$cur_month = date("m");
$month_minus_one = date("m") - 1;
$month_minus_two = date("m") - 2;

$cur_month_sql = 'SELECT * FROM billings WHERE MONTH(DATE) = '.$cur_month;

$month_minus_one_sql = 'SELECT * FROM billings WHERE MONTH(DATE) = '.$month_minus_one;

$month_minus_two_sql = 'SELECT * FROM billings WHERE MONTH(DATE) = '.$month_minus_two;

// MergeBlock is sending data from the database to the template engine, so I can reference the data in the template/view with the template engine's tags.

$TBS->MergeBlock('billings, billings2, billingsjs', $Db->GetRows($sql));

$TBS->MergeBlock('billings0', $Db->GetRows($cur_month_sql));

$TBS->MergeBlock('billings1', $Db->GetRows($month_minus_one_sql));

$TBS->MergeBlock('billings2', $Db->GetRows($month_minus_two_sql));

$TBS->Show();

$mysqli->close();

Any advice appreciated. I know people disagree about templating classes vs. plain PHP, and I know that a framework is SO worth it. But this is the path I've chosen for my next exercise and I'd like to see it through. Thanks!

posted by circular to computers & internet (17 answers total)

BTW, if the $Db stuff is confusing, I'm using a DB abstraction setup, TBSSQL, established in config.php:


include_Once("lib/tbssql_mysql.php");

$Db = new clsTbsSQL($srv='',$uid='',$pwd='',$db='',$drv=''); //make the connection

$srv="localhost";
$uid="dbuser";
$pwd="password";
$db="appdatabase";

$drv='';
$table="billings";

$Db->Connect($srv,$uid,$pwd,$db,$drv=''); // $drv is only for certain db's
posted by circular at 2:11 PM on September 13, 2011

Rather than trying to create M and C on top of a framework that only gives you V, why not try a framework with a more comprehensive MVC-style philosophy baked in, like Cake or Symfony?
posted by mkultra at 2:19 PM on September 13, 2011

BTW, I personally think you're going about this backwards:

circular: "trying to work from scratch before I ramping up to a framework"

For learning a new environment, good framework will force you into habits that reinforce standard practices, and you can always follow your code down the rabbit hole to understand what's going on. You'll hopefully find it easier (and more fun) to focus on the logic specific to your app than re-inventing the wheel.
posted by mkultra at 2:24 PM on September 13, 2011

The path you've chosen is basically writing the core of your own framework. You'll want, at the very least, a generic model class that you can extend with the specifics of each database table/ model (technically doesn't have to be 1db table=1model).

I have specificModel class extends dataModel class extends db class and that generic dataModel contains over 40 functions if you include data sanitation and validation routines but at a bare minimum I'd say you'd want a function to find a single result and populate the model with data, find_all (with criteria) and return an array of models, a save function (that knows whether its adding or updating - or separate add and update methods) and a delete method.

Based on the example you've given I'd give your billings the additional functions of find_this_month, find_last_month, find_x_months_ago
posted by missmagenta at 2:25 PM on September 13, 2011

> Rather than trying to create M and C on top of a framework that only gives you V

> I personally think you're going about this backwards

> The path you've chosen is basically writing the core of your own framework.

Right, got it, now with that out of the way... :-) (see OP)

I've poked at CodeIgniter, Symfony, Yii, Laravel, Django, Pyramid, Dancer, etc. and just feel that the learning curve (even for tiny frameworks) and plumbing required (mostly in Python's case) is not getting me results fast enough. Or the tutorials are ridiculously bad and I have hundreds of questions before I've put down a line of my own app's logic. I don't want to try to extend somebody's blog into my app. I want to write my app and then think about redoing it with a framework once I understand what the app will be like.

Anyway, I'm still slowly walking down that choose-a-framework path. But it feels backwards to me for a simple, business-driven, intranet project: Our Framework First, Your Results Last.

So, while I slowly learn someone else's framework, I'm just going to do it this backwards way.

> I have specificModel class

Thanks missmagenta. I don't understand the "I have" part -- or most of that sentence. Can you dumb it down for me a bit? Sorry. I understand that I should genericize my methods a bit to find_x_months ago rather than find_2_months ago. That helps. What about where to move that code to? Maybe that's what you were talking about.
posted by circular at 2:41 PM on September 13, 2011

I dont know php but I can chip in re: OO design.

It makes sense to move all your data access to an object instead if accessing databases in the index.php

From a practical standpoint it is very likely you will want to get you billing rows from pages other than index.php and there is a chance (this is debatable) that you will switch from mySql and will have to go around to all your pages and change mySql calls. Here is the simplest way I would do it

public class Billing{

string name;
int id;

public static Billing[] find_x_months(int months) {
//db code here to load rows into billing objects and return an array
}

public static Billing Load(int id) {
//db code here to load a row into a single billing object
}

public void Save() {
//saves this object to the db
}

public void Delete(int id) {
//db code here to delete a row from the DB

}


}

And db access you do for billing goes through the billing class, in your index.php you can simply call billing->find_x_months(2)

Like I said, this is the most basic way to do this, you also have to look out for sql injection and some other things, but you want to keep your data access out of the pages pretty much at all cost.
posted by Ad hominem at 4:06 PM on September 13, 2011

public void Delete(int id) {
//db code here to delete a row from the DB

}

Sorry, this probably shouldn't take an ID if you want to delete row you are calling it on. If you want to pass in a row id to delete you shoud use a static method.
posted by Ad hominem at 4:13 PM on September 13, 2011

I have a very mini framework - its about 7 files
1. db class - basically just an abstraction class for any mysql_ functions, so that could easily be replaced with a db class written for a different database engine
2. dataModel class which extends db. This has all the functions that all models will have (functions for finding data, populating the model, deleting the db record, it also has a fairly extensive data sanitisation routine and several generic validation functions )
3. appController - mostly just generic view handling (not templating)
4. modelController - contains the functions required for controlling models - CRUD functions mostly (separate from the app controller because controllers can exist that aren't model driven)
5. log class - fairly simple class for logging errors
6. router - determines what controller to load based on the url
7. html Helper - completely optional file that speeds up generating html output - mostly forms, but also lists, tables etc

Then an application has controllers, models and view files for each model.

Every framework I've seen handles models in a different way, I can only tell you how I do it. I wrote my framework before I even knew the others existed.

So your specificModel (eg. billingModel.class.php) wont have any functions to begin with it will just be an empty class that extends your dataModel.
My models (and I must stress I'm not saying this is the only or best way to do it, its just what I do - I wrote the whole thing in a weekend after dabbling with RoR for a few hours) have a few required fields: modelName - the name of the database table (so the name of the model doesn't have to match database table), primary - the name of the primary key field (so you're not limited to id), and a multidimensional array of fields - each field has a name, datatype (to determine how to sanitize the data) and an array of the names of validation functions to run against the field. It can also have other optional information in there such as minimum length, and resize/thumbnail/filetype options in the case of image fields

so in your case it would look something like

class billings extends dataModel
{
var $modelName = 'billings';
var $primary = 'id';
var $fields = array ('id'=>array('Type'=>int, 'validation'=>array('is_numeric')),
'date' => array('Type'=>'date', 'validation' =>array('is_date'))
);
///I dont know what other fields you'd have

// then we'd add specialist functions that are only needed by this model but will use functions from dataModel (which will use functions from db)

function find_this_month()
{
return $this->find_all('WHERE MONTH(DATE) = "'.date('m').'"'); //the WHERE part is actually optional, the function I wrote will put it in if its missing but since writing it, it reads more naturally in my head for it to be "find all where condition"

}
function find_last_month()
{
return $this->find_all('WHERE MONTH(DATE)="'.(date('m')-1).'"');
}
function find_x_months_ago($x)
{
return $this->find_all('WHERE MONTH(DATE)="'.(date('m')-$x).'"');
}

}

FWIW - your date('m')-x is not the way to go about it (eg. in January date('m') =1 so 1-1=0, 1-2 = -1 but thats a totally different topic.

Anyway, find_all is a function of dataModel - its something most if not all your models will need which is why we keep all the stuff in a generic dataModel and then just extend it with the specific model. dataModel also has your save and delete functions - because you've declared your fields and their validation routines you'll never need to write an update/insert query again, you would just populate the model with the form data and call the save function

eg. in my framework is looks something like this
$model = new model($_POST['model']);
if($model->save())
{
///yay
}
else
{
//boo - display the errors
echo $htmlHelper->drawList('ul', $model->errors);
}

The save function basically loops through all the fields and validates them then if they pass, generates and runs the sql query required to save the data based on the fields array.


I hope that helps but there really is way too much to go into here
posted by missmagenta at 4:26 PM on September 13, 2011 [1 favorite]

On SQL injection: any time you find yourself concatenating snippets of SQL with strings from elsewhere to build strings that you then pass to your DB as SQL statements, You're Doing It Wrong. What you should be using instead is prepared statements.
posted by flabdablet at 5:00 PM on September 13, 2011 [1 favorite]

> On SQL injection:

Yeah, just realized I am using that class incorrectly. It is meant to be used with placeholders instead of direct variable references, which is part of how it improves security.
posted by circular at 5:48 PM on September 13, 2011

Circular -- I feel your pain when it comes to learning frameworks. I'm a long-time PHP developer and every few months, I have a client that is adamant about Framework X, so I dive in and figure it out. It's always painful to start.

Everything you are struggling to piece together looks like you need to dive into a framework. Normally, I wouldn't recommend it to someone learning PHP, but it seems like you like things organized and I won't develop without a MVC framework. (Usually I'll tell people: "when you are tired of repeating yourself and wading thru the spaghetti code mess, then you are ready for a framework.")

Because I often work as part of a team, MVC frameworks keep us organized. (I always know that I'll find "models" code for project Z in directory Y.)

Find a fairly well-documented framework like Cake or CodeIgniter. Try to find some "Hello World"-style tutorials. You'll quickly see how the layers play together. You'll gnash and moan for a few days with a million questions -- and then it'll eventually begin to fall into place.

P.S. I think templating systems are absolute wastes of time. PHP IS a templating system. How many abstraction layers does one need?! :)
posted by bprater at 6:03 PM on September 13, 2011

Play Scala just introduced me to a templating system that I enjoy thoroughly and that was based off ASP.NET Razor. The template for the URL takes incoming arguments and then passes the body HTML elements to the more basic container

So if I had a reusable "main" container

@(title: String)
<html>
<head><title>@title</title></head>
<body>
@content
</body>
<html>

the routed page (where URLs point to would be

@(routed page template arguments...)
@main("Title String") {
<b>Here is some content</b>
}

It provides reusability in the head portion while allow the content to change dynamically. Maybe try to look for a template system based off ASP's Razor templating system would be helpful.
posted by DetriusXii at 7:34 PM on September 13, 2011

It is meant to be used with placeholders instead of direct variable references

You shouldn't even be using placeholders for this. Don't do date arithmetic in PHP, do it in SQL:

SELECT * FROM billings WHERE MONTH(DATE) = MONTH(CURDATE() - INTERVAL 1 MONTH)
posted by Rhomboid at 5:20 AM on September 14, 2011

Wouldn't all of these also select billings from August in any year? That seems to me unlikely to be what's wanted.
posted by flabdablet at 7:27 AM on September 14, 2011

Surely there must be something less clunky than

SELECT * FROM billings
WHERE YEAR(DATE) = YEAR(CURDATE() - INTERVAL :monthsAgo MONTH)
AND MONTH(DATE) = MONTH(CURDATE() - INTERVAL :monthsAgo MONTH);

but I'm sure as hell not seeing it.

Also, revisiting the original PHP, look closely at

$cur_month = date("m");
$month_minus_one = date("m") - 1;
$month_minus_two = date("m") - 2;

and think about January.

Date and time logic is probably the single most concentrated source of bugs in any project that contains any, simply because every timekeeping system in common use is insane.
posted by flabdablet at 1:21 AM on September 15, 2011

You could write it as:

SELECT * FROM billings WHERE EXTRACT(YEAR_MONTH FROM DATE) = EXTRACT(YEAR_MONTH FROM CURDATE() - INTERVAL :monthsAgo MONTH);

You could also do this with string manipulation:

SELECT * FROM billings WHERE LEFT(DATE, 7) = LEFT(CURDATE() - INTERVAL :monthsAgo MONTH, 7);

This has the advantage of being more index-friendly so that you can avoid a table scan if the column is indexed, since it explicitly refers to a prefix of the value. I'm not sure if the query optimizer is smart enough to tell that EXTRACT(YEAR_MONTH ... ) is like a prefix operator.

You could also do it as:

SELECT * FROM billings WHERE DATE(DATE) > LAST_DAY(CURDATE() - INTERVAL :monthsAgo + 1 MONTH) AND DATE(DATE) <= LAST_DAY(CURDATE() - INTERVAL :monthsAgo MONTH);

This version has the advantage that it can be easily adapted to work with timestamps in the unix epoch format. If you have a lot of records this is a more compact way to store and index timestamps, which generally equals faster.
posted by Rhomboid at 5:41 AM on September 15, 2011

You mentioned not liking some of the tutorials that you've gone through. Have you tried The Django Book. I thought they did a real good job of starting out with an example app that is pretty much brute force. Each subsequent chapter is all about introducing concepts and features that allow for a lot less code/configuration and shows you how real apps are meant to be developed with Django.
posted by mmascolino at 9:05 PM on September 15, 2011