query problems with MySQL 5.0.x

[V@no]

Ok, please test this (if it all works, I'll post it as a compability fix):

Please note, in each file is possible more then one instance of searched line, that means all of the found lines must be changed as instructed!

In index.php,  categories.php,  details.php,  postcards.php,  search.php,  top.php,  includes/functions.php (two times) find:

Code:  [Select]  [Expand]  [Hide line numbers]

1	FROM ".IMAGES_TABLE." i,  ".CATEGORIES_TABLE." c

Replace with:

Code:  [Select]  [Expand]  [Hide line numbers]

1	FROM (".IMAGES_TABLE." i,  ".CATEGORIES_TABLE." c)

In member.php,  admin/comments.php find:

Code:  [Select]  [Expand]  [Hide line numbers]

1	FROM ".COMMENTS_TABLE." c, ".IMAGES_TABLE." i

Replace with:

Code:  [Select]  [Expand]  [Hide line numbers]

1	FROM (".COMMENTS_TABLE." c, ".IMAGES_TABLE." i)

Stop here. Test every page and only if you have problem continue next:

In postcards.php find:

Code:  [Select]  [Expand]  [Hide line numbers]

1	FROM ".POSTCARDS_TABLE." p, ".IMAGES_TABLE." i

Replace with:

Code:  [Select]  [Expand]  [Hide line numbers]

1	FROM (".POSTCARDS_TABLE." p, ".IMAGES_TABLE." i)

In search.php find:

Code:  [Select]  [Expand]  [Hide line numbers]

1	FROM ".WORDLIST_TABLE." w, ".WORDMATCH_TABLE." m

Replace with:

Code:  [Select]  [Expand]  [Hide line numbers]

1	FROM (".WORDLIST_TABLE." w, ".WORDMATCH_TABLE." m)

In includes/auth.php find:

Code:  [Select]  [Expand]  [Hide line numbers]

1	FROM ".GROUP_ACCESS_TABLE." a, ".GROUP_MATCH_TABLE." m

Replace with:

Code:  [Select]  [Expand]  [Hide line numbers]

1	FROM (".GROUP_ACCESS_TABLE." a, ".GROUP_MATCH_TABLE." m)

In admin/home.php find:

Code:  [Select]  [Expand]  [Hide line numbers]

1	FROM ".USERS_TABLE." u, ".SESSIONS_TABLE." s

Replace with:

Code:  [Select]  [Expand]  [Hide line numbers]

1	FROM (".USERS_TABLE." u, ".SESSIONS_TABLE." s)

In admin/usergroups.php find:

Code:  [Select]  [Expand]  [Hide line numbers]

1	FROM ".GROUPS_TABLE." g, ".GROUP_MATCH_TABLE." gm

Replace with:

Code:  [Select]  [Expand]  [Hide line numbers]

1	FROM (".GROUPS_TABLE." g, ".GROUP_MATCH_TABLE." gm)

[Murphy]

I'm sorry, but i get always this
(tried 2 times with a new installation and an existing album)

Code:  [Select]  [Expand]  [Hide line numbers]

1 2 3 4 5 6 7

DB Error: Bad SQL Query: SELECT i.image_id, i.cat_id, i.user_id, i.image_name, i.image_description, i.image_keywords, i.image_date, i.image_active, i.image_media_file, i.image_thumb_file, i.image_download_url, i.image_allow_comments, i.image_comments, i.image_downloads, i.image_votes, i.image_rating, i.image_hits, c.cat_name, u.user_name FROM 4images_images i, 4images_categories c LEFT JOIN 4images_users u ON (u.user_id = i.user_id) WHERE i.image_active = 1 AND i.cat_id NOT IN (0) AND c.cat_id = i.cat_id LIMIT 0, 1 Unknown column 'i.user_id' in 'on clause' DB Error: Bad SQL Query: SELECT i.image_id, i.cat_id, i.user_id, i.image_name, i.image_description, i.image_keywords, i.image_date, i.image_active, i.image_media_file, i.image_thumb_file, i.image_download_url, i.image_allow_comments, i.image_comments, i.image_downloads, i.image_votes, i.image_rating, i.image_hits, c.cat_name, u.user_name FROM 4images_images i, 4images_categories c LEFT JOIN 4images_users u ON (u.user_id = i.user_id) WHERE i.image_active = 1 AND i.cat_id = 1 AND c.cat_id = i.cat_id ORDER BY image_name ASC LIMIT 0, 9 Unknown column 'i.user_id' in 'on clause' Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in x\4images\includes\db_mysql.php on line 116

[V@no]

then you missed a line in the a file. cause in the error its clear that you didnt do the changes on that particular query that produces the error.

[punzeroni]

Hi guys!

After some weeks i rejoin this discussion...

first of all something general about this problem:
@vano: I still think that the problems only exist on JOIN statements where the left operand of the statement isn't the table you use in the join statement. (that's what i explained in my very first posting) .
There are at least two ways of solving this: either you put the statements in the correct order or you use brackets as you suggested. I would recommend using the correct order rather than using brackets since the second table isn't used and so it is more optimized.
I am not sure that the corrections you suggested in the second part of your posting will help since they are not about any JOINs.

@Murphy and the rest: I put all the fixes that are necessary from my point of view in an archive. Just download it, unpack it and overwrite your original 4 image files with it (or use a merge program ). You can download the file here: http://www.milchsemmel.de/~punzeroni/4images-SQL2003fix.tar.bz2
Please tell me if it works or if you remain having problems.

Cheers
punzeroni

[V@no]

There are at least two ways of solving this: either you put the statements in the correct order or you use brackets as you suggested. I would recommend using the correct order rather than using brackets since the second table isn't used and so it is more optimized.

I'll try ask some experts on this metter.

I am not sure that the corrections you suggested in the second part of your posting will help since they are not about any JOINs.

that's what I was not sure and couldnt check myself. So, if you can confirm that it only problem when JOIN tables used, then I can remove it from my post.

[punzeroni]

I am not sure that the corrections you suggested in the second part of your posting will help since they are not about any JOINs.

that's what I was not sure and couldnt check myself. So, if you can confirm that it only problem when JOIN tables used, then I can remove it from my post.

Ok, I had a quick look in the SQL2003 syntax definitions and in the mysql 5 manual and I am pretty sure the brackets aren't necessary in FROM parts of a query where you don't have a JOIN. It wouldn't make sence anyway.

As a reference see: http://dev.mysql.com/doc/refman/5.0/en/select.html

Cheers punzeroni

[Murphy]

then you missed a line in the a file. cause in the error its clear that you didnt do the changes on that particular query that produces the error.

i changed the files with the help of "punzeroni"
now i get the error only on the starting site.
the other one are o.k.

there is no "new picture" showing on the same site too
could it be the same error?
howcan i fix it?

many thanks or helping me 

[V@no]

I just upgraded mine to v5.0.16 (lattest) and see no errors...did they added support for these type of queries?

Anyone can confirm/deny that v5.0.16 has no problems?

[punzeroni]

G'Day!

@Vano: I can definetly deny that 5.0.16 doesn't have those probs. I am using 5.0.16 at the moment and I can reproduce the errors with JOINS. Sorry mate

@Murphy: could you please post the error message. I cannot find any other JOIN problem within my code so probably it is something else. Thx

See you later

[Murphy]

after installing the server completely new, i don't get any error.
i think, there was something in the cache ?
any idea's why?

now it works perfect 

@punzeroni
error: look at reply 16!

[punzeroni]

Hi Murphy,

great that it finally works for you.
I had a look at your reply #16 but I was pretty sure that those errors should be fixed in my version. So... it must have been some caching.
Just for my interest: What server did you reinstall?

Cheers
punzeroni

[Murphy]

i reinstalled xampp
http://www.apachefriends.org/en/xampp.html

for me the best, because completely configuered

[www.operacija.com]

hi,

I install fresh copy off 4images
then I use http://www.milchsemmel.de/~punzeroni/4images-SQL2003fix.tar.bz2 patch, but I've got error:

DB Error: Bad SQL Query: SELECT i.image_id, i.cat_id, i.user_id, i.image_name, i.image_description, i.image_keywords, i.image_date, i.image_active, i.image_media_file, i.image_thumb_file, i.image_download_url, i.image_allow_comments, i.image_comments, i.image_downloads, i.image_votes, i.image_rating, i.image_hits, c.cat_name, u.user_name FROM 4images_images i, 4images_categories c LEFT JOIN 4images_users u ON (u.user_id = i.user_id) WHERE i.image_active = 1 AND i.cat_id NOT IN (0) AND c.cat_id = i.cat_id LIMIT 0, 1
Unknown column 'i.user_id' in 'on clause'

I don't know what to do now... :/

MySQL version: 5.0.18

help me, please. thnx.

[TheSteffen]

Me too 

Please help

DB Error: Bad SQL Query: SELECT i.image_id, i.cat_id, i.user_id, i.image_name, i.image_description, i.image_keywords, i.image_date, i.image_active, i.image_media_file, i.image_thumb_file, i.image_download_url, i.image_allow_comments, i.image_comments, i.image_downloads, i.image_votes, i.image_rating, i.image_hits, c.cat_name, u.username FROM 4images_images i, 4images_categories c LEFT JOIN phpbb_users u ON (u.user_id = i.user_id) WHERE i.image_active = 1 AND i.cat_id NOT IN (0) AND c.cat_id = i.cat_id LIMIT 2580, 1
Unknown column 'i.user_id' in 'on clause'

[TheSteffen]

das hier ist in der functions.php 2x vorhanden und muß auch 2x ersetzt werden

FROM ".IMAGES_TABLE." i,  ".CATEGORIES_TABLE." c

ersetzten wie oben beschrieben.

THX