sql in fields.ini

by **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.

by **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

by **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

sql in fields.ini

Who is online