Unknown column 'i.user_id' in 'on clause'

[gerbert]

Greetings all, I have read about the following error coming from upgrading to MySql 5.0.27.  I strangely started receiving it on my homepage when there have been no mySql updates on my database.

I have gone into mySql and it appears that some of the 4images tables were corrupted, so I repaired them. All my tables say "OK" now but I am still getting the error. Another thing; the error only shows up on my homepage. Any ideas why I might be getting this error? I have not changed any of my 4images code. Thanks!

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 c.cat_id = i.cat_id AND i.cat_id IN (0) ORDER BY i.image_rating DESC, i.image_votes DESC

Unknown column 'i.user_id' in 'on clause'

[thunderstrike]

In top.php file,

find (4 time)

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

replace all with:

Code: [Select]

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


[gerbert]

Thanks thunderstrike, but those lines of code were already correct, with the parenthesis. I forgot to mention that my site has been fine for the last 10 months and I haven't made any changes to the code. I'm guessing it must be something to do with MySQL since nothing has changed on my site. Thanks again. Any other ideas?

[thunderstrike]

- Which PHP version ?
- Which mySQL version ?
- Which 4images version ?

[gerbert]

oops, sorry about that.

4images: 1.7.4
mySQL: 5.0.27
PHP Version 5.2.0

[thunderstrike]

And one more PHP v5.2.x. No patch for this for now ... sorry ...
If I find, I post ...

[thunderstrike]

Ok I think I start to know fror PHP 5.2 ...

In index.php file,

find:

$sql = "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".$additional_sql.", c.cat_name".get_user_table_field(", u.", "user_name")."
        FROM (".IMAGES_TABLE." i,  ".CATEGORIES_TABLE." c)

replace:

Code: [Select]

$sql = "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".$additional_sql. get_user_table_field(", u.", "user_name")."
        FROM (".IMAGES_TABLE." i,  ".CATEGORIES_TABLE." c)

(yes, replace all)
Should work ...

[gerbert]

Thank you. I tried replacing those lines of code and it's still showing errors.

A couple other things to mention: I get the error 4 times and on my home page only - http://www.threadbombing.com/.
All of my other pages come up fine: http://www.threadbombing.com/categories.php?cat_id=19

<sub>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 c.cat_id = i.cat_id AND i.cat_id IN (0, 38, 17, 18, 1, 2, 22, 36, 19, 7, 37, 3, 6, 9, 33, 39, 8, 32, 30, 34, 10, 28, 11, 21, 27, 20, 4, 40, 35, 31, 24, 12, 26, 16, 29, 23, 13, 14, 15, 5) ORDER BY i.image_rating DESC, i.image_votes DESC
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 c.cat_id = i.cat_id AND i.cat_id IN (0, 38, 17, 18, 1, 2, 22, 36, 19, 7, 37, 3, 6, 9, 33, 39, 8, 32, 30, 34, 10, 28, 11, 21, 27, 20, 4, 40, 35, 31, 24, 12, 26, 16, 29, 23, 13, 14, 15, 5) ORDER BY i.image_hits DESC, i.image_rating DESC
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 c.cat_id = i.cat_id AND i.cat_id IN (0, 38, 17, 18, 1, 2, 22, 36, 19, 7, 37, 3, 6, 9, 33, 39, 8, 32, 30, 34, 10, 28, 11, 21, 27, 20, 4, 40, 35, 31, 24, 12, 26, 16, 29, 23, 13, 14, 15, 5) ORDER BY i.image_comments DESC, i.image_name ASC
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 c.cat_id = i.cat_id AND i.cat_id IN (0, 38, 17, 18, 1, 2, 22, 36, 19, 7, 37, 3, 6, 9, 33, 39, 8, 32, 30, 34, 10, 28, 11, 21, 27, 20, 4, 40, 35, 31, 24, 12, 26, 16, 29, 23, 13, 14, 15, 5) ORDER BY i.image_downloads DESC, i.image_name ASC
Unknown column 'i.user_id' in 'on clause'</sub>

[thunderstrike]

Is possible I have access to your server ? If so, please PM info and I tweak ...

[Nicky]

i think this is not changed
FROM ".IMAGES_TABLE." i, ".CATEGORIES_TABLE." c
you have it still in index.php in old format

look near
$num_new_images = $config['image_cells'];

and change it to:
FROM (".IMAGES_TABLE." i,  ".CATEGORIES_TABLE." c)

[gerbert]

Thank you both very much. I re-uploaded the original index.php and it has fixed the problem. I guess I had messed up the code after all.