Re: #sql_3e3a_0 for COUNT() queries
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
Re: #sql_3e3a_0 for COUNT() queries
It does not happen to me and I'm on: Mac OSX 10.4.8 PHP 5.1.4 mysql Ver 14.7 Distrib 4.1.20, for apple-darwin8.7.1 (i686) using EditLine wrapper from DarwinPorts.. "SELECT COUNT(*) AS count" works fine --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "Cake PHP" group. To post to this group, send email to cake-php@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/cake-php?hl=en -~--~~~~--~~--~--~---
Re: #sql_3e3a_0 for COUNT() queries
Nope, it happens for me sometimes too, and I'm on a Mac.
On Feb 1, 8:50 am, "DJ Spark" <[EMAIL PROTECTED]> wrote:
> it seems to happen only on windows machines, probably related to
> those silly db drivers we are destined to use forever. (see ms_sql 30
> characters limitations :)
>
> spark
>
> On 2/1/07, nate <[EMAIL PROTECTED]> wrote:
>
>
>
>
>
> > It happens when MySQL has to generate a temporary table in order to
> > calculate a set of results, as is sometimes the case with COUNT
> > statements or grouping.
>
> > On Jan 31, 11:56 pm, "oPless" <[EMAIL PROTECTED]> wrote:
> > > Apologies for top posting, not sure if this group is top - or bottom
> > > posting :-)
>
> > > I can confirm that this happens, and can be rather random (from memory
> > > it used to return a table name of "0" rather than #sql_hex_0) I
> > > suggest replacing the 'derived' string with the number '0' to be
> > > backwardly (forwardly?) compatible with other releases of mysql.
>
> > > See my notes onhttps://trac.cakephp.org/ticket/1762
>
> > > I'm usually on freenode so if anyone wants to discuss just /msg me
> > > (opless)
>
> > > regards
>
> > > On Dec 14 2006, 8:53 am, "TT" <[EMAIL PROTECTED]> wrote:
>
> > > > And here is a complete hack if you want custom named values for the
> > > > table-names (I use derived)
>
> > > > function resultSet(&$results) {
> > > > $this->results =& $results;
> > > > $this->map = array();
> > > > $num_fields = mysql_num_fields($results);
> > > > $index = 0;
> > > > $j = 0;
>
> > > > while ($j < $num_fields) {
>
> > > > $column = mysql_fetch_field($results,$j);
> > > > if (!empty($column->table)) {
> > > > if
> > > > (!(strpos($column->table,'#sql_')===false))
> > > > {
> > > > $this->map[$index++] =
> > > > array('derived', $column->name);
> > > > }
> > > > else
> > > > {
> > > > $this->map[$index++] =
> > > > array($column->table, $column->name);
> > > > }
> > > > } else {
> > > > $this->map[$index++] = array(0,
> > > > $column->name);
> > > > }
> > > > $j++;
> > > > }
> > > > }
>
> --
> [web]http://synapsisdi.com.br
> [livesets]http://djspark.com.br/mp3
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups "Cake
PHP" group.
To post to this group, send email to cake-php@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/cake-php?hl=en
-~--~~~~--~~--~--~---
Re: #sql_3e3a_0 for COUNT() queries
it seems to happen only on windows machines, probably related to
those silly db drivers we are destined to use forever. (see ms_sql 30
characters limitations :)
spark
On 2/1/07, nate <[EMAIL PROTECTED]> wrote:
>
> It happens when MySQL has to generate a temporary table in order to
> calculate a set of results, as is sometimes the case with COUNT
> statements or grouping.
>
> On Jan 31, 11:56 pm, "oPless" <[EMAIL PROTECTED]> wrote:
> > Apologies for top posting, not sure if this group is top - or bottom
> > posting :-)
> >
> > I can confirm that this happens, and can be rather random (from memory
> > it used to return a table name of "0" rather than #sql_hex_0) I
> > suggest replacing the 'derived' string with the number '0' to be
> > backwardly (forwardly?) compatible with other releases of mysql.
> >
> > See my notes onhttps://trac.cakephp.org/ticket/1762
> >
> > I'm usually on freenode so if anyone wants to discuss just /msg me
> > (opless)
> >
> > regards
> >
> > On Dec 14 2006, 8:53 am, "TT" <[EMAIL PROTECTED]> wrote:
> >
> > > And here is a complete hack if you want custom named values for the
> > > table-names (I use derived)
> >
> > > function resultSet(&$results) {
> > > $this->results =& $results;
> > > $this->map = array();
> > > $num_fields = mysql_num_fields($results);
> > > $index = 0;
> > > $j = 0;
> >
> > > while ($j < $num_fields) {
> >
> > > $column = mysql_fetch_field($results,$j);
> > > if (!empty($column->table)) {
> > > if
> > > (!(strpos($column->table,'#sql_')===false))
> > > {
> > > $this->map[$index++] =
> > > array('derived', $column->name);
> > > }
> > > else
> > > {
> > > $this->map[$index++] =
> > > array($column->table, $column->name);
> > > }
> > > } else {
> > > $this->map[$index++] = array(0,
> > > $column->name);
> > > }
> > > $j++;
> > > }
> > > }
>
>
> >
>
--
[web] http://synapsisdi.com.br
[livesets] http://djspark.com.br/mp3
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups "Cake
PHP" group.
To post to this group, send email to cake-php@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/cake-php?hl=en
-~--~~~~--~~--~--~---
Re: #sql_3e3a_0 for COUNT() queries
It happens when MySQL has to generate a temporary table in order to
calculate a set of results, as is sometimes the case with COUNT
statements or grouping.
On Jan 31, 11:56 pm, "oPless" <[EMAIL PROTECTED]> wrote:
> Apologies for top posting, not sure if this group is top - or bottom
> posting :-)
>
> I can confirm that this happens, and can be rather random (from memory
> it used to return a table name of "0" rather than #sql_hex_0) I
> suggest replacing the 'derived' string with the number '0' to be
> backwardly (forwardly?) compatible with other releases of mysql.
>
> See my notes onhttps://trac.cakephp.org/ticket/1762
>
> I'm usually on freenode so if anyone wants to discuss just /msg me
> (opless)
>
> regards
>
> On Dec 14 2006, 8:53 am, "TT" <[EMAIL PROTECTED]> wrote:
>
> > And here is a complete hack if you want custom named values for the
> > table-names (I use derived)
>
> > function resultSet(&$results) {
> > $this->results =& $results;
> > $this->map = array();
> > $num_fields = mysql_num_fields($results);
> > $index = 0;
> > $j = 0;
>
> > while ($j < $num_fields) {
>
> > $column = mysql_fetch_field($results,$j);
> > if (!empty($column->table)) {
> > if
> > (!(strpos($column->table,'#sql_')===false))
> > {
> > $this->map[$index++] =
> > array('derived', $column->name);
> > }
> > else
> > {
> > $this->map[$index++] =
> > array($column->table, $column->name);
> > }
> > } else {
> > $this->map[$index++] = array(0,
> > $column->name);
> > }
> > $j++;
> > }
> > }
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups "Cake
PHP" group.
To post to this group, send email to cake-php@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/cake-php?hl=en
-~--~~~~--~~--~--~---
Re: #sql_3e3a_0 for COUNT() queries
Apologies for top posting, not sure if this group is top - or bottom
posting :-)
I can confirm that this happens, and can be rather random (from memory
it used to return a table name of "0" rather than #sql_hex_0) I
suggest replacing the 'derived' string with the number '0' to be
backwardly (forwardly?) compatible with other releases of mysql.
See my notes on https://trac.cakephp.org/ticket/1762
I'm usually on freenode so if anyone wants to discuss just /msg me
(opless)
regards
On Dec 14 2006, 8:53 am, "TT" <[EMAIL PROTECTED]> wrote:
> And here is a complete hack if you want custom named values for the
> table-names (I use derived)
>
> function resultSet(&$results) {
> $this->results =& $results;
> $this->map = array();
> $num_fields = mysql_num_fields($results);
> $index = 0;
> $j = 0;
>
> while ($j < $num_fields) {
>
> $column = mysql_fetch_field($results,$j);
> if (!empty($column->table)) {
> if (!(strpos($column->table,'#sql_')===false))
> {
> $this->map[$index++] =
> array('derived', $column->name);
> }
> else
> {
> $this->map[$index++] =
> array($column->table, $column->name);
> }
> } else {
> $this->map[$index++] = array(0,
> $column->name);
> }
> $j++;
> }
> }
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups "Cake
PHP" group.
To post to this group, send email to cake-php@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/cake-php?hl=en
-~--~~~~--~~--~--~---
Re: #sql_3e3a_0 for COUNT() queries
And here is a complete hack if you want custom named values for the
table-names (I use derived)
function resultSet(&$results) {
$this->results =& $results;
$this->map = array();
$num_fields = mysql_num_fields($results);
$index = 0;
$j = 0;
while ($j < $num_fields) {
$column = mysql_fetch_field($results,$j);
if (!empty($column->table)) {
if (!(strpos($column->table,'#sql_')===false))
{
$this->map[$index++] = array('derived',
$column->name);
}
else
{
$this->map[$index++] =
array($column->table, $column->name);
}
} else {
$this->map[$index++] = array(0, $column->name);
}
$j++;
}
}
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
"Cake PHP" group.
To post to this group, send email to cake-php@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/cake-php?hl=en
-~--~~~~--~~--~--~---
Re: #sql_3e3a_0 for COUNT() queries
I already found it after digging a bit deeper into mysql/php The cause is this functionality: http://cn.php.net/manual/en/function.mysql-fetch-field.php which is used inside \cake\libs\model\dbo\dbo_mysql.php in function function resultSet() CakePHP retrieves the table-names from MySQL by the mysql-fetch-field() function and that function indeed returns #sql_3e3a_0 as the table-name for the count-field. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "Cake PHP" group. To post to this group, send email to cake-php@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/cake-php?hl=en -~--~~~~--~~--~--~---
#sql_3e3a_0 for COUNT() queries
Dear bakers, for some of my queries I use queries that contain a COUNT() and those are executed directly by execute() or query(). I do something like this: "COUNT(test.id) AS count" But since count does not belong to any Table, it gets assigned some random string in the resulting array in CakePHP that looks like '#sql_3e3a_0'. My guess this is set by the MySQL driver, but i want to be able to rename this or overwrite this behaviour in CakePHP. I would like to rename the random string to 'derived' or something like that, so that everytime i update the database, i will not get a new random string and having to update code that uses that variable. Any idea where to update this in the cake-code? --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "Cake PHP" group. To post to this group, send email to cake-php@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/cake-php?hl=en -~--~~~~--~~--~--~---
