Extending the schema

[zag]

Guys, I'm sure I came across a posting on the site here somewhere which outlined how to add extra fields to the member table, but I can't find it now.

I went into phpMyAdmin on the server and added in the fields and was able to populate them no problem, but as soon as I went to add in a new extension the install told me I had to remove the extra fields and I have no idea what config I need to update to make these fields permanent (or extend the schema in my way of thinking).

Can anyone set me on the right track please ? I tried searching the extension catalog but couldn't find it.

Thanks,

z

[ramjet]

http://www.typolight.org/adding-custom-fields.html will tell you :D

[zag]

Thanks for the pointer - I'm treading cautiously here as this is the first time I'm doing anything 'drastic' to the setup.

Step 1 is taking a backup of everything before I go any further, so while that's happening I'm just checking to see if I'm on the correct track.

According to the link below I need to add the field definitions into /public_html/system/modules/backend/config/database.sql

Do I just need to add in the extra lines in the middle/end of the existing definition ? It makes sense that this would be the case, but the instructions seem to indicate a separate entry just for it. My guess is that I need to update the file to look like this -

CREATE TABLE `tl_member` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `tstamp` int(10) unsigned NOT NULL default '0',
[snip]
  `stop` varchar(10) NOT NULL default '',
  `session` blob NULL,
  `zags_new_field` varchar(10) NOT NULL default '',
  `zags_second_new_field` varchar(10) NOT NULL default '',
  PRIMARY KEY  (`id`),
  KEY `username` (`username`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

So, who's right, me or the docs ?

z

[nevermetme]

The docs point out that you should create your own little module!

So you create a new folder in /system/modules/
And in there you need the database.sql file with only the field definitions that you want to add. TYPOlights install tool will scan all the /system/modules/***/config/database.sql to see which table has what fields.

So your database.sql should look as short as in the docs.

btw. using the Code tag makes reading stuff like the SQL statement way easier

[zag]

OK - thanks for the clarification.

So, I create my new module and add in the relevant text to add the new fields to the tl_member and run the install tool. And then I do similar for dca/tl_.member.php. Sounds good so far. I had a feeling the docs were more likely to be right then me alright.

Does the module definition come in to use anywhere else ? If I need to extend a few tables, do I do them all from this one module or is it a really bad idea to add fields to different tables using a single module ?

Up to now I have been using the backend relatively successfully, but it looks like I've got a lot of catching up to do on the config front.

Thanks again,

z

[nevermetme]

Well, I would say it depends on how flexible you want to be. For example, when you have custom member fields that you need in several different installations, but other fields that you need only in this one, then you better split them in different modules, so you can easy copy the often used one.

[pbrooks]

Hi all,
Just wondering about this question and in terms of packaging. Even if I'm putting the SQL files into INSTALL within the package, am I to still list the SQL in system/module/config?

Also, if I am modifying a table such as TL_MEMBER.
Do I create this syntax:

CREATE TABLE tl_member(
my_additional_column VARCHAR(255)
)

OR

ALTER TABLE tl_member ADD COLUMN my_additional_column VARCHAR(255)

Many thanks

[Tru]

Also, if I am modifying a table such as TL_MEMBER.
Do I create this syntax:

CREATE TABLE tl_member(
my_additional_column VARCHAR(255)
)

Yes, this is correct.

[zag]

Back again. Thanks to the advice here, I updated the database definition with several fields. I can happily update these fields in tl_member directly via the database. Now I'm trying to expose these fields in the back end so that I can update them a whole lot easier.

I created a file called \system\modules\z_bcrfc_modifications\config\datab ase.sql and populated it it with

CREATE TABLE `tl_member`(
`bcrfc_2010_payment_status` varchar(8) NOT NULL default '',
`bcrfc_2010_payment_amount` varchar(8) NOT NULL default '',
`bcrfc_2010_payment_date` varchar(11) NOT NULL default '',
`bcrfc_2010_jersey` varchar(3) NOT NULL default '',
`bcrfc_2010_shorts` varchar(3) NOT NULL default '',
`bcrfc_2010_socks` varchar(3) NOT NULL default '',
`bcrfc_school` varchar(48) NOT NULL default '',
`bcrfc_mother_name` varchar(24) NOT NULL default '',
`bcrfc_mother_number` varchar(24) NOT NULL default '',
`bcrfc_father_name` varchar(24) NOT NULL default '',
`bcrfc_father_number` varchar(24) NOT NULL default '',
`bcrfc_medical_notes` varchar(256) NOT NULL default '',
`bcrfc_player_name` varchar(24) NOT NULL default '',
`bcrfc_other_family` varchar(96) NOT NULL default '',
`bcrfc_2010_team` varchar(5) NOT NULL default '',
`bcrfc_2010_cash_cheque` varchar(5) NOT NULL default '',
`bcrfc_retired` varchar(96) NOT NULL default '',
`bcrfc_2010_form_complete` varchar(5) NOT NULL default '',
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

I ran the install tool and all the fields were added to tl_member. I have been populating them via the database directly for months now and at this stage I want to start populating them via the Contao backend.

Following the guidelines here - http://www.contao.org/adding-custom-fie ... l#new-name - I created a file called \system\modules\z_bcrfc_modifications\dca\tl_membe r.php and populated it with

// Modify the palette
$GLOBALS['TL_DCA']['tl_member']['palettes']['default'] = str_replace
(
'company',
'bcrfc_2010_team,company',
$GLOBALS['TL_DCA']['tl_member']['palettes']['default']
);
 
// Add the field meta data
$GLOBALS['TL_DCA']['tl_member']['fields']['bcrfc_2010_team'] = array
(
'label'     => &$GLOBALS['TL_LANG']['tl_member']['bcrfc_2010_team'],
'exclude'   => true,
'inputType' => 'text',
'eval'      => array('rgxp'=>'alnum', 'maxlength'>=5)
);

I changed the 'rgxp' eval element from the example on the page to recognise that the field is alphanumeric, and also removed the 'mandatory' element and changed the maxlength. Unfortunately when I try to view the members page on the backend I get

// Modify the palette $GLOBALS['TL_DCA']['tl_member']['palettes']['default'] = str_replace ( 'company', 'bcrfc_2010_team,company', $GLOBALS['TL_DCA']['tl_member']['palettes']['default'] ); // Add the field meta data $GLOBALS['TL_DCA']['tl_member']['fields']['bcrfc_2010_team'] = array ( 'label' => &$GLOBALS['TL_LANG']['tl_member']['bcrfc_2010_team'], 'exclude' => true, 'inputType' => 'text', 'eval' => array('rgxp'=>'alnum', 'maxlength'>=5) );

Warning: Cannot modify header information - headers already sent by (output started at /home/blackroc/public_html/system/modules/z_bcrfc_modifications/dca/tl_member.php:15) in /home/blackroc/public_html/system/libraries/Template.php on line 174
#0 /home/blackroc/public_html/system/libraries/Template.php(174): __error(2, 'Cannot modify h...', '/home/blackroc/...', 174, Array)
#1 /home/blackroc/public_html/system/modules/backend/BackendTemplate.php(135): Template->output('Content-Type: t...')
#2 /home/blackroc/public_html/typolight/main.php(286): BackendTemplate->output()
#3 /home/blackroc/public_html/typolight/main.php(102): Main->output()
#4 /home/blackroc/public_html/typolight/main.php(295): Main->run()
#5 {main}

Can anyone point me in the right direction and give me a hint as to what I'm doing wrong please ?

Thanks,

z

p.s. I've edited this a few times as I tested out various tweaks, but the above is my current config.

[zag]

OK - I'm getting there. I'm no PHP programmer, but I can reverse engineer stuff . . . I had a look at some other tl_member.php files for other modules and worked out that I was missing these lines at the top and bottom of the file -
<?php if (!defined('TL_ROOT')) die('You can not access this file directly!');
and
?>

It may be obvious to the PHP gurus out there, but it took me a while to work that one out. I will submit a comment to the manual page for this.

z