sql in fields.ini
Archived from the Xataface Users forum.
clester — Fri Dec 08, 2006 11:40 pm
Hi again,
I have this sql in the fields.ini.
__sql__=”SELECT u.* , DATEDIFF( CURDATE( ) , start_date ) AS days, a.total, ROUND( property_value / a.total *100, 1 ) AS lvr
FROM loans u
LEFT JOIN (
SELECT loan_id, sum( amount ) AS total
FROM splits
GROUP BY loan_id
) AS a ON a.loan_id = u.loan_id “
It throws this error:
Fatal error: Cannot use object of type PEAR_Error as array in C:\wamp\www\dataface\Dataface\Table.php on line 1558
If i get rid of the days and lvr it works..
The sql statement works outside of dataface so i guess from the error DF is tring to do another query using this query. maybe record counts.
Could someone please show me the corect format for the df query.
Thanks.
shannah — Mon Dec 11, 2006 12:27 pm
Looks like a but.. the __sql__ feature in the fields.ini file is not well tested. Can you send me the schema for the tables involved and maybe enough sample data to get test going. I’ll see if I can nail down the bug.
-Steve
clester — Mon Dec 11, 2006 4:11 pm
Hi Steve,
Here ya go.
– phpMyAdmin SQL Dump
– version 2.9.0.3
– http://www.phpmyadmin.net
–
– Host: localhost
– Generation Time: Dec 12, 2006 at 09:05 AM
– Server version: 5.0.27
– PHP Version: 5.2.0
–
– Database: cams
–
–
– Table structure for table loans
–
CREATE TABLE loans (
loan\_id int(11) NOT NULL auto_increment,
card\_id int(11) NOT NULL,
lender int(11) NOT NULL,
property\_value decimal(12,2) NOT NULL,
secuity text NOT NULL,
lender\_ref varchar(20) NOT NULL default ‘Unknown’,
status int(11) NOT NULL default ‘0’,
start\_date timestamp NOT NULL default CURRENT_TIMESTAMP,
sale\_date date default NULL,
consultant int(11) NOT NULL,
processor int(11) NOT NULL,
PRIMARY KEY (loan\_id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=39 ;
–
– Dumping data for table loans
–
INSERT INTO loans (loan\_id, card\_id, lender, property\_value, secuity, lender\_ref, status, start\_date, sale\_date, consultant, processor) VALUES
(1, 5, 1, 320000, ‘Fred the Dog\r\n’, ‘Q5934238’, 0, ‘2006-12-06 17:39:51’, ‘0000-00-00’, 1, 1),
(2, 3, 2, 215000, ‘12 long street’, ‘Q32039’, 0, ‘2006-12-06 17:54:10’, ‘0000-00-00’, 0, 2),
(3, 4, 1, 200000, ‘dskfjsdlkfjs’, ‘F549382’, 0, ‘2006-12-06 21:20:47’, ‘0000-00-00’, 0, 1),
(4, 7, 1, 670000, ‘’, ‘Unknown’, 0, ‘2006-12-07 09:19:02’, ‘0000-00-00’, 0, 1),
(5, 6, 1, 350000, ‘’, ‘Q4321’, 0, ‘2006-12-07 09:43:19’, ‘0000-00-00’, 0, 1),
(6, 6, 1, 750000, ‘jhlkjhlkjh’, ‘q34902’, 0, ‘2006-12-07 09:46:35’, ‘0000-00-00’, 0, 1),
(7, 8, 2, 890000, ‘Shack in Kazakstan’, ‘QF59348’, 0, ‘2006-12-07 12:00:22’, ‘0000-00-00’, 0, 1),
(8, 6, 2, 900000, ‘house’, ‘Unknown’, 0, ‘2006-12-07 12:41:11’, ‘0000-00-00’, 0, 1),
(9, 4, 1, 550000, ‘plenty’, ‘Unknown’, 0, ‘2006-12-07 12:45:20’, ‘0000-00-00’, 0, 1),
(10, 5, 2, 98098, ‘hlhljh l’, ‘QF59330’, 0, ‘2006-12-07 12:46:07’, ‘0000-00-00’, 0, 1),
(11, 9, 2, 350000, ‘’, ‘Unknown’, 0, ‘2006-12-07 13:00:48’, ‘0000-00-00’, 0, 1),
(12, 9, 1, 340000, ‘dd’, ‘Unknown’, 0, ‘2006-12-07 14:53:16’, ‘0000-00-00’, 0, 1),
(13, 5, 1, 1000000, ‘lkjlkj’, ‘Unknown’, 0, ‘2006-12-07 15:16:35’, ‘0000-00-00’, 0, 1),
(14, 4, 2, 200000, ‘test’, ‘Unknown’, 0, ‘2006-12-07 15:18:47’, ‘0000-00-00’, 0, 1),
(15, 4, 2, 600000, ‘tgklejtek jl’, ‘Unknown’, 0, ‘2006-12-07 15:21:05’, ‘0000-00-00’, 0, 1),
(16, 3, 2, 798709, ‘07987’, ‘Unknown’, 0, ‘2006-12-07 15:22:11’, ‘0000-00-00’, 0, 1),
(17, 6, 2, 7969876, ‘697698769876’, ‘Unknown’, 0, ‘2006-12-07 15:24:17’, ‘0000-00-00’, 0, 1),
(18, 6, 1, 96786987, ‘69hg hg k’, ‘Unknown’, 0, ‘2006-12-07 15:28:33’, ‘0000-00-00’, 0, 1),
(19, 7, 1, 79870, ‘gkj gg’, ‘Unknown’, 0, ‘2006-12-07 15:29:36’, ‘0000-00-00’, 0, 1),
(20, 6, 1, 790000, ‘gh hgh g’, ‘Unknown’, 0, ‘2006-12-07 15:30:33’, ‘0000-00-00’, 0, 1),
(21, 8, 1, 8098, ‘hk jhlk’, ‘Unknown’, 0, ‘2006-12-07 15:31:29’, ‘0000-00-00’, 0, 1),
(22, 3, 2, 850000, ‘’, ‘Unknown’, 0, ‘2006-12-07 15:32:30’, ‘0000-00-00’, 0, 1),
(23, 4, 1, 709, ‘hkg lhh’, ‘Unknown’, 0, ‘2006-12-07 15:33:19’, ‘0000-00-00’, 0, 1),
(24, 9, 1, 250000, ‘Huse ‘, ‘Unknown’, 0, ‘2006-12-07 15:36:36’, ‘0000-00-00’, 0, 1),
(25, 9, 1, 798798, ‘hgjhg hjgjh gk’, ‘Unknown’, 0, ‘2006-12-07 15:49:32’, ‘0000-00-00’, 0, 1),
(26, 9, 1, 8098, ‘hjlhj hljk ‘, ‘Unknown’, 0, ‘2006-12-07 15:51:24’, ‘0000-00-00’, 0, 1),
(27, 9, 1, 8098, ‘hjlhj hljk ‘, ‘q5948389’, 0, ‘2006-12-07 15:52:23’, ‘0000-00-00’, 2, 1),
(28, 4, 1, 100000, ‘lll’, ‘Unknown’, 0, ‘2006-12-07 16:00:40’, ‘0000-00-00’, 0, 1),
(29, 6, 2, 150000, ‘lklk’, ‘Unknown’, 0, ‘2006-12-07 16:16:13’, ‘0000-00-00’, 0, 1),
(30, 6, 2, 150000, ‘lklk’, ‘Unknown’, 0, ‘2006-12-07 16:16:40’, ‘0000-00-00’, 0, 1),
(31, 9, 2, 150000, ‘lkl’, ‘Unknown’, 0, ‘2006-12-07 16:18:37’, ‘0000-00-00’, 0, 1),
(32, 8, 1, 190000, ‘kjlkj’, ‘Unknown’, 0, ‘2006-12-07 16:22:51’, ‘0000-00-00’, 0, 1),
(33, 3, 1, 150000, ‘klkjl;’, ‘Unknown’, 0, ‘2006-12-07 16:23:52’, ‘0000-00-00’, 0, 1),
(34, 4, 1, 125000, ‘home sweet hme’, ‘Unknown’, 0, ‘2006-12-08 11:22:47’, ‘0000-00-00’, 0, 1),
(35, 10, 2, 340590, ‘ll Place Road’, ‘QF324903’, 0, ‘0000-00-00 00:00:00’, ‘0000-00-00’, 0, 1),
(36, 11, 1, 450000, ‘As Above’, ‘Unknown’, 0, ‘0000-00-00 00:00:00’, ‘0000-00-00’, 0, 1),
(37, 12, 1, 290000, ‘203 Truro Street\r\nTorquay’, ‘Unknown’, 0, ‘0000-00-00 00:00:00’, ‘0000-00-00’, 0, 2),
(38, 13, 1, 490000, ‘’, ‘q58300’, 0, ‘0000-00-00 00:00:00’, ‘0000-00-00’, 0, 1);
– phpMyAdmin SQL Dump
– version 2.9.0.3
– http://www.phpmyadmin.net
–
– Host: localhost
– Generation Time: Dec 12, 2006 at 09:06 AM
– Server version: 5.0.27
– PHP Version: 5.2.0
–
– Database: cams
–
–
– Table structure for table splits
–
CREATE TABLE splits (
split\_id int(11) NOT NULL auto_increment,
loan\_id int(11) NOT NULL,
product int(11) NOT NULL,
rate decimal(4,2) NOT NULL,
amount decimal(12,2) NOT NULL,
PRIMARY KEY (split\_id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=38 ;
–
– Dumping data for table splits
–
INSERT INTO splits (split\_id, loan\_id, product, rate, amount) VALUES
(25, 1, 1, 6.90, 250000.00),
(26, 35, 1, 5.08, 150000.00),
(27, 7, 1, 6.70, 100000.00),
(28, 2, 1, 5.06, 150000.00),
(29, 5, 1, 8.00, 190000.00),
(30, 2, 2, 7.05, 25000.00),
(31, 3, 2, 5.00, 50000.00),
(32, 4, 1, 7.05, 150000.00),
(33, 6, 1, 7.05, 190000.00),
(34, 8, 1, 7.50, 390000.00),
(35, 9, 1, 7.05, 190000.00),
(36, 11, 1, 8.00, 170000.00),
(37, 27, 1, 4.00, 250000.00);
Thanks in advance for your help
-Cam