|
|
here is the test i set up
this file makes table a /*
MySQL Data Transfer
Source Host: 192.168.0.44
Source Database: test
Target Host: 192.168.0.44
Target Database: test
Date: 23/04/2007 01:17:37
*/
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for a
-- ----------------------------
CREATE TABLE `a` (
`id` int(11) NOT NULL,
`field_b` int(11) default NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
-- ----------------------------
-- Records
-- ----------------------------
INSERT INTO `a` VALUES ('1', '33');
INSERT INTO `a` VALUES ('2', '25');
INSERT INTO `a` VALUES ('3', '15');
INSERT INTO `a` VALUES ('4', '45');
this makes table b
/*
MySQL Data Transfer
Source Host: 192.168.0.44
Source Database: test
Target Host: 192.168.0.44
Target Database: test
Date: 23/04/2007 01:17:45
*/
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for b
-- ----------------------------
CREATE TABLE `b` (
`id` int(11) NOT NULL,
`field_b` int(11) default NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
-- ----------------------------
-- Records
-- ----------------------------
INSERT INTO `b` VALUES ('1', '55');
INSERT INTO `b` VALUES ('2', '56');
INSERT INTO `b` VALUES ('3', '871');
INSERT INTO `b` VALUES ('4', '12');
This makes table c
/*
MySQL Data Transfer
Source Host: 192.168.0.44
Source Database: test
Target Host: 192.168.0.44
Target Database: test
Date: 23/04/2007 01:17:55
*/
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for c
-- ----------------------------
CREATE TABLE `c` (
`id` int(11) NOT NULL,
`calc_total` int(11) default NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
-- ----------------------------
-- Records
-- ----------------------------
INSERT INTO `c` VALUES ('1', null);
INSERT INTO `c` VALUES ('2', null);
INSERT INTO `c` VALUES ('3', null);
INSERT INTO `c` VALUES ('4', null);
amend the sql query in phpr to this
SELECT
`a`.`id`,
`a`.`field_b`,
`b`.`field_b`,
`a`.`field_b`+`b`.`field_b`as 'pinkone'
FROM
`a`
Inner Join `b` ON `a`.`id` = `b`.`id`
Left Join `c` ON `c`.`id` = `a`.`id`
this will allow you to see the values from table a andf b at the same time in the php scripts
It also adds the fields from a and b to give a total which I have called pinkone
you then use an after record saved event to put the value of `pinkone` into table c
hth
That worked great when running through Navicat, what I am unsure of (being a PHPRunner virgin) is how to apply that to my own project. The 'pinkone' value, should that be a field already named in Table C? I also edited the SQL manually, but it only showed the fields I am calculating, there are other fields I want listed in the list page as well as 'pinkone'.
And one last question, how would I add the event I require to update the Table C 'pinkone' value?
Many questions and probably very simple, but I am on a steep learning curve and thanks for your help so far <img src='https://asprunner.com/forums/file.php?topicimage=1&fieldname=reply&id=17191&image=1&table=forumreplies' class='bbc_emoticon' alt=':)' />
G
|