Jump to content

Mysql4 to Mysql5 migration problem - SQL Syntax


Benobis

Recommended Posts

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

Link to comment
Share on other sites

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 by Benobis
Link to comment
Share on other sites

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;

 

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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!

Link to comment
Share on other sites

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`

:thumb-up: Pikachu2000 :thumb-up:

 

Now all is fine.

Once again thanks ! :) !

 

Regards,

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.