4images Forum & Community

4images Issues / Ausgaben => Discussion & Troubleshooting => Topic started by: gerbert on September 16, 2007, 03:24:15 AM

Title: Unknown column 'i.user_id' in 'on clause'
Post by: gerbert on September 16, 2007, 03:24:15 AM

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'

Title: Re: Unknown column 'i.user_id' in 'on clause'
Post by: thunderstrike on September 16, 2007, 05:53:07 AM

In top.php file,

find (4 time)

Quote

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

replace all with:

Code: [Select]

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


Title: Re: Unknown column 'i.user_id' in 'on clause'
Post by: gerbert on September 16, 2007, 07:28:00 AM

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?

Title: Re: Unknown column 'i.user_id' in 'on clause'
Post by: thunderstrike on September 16, 2007, 01:29:01 PM

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

Title: Re: Unknown column 'i.user_id' in 'on clause'
Post by: gerbert on September 16, 2007, 08:03:51 PM

oops, sorry about that.

4images: 1.7.4
mySQL: 5.0.27
PHP Version 5.2.0

Title: Re: Unknown column 'i.user_id' in 'on clause'
Post by: thunderstrike on September 16, 2007, 08:09:31 PM

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

Title: Re: Unknown column 'i.user_id' in 'on clause'
Post by: thunderstrike on September 16, 2007, 08:28:48 PM

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

In index.php file,

find:

Quote

$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 ...

Title: Re: Unknown column 'i.user_id' in 'on clause'
Post by: gerbert on September 16, 2007, 09:05:19 PM

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>

Title: Re: Unknown column 'i.user_id' in 'on clause'
Post by: thunderstrike on September 16, 2007, 09:09:57 PM

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

Title: Re: Unknown column 'i.user_id' in 'on clause'
Post by: Nicky on September 16, 2007, 09:46:42 PM

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)

Title: Re: Unknown column 'i.user_id' in 'on clause'
Post by: gerbert on September 17, 2007, 06:41:50 PM

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.