Tabellen vernüpfen...

[Dungeonwatcher]

Hi großer Meister!

Bevor ich mich heute Abend weiter mit der Lotterie befasse hätte ich noch folgendes Phänomen zu lösen. Ich bin dabei mir eine Bildersuche für das original Smartor Album zu bauen. Das funktioniert schon fast so wie ich es möchte und sieht zur Zeit so aus:



Ich kämpfe zur Zeit mit dem Problem, das die Werte für das Rating und den Kommentaren nicht bzw. falsch angezeigt werden. Alle anderen Werte sind da und auch korrekt.
Folgende SQL Abfrage tätige ich dazu:

Code: Alles auswählen

	$sql = "SELECT p.pic_id, p.pic_title, p.pic_desc, p.pic_user_id, p.pic_user_ip, p.pic_username, p.pic_time, p.pic_cat_id, p.pic_view_count, p.pic_approval, c.cat_id, c.cat_title, c.cat_user_id, ct.comment_text, p.pic_filename as picfilename, r.rate_pic_id, AVG(r.rate_point) AS rating, COUNT(DISTINCT ct.comment_id) AS comments
			FROM " . ALBUM_TABLE ." AS p
				LEFT JOIN ". ALBUM_CAT_TABLE ." AS c ON p.pic_cat_id = c.cat_id
				LEFT JOIN ". ALBUM_RATE_TABLE ." AS r ON p.pic_id = r.rate_pic_id
				LEFT JOIN ". ALBUM_COMMENT_TABLE ." AS ct ON p.pic_id = ct.comment_pic_id
			WHERE p.pic_approval = 1
				AND " . $where .  " LIKE '%" . $s . "%'
				AND p.pic_cat_id = c.cat_id
				" . $search_pg . "
			ORDER BY p.pic_time ASC LIMIT ".$limit_sql."";

Der dazugehörige PHP Code zur Anzeige sieht so aus:

Code: Alles auswählen

'RATING' => ($album_config['rate'] == 1) ? ( '<a href="'. append_sid("album_rate.$phpEx?pic_id=". $picrow['pic_id']) . '">' . $lang['Rating'] . '</a>: ' . $picrow['rating'] . '<br />') : '',

'COMMENTS' => ($album_config['comment'] == 1) ? ( '<a href="'. append_sid("album_comment.$phpEx?pic_id=". $picrow['pic_id']) . '">' . $lang['Comments'] . '</a>: ' . $picrow['comments'] . '<br />') : '',

Offenbar wird die pic_id nicht korrekt übergeben bzw. ausgewertet. Was läuft da falsch?

Bye

[oxpus]

Zum einen solltest du die Bedingungen in JOIN LEFT Anweisungen ausgehend von der zu verbindenden Tabelle aufbauen.
Also nicht so:

Code: Alles auswählen

            LEFT JOIN ". ALBUM_CAT_TABLE ." AS c ON p.pic_cat_id = c.cat_id
            LEFT JOIN ". ALBUM_RATE_TABLE ." AS r ON p.pic_id = r.rate_pic_id
            LEFT JOIN ". ALBUM_COMMENT_TABLE ." AS ct ON p.pic_id = ct.comment_pic_id

sondern so:

Code: Alles auswählen

            LEFT JOIN ". ALBUM_CAT_TABLE ." AS c ON c.cat_id = p.pic_cat_id
            LEFT JOIN ". ALBUM_RATE_TABLE ." AS r ON r.rate_pic_id = p.pic_id
            LEFT JOIN ". ALBUM_COMMENT_TABLE ." AS ct ON ct.comment_pic_id = p.pic_id

Dann rechnest du Durchschnittswerte und zählst die Kommentare zusammen, ohne die restlichen Felder zu gruppieren.
Damit erhälst du nur Einzeldatensätze, die ein falsches Bild liefern.
Also vor ORDER BY müsste noch das rein:

Code: Alles auswählen

GROUP BY p.pic_id, p.pic_title, p.pic_desc, p.pic_user_id, p.pic_user_ip, p.pic_username, p.pic_time, p.pic_cat_id, p.pic_view_count, p.pic_approval, c.cat_id, c.cat_title, c.cat_user_id, ct.comment_text, p.pic_filename as picfilename, r.rate_pic_id

Und zuletzt wäre durch den Join mit der Kategorietabelle auch die Zeile

Code: Alles auswählen

            AND p.pic_cat_id = c.cat_id

überflüssig, bzw. sogar falsch.
Ansonsten sehe ich zunächst keinen weiteren Fehler...

[Dungeonwatcher]

Zum einen solltest du die Bedingungen in JOIN LEFT Anweisungen ausgehend von der zu verbindenden Tabelle aufbauen.

Das ist allerdings war.

Dann rechnest du Durchschnittswerte und zählst die Kommentare zusammen, ohne die restlichen Felder zu gruppieren.
Damit erhälst du nur Einzeldatensätze, die ein falsches Bild liefern.

Aahja.

Also vor ORDER BY müsste noch das rein:

Code: Alles auswählen

GROUP BY p.pic_id, p.pic_title, p.pic_desc, p.pic_user_id, p.pic_user_ip, p.pic_username, p.pic_time, p.pic_cat_id, p.pic_view_count, p.pic_approval, c.cat_id, c.cat_title, c.cat_user_id, ct.comment_text, p.pic_filename as picfilename, r.rate_pic_id

Sowie ich dies eintrage funktioniert die Abfrage garnicht mehr und liefert die Fehlermeldung "Couldn't obtain a list of matching information (searching for: )" zurück.

Und zuletzt wäre durch den Join mit der Kategorietabelle auch die Zeile

Code: Alles auswählen

            AND p.pic_cat_id = c.cat_id

überflüssig, bzw. sogar falsch.

Hmmm, das ist noch ein Überbleibsel des Original Scripts. Das Entfernen schadet jedenfalls nicht.

Ansonsten sehe ich zunächst keinen weiteren Fehler...

Nee, ganz so einfach ist es leider nicht.

[oxpus]

Kannst du mal bitte die komplette Fehlermeldung posten?
Dein Zitat scheint nur ein Teil dessen zu sein.

[Dungeonwatcher]

Moin, moin!

Stimmt, da fehlt tatsächlich etwas, denn lt. Quellcode sieht die Meldung so aus:

Couldn't obtain a list of matching information (searching for: $search)

Da geht wohl der Suchbegriff verloren?

Der kplt. Quellcode nach Änderung sieht nun so aus:

Code: Alles auswählen

	$count_sql = "SELECT COUNT(pic_id) AS count
					FROM " . ALBUM_TABLE . ' AS p,' . ALBUM_CAT_TABLE . " AS c
					WHERE p.pic_approval = 1
						AND " . $where .  " LIKE '%" . $s . "%'
						" . $search_pg . "
						AND p.pic_cat_id = c.cat_id";

	if( !($result = $db->sql_query($count_sql)) )
	{
		message_die(GENERAL_ERROR, 'Could not count pics'.$m, '', __LINE__, __FILE__, $count_sql);
	}

	$row = $db->sql_fetchrow($result);
	$total_pics = $row['count'];

	$sql = "SELECT p.pic_id, p.pic_title, p.pic_desc, p.pic_user_id, p.pic_user_ip, p.pic_username, p.pic_time, p.pic_cat_id, p.pic_view_count, p.pic_approval, c.cat_id, c.cat_title, c.cat_user_id, ct.comment_text, p.pic_filename as picfilename, r.rate_pic_id, AVG(r.rate_point) AS rating, COUNT(DISTINCT ct.comment_id) AS comments 
			FROM " . ALBUM_TABLE ." AS p
				LEFT JOIN ". ALBUM_CAT_TABLE ." AS c ON c.cat_id = p.pic_cat_id
				LEFT JOIN ". ALBUM_RATE_TABLE ." AS r ON r.rate_pic_id = p.pic_id
				LEFT JOIN ". ALBUM_COMMENT_TABLE ." AS ct ON ct.comment_pic_id = p.pic_id
			WHERE p.pic_approval = 1
				AND " . $where .  " LIKE '%" . $s . "%'
				" . $search_pg . "
			GROUP BY p.pic_id, p.pic_title, p.pic_desc, p.pic_user_id, p.pic_user_ip, p.pic_username, p.pic_time, p.pic_cat_id, p.pic_view_count, p.pic_approval, c.cat_id, c.cat_title, c.cat_user_id, ct.comment_text, p.pic_filename as picfilename, r.rate_pic_id
			ORDER BY p.pic_time ASC LIMIT ".$limit_sql."";
	if ( !($result = $db->sql_query($sql)) )
	{
		message_die(GENERAL_ERROR, "Couldn't obtain a list of matching information (searching for: $search)", "", __LINE__, __FILE__, $sql);
	}

[oxpus]

Füg doch mal vor

Code: Alles auswählen

   if ( !($result = $db->sql_query($sql)) )

ein echo $sql; ein, damit die SQL-Anweisung angezeigt wird und führe die direkt gegen die Datenbank aus. Dann ist die Fehlermeldung schon qualifizierter.

[Dungeonwatcher]

Das ergibt dann dies:

SELECT p.pic_id, p.pic_title, p.pic_desc, p.pic_user_id, p.pic_user_ip, p.pic_username, p.pic_time, p.pic_cat_id, p.pic_view_count, p.pic_approval, c.cat_id, c.cat_title, c.cat_user_id, ct.comment_text, p.pic_filename as picfilename, r.rate_pic_id, AVG(r.rate_point) AS rating, COUNT(DISTINCT ct.comment_id) AS comments FROM phpbb_album AS p LEFT JOIN phpbb_album_cat AS c ON c.cat_id = p.pic_cat_id LEFT JOIN phpbb_album_rate AS r ON r.rate_pic_id = p.pic_id LEFT JOIN phpbb_album_comment AS ct ON ct.comment_pic_id = p.pic_id WHERE p.pic_approval = 1 AND p.pic_username LIKE '%Dungeonwatcher%' GROUP BY p.pic_id, p.pic_title, p.pic_desc, p.pic_user_id, p.pic_user_ip, p.pic_username, p.pic_time, p.pic_cat_id, p.pic_view_count, p.pic_approval, c.cat_id, c.cat_title, c.cat_user_id, ct.comment_text, p.pic_filename as picfilename, r.rate_pic_id ORDER BY p.pic_time ASC LIMIT 20

[oxpus]

Und wenn du das gegen die Datenbank direkt fährst?
Was kommt dann als Fehlermeldung?

[Dungeonwatcher]

Du meinst direkt per phpMA? Das ergibt dies:

#1064 - 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 'as picfilename, r.rate_pic_id ORDER BY p.pic_time ASC LIMIT 20' at line 1

[Dungeonwatcher]

Hi großer Meister!

Ich hab's hinbekommen.

Das ganze sieht jetzt so aus und ist auch etwas einfacher:

Code: Alles auswählen

	$sql = "SELECT p.pic_id, p.pic_title, p.pic_desc, p.pic_user_id, p.pic_user_ip, p.pic_username, p.pic_time, p.pic_cat_id, p.pic_view_count, p.pic_approval, c.cat_id, c.cat_title, c.cat_user_id, p.pic_filename as picfilename, r.rate_pic_id, AVG(r.rate_point) AS rating, COUNT(DISTINCT ct.comment_id) AS comments
         FROM " . ALBUM_TABLE ." AS p
            LEFT JOIN ". ALBUM_CAT_TABLE ." AS c ON p.pic_cat_id = c.cat_id
            LEFT JOIN ". ALBUM_RATE_TABLE ." AS r ON p.pic_id = r.rate_pic_id
            LEFT JOIN ". ALBUM_COMMENT_TABLE ." AS ct ON p.pic_id = ct.comment_pic_id
         WHERE p.pic_approval = 1
            AND " . $where .  " LIKE '%" . $s . "%'
            AND p.pic_cat_id = c.cat_id
            " . $search_pg . "
            GROUP BY p.pic_id
         ORDER BY p.pic_time ASC LIMIT ".$limit_sql."";

Dankeschön für deine Unterstützung und Mühe.

Jetzt geht's an die Rechteverwaltung, denn schließlich soll nicht jeder User alle Bilder durchsuchen können, sondern nur die für die er Freigeschaltet ist.

Bye

[oxpus]

Entweder so halt oder man hätte in der group by Anweisung die Feld-Aliasnamen rausgenommen ...