MySql: The power of Views (or Better late than never)

I sometimes get the sour feeling of “there’s something fundamentally wrong with this! It works, but nevertheless…” It happens in all aspects of life, including programming. Some of (not always!) the times this feeling causes curiosity and energy to strive for a new perspective, new knowledge, new ways of facing the problem. Aspects of inutition and creativity, I guess…

In my current project there’s an awful lot of SQL going on. Lots of connected tables, nestled selects etc. In the preparation for the next level of full text searchability, I stumbled upon the concept of Views in MySql. I can’t believe that I didn’t know this beauty before!

In short, a View is a virtual database table where the content is a result of a underlying sql statement. It can be accessed by normal queries just like any table – a view can build its content depending on tables as well as other views.

For a good introduction of MySql Views, check this Techtopia blogpost out.

Creating a View in PhpMyAdmin is a breeze, there’s a Create View link in the right bottom of any table view:

I haven’t found any button to retrieving the underlaying View sql though. However, it can be done by using a normal “SHOW CREATE VIEW tablename” sql statement.

The concept of views will ease my further development a lot! I’m glad I did listen to that feeling…

The quest for client-side search query language

…just led me to CQL (Contextual Query Language). Thanks StackOverflow and njk for showing the way!

(Please note that since version 1.2, “Contextual Query Language” is changed from former “Common Query Language”)

This lets the user write advanced searches using logical operators, indexes, even proximities… So, let’s create a search for all the cql-to-php-to-sql libraries laying around waiting for to be implemented…

Kohana, Formo and many-to-many relations (“habtm”)

Just got the solution for how to handle many-to-many relations in Formo. Thank you, Ben!

Three tables, groups (id, name), users (id, name) and the pivot table groups_users(group_id, user_id).

class User_Model extends ORM {
protected $has_and_belongs_to_many = array('groups');
}
class Group_Model extends ORM {
protected $has_and_belongs_to_many = array('users');
}

And here’s how to in the controller handle display of an user form, complete with checkbuttons for toggling connections to groups:

public function user($id = false) {
$user = new User_Model($id);
$form = Formo::factory()
->orm($user)
->habtm('user', 'group')
->add('Submit');
if ($form->validate()) $form->save();
echo $form;
}

This gives the following form. The magic lies in the habtm plugin (has to be active either by Formo config or by Formo::factory()->plugin(‘habtm’). Not one single sql line, not one line of deadly tedious form populating and deriving…

Ok, not pretty, but that’s another story.

Soo proud. Just struck me what “habtm” stands for.
Na na, won’t tell ya!

Kohana PHP with ORM and Formo – db made easy!

After some days prototyping with Kohana, I start to realize the strength and beauty!

One of the every-day needs in my world is to coordinate different database tables to get things to work. Most of the time this is done by hand, using PhpMyAdmin. For basic stuff, this works fine, but very often there’s the bad feeling of should-have-a-real-admin-interface-for-this…

No bad feelings no more! The combination of Kohana’s Object Relational Mapping library (ORM) and the Formo module makes it super-easy to create a table-admin-interface, including related tables. No SQL at all, no defining form layouts at all! (Ok, there can be a need for some validation information, some styling etc.)

Here’s an example, based on two tables, schools and students:

schools: <- Note: Table name in plural
id:int autoincrement
name:varchar

students:  <- Note: Table name in plural
id:int autoincrement
school_id:int   <- Note! Referred table as singular + "_id"
name:varchar

Two models for those tables:

// models/student.php
class Student_Model extends ORM {
protected $belongs_to = array("school");
// Defines the connection to school - "each student belonst to a school"
}
// models/school.php
class School_Model extends ORM {
protected $has_many = array("students");
// Defines the connection to students - "each school has many students"
}

So for the controller. Here’s the real beauty: In the listAll action below the schools are fetched, and ORM handles automatically the relationship and fetches all connected students as well!

// controllers/controllername.php
public function listAll() {
$schoolModel = new School_Model();
$schools = $schoolModel->find_all();
foreach ($schools as $school) {
echo "<br/><b>$school->name - ".html::anchor("controllername/school/{$school->id}", "edit")."</b>";
foreach ($school->students as $student) {
echo "<br/>- $student->name - ".html::anchor("controllername/student/{$student->id}", "edit");
}
}
}

The actions below handle both creation and editing of posts. The super-handy Formo library maps out a complete form – including dropselect for selecting a school for the students! Kind of magic!

// controllers/ormtest.php
public function school($id = false) {
$school = new School_Model($id);
$form = Formo::factory()->orm($school)->add("Submit");
echo $form;
if ($form->validate()) {
$form->save();
}
}

public function student($id = false) {
$student = new Student_Model($id);
$form = Formo::factory()
  		->orm($student)
  		->add("Submit");
if ($form->validate()) $form->save();
echo $form;
}

The only “tricky” part is that you have to get the table names and references to them right according to the ORM naming conventions. Here’s a short summary:

• Table names should be plural
  (“schools”, “students” – this makes sense because the table is a list with many items)
• Referring field names should be singular plus “_id”
  (“school_id” – logically because a single student belongs to one single school)
• Model names should be singular.
  (The model file for a student is named “student.php”, and the class is named “class Student_Model extends ORM”. A model most of the time represent one single item: one student or one school. The exception is when we want a listing of all items in the table. Have a look at the listAll code above.)

The models need to include information about the relations.

• The student belongs to one of many schools, therefore we use
  protected $belongs_to = array(’schools’);
  with plural in “schools”
• The school has one or many students, thus
  protected $has_many = array(’students’);

Note that this relates to a one-to-many relationship.

Happy crudding with Kohana

Another superb Kohana solution: CRUD Scaffold (projects.nathanbentley.com/projects/show/crudscaffold) by Nathan Bentley. Just install, write some controller and model lines, and you have a nice JQuery-driven table editor – just the way it should be!:

Thank you, Nathan!

Now, there’s just left to sort out how to handle related tables…

Php: Getting it together with Kohana?

Somewhere deep inside you carry a vision, a longing, a vague picture of “getting it all together”… At least I do – in every aspect of life..!

Since I started php coding some years ago, I’ve always had a bad taste in my mouth. It’s so ugly, so diversitive, so sprawling, so poly-cultural (in a bad sense), so non-coherent… This feeling has been accentuated since I started using Flex and Actionscript 3 with its java-like package concept combined with the superb Eclipse based editor… At last an environment that helped me to good and professional-like thinking, structure and habits!

Since some months, I’ve been able to get my php-solutions together in a low-level kind-of-framework-like structure, where I (at last!) quickly and easily can reach what I need: GD-processing, File handling stuff, SQL processing, debugging, ZendAmf stuff etc…

Connecting to my initial philosophies, I would like to weave this basic every-day utility stuff into something that has full limit-less production capabilities, including crudding, cms-ing, caching etc… And maybe I’ve found what I’ve been looking for – Kohana (konanaphp.com)! With its CodeIgniter roots, it’s the far most elegant and simple php framework that I’ve been able to understand this far..!

It’s layered concistent structure of system-modules-application makes it so quick and easy! Just one example: Yesterday, I downloaded and tried the s7n cms system (http://code.google.com/p/s7ncms/) created in Kohana by Eduard Baun. The complete cms core as one module! Extensions of the cms also as modules! The fundamental Kohana layout virtually untouched! Some months ago I investigated some Zend cms solutions, and they were a complicated mess compared to this one…

S7N admin interface, created with Kohana PHP

And it looks good too…

Foreign key relations in MySql – Avoiding accidental deletion of posts

The normal case when using foreign key relationships in database tables might be “ON DELETE CASCADE ON UPDATE CASCADE”. If you have a table connecting a user_id to an activity_id, this means that deleting the activity post also deletes the connection post. Fine! The activity is gone, and so the table post defining the connection between the (gone) activity_id and the user_id.

However, if you have a person table in wich you define the connection to, let’s say, city_id, and use a foreign key on that releationship, you’ve better watch up! What happens if you accidentally delete the current city? The “ON DELETE CASCADE” directive causes the deletion of that person too! And this might cause the deletion of importand information connected to that person… Big No No!

The solution lies in defining the relation “ON DELETE SET NULL”. This means that the person’s city_id is set to NULL, not that the person post is deleted. Much better! Pelase note that the city_id field has to be defined to accept NULL as an alternative, otherwise MySql won’t let you define that relationship.on

Setting up foreign key relations in MySql databases

When using the InnoDB table format in MySql, there’s the possibility to define foreign key relations to guarantee referential integrity.

Setting up the relations by using “ALTER TABLE” sql statements is not that trivial though. I had some struggles the other day… Luckily there’s a view in PhpMyAdmin that takes care of this. Check out this post from Jim Epler’s blog. Thanks Jim!