Okay after getting quite frustrated, (and probably frustrating phishy
and phpnut on irc) I wrote a testcase and amended it to trac here:
https://trac.cakephp.org/ticket/1762
I've duplicated the post here for completeness!
Regards,
Simon
=
Scratch the last I've determined that it's the version of SQL, as
phpnut mentioned on IRC anything mysql 4 isn't supported. Which is
good, because 4.1.10 (ports, ppc mac os 10.3.9) does this, and 4.0.25
(intel debian stable) doesn't (I've not been able to check anything
else)
I decided to do this test case because I wasn't sure if I was going
mad with the versions of php or whatever. So it looks very much like
mysql doing this! not the client libraries or the version of php.
{{{
$USERNAME=FILL ME IN;
$PASSWORD=ME TOO;
$DB =ME ALSO
$HOST=AND ME
$link = mysql_connect($HOST, $USERNAME, $PASSWORD)
or die (Could not connect);
$sql = SELECT count(*) as count,sectors.* FROM jobs,sectors where
sectors.id=jobs.sector_id GROUP BY id;
$result = mysql_db_query($DB,$sql);
echo Result: $result\n;
echo mysql_errno($link) . : . mysql_error($link). \n;
$num_fields = mysql_num_fields($result);
$index = 0;
$j = 0;
while ($j $num_fields) {
$column = mysql_fetch_field($result,$j);
print_r($column);
echo \n;
$j++;
}
?
}}}
Now the data
{{{
CREATE TABLE `jobs` (
`id` int(11) NOT NULL auto_increment,
`sector_id` int(11) default NULL,
`consultant_id` int(11) default NULL,
`jobtype_id` int(11) NOT NULL default '4',
`duration` varchar(255) default NULL,
PRIMARY KEY (`id`)
) TYPE=MyISAM;
INSERT INTO `jobs`
(`id`,`sector_id`,`consultant_id`,`jobtype_id`,`duration`) VALUES
(987,28,22,3,Permanent);
INSERT INTO `jobs`
(`id`,`sector_id`,`consultant_id`,`jobtype_id`,`duration`) VALUES
(984,16,12,2,Permanent);
INSERT INTO `jobs`
(`id`,`sector_id`,`consultant_id`,`jobtype_id`,`duration`) VALUES
(737,10,18,4,Permanent);
INSERT INTO `jobs`
(`id`,`sector_id`,`consultant_id`,`jobtype_id`,`duration`) VALUES
(957,16,12,2,Permanent);
INSERT INTO `jobs`
(`id`,`sector_id`,`consultant_id`,`jobtype_id`,`duration`) VALUES
(814,37,4,3,Permanent);
INSERT INTO `jobs`
(`id`,`sector_id`,`consultant_id`,`jobtype_id`,`duration`) VALUES
(915,39,22,3,Permanent);
INSERT INTO `jobs`
(`id`,`sector_id`,`consultant_id`,`jobtype_id`,`duration`) VALUES
(917,39,13,3,Temp);
INSERT INTO `jobs`
(`id`,`sector_id`,`consultant_id`,`jobtype_id`,`duration`) VALUES
(1005,38,18,1,Permanent);
INSERT INTO `jobs`
(`id`,`sector_id`,`consultant_id`,`jobtype_id`,`duration`) VALUES
(1010,16,20,2,Permanent);
CREATE TABLE `sectors` (
`id` int(11) NOT NULL auto_increment,
`name` varchar(100) default NULL,
PRIMARY KEY (`id`)
) TYPE=MyISAM;
INSERT INTO `sectors` (`id`,`name`) VALUES (13,Charities);
INSERT INTO `sectors` (`id`,`name`) VALUES (12,Care);
INSERT INTO `sectors` (`id`,`name`) VALUES (10,Accountancy);
INSERT INTO `sectors` (`id`,`name`) VALUES (11,Arts);
INSERT INTO `sectors` (`id`,`name`) VALUES (14,Consultancy);
INSERT INTO `sectors` (`id`,`name`) VALUES (15,Councils);
INSERT INTO `sectors` (`id`,`name`) VALUES (16,Education);
INSERT INTO `sectors` (`id`,`name`) VALUES (17,Engineering);
INSERT INTO `sectors` (`id`,`name`) VALUES (18,Entertainment);
INSERT INTO `sectors` (`id`,`name`) VALUES (19,Events);
INSERT INTO `sectors` (`id`,`name`) VALUES (20,Finance/Banking);
INSERT INTO `sectors` (`id`,`name`) VALUES (21,FMCG);
INSERT INTO `sectors` (`id`,`name`) VALUES (22,Fashion);
INSERT INTO `sectors` (`id`,`name`) VALUES (23,Government
Departments);
INSERT INTO `sectors` (`id`,`name`) VALUES (24,Hospitality);
INSERT INTO `sectors` (`id`,`name`) VALUES (25,Housing);
INSERT INTO `sectors` (`id`,`name`) VALUES (45,Industrial);
INSERT INTO `sectors` (`id`,`name`) VALUES (27,Insurance);
INSERT INTO `sectors` (`id`,`name`) VALUES (28,IT);
INSERT INTO `sectors` (`id`,`name`) VALUES (29,Legal);
INSERT INTO `sectors` (`id`,`name`) VALUES (30,Marketing/Sales);
INSERT INTO `sectors` (`id`,`name`) VALUES (31,Media/PR/
Advertising);
INSERT INTO `sectors` (`id`,`name`) VALUES (32,Medical/National
Health);
INSERT INTO `sectors` (`id`,`name`) VALUES (33,Non-for-Profit);
INSERT INTO `sectors` (`id`,`name`) VALUES (34,Oil/Gas);
INSERT INTO `sectors` (`id`,`name`) VALUES (35,Property/Real
Estate);
INSERT INTO `sectors` (`id`,`name`) VALUES (36,Publishing);
INSERT INTO `sectors` (`id`,`name`) VALUES (37,Recruitment/
Resourcing);
INSERT INTO `sectors` (`id`,`name`) VALUES (38,Retail);
INSERT INTO `sectors` (`id`,`name`) VALUES (39,Secretarial/
Administration);
INSERT INTO `sectors` (`id`,`name`) VALUES (40,Sport);
INSERT INTO `sectors` (`id`,`name`) VALUES (41,Support Services);
INSERT INTO `sectors` (`id`,`name`) VALUES
(42,Telecommunications);
INSERT INTO `sectors` (`id`,`name`) VALUES (43,Training);
INSERT INTO `sectors` (`id`,`name`) VALUES (44,Travel);
INSERT INTO `sectors` (`id`,`name`) VALUES (46,Sales);
INSERT INTO `sectors` (`id`,`name`) VALUES (48,Construction);
INSERT INTO `sectors`
