Author Topic: MySQL 5: Unknown column 'i.user_id' in 'on clause' - problem and solution  (Read 7221 times)

0 Members and 1 Guest are viewing this topic.

Offline smocanu

  • Pre-Newbie
  • Posts: 2
    • View Profile
    • PartyFolio - free photo hosting
Hi,

I have just installed a test copy of 4images on a FC4 PHP5 / MySQL 5.0.16 machine and I see this error: Unknown column 'i.user_id' in 'on clause'

The problem is caused by the SQL in the following files:
categories.php
details.php
includes/functions.php
index.php
lightbox.php
search.php

(I found these with grep -ilr '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_thumb_file, i.image_download_url' *)

Instead of
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".$additional_sql.", c.cat_name".get_user_table_field(", u.", "user_name")."
          FROM ".IMAGES_TABLE." i, ".CATEGORIES_TABLE." c
          LEFT JOIN ".USERS_TABLE." u ON (".get_user_table_field("u.", "user_id")." = i.user_id)
          WHERE i.image_active = 1
          $sql_where_query
          AND c.cat_id = i.cat_id AND i.cat_id IN ($cat_id_sql)
the code should be
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".$additional_sql.", c.cat_name".get_user_table_field(", u.", "user_name")."
          FROM ".IMAGES_TABLE." i INNER JOIN ".CATEGORIES_TABLE." c ON i.cat_id=c.cat_id
          LEFT JOIN ".USERS_TABLE." u ON (".get_user_table_field("u.", "user_id")." = i.user_id)
          WHERE i.image_active = 1
          $sql_where_query
          AND i.cat_id IN ($cat_id_sql)
... which would be the normal syntax for a join...

Good luck

Offline Acidgod

  • Moderator
  • 4images Guru
  • *****
  • Posts: 2.421
  • It's me?
    • View Profile
    • Flash-Webdesign
Re: MySQL 5: Unknown column 'i.user_id' in 'on clause' - problem and solution
« Reply #1 on: December 13, 2005, 03:40:15 PM »
please search for mysql5...

Offline smocanu

  • Pre-Newbie
  • Posts: 2
    • View Profile
    • PartyFolio - free photo hosting
Re: MySQL 5: Unknown column 'i.user_id' in 'on clause' - problem and solution
« Reply #2 on: December 13, 2005, 03:43:03 PM »
please search for mysql5...

I only found German results. You can include my patch in your release, the problem might replicate with newer versions of mysql.

Regards

Offline V@no

  • If you don't tell me what to do, I won't tell you where you should go :)
  • Global Moderator
  • 4images Guru
  • *****
  • Posts: 17.849
  • mmm PHP...
    • View Profile
    • 4images MODs Demo
Re: MySQL 5: Unknown column 'i.user_id' in 'on clause' - problem and solution
« Reply #3 on: December 13, 2005, 03:50:03 PM »
actualy search keywords are "mysql 5" (include quotes)
Your first three "must do" before you ask a question:
Please do not PM me asking for help unless you've been specifically asked to do so. Such PMs will be deleted without answer. (forum rule #6)
Extension for Firefox/Thunderbird: Master Password+    Back/Forward History Tweaks (restartless)    Cookies Manager+    Fit Images (restartless for Thunderbird)

Offline schillD

  • Pre-Newbie
  • Posts: 1
    • View Profile
Re: MySQL 5: Unknown column 'i.user_id' in 'on clause' - problem and solution
« Reply #4 on: February 02, 2006, 02:12:48 PM »
Hi

Thanks that was a Big Help!
There are 4 lines of Script in Top.php witch also include the Faulty Join Syntax...
Grüsse
Chris

Offline max.cady

  • Jr. Member
  • **
  • Posts: 67
    • View Profile
Muchas gracias ...  :mrgreen:

I just installed the new version 1.7.2 and this helped a lot when I tried to reinstall some mods (such as variable images on frontpage).
 
Phew ... I think you saved me a lot of headache ... :)