Author Topic: query problems with MySQL 5.0.x  (Read 229680 times)

0 Members and 1 Guest are viewing this topic.

Offline www.operacija.com

  • Pre-Newbie
  • Posts: 3
    • View Profile
Re: query problems with MySQL 5.0.x
« Reply #30 on: January 15, 2006, 07:24:39 PM »
das hier ist in der functions.php 2x vorhanden und muß auch 2x ersetzt werden :roll:
Quote
FROM ".IMAGES_TABLE." i,  ".CATEGORIES_TABLE." c

ersetzten wie oben beschrieben.

THX


in english, please...

Offline Acidgod

  • Moderator
  • 4images Guru
  • *****
  • Posts: 2.421
  • It's me?
    • View Profile
    • Flash-Webdesign
Re: query problems with MySQL 5.0.x
« Reply #31 on: January 15, 2006, 09:26:25 PM »
habe das erste posting mal um two times erweitert und hoffe das es dann jeder "schnallt"... (o:

Offline rager99

  • Pre-Newbie
  • Posts: 1
    • View Profile
Re: query problems with MySQL 5.0.x
« Reply #32 on: January 16, 2006, 08:36:09 AM »
@ www.operacija.com

copy functions.php from the include-directory to the includes-directory. There's a path-error in the archiv from punzeroni. Then it works fine  :D

Greetings

Michael

Offline |Rene|

  • Newbie
  • *
  • Posts: 47
    • View Profile
    • Bauwagencrew
Re: query problems with MySQL 5.0.x
« Reply #33 on: January 20, 2006, 06:52:50 PM »
I just updated my files with the files from punzeroni, only the index.php I fixed on my own as described by Vano because otherwise I get an error with my template.

Now, I only get one 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 c.cat_id = i.cat_id AND i.cat_id NOT IN (0) ORDER BY i.image_date DESC LIMIT 4

I can't find the the error, functions.php is ok.

My index.php is like, I don't know what I ignore :(

$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 AND c.cat_id = i.cat_id AND i.cat_id NOT IN (".get_auth_cat_sql("auth_viewcat", "NOTIN").")
        ORDER BY i.image_date DESC
        LIMIT $num_new_images";

Can anyone help me?

---------------------------------------------------------------------------------------------------------------

EDIT: Sorry, found the error, now it works.

Offline TheOracle

  • Hero Member
  • *****
  • Posts: 875
    • View Profile
Re: query problems with MySQL 5.0.x
« Reply #34 on: January 20, 2006, 08:11:37 PM »
Quote

EDIT: Sorry, found the error, now it works.


Would it be possible to share with the community what you did exacly in order to solve this problem ? ;)

Offline ch€ri{Bi}²

  • Sr. Member
  • ****
  • Posts: 315
  • A PRoBLeM wIthOUt SoLuTioN Is NoT rEAllY a PRoBLeM
    • View Profile
    • Pat's Gallery
Re: query problems with MySQL 5.0.x
« Reply #35 on: January 20, 2006, 08:29:03 PM »
hummm....
it seems this part of the code:
Quote
(FROM ".IMAGES_TABLE." i,  ".CATEGORIES_TABLE." c)
should be:
Quote
FROM (".IMAGES_TABLE." i,  ".CATEGORIES_TABLE." c)
:wink:

the problem is still present  in MySQL version: 5.0.18.

Cheers!
ch€ri{Bi}²


Offline Michinator

  • Jr. Member
  • **
  • Posts: 87
    • View Profile
Re: query problems with MySQL 5.0.x
« Reply #36 on: February 11, 2006, 01:11:25 PM »
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.phpcategories.phpdetails.phppostcards.phpsearch.phptop.phpincludes/functions.php (two times) find:
Code: [Select]
FROM ".IMAGES_TABLE." i,  ".CATEGORIES_TABLE." cReplace with:
Code: [Select]
FROM (".IMAGES_TABLE." i,  ".CATEGORIES_TABLE." c)

In member.phpadmin/comments.php find:
Code: [Select]
FROM ".COMMENTS_TABLE." c, ".IMAGES_TABLE." iReplace with:
Code: [Select]
FROM (".COMMENTS_TABLE." c, ".IMAGES_TABLE." i)

hi, i have done this (above) and everything works fine. but only "top.php" makes problems:

Code: [Select]
DB Error: Bad SQL Query: SELECT i.image_id, i.user_id, i.cat_id, i.image_name, i.image_rating, i.image_votes, 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 i.cat_id = c.cat_id ORDER BY i.image_votes DESC, i.image_name ASC LIMIT 10
Unknown column 'i.user_id' in 'on clause'

DB Error: Bad SQL Query: SELECT i.image_id, i.user_id, i.cat_id, i.image_name, 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 i.cat_id = c.cat_id ORDER BY i.image_hits DESC, i.image_name ASC LIMIT 10
Unknown column 'i.user_id' in 'on clause'

DB Error: Bad SQL Query: SELECT i.image_id, i.user_id, i.cat_id, i.image_name, i.image_downloads, 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 i.cat_id = c.cat_id ORDER BY i.image_downloads DESC, i.image_name ASC LIMIT 10
Unknown column 'i.user_id' in 'on clause'

can someone help me? thank you in advance!

nice greetings from germany,
your michael.
« Last Edit: February 11, 2006, 01:27:10 PM by Michinator »
Never be afraid to try something new. Remember that amateurs built the Ark. Professionals built the Titanic.

Offline ch€ri{Bi}²

  • Sr. Member
  • ****
  • Posts: 315
  • A PRoBLeM wIthOUt SoLuTioN Is NoT rEAllY a PRoBLeM
    • View Profile
    • Pat's Gallery
Re: query problems with MySQL 5.0.x
« Reply #37 on: February 11, 2006, 02:50:24 PM »
Quote
hi, i have done this (above) and everything works fine. but only "top.php" makes problems:
... and did you make the modification on top.php?
 :idea: can you show us your top.php file?
ch€ri{Bi}²


Offline Michinator

  • Jr. Member
  • **
  • Posts: 87
    • View Profile
Re: query problems with MySQL 5.0.x
« Reply #38 on: February 11, 2006, 02:57:46 PM »
Quote
hi, i have done this (above) and everything works fine. but only "top.php" makes problems:
... and did you make the modification on top.php?
 :idea: can you show us your top.php file?

yes, of course. here is the passage of my modified top.php:

Code: [Select]
// Rating
$sql = "SELECT i.image_id, i.user_id, i.cat_id, i.image_name, i.image_rating, i.image_votes, 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 AND i.cat_id NOT IN ($cat_id_sql) AND i.cat_id = c.cat_id
        $cat_match_sql
        ORDER BY i.image_rating DESC, i.image_name ASC
        LIMIT 10";
$result = $site_db->query($sql);
$top_list = array();
$i = 1;
while ($row = $site_db->fetch_array($result)) {
  $top_list[$i] = $row;
  $i++;
}
$site_db->free_result();

for ($i = 1; $i <= 10; $i++) {
  if (isset($top_list[$i])) {
    $register_array['image_rating_'.$i] = (check_permission("auth_viewimage", $top_list[$i]['cat_id'])) ? "<a href=\"".$site_sess->url(ROOT_PATH."details.php?".URL_IMAGE_ID."=".$top_list[$i]['image_id'])."\">".htmlspecialchars($top_list[$i]['image_name'])."</a>" : htmlspecialchars($top_list[$i]['image_name']);
    $register_array['image_rating_openwindow_'.$i] = (check_permission("auth_viewimage", $top_list[$i]['cat_id'])) ? "<a href=\"".$site_sess->url(ROOT_PATH."details.php?".URL_IMAGE_ID."=".$top_list[$i]['image_id'])."\" onclick=\"opendetailwindow()\" target=\"detailwindow\">".htmlspecialchars($top_list[$i]['image_name'])."</a>" : htmlspecialchars($top_list[$i]['image_name']);
    if (isset($top_list[$i][$user_table_fields['user_name']]) && $top_list[$i]['user_id'] != GUEST) {
      $user_profile_link = (!empty($url_show_profile)) ? preg_replace("/{user_id}/", $top_list[$i]['user_id'], $url_show_profile) : ROOT_PATH."member.php?action=showprofile&amp;".URL_USER_ID."=".$top_list[$i]['user_id'];
      $register_array['image_rating_user_'.$i] = "<a href=\"".$site_sess->url($user_profile_link)."\">".htmlspecialchars($top_list[$i][$user_table_fields['user_name']])."</a>";
    }
    else {
      $register_array['image_rating_user_'.$i] = $lang['userlevel_guest'];
    }
    $register_array['image_rating_cat_'.$i] = "<a href=\"".$site_sess->url(ROOT_PATH."categories.php?".URL_CAT_ID."=".$top_list[$i]['cat_id'])."\">".htmlspecialchars($top_list[$i]['cat_name'])."</a>";
    $register_array['image_rating_number_'.$i] = "<b>".$top_list[$i]['image_rating']."</b> (".$top_list[$i]['image_votes']." ".$lang['votes'].")";
  }
  else {
    $register_array['image_rating_'.$i] = "--";
    $register_array['image_rating_user_'.$i] = "--";
    $register_array['image_rating_cat_'.$i] = "--";
    $register_array['image_rating_number_'.$i] = "--";
  }
}

// Votes

nice greetings from germany,
your micha.
Never be afraid to try something new. Remember that amateurs built the Ark. Professionals built the Titanic.

Offline TheOracle

  • Hero Member
  • *****
  • Posts: 875
    • View Profile
Re: query problems with MySQL 5.0.x
« Reply #39 on: February 11, 2006, 03:17:00 PM »
Quote

$user_profile_link = (!empty($url_show_profile)) ? preg_replace("/{user_id}/", $top_list[$i]['user_id'], $url_show_profile) : ROOT_PATH."member.php?action=showprofile&amp;".URL_USER_ID."=".$top_list[$i]['user_id'];


can be replaced with :

Code: [Select]

$user_profile_link = (!empty($url_show_profile)) ? str_replace("{user_id}", $top_list[$i]['user_id'], $url_show_profile) : ROOT_PATH."member.php?action=showprofile&amp;".URL_USER_ID."=".$top_list[$i]['user_id'];


Seems to load faster. ;)

Offline Michinator

  • Jr. Member
  • **
  • Posts: 87
    • View Profile
Re: query problems with MySQL 5.0.x
« Reply #40 on: February 11, 2006, 03:54:33 PM »
Quote

$user_profile_link = (!empty($url_show_profile)) ? preg_replace("/{user_id}/", $top_list[$i]['user_id'], $url_show_profile) : ROOT_PATH."member.php?action=showprofile&amp;".URL_USER_ID."=".$top_list[$i]['user_id'];


can be replaced with :

Code: [Select]

$user_profile_link = (!empty($url_show_profile)) ? str_replace("{user_id}", $top_list[$i]['user_id'], $url_show_profile) : ROOT_PATH."member.php?action=showprofile&amp;".URL_USER_ID."=".$top_list[$i]['user_id'];


Seems to load faster. ;)

sorry, but it doesn't fix my "top.php" problem. can someone help out?

nice greetings from germany,
your micha.
Never be afraid to try something new. Remember that amateurs built the Ark. Professionals built the Titanic.

Offline TheOracle

  • Hero Member
  • *****
  • Posts: 875
    • View Profile
Re: query problems with MySQL 5.0.x
« Reply #41 on: February 11, 2006, 04:16:45 PM »
Sorry, I do not know according to your specific problem since I do not use mySQL 5. However, which mySQL 5 version do you use (ex: 5.xx) ?

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: query problems with MySQL 5.0.x
« Reply #42 on: February 11, 2006, 05:29:57 PM »
yes, of course. here is the passage of my modified top.php:
This is only one part of the file, there are should be atleast 3 more same parts. You must do the changes to all simular mysql queries.

Quote

$user_profile_link = (!empty($url_show_profile)) ? preg_replace("/{user_id}/", $top_list[$i]['user_id'], $url_show_profile) : ROOT_PATH."member.php?action=showprofile&".URL_USER_ID."=".$top_list[$i]['user_id'];


can be replaced with :

Code: [Select]

$user_profile_link = (!empty($url_show_profile)) ? str_replace("{user_id}", $top_list[$i]['user_id'], $url_show_profile) : ROOT_PATH."member.php?action=showprofile&".URL_USER_ID."=".$top_list[$i]['user_id'];


Seems to load faster. ;)
Stop posting something not related to the question!
If you want talking about something offtopic, do it either in chit-chat or send PM to that person!
How hard is that to understand that you only confuse people by this kind of posts!
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 TheOracle

  • Hero Member
  • *****
  • Posts: 875
    • View Profile
Re: query problems with MySQL 5.0.x
« Reply #43 on: February 11, 2006, 05:32:19 PM »
Quote

Stop posting something not related to the question!
If you want talking about something offtopic, do it either in chit-chat or send PM to that person!
How hard is that to understand that you only confuse people by this kind of posts!


I should of never posted here before this issue was resolved. Sorry on this one.  :oops:

Offline Michinator

  • Jr. Member
  • **
  • Posts: 87
    • View Profile
Re: query problems with MySQL 5.0.x
« Reply #44 on: February 11, 2006, 06:35:19 PM »
Thank you very very much V@no! You are the greatest sql king I have ever seen! ;-)

Nice greetings from Germany and a happy weekend!

Micha.
« Last Edit: February 11, 2006, 08:47:17 PM by Michinator »
Never be afraid to try something new. Remember that amateurs built the Ark. Professionals built the Titanic.