Benobis Posted January 6, 2013 Share Posted January 6, 2013 Hi, I changed mysql4 to mysql5 and have a problem. Propably this query from mysql4 (worked there fine) gives me syntax error in mysql5: $q="select gs_hr.rin$hr as rin,gs_hr.uin$hr as uin,gs_hr.cl$hr as cl,gs_hr.out$hr as out,gs_hr.f$hr as f,gs_hr.a$hr as a,gs_hr.rin$hrl as rinhrl,gs_hr.out$hrl as outhrl,gs_hr.d as d,gs_tr.r as r,gs_tr.t as t,gs_tr.tr as tr,gs_tr.otr as otr,gs_tr.htr as htr,gs_tr.hotr as hotr,gs_tr.dowed as dowed,gs_tr.a as ca,gs_tr.drin as drin,gs_tr.dout as dout,gs_tr.etr as etr from gs_hr,gs_tr where gs_hr.d=gs_tr.d"; I need this query working on mysql5. Could you please help me convert it ? Benobis Quote Link to comment https://forums.phpfreaks.com/topic/272745-mysql4-to-mysql5-migration-problem-sql-syntax/ Share on other sites More sharing options...
jazzman1 Posted January 6, 2013 Share Posted January 6, 2013 Can you echo it we to see the real string? Quote Link to comment https://forums.phpfreaks.com/topic/272745-mysql4-to-mysql5-migration-problem-sql-syntax/#findComment-1403527 Share on other sites More sharing options...
Benobis Posted January 6, 2013 Author Share Posted January 6, 2013 (edited) Hi, Thanks for reply. Echo shows me: 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 'out,gs_hr.f4 as f,gs_hr.a4 as a,gs_hr.rin3 as rinhrl,gs_hr.out3 as outhrl,gs_hr.' at line 1 On site is got error: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in ... 131 Lines 129-131: 129 | $q="select gs_hr.rin$hr as rin,gs_hr.uin$hr as uin,gs_hr.cl$hr as cl,gs_hr.out$hr as out,gs_hr.f$hr as f,gs_hr.a$hr as a,gs_hr.rin$hrl as rinhrl,gs_hr.out$hrl as outhrl,gs_hr.d as d,gs_tr.r as r,gs_tr.t as t,gs_tr.tr as tr,gs_tr.otr as otr,gs_tr.htr as htr,gs_tr.hotr as hotr,gs_tr.dowed as dowed,gs_tr.a as ca,gs_tr.drin as drin,gs_tr.dout as dout,gs_tr.etr as etr from gs_hr,gs_tr where gs_hr.d=gs_tr.d"; 130 | $r=mysql_query($q); 131 | while($row=mysql_fetch_array($r)){$d=$row["d"]; Please help Edited January 6, 2013 by Benobis Quote Link to comment https://forums.phpfreaks.com/topic/272745-mysql4-to-mysql5-migration-problem-sql-syntax/#findComment-1403580 Share on other sites More sharing options...
jazzman1 Posted January 6, 2013 Share Posted January 6, 2013 No, just stop it: Line: 129 $q="select gs_hr.rin$hr as rin,gs_hr.uin$hr as uin,gs_hr.cl$hr as cl,gs_hr.out$hr as out,gs_hr.f$hr as f,gs_hr.a$hr as a,gs_hr.rin$hrl as rinhrl,gs_hr.out$hrl as outhrl,gs_hr.d as d,gs_tr.r as r,gs_tr.t as t,gs_tr.tr as tr,gs_tr.otr as otr,gs_tr.htr as htr,gs_tr.hotr as hotr,gs_tr.dowed as dowed,gs_tr.a as ca,gs_tr.drin as drin,gs_tr.dout as dout,gs_tr.etr as etr from gs_hr,gs_tr where gs_hr.d=gs_tr.d" echo $q; exit; Quote Link to comment https://forums.phpfreaks.com/topic/272745-mysql4-to-mysql5-migration-problem-sql-syntax/#findComment-1403594 Share on other sites More sharing options...
Benobis Posted January 6, 2013 Author Share Posted January 6, 2013 added. Got messg on the screen: select gs_hr.rin12 as rin,gs_hr.uin12 as uin,gs_hr.cl12 as cl,gs_hr.out12 as out,gs_hr.f12 as f,gs_hr.a12 as a,gs_hr.rin11 as rinhrl,gs_hr.out11 as outhrl,gs_hr.d as d,gs_tr.r as r,gs_tr.t as t,gs_tr.tr as tr,gs_tr.otr as otr,gs_tr.htr as htr,gs_tr.hotr as hotr,gs_tr.dowed as dowed,gs_tr.a as ca,gs_tr.drin as drin,gs_tr.dout as dout,gs_tr.etr as etr from gs_hr,gs_tr where gs_hr.d=gs_tr.d Can you help me please? Quote Link to comment https://forums.phpfreaks.com/topic/272745-mysql4-to-mysql5-migration-problem-sql-syntax/#findComment-1403676 Share on other sites More sharing options...
kicken Posted January 6, 2013 Share Posted January 6, 2013 Mysql Reserved Words - OUT is on that list. Change your alias name, or surround it with backticks. Quote Link to comment https://forums.phpfreaks.com/topic/272745-mysql4-to-mysql5-migration-problem-sql-syntax/#findComment-1403678 Share on other sites More sharing options...
Benobis Posted January 6, 2013 Author Share Posted January 6, 2013 Hi, Can you give me example how it should look like with backticks please? Quote Link to comment https://forums.phpfreaks.com/topic/272745-mysql4-to-mysql5-migration-problem-sql-syntax/#findComment-1403680 Share on other sites More sharing options...
Pikachu2000 Posted January 6, 2013 Share Posted January 6, 2013 Kicken linked the manual page. Did you even bother to look at it? Quote Link to comment https://forums.phpfreaks.com/topic/272745-mysql4-to-mysql5-migration-problem-sql-syntax/#findComment-1403683 Share on other sites More sharing options...
Benobis Posted January 6, 2013 Author Share Posted January 6, 2013 yes, i checked it...but sorry... im not sure how to use backticks .. should it be somethg like this: $q="select 'gs_hr.rin$hr' as 'rin','gs_hr.uin$hr' as 'uin', ...... ? Quote Link to comment https://forums.phpfreaks.com/topic/272745-mysql4-to-mysql5-migration-problem-sql-syntax/#findComment-1403684 Share on other sites More sharing options...
Jessica Posted January 6, 2013 Share Posted January 6, 2013 A backtick is ` It's usually next to the 1 on your keyboard. Quote Link to comment https://forums.phpfreaks.com/topic/272745-mysql4-to-mysql5-migration-problem-sql-syntax/#findComment-1403685 Share on other sites More sharing options...
Pikachu2000 Posted January 6, 2013 Share Posted January 6, 2013 'quotes' and `backticks` Quote Link to comment https://forums.phpfreaks.com/topic/272745-mysql4-to-mysql5-migration-problem-sql-syntax/#findComment-1403686 Share on other sites More sharing options...
Benobis Posted January 6, 2013 Author Share Posted January 6, 2013 ok.... thanks ... so it will be fine like this: $q="select `gs_hr.rin$hr` as `rin`,`gs_hr.uin$hr` as `uin`, ...... ? Quote Link to comment https://forums.phpfreaks.com/topic/272745-mysql4-to-mysql5-migration-problem-sql-syntax/#findComment-1403687 Share on other sites More sharing options...
PFMaBiSmAd Posted January 6, 2013 Share Posted January 6, 2013 Also, the issue is only for one of the alias names. Quote Link to comment https://forums.phpfreaks.com/topic/272745-mysql4-to-mysql5-migration-problem-sql-syntax/#findComment-1403689 Share on other sites More sharing options...
Jessica Posted January 6, 2013 Share Posted January 6, 2013 Maybe you should use some reasonable names. There's no way anyone can read that. Quote Link to comment https://forums.phpfreaks.com/topic/272745-mysql4-to-mysql5-migration-problem-sql-syntax/#findComment-1403693 Share on other sites More sharing options...
Benobis Posted January 6, 2013 Author Share Posted January 6, 2013 i see ... but i have to use backticks like this: $q="select `gs_hr.rin$hr` as `rin`,`gs_hr.uin$hr` as `uin`, ? Quote Link to comment https://forums.phpfreaks.com/topic/272745-mysql4-to-mysql5-migration-problem-sql-syntax/#findComment-1403708 Share on other sites More sharing options...
jazzman1 Posted January 6, 2013 Share Posted January 6, 2013 i see ... but i have to use backticks like this: $q="select `gs_hr.rin$hr` as `rin`,`gs_hr.uin$hr` as `uin`, ? yes Quote Link to comment https://forums.phpfreaks.com/topic/272745-mysql4-to-mysql5-migration-problem-sql-syntax/#findComment-1403711 Share on other sites More sharing options...
jazzman1 Posted January 6, 2013 Share Posted January 6, 2013 Make sure that you have an empty space between a select and the first backtick, after and before FROM too! Quote Link to comment https://forums.phpfreaks.com/topic/272745-mysql4-to-mysql5-migration-problem-sql-syntax/#findComment-1403729 Share on other sites More sharing options...
Pikachu2000 Posted January 6, 2013 Share Posted January 6, 2013 I'm relatively certain you don't need backticks with this syntax: table.field, and if you did use them for whatever reason, it would be `table`.`field` rather than `table.field` Quote Link to comment https://forums.phpfreaks.com/topic/272745-mysql4-to-mysql5-migration-problem-sql-syntax/#findComment-1403732 Share on other sites More sharing options...
Benobis Posted January 6, 2013 Author Share Posted January 6, 2013 Hi, Thanks for reply. So it should looks like: $q="select `gs_hr`.`rin$hr` as `rin`,`gs_hr`.`uin$hr` as `uin`,`gs_hr`.`cl$hr` as `cl`,`gs_hr`.`out$hr` as `out`,`gs_hr`.`f$hr` as `f`,`gs_hr`.`a$hr` as `a`,`gs_hr`.`rin$hrl` as `rinhrl`,`gs_hr`.`out$hrl` as `outhrl`,`gs_hr`.`d` as `d`,`gs_tr`.`r` as `r`,`gs_tr`.`t` as `t`,`gs_tr`.`tr` as `tr`,`gs_tr`.`otr` as `otr`,`gs_tr`.`htr` as `htr`,`gs_tr`.`hotr` as `hotr`,`gs_tr`.`dowed` as `dowed`,`gs_tr`.`a` as `ca`,`gs_tr`.`drin` as `drin`,`gs_tr`.`dout` as `dout`,`gs_tr`.`etr` as `etr` from `gs_hr`,`gs_tr` where gs_hr.d=gs_tr.d"; Right? Quote Link to comment https://forums.phpfreaks.com/topic/272745-mysql4-to-mysql5-migration-problem-sql-syntax/#findComment-1403736 Share on other sites More sharing options...
Pikachu2000 Posted January 6, 2013 Share Posted January 6, 2013 No. You're over-complicating it to death. Just table.field is fine, backticks should never be needed with that syntax. Your initial problem was caused by one of the AS aliases: as out, not a table or field name. Really, your best course of action would be to simply forget about the backticks and use a different field alias; one that isn't a reserved word. Quote Link to comment https://forums.phpfreaks.com/topic/272745-mysql4-to-mysql5-migration-problem-sql-syntax/#findComment-1403741 Share on other sites More sharing options...
jazzman1 Posted January 6, 2013 Share Posted January 6, 2013 Pika's reply is correct! Your sql string is not readable at all, try to re-design it. Quote Link to comment https://forums.phpfreaks.com/topic/272745-mysql4-to-mysql5-migration-problem-sql-syntax/#findComment-1403763 Share on other sites More sharing options...
Christian F. Posted January 6, 2013 Share Posted January 6, 2013 First step should be to give your tables and your fields some meaningful names. I have no idea what any of those fields or tables contain, and I can guarantee you that you won't either in about 2-3 months time. So since you're already migrating, do yourself the favour to redesign and clean up the database. You'll thank yourself later, trust me! Quote Link to comment https://forums.phpfreaks.com/topic/272745-mysql4-to-mysql5-migration-problem-sql-syntax/#findComment-1403775 Share on other sites More sharing options...
Benobis Posted January 7, 2013 Author Share Posted January 7, 2013 Thank you all for your replies and help. This trick saved my life I'm relatively certain you don't need backticks with this syntax: table.field, and if you did use them for whatever reason, it would be `table`.`field` rather than `table.field` Pikachu2000 Now all is fine. Once again thanks ! ! Regards, Quote Link to comment https://forums.phpfreaks.com/topic/272745-mysql4-to-mysql5-migration-problem-sql-syntax/#findComment-1403884 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.