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]

Categories

Recent Comments

Tags

5 Comments

  1. January 1, 2006

    I am trying to use an example of this file with

    include(“journal.class.php”);
    new journal();

    it is writing the classfile to the browser though and cant use the journal class. Could you let me know how to use this classfile?
    I am simply trying to insert two values into a new table.
    thanks
    calvin

  2. January 1, 2006

    have solved the writting to the browser issue! silly me forgot the script tags “”.

  3. January 1, 2006

    I have the following error upon further examination AND the original error which suggests sql syntax, however people on the forums cant see a problem with it
    error

    Warning: Missing argument 1 for journal::journal() in C:\Uniform3_1_1s\www\landedAt\components\com_journal\journal.class.php on line 7
    hiDB function failed with error number 1064
    You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘from,to) VALUES (‘2005-12-05’, ‘2005-12-07′)’ at line 1 SQL=INSERT INTO stay (from,to) VALUES (‘2005-12-05’, ‘2005-12-07’)

    error-END

    I have reason to doubt that I need this classfile and can use $database on its own.
    thanks for suggestions.

  4. January 1, 2006

    It seems that using “from” as a field with php and mysql isnt good, this is the main problem-solved.

    But this classfile isnt necessary! and Mambo 4.5.3 doesnt seem like it is any longer the same as joomla.

    Thanks for your offer of help.

    calvin

  5. January 1, 2006

    No, it’s always best to avoid using “reserved words” in a column, that includes words like “order”.

    The syntax to use should include ticks ` such as

    INSERT INTO `stay` (`from`,`to`) VALUES ('2005-12-05', '2005-12-07')

    The ticks tell mysql that the text is a column or table name, and not a command.

    While you can use $database directly it’s useful to use the classes and much quicker to code and debug.

    I’ve got one site on Joomla and so far haven’t found any clashes with Mambo code. Certainly nothing so far as the way the database is handled.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.