[eluser]kodai[/eluser]
I used this code before in SVN 1.62 and run well but in 1.72 run with error
Code: $this->db->select('tbl_menu.ID_MENU, concat(repeat(" ",length(MENU_PATH)/4),tbl_menu.MENU_NAME) as MENU_NAME2,ID_MENU_PARENT,MENU_PATH,SEQUENCE_NUMBER,MENU_URL,MENU_TYPE,STATUS_ACTIVE,MENU_NAME');
$this->db->from("tbl_menu");
$this->db->orderby('concat(MENU_PATH,if(ID_MENU>999,"",if(ID_MENU>99,"0",if(ID_MENU>9,"00","000"))),ID_MENU)');
$this->db->orderby('ID_MENU_PARENT','ASC');
$this->db->orderby('MENU_PATH','ASC');
Quote:A Database Error Occurred
Error Number: 1064
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM (`tbl_menu`) ORDER BY concat(MENU_PATH, if(ID_MENU>999, `""`, if(ID_MENU>99' at line 2
SELECT `tbl_menu`.`ID_MENU`, concat(repeat(" ", length(MENU_PATH)/4), `tbl_menu`.`MENU_NAME)` as MENU_NAME2, `ID_MENU_PARENT`, `MENU_PATH`, `SEQUENCE_NUMBER`, `MENU_URL`, `MENU_TYPE`, `STATUS_ACTIVE`, `MENU_NAME` FROM (`tbl_menu`) ORDER BY concat(MENU_PATH, if(ID_MENU>999, `""`, if(ID_MENU>99, `"0"`, if(ID_MENU>9, `"00"`, `"000")))`, `ID_MENU)`, `ID_MENU_PARENT` ASC, `MENU_PATH` ASC
Probably someone can help to investigate it? Thanks
[eluser]rogierb[/eluser]
Your query gets escaped automatically in 1.7.2.
Try using single quotes.
[eluser]kodai[/eluser]
[quote author="rogierb" date="1258648638"]Your query gets escaped automatically in 1.7.2.
Try using single quotes.[/quote]
I have try, but still, can you show me which that i must change to single quotes?
THanks
[eluser]rogierb[/eluser]
Code: $this->db->orderby('concat(MENU_PATH,if(ID_MENU>999,"",if(ID_MENU>99,"0",if(ID_MENU>9,"00","000"))),ID_MENU)');
into
Code: $this->db->orderby("concat(MENU_PATH,if(ID_MENU>999,'',if(ID_MENU>99,'0',if(ID_MENU>9,'00','000'))),ID_MENU)");
And the other parts aswell if they give you any trouble.
[eluser]kodai[/eluser]
Quote:A Database Error Occurred
Error Number: 1064
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' `ID_MENU)`, `ID_MENU_PARENT` ASC, `MENU_PATH` ASC' at line 3
SELECT `ID_MENU`, concat(repeat(" ", length(MENU_PATH)/4), `MENU_NAME` ) as MENU_NAME2, `tbl_menu`.* FROM (`tbl_menu`) ORDER BY concat(MENU_PATH, if(ID_MENU>999, `''`, if(ID_MENU>99, `'0'`, if(ID_MENU>9, `'00'`, `'000')))`, `ID_MENU)`, `ID_MENU_PARENT` ASC, `MENU_PATH` ASC
This the last error , i have try also to change 0 with repeat(char(48),x) but still error.
[eluser]aptereket[/eluser]
don't use quotes `
[eluser]kodai[/eluser]
I have spend 2 days for this kind of problem, seem bugs in database class
Thanks
[eluser]kodai[/eluser]
[quote author="aptereket" date="1258777930"]don't use quotes `[/quote]
please copy paste this code and run it
$this->db->orderby("concat(MENU_PATH,if(ID_MENU>999,'',if(ID_MENU>99,'0',if(ID_MENU>9,'00','000'))),ID_MENU)");
then you will know what the problem is
[eluser]kodai[/eluser]
[quote author="kodai" date="1259043968"][quote author="aptereket" date="1258777930"]don't use quotes `[/quote]
please copy paste this code and run it
$this->db->orderby("concat(MENU_PATH,if(ID_MENU>999,'',if(ID_MENU>99,'0',if(ID_MENU>9,'00','000'))),ID_MENU)");
then you will know what the problem is [/quote]
Still the same error :
Thanks for your help, but please create the table and try your self :
Code: CREATE TABLE "tbl_menu" (
"ID_MENU" int(11) NOT NULL AUTO_INCREMENT,
"ID_MENU_PARENT" varchar(10) DEFAULT NULL,
"MENU_NAME" varchar(255) DEFAULT NULL,
"SEQUENCE_NUMBER" int(11) DEFAULT NULL,
"MENU_URL" varchar(255) DEFAULT NULL,
"MENU_ICON" varchar(50) DEFAULT NULL,
"MENU_ATTRIBUTE" varchar(255) DEFAULT NULL,
"MENU_TYPE" varchar(20) DEFAULT NULL,
"STATUS_ACTIVE" int(11) DEFAULT NULL,
"MENU_PATH" varchar(255) DEFAULT NULL,
PRIMARY KEY ("ID_MENU")
) AUTO_INCREMENT=1;
[eluser]InsiteFX[/eluser]
You should be escaping the fields not using quotes, also you did not specify the database engine type and the charset.
Code: CREATE TABLE `tbl_menu` (
`ID_MENU` int(11) NOT NULL AUTO_INCREMENT,
`ID_MENU_PARENT` varchar(10) DEFAULT NULL,
`MENU_NAME` varchar(255) DEFAULT NULL,
`SEQUENCE_NUMBER` int(11) DEFAULT NULL,
`MENU_URL` varchar(255) DEFAULT NULL,
`MENU_ICON` varchar(50) DEFAULT NULL,
`MENU_ATTRIBUTE` varchar(255) DEFAULT NULL,
`MENU_TYPE` varchar(20) DEFAULT NULL,
`STATUS_ACTIVE` int(11) DEFAULT NULL,
`MENU_PATH` varchar(255) DEFAULT NULL,
PRIMARY KEY (`ID_MENU`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;
Enjoy
InsiteFX
|