4images Forum & Community

4images Issues / Ausgaben => Discussion & Troubleshooting => Topic started by: punzeroni on October 25, 2005, 01:50:56 PM

Title: query problems with MySQL 5.0.x
Post by: punzeroni on October 25, 2005, 01:50:56 PM
Hi there,

I'm using 4images in version 1.7.1.

I recently updated my mysql server to version 5 (5.0.13-rc). After that I received heaps of SQL-Errors like this:
Quote
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 i.cat_id NOT IN (0, -1) AND c.cat_id = i.cat_id LIMIT 290, 1
Unknown column 'i.user_id' in 'on clause'

The error messages are all looking the same: "Unknown column 'XY' in 'on clause'"
As a result 4images doesn't show any images  8O

So i had a search through the mysql bug database and discovered the following:
http://bugs.mysql.com/bug.php?id=12943

To summarize it, among other things they say that the join-SQL syntax used by 4images is not SQL:2003 compliant. Since version 5 mysql claims to be fully SQL:2003 compliant and thus complains about those queries. As it seems the mysql guys are working on some fixes to make mysql 5 work with non SQL:2003 compliant queries.

If you take a look at the example quoted above you can see the problem: the 2 tables (u and i) mentioned in the ON statement of JOIN need to be operands of the JOIN statement. But currently the operands are c and u. 

the correct syntax needs to be something like this:
Quote
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_categories c, 4images_images i
LEFT JOIN 4images_users u ON (u.user_id = i.user_id)

WHERE i.image_active = 1 AND i.cat_id NOT IN (0, -1) AND c.cat_id = i.cat_id LIMIT 290, 1

this problem exists in a lot of 4images files

I took the liberty to rewrite some queries in order to make them SQL:2003 compliant.
I put my altered files here:
http://www.milchsemmel.de/~punzeroni/4images-SQL2003fix.tar.bz2

Cheers
punzeroni
Title: Re: query problems with MySQL 5.0.x
Post by: V@no on October 25, 2005, 02:49:32 PM
/me didn't know v5.x was already released!

Since you are so up-to-date, what about the lattest version 5.0.15?  can you see if it works there?
Title: Re: query problems with MySQL 5.0.x
Post by: punzeroni on October 25, 2005, 03:47:47 PM
Quote
what about the lattest version 5.0.15?  can you see if it works there?

5.0.15 came into gentoo portage this night and it just finished compiling 10 minutes ago but the problems remain. if you turn around the JOIN operands - as explained in my example - it works.

See you
punzeroni
Title: Re: query problems with MySQL 5.0.x
Post by: darkman83 on October 28, 2005, 01:02:21 AM
okay guys...before i use this fiy i get 2 of this errors...and after i use this i get only one, i think the only thing wich need to changed is the random image displaying...mhm...i dunno what to change, but i read the topic..my i be noobish? have also tried to disable the random image displying...but i dunno how lol
I use MySQL 5.0.15 xD

So any fixes there?
Title: Re: query problems with MySQL 5.0.x
Post by: punzeroni on October 28, 2005, 01:13:37 PM
Hi darkman,

did you also overwrite the file functions.php in the include subdir? I reckon the random-function is in there.

Cheers
punzeroni
Title: Re: query problems with MySQL 5.0.x
Post by: darkman83 on October 28, 2005, 03:28:20 PM
yes, i've overwritten all files...also functions.php...i think you have forgotten to fix this ^^
Title: Re: query problems with MySQL 5.0.x
Post by: punzeroni on October 28, 2005, 03:35:23 PM
@darkman

I'm not sure what your problem is. I think I fixed all the files I could find. Random pictures work for me. Could you please post your error message and - if possible - all of the 4images dist files. Then I'll have a look.
Title: Re: query problems with MySQL 5.0.x
Post by: darkman83 on October 28, 2005, 03:48:03 PM
http://dm.hopto.org/4images/

as i said...before i get 2 of those lines...and after replacing the files only 1!!!
Title: Re: query problems with MySQL 5.0.x
Post by: V@no on October 28, 2005, 11:43:18 PM
Do you think its a bug in mysql itself? well, I do, because for me it doesnt make sence why would it metter the sequence of the tables in the query...

[EDIT]
I digged out something, its NOT a bug:
http://bugs.mysql.com/bug.php?id=13551

Quote from: Sergei Golubchi
This is a change that was made in 5.0.15 to make MySQL more compliant with the
standard.
According to the SQL:2003

<from clause> ::= FROM <table reference list>
<table reference list> ::=
    <table reference> [ { <comma> <table reference> }... ]
<table reference> ::=
    <table factor>
  | <joined table>
<joined table> ::=
    <cross join>
  | <qualified join>
  | <natural join>
...

Thus when you write

  ... FROM t1 , t2 LEFT JOIN t3 ON (expr)

it is parsed as

(1)    ... FROM t1 , (t2 LEFT JOIN t3 ON (expr))

and not as

(2)    ... FROM (t1 , t2) LEFT JOIN t3 ON (expr)

so, from expr you can only refer to columns of t2 and t3 - operands of the
join.
Workaround - to put parentheses explicitly as in (2). Then you can refer to t1
columns from expr.

Unfortunately, this change is not properly documented in the manual, it will be
fixed.

So, try instead of switching the tables around replace
Code: [Select]
FROM 4images_images i, 4images_categories c with:
Code: [Select]
FROM (4images_images i, 4images_categories c) see if it helps
Title: Re: query problems with MySQL 5.0.x
Post by: darkman83 on October 29, 2005, 12:34:47 PM
okay i've tried....if i do so it shows me no more images up and i get those two error lines back as before xD
Title: Re: query problems with MySQL 5.0.x
Post by: punzeroni on October 29, 2005, 04:23:30 PM
Hi guys,

sorry I was gone for a while... Did a kernel update which didn't work as it was supposed to :-)

@darkman: I can't find any additional problems in my files. So maybe send me your whole 4images distribution and I'll have a look at it (ok... you might leave the passwords out :-) )

@V@no: I gave Sergei's brackets a try for some examples and it works for me. So which version you finally choose seems to be an aestetical question.

Ciao
punzeroni
Title: Re: query problems with MySQL 5.0.x
Post by: V@no on October 29, 2005, 06:16:51 PM
@V@no: I gave Sergei's brackets a try for some examples and it works for me. So which version you finally choose seems to be an aestetical question.
I'd say the brackets version ;)
But, I'm still waiting for Jan's responce about "would it be faster to left join the second table instead of joining them as it is now":
Quote
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
LEFT JOIN 4images_categories c ON (c.cat_id = i.cat_id)
LEFT JOIN 4images_users u ON (u.user_id = i.user_id)
WHERE i.image_active = 1 AND i.cat_id NOT IN (0, -1) LIMIT 290, 1
In fact since the only value is being read from 4images_categories table is "cat_name", and that value is avalabe by default in $cat_cache array, I'd say 4images_categories table should not be included in the query at all and the final queries with work around would be:
Code: [Select]
    $sql = "SELECT DISTINCT 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".get_user_table_field(", u.", "user_name")."
            FROM ".IMAGES_TABLE." i
            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)
            ORDER BY RAND()";
    $result = $site_db->query($sql);
    while ($row = $site_db->fetch_array($result)) {
      $random_image_cache[$row['cat_id']] = $row;
      $random_image_cache[$row['cat_id']]['cat_name'] = $cat_cache[$row['cat_id']]['cat_name'];

And

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".get_user_table_field(", u.", "user_name")."
            FROM ".IMAGES_TABLE." i
            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)
            LIMIT $number, 1";
    $random_image_cache[0] = $site_db->query_firstrow($sql);
    $random_image_cache[0]['cat_name'] = $cat_cache[$random_image_cache[0]['cat_id']]['cat_name'];
Title: Re: query problems with MySQL 5.0.x
Post by: darkman83 on October 29, 2005, 11:24:30 PM
Ah okay...thanks...that worked...no more error is showing up ^^
YEAHH BIG THANKS!!!!  :lol: :mrgreen: :D :)
Title: Re: query problems with MySQL 5.0.x
Post by: baconzoo on November 16, 2005, 01:51:48 PM
Can you spell out the process of fixing this?  For the lame?
Title: Re: query problems with MySQL 5.0.x
Post by: Murphy on December 04, 2005, 07:58:54 PM
please give us a chance to fix it  :roll:
Title: Re: query problems with MySQL 5.0.x
Post by: V@no on December 04, 2005, 08:26:19 PM
Ok, please test this (if it all works, I'll post it as a compability fix):

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)

Stop here. Test every page and only if you have problem continue next:

In postcards.php find:
Code: [Select]
FROM ".POSTCARDS_TABLE." p, ".IMAGES_TABLE." iReplace with:
Code: [Select]
FROM (".POSTCARDS_TABLE." p, ".IMAGES_TABLE." i)

In search.php find:
Code: [Select]
FROM ".WORDLIST_TABLE." w, ".WORDMATCH_TABLE." mReplace with:
Code: [Select]
FROM (".WORDLIST_TABLE." w, ".WORDMATCH_TABLE." m)

In includes/auth.php find:
Code: [Select]
FROM ".GROUP_ACCESS_TABLE." a, ".GROUP_MATCH_TABLE." mReplace with:
Code: [Select]
FROM (".GROUP_ACCESS_TABLE." a, ".GROUP_MATCH_TABLE." m)

In admin/home.php find:
Code: [Select]
FROM ".USERS_TABLE." u, ".SESSIONS_TABLE." sReplace with:
Code: [Select]
FROM (".USERS_TABLE." u, ".SESSIONS_TABLE." s)

In admin/usergroups.php find:
Code: [Select]
FROM ".GROUPS_TABLE." g, ".GROUP_MATCH_TABLE." gmReplace with:
Code: [Select]
FROM (".GROUPS_TABLE." g, ".GROUP_MATCH_TABLE." gm)
Title: Re: query problems with MySQL 5.0.x
Post by: Murphy on December 04, 2005, 11:41:57 PM
I'm sorry, but i get always this
(tried 2 times with a new installation and an existing album)

Code: [Select]
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 i.cat_id NOT IN (0) AND c.cat_id = i.cat_id LIMIT 0, 1
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 i.cat_id = 1 AND c.cat_id = i.cat_id ORDER BY image_name ASC LIMIT 0, 9
Unknown column 'i.user_id' in 'on clause'

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in x\4images\includes\db_mysql.php on line 116
Title: Re: query problems with MySQL 5.0.x
Post by: V@no on December 05, 2005, 01:01:03 AM
then you missed a line in the a file. cause in the error its clear that you didnt do the changes on that particular query that produces the error.
Title: Re: query problems with MySQL 5.0.x
Post by: punzeroni on December 05, 2005, 11:01:08 AM
Hi guys!

After some weeks i rejoin this discussion...

first of all something general about this problem:
@vano: I still think that the problems only exist on JOIN statements where the left operand of the statement isn't the table you use in the join statement. (that's what i explained in my very first posting) .
There are at least two ways of solving this: either you put the statements in the correct order or you use brackets as you suggested. I would recommend using the correct order rather than using brackets since the second table isn't used and so it is more optimized.
I am not sure that the corrections you suggested in the second part of your posting will help since they are not about any JOINs.

@Murphy and the rest: I put all the fixes that are necessary from my point of view in an archive. Just download it, unpack it and overwrite your original 4 image files with it (or use a merge program :-) ). You can download the file here: http://www.milchsemmel.de/~punzeroni/4images-SQL2003fix.tar.bz2
Please tell me if it works or if you remain having problems.

Cheers
punzeroni
Title: Re: query problems with MySQL 5.0.x
Post by: V@no on December 05, 2005, 02:47:48 PM
There are at least two ways of solving this: either you put the statements in the correct order or you use brackets as you suggested. I would recommend using the correct order rather than using brackets since the second table isn't used and so it is more optimized.
I'll try ask some experts on this metter.


I am not sure that the corrections you suggested in the second part of your posting will help since they are not about any JOINs.
that's what I was not sure and couldnt check myself. So, if you can confirm that it only problem when JOIN tables used, then I can remove it from my post.
Title: Re: query problems with MySQL 5.0.x
Post by: punzeroni on December 05, 2005, 04:04:46 PM

I am not sure that the corrections you suggested in the second part of your posting will help since they are not about any JOINs.
that's what I was not sure and couldnt check myself. So, if you can confirm that it only problem when JOIN tables used, then I can remove it from my post.

Ok, I had a quick look in the SQL2003 syntax definitions and in the mysql 5 manual and I am pretty sure the brackets aren't necessary in FROM parts of a query where you don't have a JOIN. It wouldn't make sence anyway.

As a reference see: http://dev.mysql.com/doc/refman/5.0/en/select.html

Cheers punzeroni
Title: Re: query problems with MySQL 5.0.x
Post by: Murphy on December 07, 2005, 12:43:54 PM
then you missed a line in the a file. cause in the error its clear that you didnt do the changes on that particular query that produces the error.
i changed the files with the help of "punzeroni"
now i get the error only on the starting site.
the other one are o.k.

there is no "new picture" showing on the same site too
could it be the same error?
howcan i fix it?

many thanks or helping me  :wink:
Title: Re: query problems with MySQL 5.0.x
Post by: V@no on December 10, 2005, 07:36:34 AM
I just upgraded mine to v5.0.16 (lattest) and see no errors...did they added support for these type of queries?

Anyone can confirm/deny that v5.0.16 has no problems?
Title: Re: query problems with MySQL 5.0.x
Post by: punzeroni on December 10, 2005, 03:55:57 PM
G'Day!

@Vano: I can definetly deny that 5.0.16 doesn't have those probs. I am using 5.0.16 at the moment and I can reproduce the errors with JOINS. Sorry mate :-)

@Murphy: could you please post the error message. I cannot find any other JOIN problem within my code so probably it is something else. Thx

See you later
Title: Re: query problems with MySQL 5.0.x
Post by: Murphy on December 10, 2005, 04:18:23 PM
after installing the server completely new, i don't get any error.
i think, there was something in the cache ?
any idea's why?

now it works perfect  :wink:

@punzeroni
error: look at reply 16!


Title: Re: query problems with MySQL 5.0.x
Post by: punzeroni on December 10, 2005, 04:23:38 PM
Hi Murphy,

great that it finally works for you.
I had a look at your reply #16 but I was pretty sure that those errors should be fixed in my version. So... it must have been some caching.
Just for my interest: What server did you reinstall?

Cheers
punzeroni
Title: Re: query problems with MySQL 5.0.x
Post by: Murphy on December 10, 2005, 05:07:25 PM
i reinstalled xampp
http://www.apachefriends.org/en/xampp.html

for me the best, because completely configuered
Title: Re: query problems with MySQL 5.0.x
Post by: www.operacija.com on January 09, 2006, 06:19:15 PM
hi,

I install fresh copy off 4images
then I use http://www.milchsemmel.de/~punzeroni/4images-SQL2003fix.tar.bz2 patch, but I've got error:

Quote
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 i.cat_id NOT IN (0) AND c.cat_id = i.cat_id LIMIT 0, 1
Unknown column 'i.user_id' in 'on clause'

I don't know what to do now... :/

MySQL version: 5.0.18

help me, please. thnx.
Title: Re: query problems with MySQL 5.0.x
Post by: TheSteffen on January 15, 2006, 03:14:26 PM
Me too  :(

Please help

Quote
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.username FROM 4images_images i, 4images_categories c LEFT JOIN phpbb_users u ON (u.user_id = i.user_id) WHERE i.image_active = 1 AND i.cat_id NOT IN (0) AND c.cat_id = i.cat_id LIMIT 2580, 1
Unknown column 'i.user_id' in 'on clause'
Title: Re: query problems with MySQL 5.0.x
Post by: TheSteffen on January 15, 2006, 07:11:53 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
Title: Re: query problems with MySQL 5.0.x
Post by: www.operacija.com 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...
Title: Re: query problems with MySQL 5.0.x
Post by: Acidgod 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:
Title: Re: query problems with MySQL 5.0.x
Post by: rager99 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
Title: Re: query problems with MySQL 5.0.x
Post by: |Rene| 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.
Title: Re: query problems with MySQL 5.0.x
Post by: TheOracle 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 ? ;)
Title: Re: query problems with MySQL 5.0.x
Post by: ch€ri{Bi}² 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!
Title: Re: query problems with MySQL 5.0.x
Post by: Michinator 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.
Title: Re: query problems with MySQL 5.0.x
Post by: ch€ri{Bi}² 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?
Title: Re: query problems with MySQL 5.0.x
Post by: Michinator 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.
Title: Re: query problems with MySQL 5.0.x
Post by: TheOracle 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. ;)
Title: Re: query problems with MySQL 5.0.x
Post by: Michinator 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.
Title: Re: query problems with MySQL 5.0.x
Post by: TheOracle 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) ?
Title: Re: query problems with MySQL 5.0.x
Post by: V@no 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!
Title: Re: query problems with MySQL 5.0.x
Post by: TheOracle 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:
Title: Re: query problems with MySQL 5.0.x
Post by: Michinator 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.
Title: Re: query problems with MySQL 5.0.x
Post by: horo on February 11, 2006, 06:40:47 PM
I'm using MySQL 5.0.18 on Win32 along with PHP 5.1.2. I did all the changes from #15 and it works. There are 1, 2 or 4 lines per file to change. Continue searching after the first occurence until the file end.

Ich benutze MySQL 5.0.18 mit PHP 5.1.2 auf Win32. Ich führte alle Änderungen von #15 durch und es funktioniert. Es gibt 1, 2 oder 4 Zeilen in den verschiedenen Dateien zu korrigieren. Unbedingt nach dem ersten Vorkommen bis zum Dateiende weitersuchen.

There is another problem: Login/Logout. After login (or logout), a blank page or an error msg from the webserver that the requested URL wouldn't be on the server. However, pressing the back-button on the browser and going to the main screen reveils that I am logged in. Same stuff for logout.

Es gibt ein anderes Problem: Login/Logout. Nach dem loogin (oder logout) erscheint eine leere Seite oder ein Hinweis vom Webserver, dass der URL hier nicht gefunden werden konnte. Drückt man den Zurück-Knopf am Browser und geht auf die Hauptseite, sieht man sich eingeloggt (ditto logout).

You may test this behaviour on Dieses Verhalten kann man testen auf http://horo.dyndns.org/4images/ User='tester' pass='tester'.

Suggestions? Vorschläge?
Title: Re: query problems with MySQL 5.0.x
Post by: V@no on February 11, 2006, 06:45:53 PM
And what does it have to do with MySQL v5.x problem?
Please start a new topic regarding this issue.
Title: Re: query problems with MySQL 5.0.x
Post by: horo on February 11, 2006, 06:52:02 PM
Ok, I will. Thought it might have a connection. Sorry for the inconvenience.
Title: Re: query problems with MySQL 5.0.x
Post by: Kruser on February 17, 2006, 11:37:08 AM
sowas hier auch mit klammern ersetzen ??

 FROM ".POSTCARDS_TABLE." p, ".IMAGES_TABLE." i

oder sowas :

 sql = "DELETE FROM ".POSTCARDS_TABLE."
          WHERE (postcard_date < $expiry)";
  $site_db->query($sql);


  ????
Title: Re: query problems with MySQL 5.0.x
Post by: horo on February 20, 2006, 03:48:11 PM
Kruser - Ich habe alles von #15 ersetzt:

FROM ".blabla_TABLE." x, ".blabla_TABLE." y
FROM (".blabla_TABLE." x, ".blabla_TABLE." y)

dann hat's geklappt.
Title: Re: query problems with MySQL 5.0.x
Post by: ruudvroon on February 21, 2006, 08:36:38 PM
Ok, please test this (if it all works, I'll post it as a compability fix):

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)


In some of the files V@no mentioned before, you can find the lines more than once (e.g. top.php and index.php), replace all these lines.
Also look for (only one space in the middle) in all the files:
Code: [Select]
FROM ".IMAGES_TABLE." i, ".CATEGORIES_TABLE." cReplace with:
Code: [Select]
FROM (".IMAGES_TABLE." i, ".CATEGORIES_TABLE." c)
V@no forgot to mention that you also need to edit these files: lightbox.php and includes/page_header.php

Look for:
Code: [Select]
FROM ".IMAGES_TABLE." i,  ".CATEGORIES_TABLE." cReplace with:
Code: [Select]
FROM (".IMAGES_TABLE." i,  ".CATEGORIES_TABLE." c)

In some plugins for the admin panel (e.g. Batch ZIP Import, Batch Import AND Postcard Viewer

Look for:
Code: [Select]
$PHP_SELFReplace with:
Code: [Select]
$_SERVER['PHP_SELF']

I think everythink has to work now. :D
Title: Re: query problems with MySQL 5.0.x
Post by: raagaswaram on March 22, 2006, 11:08:04 PM
even after editing it im getting this 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) 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, 1, 117, 9, 17, 18, 52, 116) AND c.cat_id = i.cat_id LIMIT 1490, 1
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'c LEFT JOIN 4images_users u ON (u.user_id = i.user_
Title: Re: query problems with MySQL 5.0.x
Post by: V@no on March 23, 2006, 12:40:42 AM
You have an extra 'c':[qcode] FROM (4images_images i, 4images_categories c) c LEFT JOIN[/qcode]
Title: Re: query problems with MySQL 5.0.x
Post by: raagaswaram on March 23, 2006, 09:44:43 PM
hey in which file?
Title: Re: query problems with MySQL 5.0.x
Post by: V@no on March 24, 2006, 12:45:35 AM
I dont know...you changed it...
Title: Re: query problems with MySQL 5.0.x
Post by: claire-l on March 29, 2006, 08:35:48 PM
Hello :)

I was using 4images on two web sites free.fr and it worked well, but Free has just changed mysql from v4 to v5 and everything goes wrong :(

well, i downloaded the punzeroni's files http://www.milchsemmel.de/~punzeroni/4images-SQL2003fix.tar.bz2 (thanks a lot) and replace the old files, and now i've got this error on index.php :

Code: [Select]
Fatal error: Call to undefined function: create_cache_id() in /var/www/sdb/1/3/mysite/index.php on line 56
and i don't know how to fix it.

could someone help ? :|
Title: Re: query problems with MySQL 5.0.x
Post by: V@no on March 30, 2006, 02:14:48 AM
the files you downloaded are for v1.7.1 and you seems to using quiet out dated v1.7
So, the solution is restore your backups (I hope you do backups before you do any changes) and then manualy fix the code by following hints from this topic.
Title: Re: query problems with MySQL 5.0.x
Post by: claire-l on March 30, 2006, 11:34:33 AM
thanks a lot :)

i think i read bad the first time  :roll:

everything seems to work good now.
Title: Re: query problems with MySQL 5.0.x
Post by: alphavto on May 03, 2006, 02:48:44 AM
Greats. Thanx.
Btw.: when you have install the MOD Mini-Top, you must also changing this in mini-top.php
Title: Re: query problems with MySQL 5.0.x
Post by: mczapkie on May 11, 2006, 02:24:42 PM
Ok, please test this (if it all works, I'll post it as a compability fix):
...

The SQL error vanished, by all my images either - there are standard 4images miniatures (jpg eye) instead of previous in images listing,
and 4images "error404" icon instead of image on details page:
http://149.156.194.203/~mczapkie/4images/index.php

I have no idea how to fix it.

Regards,
Mc
Title: Re: query problems with MySQL 5.0.x
Post by: V@no on May 11, 2006, 02:52:33 PM
Doubtfull it has anything to do with MySLQ 5 issue
http://149.156.194.203/~mczapkie/4images/details.php?image_id=1502 <- file not found.
Title: Re: query problems with MySQL 5.0.x
Post by: mczapkie on May 11, 2006, 03:16:38 PM
But the files exist physically at his /data/media and /data/thumbnails locations as it was before SQL upgrade.
Seems that php cant find the proper path and make "if else" statement substitution.
Title: Re: query problems with MySQL 5.0.x
Post by: V@no on May 11, 2006, 03:38:58 PM
In the database 4images stores only filenames.
Check permissions and caSe of filenames. Unix systems are case sensetive.
Title: Re: query problems with MySQL 5.0.x
Post by: mczapkie on May 11, 2006, 04:47:25 PM
Check permissions...
Bingo - most of directories were owned by noobody instead of user, i dont know why, but chown fixed it - jetz alles gut :)
THX for suggestion.
Title: Re: query problems with MySQL 5.0.x
Post by: Ston4Img on May 20, 2006, 06:11:02 PM
Ok, please test this (if it all works, I'll post it as a compability fix):
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!


And for persons they have the Slideshow install zu mast chance this:

In slideshow.php
find:
Code: [Select]
FROM ".IMAGES_TABLE." i,  ".CATEGORIES_TABLE." cReplace with:
Code: [Select]
FROM (".IMAGES_TABLE." i,  ".CATEGORIES_TABLE." c)
Line: 72

Title: Re: query problems with MySQL 5.0.x
Post by: masterzmind2004 on November 14, 2006, 06:23:21 PM
Check permissions...
Bingo - most of directories were owned by noobody instead of user, i dont know why, but chown fixed it - jetz alles gut :)
THX for suggestion.


I m also facing the problem after upgrading to mysql v5.0

I have replaced all files and now getting error file not found on images.

Let me know where you set the permission and which were the permissions ,

http://www.desktopwallpapers.ws

Thanks in advance

Tulip
Title: Re: query problems with MySQL 5.0.x
Post by: Toso on December 07, 2006, 01:30:45 PM
It works. :mrgreen:

This is my last error message:

Quote
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 album_images i, album_categories c LEFT JOIN album_users u ON (u.user_id = i.user_id) WHERE i.image_active = 1 AND i.cat_id NOT IN (0) AND c.cat_id = i.cat_id LIMIT 4325, 1
Unknown column 'i.user_id' in 'on clause'

I think it relates to the random image !?!
Title: Re: query problems with MySQL 5.0.x
Post by: Ferus on February 15, 2007, 04:44:17 PM
I do this repair on my 4images 1.7.4 and only on index.php have error and the new images is not vieved.
Error Code:
Code: [Select]
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, 164, 96, 12, 93, 95, 46, 147, 74, 129, 143, 118, 11, 110, 139, 160, 1, 29, 48, 92, 13, 119, 161, 144, 142, 75, 128, 43, 146, 148, 165, 2, 151, 30, 28, 14, 91, 145, 10, 162, 76, 120, 79, 3, 49, 123, 41, 171, 149, 31, 4, 106, 15, 90, 125, 32, 50, 86, 77, 170, 80, 114, 44, 152, 89, 5, 17, 70, 112, 172, 177, 26, 78, 94, 55, 84, 88, 18, 108, 82, 25, 6, 155, 56, 85, 19, 87, 166, 158, 16, 7, 104, 57, 37, 20, 103, 83, 54, 8, 127, 58, 168, 38, 21, 141, 81, 9, 154, 150, 53, 22, 51, 167, 27, 45, 130, 109, 23, 173, 102, 122, 153, 126, 169, 59, 24, 111, 40, 174, 115, 105, 107, 178, 113, 39, 156, 176, 159, 60, 140, 157, 61, 101, 62, 63, 64, 65, 66, 67, 124, 68, 69, 71, 72, 73) ORDER BY i.image_date DESC LIMIT 20
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') LEFT JOIN 4images_users u ON (u.user_id = i.user_id) WHERE i.i' at line 2
Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /includes/db_mysql.php on line 116

Okay, i do some rest of repair and i got :

Code: [Select]
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, 164, 96, 12, 93, 95, 46, 147, 74, 129, 143, 118, 11, 110, 139, 160, 1, 29, 48, 92, 13, 119, 161, 144, 142, 75, 128, 43, 146, 148, 165, 2, 151, 30, 28, 14, 91, 145, 10, 162, 76, 120, 79, 3, 49, 123, 41, 171, 149, 31, 4, 106, 15, 90, 125, 32, 50, 86, 77, 170, 80, 114, 44, 152, 89, 5, 17, 70, 112, 172, 177, 26, 78, 94, 55, 84, 88, 18, 108, 82, 25, 6, 155, 56, 85, 19, 87, 166, 158, 16, 7, 104, 57, 37, 20, 103, 83, 54, 8, 127, 58, 168, 38, 21, 141, 81, 9, 154, 150, 53, 22, 51, 167, 27, 45, 130, 109, 23, 173, 102, 122, 153, 126, 169, 59, 24, 111, 40, 174, 115, 105, 107, 178, 113, 39, 156, 176, 159, 60, 140, 157, 61, 101, 62, 63, 64, 65, 66, 67, 124, 68, 69, 71, 72, 73) ORDER BY i.image_date DESC LIMIT 20 Unknown column 'i.user_id' in 'on clause'
Title: Re: query problems with MySQL 5.0.x
Post by: ScreamLP on April 16, 2007, 01:26:01 AM
Hallo
ich weis es ist ein altes Thema :?

Aber mir werde folgende Meldungen angezeigt

Code: [Select]
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 i.cat_id NOT IN (0) AND c.cat_id = i.cat_id LIMIT 279, 1
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 NOT IN (0) ORDER BY i.image_date DESC LIMIT 4
Unknown column 'i.user_id' in 'on clause'

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /home/www/doc/7136/net-24.info/www/includes/db_mysql.php on line 116


Mein Prob ist ich kann absolut kein english.
Sollte hier also irgendwo die Lösung stehen verstehe ich sie nicht.

Gallery Version: 1.7.1
PHP Version 4.4.6
MySQL-Client-Version: 5.0.18
http://www.net-24.info/


Wie gesagt ich weis das das hier ein altes Thema ist und ihr seit es bestimmt auch Leid aber bitte helft mir
Title: Re: query problems with MySQL 5.0.x
Post by: Jan-Lukas on June 27, 2007, 05:40:43 PM
Hosteurope hat auch die Nacht updates gefahren, dieser Threat hilft natürlich auch da gegen

Danke, für die Lösung

Harald
Title: Re: query problems with MySQL 5.0.x
Post by: HorrorCrafT on June 28, 2007, 12:34:03 AM
Hosteurope hat auch die Nacht updates gefahren, dieser Threat hilft natürlich auch da gegen

Danke, für die Lösung

Harald
ging mir genauso :mrgreen: heute morgen hat keine meiner bildergalerien mehr funktioniert :?

noch ein tipp:
vermutlich hat fast jeder add-ons eingebaut. in diesen php dateien (z.b. comments_all.php - ist das "alle kommentare addon" oder potm.php - "picture of the month addon") müsst ihr die klammern bei einem FROM befehl auch setzen! am besten ihr sucht nur nach "FROM" und schaut ob die gefundene zeile aus mehr als nur einer variable besteht. wenn ja, dann klammer drumrum. hat bei mir wunderbar geklappt und nun funktioniert wieder alles.
Title: Re: query problems with MySQL 5.0.x
Post by: baghdad4ever on October 25, 2007, 12:08:01 PM
Ok, please test this (if it all works, I'll post it as a compability fix):

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)

Stop here. Test every page and only if you have problem continue next:

In postcards.php find:
Code: [Select]
FROM ".POSTCARDS_TABLE." p, ".IMAGES_TABLE." iReplace with:
Code: [Select]
FROM (".POSTCARDS_TABLE." p, ".IMAGES_TABLE." i)

In search.php find:
Code: [Select]
FROM ".WORDLIST_TABLE." w, ".WORDMATCH_TABLE." mReplace with:
Code: [Select]
FROM (".WORDLIST_TABLE." w, ".WORDMATCH_TABLE." m)

In includes/auth.php find:
Code: [Select]
FROM ".GROUP_ACCESS_TABLE." a, ".GROUP_MATCH_TABLE." mReplace with:
Code: [Select]
FROM (".GROUP_ACCESS_TABLE." a, ".GROUP_MATCH_TABLE." m)

In admin/home.php find:
Code: [Select]
FROM ".USERS_TABLE." u, ".SESSIONS_TABLE." sReplace with:
Code: [Select]
FROM (".USERS_TABLE." u, ".SESSIONS_TABLE." s)

In admin/usergroups.php find:
Code: [Select]
FROM ".GROUPS_TABLE." g, ".GROUP_MATCH_TABLE." gmReplace with:
Code: [Select]
FROM (".GROUPS_TABLE." g, ".GROUP_MATCH_TABLE." gm)



i have the same problem

but after make the above changes

it is work fine


but i have now

new problem

i cant upload any picture :roll:

how fix that


php version 5.2.4

mysql          5.0.27

4images        1.7
Title: Re: query problems with MySQL 5.0.x
Post by: jbrusi on October 30, 2007, 11:45:32 AM
Well i did all of V@no's changes and the user interface seemed to work OK.

I then encountered other problems when trying to delete a photo. I am assumming this is related to MySQL 5 as well. I am running 1.7.1

¿Will the current version of 4 images be able to deal with MySQL 5?

Thanks
Title: Re: query problems with MySQL 5.0.x
Post by: kai on October 30, 2007, 12:32:07 PM

¿Will the current version of 4 images be able to deal with MySQL 5?

Yes, download here:
http://www.4homepages.de/4images/download.php
Title: Re: query problems with MySQL 5.0.x
Post by: Gibsy on October 30, 2007, 06:12:36 PM
Hallo,

ich habe alle änderungen getätigt und die fehler sind auch behoben, bei Seiten aufruf erscheinen keine fehler mehr, nun kann ich aber keine neuen Kategorien erstellen und bilder hinzufügen geht auch nicht, die Thumpnails gehen nicht auf :oops:
Wenn ich neue Kategorie erstelle kommen alle Kategorien die existieren dutzend mal und hört nicht auf, ich muß dann das Fenster schließen und wenn ich meine 4images Seite aufrufe kommt diese Meldung:
Fatal error: Allowed memory size of 67108864 bytes exhausted (tried to allocate 65979 bytes) in /var/www/web80/html/dim/4images/includes/functions.php on line 1165
Ich muß dann bei phpMyAdmin 4images_categoies die erstellte Kategorie löschen damit es wieder funktioniert.
Wenn ich bilder hinzufüge und klicke auf den Thumpnail dann passiert nichts, die gleiche seite erscheint wieder :oops:

Kann mir jemand helfen und hat ne Idee wo der fehler liegen könnte :cry: ich Danke im voraus.
Title: Re: query problems with MySQL 5.0.x
Post by: geoffbryant on January 12, 2008, 07:45:48 AM
I've made all of the changes suggested in this thread and everything works except that I cannot upload or edit images from the admin control panel. I'm using 4images 1.7.1 with MySQL 5.2.5.

I know 1.7.4 may offer a solution but I can't really upgrade because of all the mods I've made.

Any ideas?
Title: Re: query problems with MySQL 5.0.x
Post by: ipicture on January 25, 2008, 09:32:55 AM
Last night my provider also changes to MySQL 5.0 and I had this errors.

I changed all suggested lines v@no posted in #15, additional the mini_top.php for the error on the index. Thanks allot... :D

Greetings
Klaus

@ qgeoffbryant
Did you also change the "admin/home.php" ?
Title: Re: query problems with MySQL 5.0.x
Post by: Babybrit on March 28, 2008, 03:56:46 AM
after doing all that nothing has changed. anybody have any ideas.

Ok, please test this (if it all works, I'll post it as a compability fix):

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)

Stop here. Test every page and only if you have problem continue next:

In postcards.php find:
Code: [Select]
FROM ".POSTCARDS_TABLE." p, ".IMAGES_TABLE." iReplace with:
Code: [Select]
FROM (".POSTCARDS_TABLE." p, ".IMAGES_TABLE." i)

In search.php find:
Code: [Select]
FROM ".WORDLIST_TABLE." w, ".WORDMATCH_TABLE." mReplace with:
Code: [Select]
FROM (".WORDLIST_TABLE." w, ".WORDMATCH_TABLE." m)

In includes/auth.php find:
Code: [Select]
FROM ".GROUP_ACCESS_TABLE." a, ".GROUP_MATCH_TABLE." mReplace with:
Code: [Select]
FROM (".GROUP_ACCESS_TABLE." a, ".GROUP_MATCH_TABLE." m)

In admin/home.php find:
Code: [Select]
FROM ".USERS_TABLE." u, ".SESSIONS_TABLE." sReplace with:
Code: [Select]
FROM (".USERS_TABLE." u, ".SESSIONS_TABLE." s)

In admin/usergroups.php find:
Code: [Select]
FROM ".GROUPS_TABLE." g, ".GROUP_MATCH_TABLE." gmReplace with:
Code: [Select]
FROM (".GROUPS_TABLE." g, ".GROUP_MATCH_TABLE." gm)
Title: Re: query problems with MySQL 5.0.x
Post by: Nicky on March 28, 2008, 08:18:57 AM
hi,

Quote
after doing all that nothing has changed. anybody have any ideas.

if you did the changes and uploaded changed files it must work..
except your ftp client didn't overwrite the old files on the server..

check that
Title: Re: query problems with MySQL 5.0.x
Post by: Babybrit on March 30, 2008, 07:34:28 AM
hi,

Quote
after doing all that nothing has changed. anybody have any ideas.

if you did the changes and uploaded changed files it must work..
except your ftp client didn't overwrite the old files on the server..

check that

yes it does over-right some of them. I now get this error on other pages to

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, u.user_email FROM 4images_images i, 4images_categories c LEFT JOIN 4images_users u ON (u.user_id = i.user_id) WHERE i.image_id = 18391 AND c.cat_id = i.cat_id
Unknown column 'i.user_id' in 'on clause'

Warning: Cannot modify header information - headers already sent by (output started at /home/britney/includes/db_mysql.php:188) in /home/britney/details.php on line 54

plus I still get the other one.

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 i.cat_id = 475 AND c.cat_id = i.cat_id ORDER BY image_name ASC LIMIT 0, 12
Unknown column 'i.user_id' in 'on clause'

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /home/britney/includes/db_mysql.php on line 116

the files are a mess now can I please PM or email brits_justified@hotmail.com one of you pros the log in details so you can log in & fix it please.
Title: Re: query problems with MySQL 5.0.x
Post by: KurtW on March 30, 2008, 08:51:56 AM
Hi,

you forgot one of the codes.

The codes to change on the top are only codes from the standard 4images files.
Have you include mods in your page? Search modcodes, they also connect to the db with the wrong code (without msql5)


Kurt
Title: Re: query problems with MySQL 5.0.x
Post by: Babybrit on April 11, 2008, 07:28:50 AM
Please Someone reply I am down to 100+ hits a day before this happened I would get 10,000 + a day please help me.

"Have you include mods in your page? Search modcodes" I did a search & nothing came up.

thanks for all your help everyone to.

This comes up when you click on a picture to see it bigger & not as a Thumbnail.

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, u.user_email FROM 4images_images i, 4images_categories c LEFT JOIN 4images_users u ON (u.user_id = i.user_id) WHERE i.image_id = 18797 AND c.cat_id = i.cat_id
Unknown column 'i.user_id' in 'on clause'

Warning: Cannot modify header information - headers already sent by (output started at /home/britney/includes/db_mysql.php:188) in /home/britney/details.php on line 54
Title: Re: query problems with MySQL 5.0.x
Post by: Nicky on April 11, 2008, 09:08:18 AM
send ftp account via PM.. i will look on it in few hours (~12)
Title: Re: query problems with MySQL 5.0.x
Post by: Nicky on April 14, 2008, 09:03:16 PM
solved... details.php didn't match MySQL 5
Title: Re: query problems with MySQL 5.0.x
Post by: tripiyon on April 24, 2008, 11:47:50 AM
Hello to all.

I have done all the changes that he says V@no, but me the links do not work
The page returns to remain equal.

My version is the 1.7 and I have Mysql 5.0.45

Here you can see my gallery.
http://www.galeriatripiyon.com/galeria
Title: Re: query problems with MySQL 5.0.x
Post by: Neo1 on April 25, 2008, 12:49:31 PM
Thanks a lot ...this works!!

But i still got one Error-Page ..i guess from a mod i use. It is the "show all comments of user"

http://www.terradreams.de/gallery/member.php?action=showcomments&user_id=3597

Code: [Select]
DB Error: Bad SQL Query: SELECT i.image_id, i.cat_id, i.image_name, c.cat_name, i.image_media_file, i.image_thumb_file, i.image_allow_comments FROM 4images_images i, 4images_categories c LEFT JOIN 4images_users u ON (u.user_id = i.user_id) WHERE i.image_id = 6663 AND c.cat_id = i.cat_id
Unknown column 'i.user_id' in 'on clause'

DB Error: Bad SQL Query: SELECT i.image_id, i.cat_id, i.image_name, c.cat_name, i.image_media_file, i.image_thumb_file, i.image_allow_comments FROM 4images_images i, 4images_categories c LEFT JOIN 4images_users u ON (u.user_id = i.user_id) WHERE i.image_id = 6664 AND c.cat_id = i.cat_id
Unknown column 'i.user_id' in 'on clause'

DB Error: Bad SQL Query: SELECT i.image_id, i.cat_id, i.image_name, c.cat_name, i.image_media_file, i.image_thumb_file, i.image_allow_comments FROM 4images_images i, 4images_categories c LEFT JOIN 4images_users u ON (u.user_id = i.user_id) WHERE i.image_id = 6644 AND c.cat_id = i.cat_id
Unknown column 'i.user_id' in 'on clause'

DB Error: Bad SQL Query: SELECT i.image_id, i.cat_id, i.image_name, c.cat_name, i.image_media_file, i.image_thumb_file, i.image_allow_comments FROM 4images_images i, 4images_categories c LEFT JOIN 4images_users u ON (u.user_id = i.user_id) WHERE i.image_id = 6641 AND c.cat_id = i.cat_id
Unknown column 'i.user_id' in 'on clause'

DB Error: Bad SQL Query: SELECT i.image_id, i.cat_id, i.image_name, c.cat_name, i.image_media_file, i.image_thumb_file, i.image_allow_comments FROM 4images_images i, 4images_categories c LEFT JOIN 4images_users u ON (u.user_id = i.user_id) WHERE i.image_id = 6631 AND c.cat_id = i.cat_id
Unknown column 'i.user_id' in 'on clause'

I made all changes but i still got the error on this function.


EDIT!!

Sorry ....i found the line.

If you got that mod (show all comments of user) in your member.php, search for
Code: [Select]
FROM ".IMAGES_TABLE." i,  ".CATEGORIES_TABLE." c
replace it with
Code: [Select]
FROM (".IMAGES_TABLE." i,  ".CATEGORIES_TABLE." c)
Title: Re: query problems with MySQL 5.0.x
Post by: Nicky on April 26, 2008, 11:14:17 PM
Hello to all.

I have done all the changes that he says V@no, but me the links do not work
The page returns to remain equal.

My version is the 1.7 and I have Mysql 5.0.45

Here you can see my gallery.
http://www.galeriatripiyon.com/galeria

hi tripiyon,

where are you getting errors, i don't see them
Title: Re: query problems with MySQL 5.0.x
Post by: tripiyon on April 27, 2008, 11:31:56 AM
Hi Nicky,

The problem is that the gallery does not work.
No link works and I cannot enter as user.

It does not work at all.

P.D.: Forgive my Englishman(English), I am Spanish and do not dominate the language.

Kind Regards
Title: Re: query problems with MySQL 5.0.x
Post by: Nicky on April 27, 2008, 09:59:08 PM
hi,

send me ftp and 4images admin accounts.. i will look on it tomorrow in the evening
Title: Re: query problems with MySQL 5.0.x
Post by: tripiyon on April 29, 2008, 10:13:10 AM
Hi Nicky,

I ask for excuses you for the lateness.
I leave a link with the files of the gallery, in order that you could see them.

HERE (http://www.tripiyon.com/files_gallery.zip)

Thank you very much
Title: Re: query problems with MySQL 5.0.x
Post by: sigma. on June 24, 2008, 11:13:31 PM
I've made all of the changes suggested in this thread and everything works except that I cannot upload or edit images from the admin control panel. I'm using 4images 1.7.1 with MySQL 5.2.5.

I know 1.7.4 may offer a solution but I can't really upgrade because of all the mods I've made.

Any ideas?

Im having the exact same problem. I tried Vano's fixes but nothing changed.
Title: Re: query problems with MySQL 5.0.x
Post by: axlrose on September 03, 2008, 09:54:21 PM
Greetings!

Admin, Mods & Fellow members,

Here's my site problem for reference:
http://freeadsph.com/faq.php
http://freeadsph.com/contact.php

Using:
4images Version: 1.7.6
PHP Version: 4.4.6
MySQL Version: 5
http://www.freeadsph.com/


I don't know if this is the right thread for me to ask question after exploring for possible answer i found this 7 pages thread and my problem looks similar but i don't know where to start really since my site properly running until my host upgraded yesterday from MSql 4/Php4 to MSql 5/Php 5 .

My problem to wit:

Admin Problem:

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, 13, 11, 10, 9, 2, 6, 7, etc...) AND i.image_date >=1219531456 ORDER BY RANT () LIMIT 3 Unknown column "i.user_id"in'on clause.

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /home/freeads/public_html/includes/db_mysql.php on line 116


User Problem: An unexpected error occured. Please try again later

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /home/freeads/public_html/includes/db_mysql.php on line 116


Not only that as i observe when i minimize its size the contents/layout will also minimize. So, what's wrong? Can anyone help me? Or do i have to re-install again it.
Give me an idea please or can someone help me to solve this problem.


Thanks a lot...


Regards,
axlrose
from Philippines
Title: Re: query problems with MySQL 5.0.x
Post by: V@no on September 04, 2008, 02:21:39 AM
Hello and welcome to 4images forum.

faq.php and contact.php are not part of 4images package, you must have made them yourself, and since only they are affected I can assume the problem is in their code.

Either way, this is a weird error. Can you attach faq.php file? (zip it first)
Title: Re: query problems with MySQL 5.0.x
Post by: axlrose on September 04, 2008, 06:43:50 AM
Hello and welcome to 4images forum.

faq.php and contact.php are not part of 4images package, you must have made them yourself, and since only they are affected I can assume the problem is in their code.

Either way, this is a weird error. Can you attach faq.php file? (zip it first)

Thanks for the quick reply.
Attached herewith the weird error files.

Thanks...
Title: Re: query problems with MySQL 5.0.x
Post by: V@no on September 04, 2008, 08:25:23 AM
As it was discribed in this topic, replace
Code: [Select]
        FROM ".IMAGES_TABLE." i,  ".CATEGORIES_TABLE." c
With
Code: [Select]
        FROM (".IMAGES_TABLE." i,  ".CATEGORIES_TABLE." c)
Title: Re: query problems with MySQL 5.0.x
Post by: axlrose on September 04, 2008, 09:56:07 AM
As it was discribed in this topic, replace
Code: [Select]
        FROM ".IMAGES_TABLE." i,  ".CATEGORIES_TABLE." c
With
Code: [Select]
        FROM (".IMAGES_TABLE." i,  ".CATEGORIES_TABLE." c)

Actually, i do that already. But, still not working?
Do i have to re install it? What do you think?
Title: Re: query problems with MySQL 5.0.x
Post by: Nicky on September 04, 2008, 10:13:30 AM
hi,

for test, try only with this code in faq.php

Code: [Select]
<?php 
/************************************************************************** 
 *                                                                        * 
 *    4images - A Web Based Image Gallery Management System               * 
 *    ----------------------------------------------------------------    * 
 *                                                                        * 
 *             File: faq.php                                           * 
 *        Copyright: (C) 2002 Jan Sorgalla                                * 
 *            Email: jan@4homepages.de                                    * 
 *              Web: http://www.4homepages.de                             * 
 *    Scriptversion: 1.7                                                  * 
 *                                                                        * 
 *    Never released without support from: Nicky (http://www.nicky.net)   * 
 *                                                                        * 
 ************************************************************************** 
 *                                                                        * 
 *    Dieses Script ist KEINE Freeware. Bitte lesen Sie die Lizenz-       * 
 *    bedingungen (Lizenz.txt) für weitere Informationen.                 * 
 *    ---------------------------------------------------------------     * 
 *    This script is NOT freeware! Please read the Copyright Notice       * 
 *    (Licence.txt) for further information.                              * 
 *                                                                        * 
 *************************************************************************/ 

$main_template "faq"

define('GET_CACHES'1); 
define('ROOT_PATH''./'); 
define('GET_USER_ONLINE'1); 
include(
ROOT_PATH.'global.php'); 
require(
ROOT_PATH.'includes/sessions.php'); 
include(
ROOT_PATH.'includes/page_header.php'); 

 
//----------------------------------------------------- 
//--- Clickstream ------------------------------------- 
//----------------------------------------------------- 
$clickstream "<span class=\"clickstream\"><a href=\"".$site_sess->url(ROOT_PATH."index.php")."\" class=\"clickstream\">".$lang['home']."</a>".$config['category_separator'].$txt_clickstream."</span>"

//----------------------------------------------------- 
//--- Print Out --------------------------------------- 
//----------------------------------------------------- 
$site_template->register_vars(array( 
  
"content" => $content
  
"msg" => $msg
  
"clickstream" => $clickstream
  
"lang_control_panel" => $lang['control_panel'
)); 
$site_template->print_template($site_template->parse_template($main_template)); 
include(
ROOT_PATH.'includes/page_footer.php'); 
?>


[EDIT]
and btw. like V@no wrote in his previous post this will solve your issue.
Title: Re: query problems with MySQL 5.0.x
Post by: V@no on September 04, 2008, 03:02:43 PM
Actually, i do that already. But, still not working?
Do i have to re install it? What do you think?

In the attached files you don't have it...
Title: Re: query problems with MySQL 5.0.x
Post by: fotopolis on September 05, 2008, 01:29:02 AM
Hello.
I've migrated mysql 4 to mysql 5.
I've done the changes in the second page and now it doesn't have the sql error in the top of the page.
But the problem is than I cannot login and I cannot navigate to categories, to images and no pages can be loaded.
This is the url: http://www.vuestrasfotos.com/

Please can anyone help me?


Thanks a lot!
Title: Re: query problems with MySQL 5.0.x
Post by: V@no on September 05, 2008, 01:44:24 AM
Did you migrate from one server to another? it looks like your images physically missing on the server.

As of "can't login", does it show you any messages when you try login?
Title: Re: query problems with MySQL 5.0.x
Post by: fotopolis on September 05, 2008, 02:09:49 AM
Yes, I've migrated to another new server, in other hosting.
The images are not uploaded yet.  because they are about 1,7GB. But pics or thumbnails is not the problem.
When I login, it loads the main page again.
When I clic on a category, it loads the main page again.
Nothing happens when you clic. Allways load the main page again.
And it's impossible to login.

Thanks!
Title: Re: query problems with MySQL 5.0.x
Post by: V@no on September 05, 2008, 04:37:01 AM
Can you check database with phpmyadmin. is everything ok there?
Title: Re: query problems with MySQL 5.0.x
Post by: fotopolis on September 05, 2008, 07:45:42 AM
Evrtything in the galery is ok.
It has the categories, the number of photos, the number of users, etc.
But It seems like in login and visiting the web it doesn't connect.
Title: Re: query problems with MySQL 5.0.x
Post by: fotopolis on September 05, 2008, 10:39:47 AM
Is it possible some of this sentences must be changed too?

FROM ".COMMENTS_TABLE." c
FROM ".IMAGES_TABLE."
FROM ".IMAGES_TABLE."
$sql = "DELETE FROM ".SUBSCRIPTIONS_TABLE."


Thanks!
Title: Re: query problems with MySQL 5.0.x
Post by: fotopolis on September 05, 2008, 11:55:37 AM
I think everything is fixed with php5 compatible too.
Problems login and problems qith categories / details are fixed too.
I had two problems: mysql 5 and php5 compatible.

http://www.4homepages.de/forum/index.php?topic=6632.0


Thanks a lot!
Title: Re: query problems with MySQL 5.0.x
Post by: axlrose on September 05, 2008, 11:52:55 PM
hi,

for test, try only with this code in faq.php

Code: [Select]
<?php 
/************************************************************************** 
 *                                                                        * 
 *    4images - A Web Based Image Gallery Management System               * 
 *    ----------------------------------------------------------------    * 
 *                                                                        * 
 *             File: faq.php                                           * 
 *        Copyright: (C) 2002 Jan Sorgalla                                * 
 *            Email: jan@4homepages.de                                    * 
 *              Web: http://www.4homepages.de                             * 
 *    Scriptversion: 1.7                                                  * 
 *                                                                        * 
 *    Never released without support from: Nicky (http://www.nicky.net)   * 
 *                                                                        * 
 ************************************************************************** 
 *                                                                        * 
 *    Dieses Script ist KEINE Freeware. Bitte lesen Sie die Lizenz-       * 
 *    bedingungen (Lizenz.txt) für weitere Informationen.                 * 
 *    ---------------------------------------------------------------     * 
 *    This script is NOT freeware! Please read the Copyright Notice       * 
 *    (Licence.txt) for further information.                              * 
 *                                                                        * 
 *************************************************************************/ 

$main_template "faq"

define('GET_CACHES'1); 
define('ROOT_PATH''./'); 
define('GET_USER_ONLINE'1); 
include(
ROOT_PATH.'global.php'); 
require(
ROOT_PATH.'includes/sessions.php'); 
include(
ROOT_PATH.'includes/page_header.php'); 

 
//----------------------------------------------------- 
//--- Clickstream ------------------------------------- 
//----------------------------------------------------- 
$clickstream "<span class=\"clickstream\"><a href=\"".$site_sess->url(ROOT_PATH."index.php")."\" class=\"clickstream\">".$lang['home']."</a>".$config['category_separator'].$txt_clickstream."</span>"

//----------------------------------------------------- 
//--- Print Out --------------------------------------- 
//----------------------------------------------------- 
$site_template->register_vars(array( 
  
"content" => $content
  
"msg" => $msg
  
"clickstream" => $clickstream
  
"lang_control_panel" => $lang['control_panel'
)); 
$site_template->print_template($site_template->parse_template($main_template)); 
include(
ROOT_PATH.'includes/page_footer.php'); 
?>


[EDIT]
and btw. like V@no wrote in his previous post this will solve your issue.

Tried already your code my FAQ page already working now, great help and a big thanks!
Superb support here...

And i'll try @vano's idea too later...
Title: Re: query problems with MySQL 5.0.x
Post by: axlrose on September 05, 2008, 11:57:03 PM
Actually, i do that already. But, still not working?
Do i have to re install it? What do you think?

In the attached files you don't have it...

Ill' do it again later to fix my problem especially my contact us page since my FAQ page was already working with the help of Admin Nicky, you have both great idea and support.
Title: Re: query problems with MySQL 5.0.x
Post by: axlrose on September 06, 2008, 02:23:55 AM
Thanks all already fix my problem with the help of Ivan Works; http://www.4homepages.de/forum/index.php?topic=8987.135
Title: Re: query problems with MySQL 5.0.x
Post by: djith on September 07, 2008, 12:25:10 PM
After all the changes whit the brackets i still get the error, my server is using mysql 5.0.51 and i'm using 4images 1.7.1 and made so many mods on it that i need to keep the 1.7.1 !!
 the error is:

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 i.cat_id NOT IN (0) AND c.cat_id = i.cat_id LIMIT 1683, 1
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 i.cat_id = 1 AND c.cat_id = i.cat_id ORDER BY image_downloads DESC LIMIT 0, 32
Unknown column 'i.user_id' in 'on clause'

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /home/avata5/public_html/includes/db_mysql.php on line 116

Title: Re: query problems with MySQL 5.0.x
Post by: V@no on September 07, 2008, 01:47:19 PM
Then you missed some.
I just published more complete tutorial (http://www.4homepages.de/forum/index.php?topic=22668.0)
Title: Re: query problems with MySQL 5.0.x
Post by: Vincent on December 30, 2010, 01:34:29 PM
After my Hoster changed the MySQL Server - i have done this http://www.4homepages.de/forum/index.php?topic=10184.0;msg=53697
but top.php is not holding this information and also still gives error!

have a nice day
vincent
Title: Re: query problems with MySQL 5.0.x
Post by: Rembrandt on December 30, 2010, 03:01:23 PM
...but top.php is not holding this information and also still gives error!
...

keine ahnung wie die top.php in der V.1.7 aussieht aber du brauchst doch nur in den SQL abfragen das:
".IMAGES_TABLE." i, ".CATEGORIES_TABLE." c
in klammern setzen.

mfg Andi