Author Topic: Unknown column 'i.user_id' in 'on clause'  (Read 7195 times)

0 Members and 1 Guest are viewing this topic.

Offline gerbert

  • Newbie
  • *
  • Posts: 21
    • View Profile
Unknown column 'i.user_id' in 'on clause'
« 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'


Offline thunderstrike

  • 4images Guru
  • *******
  • Posts: 2.327
    • View Profile
Re: Unknown column 'i.user_id' in 'on clause'
« Reply #1 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)
8 steps need when ask question -

- PHP version (ACP - > phpinfo())
- mySQL version (ACP - > phpinfo())
- 4images version
- Post screenshot / URL
- Post code in BB Code (no need full file for code) or post attach file
- It doesn't work. What is say - what is do for no work
- Install MOD ? If so - please say (troubleshooting)
- Read FAQ ? Install Bug fixes ?

Offline gerbert

  • Newbie
  • *
  • Posts: 21
    • View Profile
Re: Unknown column 'i.user_id' in 'on clause'
« Reply #2 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?

Offline thunderstrike

  • 4images Guru
  • *******
  • Posts: 2.327
    • View Profile
Re: Unknown column 'i.user_id' in 'on clause'
« Reply #3 on: September 16, 2007, 01:29:01 PM »
- Which PHP version ?
- Which mySQL version ?
- Which 4images version ?
8 steps need when ask question -

- PHP version (ACP - > phpinfo())
- mySQL version (ACP - > phpinfo())
- 4images version
- Post screenshot / URL
- Post code in BB Code (no need full file for code) or post attach file
- It doesn't work. What is say - what is do for no work
- Install MOD ? If so - please say (troubleshooting)
- Read FAQ ? Install Bug fixes ?

Offline gerbert

  • Newbie
  • *
  • Posts: 21
    • View Profile
Re: Unknown column 'i.user_id' in 'on clause'
« Reply #4 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

Offline thunderstrike

  • 4images Guru
  • *******
  • Posts: 2.327
    • View Profile
Re: Unknown column 'i.user_id' in 'on clause'
« Reply #5 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 ...
8 steps need when ask question -

- PHP version (ACP - > phpinfo())
- mySQL version (ACP - > phpinfo())
- 4images version
- Post screenshot / URL
- Post code in BB Code (no need full file for code) or post attach file
- It doesn't work. What is say - what is do for no work
- Install MOD ? If so - please say (troubleshooting)
- Read FAQ ? Install Bug fixes ?

Offline thunderstrike

  • 4images Guru
  • *******
  • Posts: 2.327
    • View Profile
Re: Unknown column 'i.user_id' in 'on clause'
« Reply #6 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 ...
8 steps need when ask question -

- PHP version (ACP - > phpinfo())
- mySQL version (ACP - > phpinfo())
- 4images version
- Post screenshot / URL
- Post code in BB Code (no need full file for code) or post attach file
- It doesn't work. What is say - what is do for no work
- Install MOD ? If so - please say (troubleshooting)
- Read FAQ ? Install Bug fixes ?

Offline gerbert

  • Newbie
  • *
  • Posts: 21
    • View Profile
Re: Unknown column 'i.user_id' in 'on clause'
« Reply #7 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

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'





Offline thunderstrike

  • 4images Guru
  • *******
  • Posts: 2.327
    • View Profile
Re: Unknown column 'i.user_id' in 'on clause'
« Reply #8 on: September 16, 2007, 09:09:57 PM »
Is possible I have access to your server ? If so, please PM info and I tweak ...
8 steps need when ask question -

- PHP version (ACP - > phpinfo())
- mySQL version (ACP - > phpinfo())
- 4images version
- Post screenshot / URL
- Post code in BB Code (no need full file for code) or post attach file
- It doesn't work. What is say - what is do for no work
- Install MOD ? If so - please say (troubleshooting)
- Read FAQ ? Install Bug fixes ?

Offline Nicky

  • Administrator
  • 4images Guru
  • *****
  • Posts: 3.195
    • View Profile
Re: Unknown column 'i.user_id' in 'on clause'
« Reply #9 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)

cheers
Nicky
Your first three "must do" before you ask a question ! ( by V@no)
- please read the Forum Rules ...
- please study the FAQ ...
- please try to Search for your answer ...

nicky.net 4 4images
Signature stolen from mawenzi

Offline gerbert

  • Newbie
  • *
  • Posts: 21
    • View Profile
Re: Unknown column 'i.user_id' in 'on clause'
« Reply #10 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.