This topic is locked

concat values from several fields and storing in one field

8/20/2006 10:47:45 AM
PHPRunner General questions
T
thesofa author

Hi

Looks like I have come unstuck here

CREATE TABLE `pupils` (

`login` varchar(255) NOT NULL,

`fname` varchar(255) default NULL,

`lname` varchar(255) default NULL,

`intake` varchar(255) default NULL,

`idPupil` int(11) NOT NULL auto_increment,

PRIMARY KEY (`login`,`idPupil`),

UNIQUE KEY `idPupil` (`idPupil`),

UNIQUE KEY `login` (`login`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE `yeargroup` (

`intake` smallint(4) NOT NULL,

`YearGroup` tinyint(2) NOT NULL

) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE `detentions` (

`ID` int(11) NOT NULL auto_increment,

`DateGiven` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,

`TimeGiven` datetime default NULL,

`detainee` int(11) NOT NULL,

`donor` int(11) default NULL,

`TutorGroup` varchar(3) default NULL,

`DayForDetention` datetime NOT NULL,

`sess` int(11) NOT NULL,

`Lunchtime` varchar(1) default NULL,

`Done` tinyint(4) default NULL,

`reason` int(11) default NULL,

`Origin` int(11) default NULL,

`MoveToDept` datetime default NULL,

`DeptHead` int(11) default NULL,

`ReasonForDept` int(11) default NULL,

`OriginalDate` datetime default NULL,

`DoneAtDept` tinyint(4) default NULL,

`MovedToAfterSchool` datetime default NULL,

PRIMARY KEY (`detainee`,`DayForDetention`,`sess`),

UNIQUE KEY `ID` (`ID`),

KEY `Lunchtime` (`Lunchtime`),

KEY `To` (`detainee`),

KEY `TutorGroup` (`TutorGroup`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1;
this is a shortened version of what I want to do

In the add a detention record view, based on the detentions table, I have a lookup table selector based on the Yeargroup table, the staff see year 7, 8, 9, 10, or 11. Once the yeargroup is selected, the staff see only year 7 pupils in the pupil name selector in the add record form.

The value i need to link the yeargroup table to the pupils table is the `intake` field.

The value I need to store in the detentions table is derived from the `YearGroup` field oin the `yeargroup` table, concatenated with the first letter of the `login` field from the pupils table.

So if I select year 7, and the form then filters the pupils table and only shows me the pupils with an intake field of value 2006.

If I then select the pupil John Smith, who has a login of PSmithJ, the Tutorgroup value I need to save in the TutorGroup field in the database is 7P.

How do I do this?

J
Jane 8/21/2006

Hi,
you can do it using events.

Here is a sample code for the BeforeAdd event. I have no idea if this code works. Use it on your own risk:

function BeforeAdd(&$values)

{

global $conn;

$str1 = "select from `pupils` where `idPupil`=".$values["detainee"];

$rs1 = db_query($str1,$conn);

$data1 = db_fetch_array($rs1);
$str2 = "select
from `yeargroup` where `intake`=".$data1["intake"];

$rs2 = db_query($str2,$conn);

$data2 = db_fetch_array($rs2);
$firstletter = substr($data1["login"],0,1);

$values["TutorGroup"] = "'".$data2["YearGroup"].$firstletter."'";
return true;
}

T
thesofa author 8/21/2006

Thank you, what a superb forum and product.

Many thanks again.