phpBB.de

Verfasst: **04.07.2007 17:31**

Hallo,

auf meinem Server wurde ein neues MYSQL (Version 5) augespielt, jetzt habe ich Probleme z.B. mit der Suche in Verbindung mit dem Kategorie Hack, d.h. wenn ich suchen will erhalte ich z.B: folgende Fehlermeldung:

Code: Alles auswählen

Could not obtain search results

DEBUG MODE

SQL Error : 1054 Unknown column 't.k_id' in 'on clause'

SELECT t.*, f.forum_id, f.forum_name, u.username, u.user_id, u2.username as user2, u2.user_id as id2, p.post_username, p2.post_username AS post_username2, p2.post_time, k.kategorie FROM phpbb_topics t, phpbb_forums f, phpbb_users u, phpbb_posts p, phpbb_posts p2, phpbb_users u2 LEFT JOIN phpbb_topic_cat as k ON t.k_id=k.k_id WHERE t.topic_id IN (403, 1107, 1172, 1208, 1228, 1230, 1231, 1236, 1237) AND t.topic_poster = u.user_id AND f.forum_id = t.forum_id AND p.post_id = t.topic_first_post_id AND p2.post_id = t.topic_last_post_id AND u2.user_id = p2.poster_id ORDER BY p2.post_time DESC LIMIT 0, 25

Line : 899
File : search.php

Kann mir jemand weiterhelfen?
Hab noch mit dem k_id Probleme in anderen Scripten!
Liegt das vielleicht an dem LEFT JOINT oder LEFT OUTER JOINT?

Dank euch!

Verfasst: **04.07.2007 20:24**

Hallo

Liegt das vielleicht an dem LEFT JOINT oder LEFT OUTER JOINT?

Ja genau daran liegt es. Du müsstest das also entsprechend ändern.

Hier mal eine kleine Hilfe dazu

However, the precedence of the comma operator is less than of INNER JOIN, CROSS JOIN, LEFT JOIN, and so on. If you mix comma joins with the other join types when there is a join condition, an error of the form Unknown column 'col_name' in 'on clause' may occur. Information about dealing with this problem is given later in this section.

und das hier

#

Previously, the comma operator (,) and JOIN both had the same precedence, so the join expression t1, t2 JOIN t3 was interpreted as ((t1, t2) JOIN t3). Now JOIN has higher precedence, so the expression is interpreted as (t1, (t2 JOIN t3)). This change affects statements that use an ON clause, because that clause can refer only to columns in the operands of the join, and the change in precedence changes interpretation of what those operands are.

Example:

CREATE TABLE t1 (i1 INT, j1 INT);
CREATE TABLE t2 (i2 INT, j2 INT);
CREATE TABLE t3 (i3 INT, j3 INT);
INSERT INTO t1 VALUES(1,1);
INSERT INTO t2 VALUES(1,1);
INSERT INTO t3 VALUES(1,1);
SELECT * FROM t1, t2 JOIN t3 ON (t1.i1 = t3.i3);

Previously, the SELECT was legal due to the implicit grouping of t1,t2 as (t1,t2). Now the JOIN takes precedence, so the operands for the ON clause are t2 and t3. Because t1.i1 is not a column in either of the operands, the result is an Unknown column 't1.i1' in 'on clause' error. To allow the join to be processed, group the first two tables explicitly with parentheses so that the operands for the ON clause are (t1,t2) and t3:

SELECT * FROM (t1, t2) JOIN t3 ON (t1.i1 = t3.i3);

Alternatively, avoid the use of the comma operator and use JOIN instead:

SELECT * FROM t1 JOIN t2 JOIN t3 ON (t1.i1 = t3.i3);

This change also applies to statements that mix the comma operator with INNER JOIN, CROSS JOIN, LEFT JOIN, and RIGHT JOIN, all of which now have higher precedence than the comma operator.

Quelle

Grüße: Mahony

Verfasst: **04.07.2007 20:44**

Genau. Ersetze im "FROM" einfach alle "," durch "JOIN"....

LG, Olli

Verfasst: **05.07.2007 07:44**

Dank euch, hatte es mittlerweile rausbekommen, man kann auch einfach den Part zwischen FROM und JOIN in Klammer () setzen.

phpBB.de

Probleme mit neuer SQL Version

Probleme mit neuer SQL Version