Inserts and Updates

I’m collaborating on a set of components for the Mambo 4.5.3 site over at PropertyTalk. The first draft was written by the site owner and I’ve gone in an Mambo-ised them.

That’s meant using the pagination tools, putting the styles and formatting in place and changing the raw inserts and updates to using the table class concept.

The Mambo / Joomla way of managing records

Gone is the need to write insert and update statements and slog through the matching process, adding slashes etc. The CMS does it all for you via the mosDBTable class.

Every custom component should have a file called compname.class.php

This file holds the information about each column in each table in the database that relates to the component. Several classes can be included in a single file. Core components have their classes described in other parts of the system but weblinks is a good example.

Here’s the class (with some comments stripped out)

[php]class mosWeblink extends mosDBTable {
var $id=null;
var $catid=null;
var $sid=null;
var $title=null;
var $url=null;
var $description=null;
var $date=null;
var $hits=null;
var $published=null;
var $checked_out=null;
var $checked_out_time=null;
var $ordering=null;
var $archived=null;
var $approved=null;
var $params=null;

function mosWeblink( &$db ) {
$this->mosDBTable( ‘#__weblinks’, ‘id’, $db );
}
function check() {
// filter malicious code
$ignoreList = array( ‘params’ );
$this->filter( $ignoreList );

// specific filters
$iFilter = new InputFilter();

if ($iFilter->badAttributeValue( array( ‘href’, $this->url ))) {
$this->_error = ‘Please provide a valid URL’;
return false;
}

/** check for valid name */
if (trim( $this->title ) == ”) {
$this->_error = _WEBLINK_TITLE;
return false;
}

if ( !( eregi( ‘http://’, $this->url ) || ( eregi( ‘https://’,$this->url ) ) || ( eregi( ‘ftp://’,$this->url ) ) ) ) {
$this->url = ‘http://’.$this->url;
}

/** check for existing name */
$this->_db->setQuery( “SELECT id FROM #__weblinks ”
. “\nWHERE title=’$this->title’ AND catid=’$this->catid'”
);

$xid = intval( $this->_db->loadResult() );
if ($xid && $xid != intval( $this->id )) {
$this->_error = _WEBLINK_EXIST;
return false;
}
return true;
}
}[/php]

This class has 3 parts.

First off it’s given a name and each of the columns (fields) are created. Here the default value is null, but they could be given default values.

Then a function with the same name as the class is created. This is mandatory and is the constructor for the class. It receives in the database variable and sets up the link to the table that it manages.

Finally a function called check is created. This is an option is data validation is required prior to every save. It returns true if the save can proceed, and false if a check fails.

Check utilises an underlying variable $this->_error to hold the message associated with any problems that need to be fed back to the user.

Inserts versus Updates

Mambo/Joomla look at the record that is being saved and make the decision on whether a save or an update are required. No need to test variables, it does it all for you.

The only gotcha that I’ve found is that updates change all the values from the existing record, even if they weren’t on the form.

That can lead to maintenance issues if the component is large and there are alot of forms and the site is undergoing alot of changes as it develops (RAD anyone?)

I get around this with a simple check (ummm, yes, so it means I need to check the id) – but it does mean I get a one size fits all function I can use for all the tables, and because it loads before it binds if I’ve added a new column to the table but missed it from a form it doesn’t matter.

Line 10 of the save function loads up the existing values and line 12 overwrites them with the values from the form. If a value doesn’t exist on the form, then no change will be made and the udpate statement will use the old value.

Without the $row->load then the class would have a null value, and that would be written to the database.

[php]function save($id, $type){

global $database;

switch ($type) {
case ‘CompExample’:
$row = new dbCompExample($database);
break;
}
if ($row->id > 0) $row->load($id);

if (!$row->bind( $_POST )) {
echo “\n”;
exit();
}
if (!$row->check()) {
echo “\n”;
exit();
}
if (!$row->store()) {
echo “\n”;
exit();
}
mosRedirect( ‘index2.php?option=com_mycomp’, ‘Saved’ );
}//save
[/php]