[RESOLVED] My query not compatible in another MySQL version

kcgame

Hi all,

I'm encounter a problem after uploading my scripts that contain the following:

$topic2=mysql_query("SELECT parent_id, name, author, create_t, child_id, id FROM topic as foo WHERE child_id = (select MAX(child_id) from topic WHERE realm_id = '$newarray[RealmID]' AND parent_id = foo.parent_id) AND realm_id = '$newarray[RealmID]' ORDER BY create_t DESC",$link ) or die(mysql_error());

The above query works fine on my local terminal which is running 4.1.12 but when i upload it onto the server, it prompts 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 'select MAX(child_id) from topic WHERE realm_id = '1' AND parent

Any help / advice will be greatly appreciated. Thank you.

leatherback

Probably a variable is not set properly. Echo out the querystring too, and see whether al variables are passed properly.

kcgame

leatherback wrote:
Probably a variable is not set properly. Echo out the querystring too, and see whether al variables are passed properly.

Hi leatherback,

This query works fine locally (on my local computer), so i assume that mysql 4.0.22 (on the server) does not accept this format 🙁 Will try out your suggestion anyway..thanks.

kcgame

Problem solved 🙂

Get the solution from a SQL expert

select foo.parent_id
     , foo.name
     , foo.author
     , foo.create_t
     , foo.child_id
     , foo.id
  from topic as foo 
inner
  join topic as bar 
    on foo.parent_id 
     = bar.parent_id 
 where bar.realm_id = $newarray[RealmID]
group
    by foo.parent_id
     , foo.name
     , foo.author
     , foo.create_t
     , foo.child_id
     , foo.id
having foo.child_id
     = max(bar.child_id)
order 
    by foo.create_t desc

Thanks for the help anyway.