Strona główna własnego forum Opcje

[Denver]

Witam. Napisałem swoje forum (nie korzystam więc z phpBB). Mam problem z zapytaniem generującym strukturę forum, tj. kategorie, w nich fora, a dla każdego forum jest dopisana ostatnia wiadomość (i do niej link).

Struktura tabel mniej więcej taka jak w phpBB. System szablonów napisałem sam, więc niech nie dziwią was polecenia $tlp -> add...

Oto część kodu php z pliku index.php


[php:1:93305db715]<?php
$sql = "SELECT
c.id AS c_id, c.name AS c_name,
f.id AS f_id, f.name AS f_name, f.description AS f_description
FROM ".$tables['forums']." f, ".$tables['categories']." c
WHERE f.category_id=c.id
ORDER BY c.sequence ASC, f.sequence ASC";

$for_result = mysql_query ($sql);
if ($for_result) {

$last_category = 0;
while ($for_row = mysql_fetch_assoc ($for_result)) {

$cat_id = $for_row ['c_id'];

if ($last_category != $cat_id) {

$last_category = $cat_id;
$cat_name = $for_row ['c_name'];

$tpl -> add ("CATEGORY_ID", $cat_id);
$tpl -> add ("CATEGORY_NAME", $cat_name);
print $tpl -> get ("forum_category");

}

$last_post = $lang ['No_Posts'];
$topics_counter = $posts_counter = 0;
$forum_id = $for_row ['f_id'];

$sql = "SELECT p.id FROM ".$tables['posts']." p, ".$tables['topics']." t
WHERE p.topic_id=t.id AND t.forum_id=$forum_id";
$posts_counter = mysql_num_rows ( mysql_query ($sql) );


$sql = "SELECT p.id, p.author_id, p.date FROM ".$tables['posts']." p, ".$tables['topics']." t
WHERE t.forum_id=$forum_id AND p.topic_id=t.id ORDER BY p.id DESC LIMIT 1";
$result = mysql_query ($sql);

while ($post_row = mysql_fetch_assoc ($result)) {

$sql = "SELECT id FROM ".$tables['topics']." WHERE forum_id=$forum_id";
$topics_counter = mysql_num_rows ( mysql_query ($sql) );

$last_post_id = $post_row ['id'];
$post_date = $post_row ['date'];

if ($post_date) {

$tpl -> add ("LAST_POST_DATE", strftime ("%d.%m.%Y %H:%M", $post_date));
$tpl -> add ("AUTHOR_ID", $post_row ['author_id']);
$tpl -> add ("AUTHOR_NAME", getNameByID ($post_row ['author_id']));

$tpl -> add ("TOPIC_ID", $topic_id);
$tpl -> add ("LAST_POST_ID", $last_post_id);
$last_post = $tpl -> get ("forum_last_post");

$time = getElement ("last_visit", $tables['users'], "name='".$_COOKIE ['logged_username']."'");
$time -= LAST_POST_OFFSET;

if ($post_date >= $time) {
$tpl -> add ("FORUM_FOLDER", "_new");
} else {
$tpl -> add ("FORUM_FOLDER", "");
}

}

}


if ($topics_counter == 0 || $_COOKIE ['logged'] != 1) {
$tpl -> add ("FORUM_FOLDER", "");
}
$tpl -> add ("FORUM_ID", $for_row ['f_id']);
$tpl -> add ("FORUM_NAME", $for_row ['f_name']);
$tpl -> add ("FORUM_DESCRIPTION", $for_row ['f_description']);
$tpl -> add ("TOPICS", $topics_counter);
$tpl -> add ("POSTS", $posts_counter);
$tpl -> add ("LAST_POST", $last_post);



print $tpl -> get ("forum_forum");

}


$total_topics = mysql_num_rows ( mysql_query ("SELECT id FROM ".$tables['topics']) );
$tpl -> add ("STATS_TOTAL_TOPICS", $total_topics);

$total_posts = mysql_num_rows ( mysql_query ("SELECT id FROM ".$tables['posts']) );
$tpl -> add ("STATS_TOTAL_POSTS", $total_posts);

$total_users = mysql_num_rows ( mysql_query ("SELECT id FROM ".$tables['users']) );
$tpl -> add ("STATS_TOTAL_USERS", $total_users);

$last_user_id = getLastElement ($tables['users'], "id > 0");
$tpl -> add ("STATS_LAST_USER_ID", $last_user_id);

$last_user_name = getElement ("name", $tables['users'], "id = $last_user_id");
$tpl -> add ("STATS_LAST_USER_NAME", $last_user_name);


$sql = "SELECT user_id FROM ".$tables ['users_online']." WHERE time >=".(time () - USERS_ONLINE_OFFSET)." ORDER BY user_id ASC";
$result = mysql_query ($sql);
$results = mysql_num_rows ($result);

$count = 0;
while ($row = mysql_fetch_assoc ($result)) {
$count++;

$username = getNameByID ($row ['user_id']);
$users_online .= "<a href='profile.php?mode=view&user_id=".$row ['user_id']."'>$username</a>";
if ($count < $results) $users_online .= ", ";
}
$tpl -> add ("STATS_USERS_ONLINE_COUNT", $count);
$tpl -> add ("STATS_USERS_ONLINE", $users_online);

$users_record = getElement ("users_record", $tables ['config'], "1=1");
$tpl -> add ("STATS_USERS_RECORD", $users_record);

$users_record_date = getElement ("users_record_date", $tables ['config'], "1=1");
$tpl -> add ("STATS_USERS_RECORD_DATE", strftime ("%d.%m.%Y %H:%M", $users_record_date));

print $tpl -> get ("forum_list_end");
} else {
information ($lang ['Information'], $lang ['Cant_Read_Category'], 0);
}
?>[/php:1:93305db715]


Problem jest w tym, że zapytanie wykonuje się dosyć długo (ok. 2 sekund) na forum, gdzie postów jest ok. 2000.

Moje pytanie: Co należało by w tym skrypcie zoptymalizować?

Podpatrzyłem skrypt forum phpBB i zauważyłem tam w pewnym momencie polecenie "LEFT JOIN ....". Szukałem w manualu MySQL objaśnienia, ale coś nie bardzo to jest opisane (IMG:http://forum.php.pl/style_emoticons/default/sad.gif)

Proszę o pomoc.


PS. Forum znajduje się na TEJ STRONIE

----------------
php Poczatkujacy >> Skrypty
Seth