Welcome Guest, Not a member yet? Register   Sign In
Weird in 1.72
#1

[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 Smile
#2

[eluser]rogierb[/eluser]
Your query gets escaped automatically in 1.7.2.
Code:
`”“`

Try using single quotes.
#3

[eluser]kodai[/eluser]
[quote author="rogierb" date="1258648638"]Your query gets escaped automatically in 1.7.2.
Code:
`”“`

Try using single quotes.[/quote]
I have try, but still, can you show me which that i must change to single quotes?
THanks
#4

[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.
#5

[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 Sad, i have try also to change 0 with repeat(char(48),x) but still error.
#6

[eluser]aptereket[/eluser]
don't use quotes `
#7

[eluser]kodai[/eluser]
I have spend 2 days for this kind of problem, seem bugs in database class Sad
Thanks
#8

[eluser]kodai[/eluser]
[quote author="aptereket" date="1258777930"]don't use quotes `[/quote]

please copy paste this code and run it Smile

$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 Smile
#9

[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 Smile

$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 Smile[/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;
#10

[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




Theme © iAndrew 2016 - Forum software by © MyBB