Description:
I noticed a bug in MySQL engine: building a form (in a PHP script) I need to get the name of the table which the field of select comes from - I use the mysql_field_table() function. On my machine (WinXP Profesional, latest SP..., PHP 4.3.4)
Running MySQL 4.0.17 everything worked perefectly. When I have upgraded my MySQL engine to version 4.1.1-alpha, I started to notice some problems - function mysql_field_table() stopped to work with all statements containing either group by and/or order by statements.
For me, two solutions work for 100% each - leaving everythig else unchanged:
1) ommit all group by and order by statements in all scipts
2) change MySQL version back to version 4.0.17.
With my MySQL 4.1.1-alpha this problem appeared, the same statement running in identical environment (PHP engine 4.3.4, same script), except for the MySQL server which I changed back to version 4.0.17 - everything is OK again. As an example follows one of the statements which case the problem:
select den, hod_id, vyuka.tr_ozn_id, predm_id, vyuka.mist_id, concat('<a href=./d_v_zaci.php?vyuka_id=',replace(vyuka_id,' ','%20'),'>Výsledky</a>') as moznosti from vyuka, tridy_oznac, ucitele where vyuka.tr_ozn_id=tridy_oznac.tr_ozn_id and vyuka.ucit_id=ucitele.ucit_id and tridy_oznac.rok_id='1' and ucitele.osoba_id='admin' order by den, hod_id
I hope I supplied all the information that could be useful for You solving the problem. I do not think it is a PHP, problem, because, as I stated before, this happens only with MySQL 4.1.1-alpha.
J.Resler
How to repeat:
select den, hod_id, vyuka.tr_ozn_id, predm_id, vyuka.mist_id, concat('<a href=./d_v_zaci.php?vyuka_id=',replace(vyuka_id,' ','%20'),'>Výsledky</a>') as moznosti from vyuka, tridy_oznac, ucitele where vyuka.tr_ozn_id=tridy_oznac.tr_ozn_id and vyuka.ucit_id=ucitele.ucit_id and tridy_oznac.rok_id='1' and ucitele.osoba_id='admin' order by den, hod_id
Structure of the tables used in the statement is following:
CREATE TABLE `vyuka` (
`vyuka_id` int(10) unsigned NOT NULL auto_increment,
`tr_ozn_id` int(11) NOT NULL default '0',
`predm_id` varchar(6) NOT NULL default '',
`ucit_id` varchar(15) NOT NULL default '',
`mist_id` varchar(10) default NULL,
`den` enum('Po','Út','St','Èt','Pá') NOT NULL default 'Po',
`hod_id` char(1) NOT NULL default '',
PRIMARY KEY (`vyuka_id`),
UNIQUE KEY `ucitel_unik` (`ucit_id`,`den`,`hod_id`),
UNIQUE KEY `trida_unik` (`tr_ozn_id`,`den`,`hod_id`),
UNIQUE KEY `mistnost_unik` (`mist_id`,`den`,`hod_id`)
) TYPE=MyISAM;
CREATE TABLE `tridy_oznac` (
`tr_ozn_id` int(10) unsigned NOT NULL auto_increment,
`trida_ozn` varchar(10) NOT NULL default '',
`trida_id` int(11) NOT NULL default '0',
`rok_id` int(11) NOT NULL default '0',
`ucit_id` varchar(15) NOT NULL default '',
`mist_id` varchar(10) NOT NULL default '',
PRIMARY KEY (`tr_ozn_id`),
UNIQUE KEY `trida_id` (`trida_id`,`rok_id`)
) TYPE=MyISAM;
CREATE TABLE `ucitele` (
`ucit_id` varchar(15) NOT NULL default '',
`osoba_id` varchar(15) NOT NULL default '',
PRIMARY KEY (`ucit_id`),
UNIQUE KEY `osoba_id` (`osoba_id`)
) TYPE=MyISAM;
Suggested fix:
???