...

View Full Version : Resolved Minimize the number of query?



kenshn111
12-25-2011, 11:24 AM
The following code runs a query every page visit, is there a way to make it run whenever a new post is made? I've thought of cache but I don't know how to make it work :( I'm trying to get new topics from phpBB3.

<?php
$dbhost = '*';
$dbuser = '*';
$dbpasswd = '*';
$forum_url = "http://*.*.org/forums/viewtopic.php?";
mysql_connect ($dbhost, $dbuser, $dbpasswd);
mysql_select_db("forum");
$recent = mysql_query ("SELECT * FROM phpbb_topics WHERE (forum_id='2') OR (forum_id='3') ORDER BY topic_time DESC LIMIT 6");

while ($recent_row = mysql_fetch_assoc($recent))
{
//get data
$topic_id = $recent_row['topic_id'];
$forum_id = $recent_row['forum_id'];
$post_time = $recent_row['topic_time'];
$friendly = date('Y-m-d', $post_time);
//get topic name
$topic_name = mysql_query("SELECT topic_title FROM phpbb_topics WHERE topic_id='$topic_id'");
$topic_name = mysql_fetch_assoc($topic_name);
$topic_name = $topic_name['topic_title'];
echo '<div id="post_linsk">';
echo '<span class="date_post">'.$friendly.'</span>';
echo "<a href='".$forum_url."f=".$forum_id."&t=".$topic_id."' class='post_link' >".$topic_name."</a></div><br/>";

}
?>

Advance thanks! Merry Christmas!

Apothem
12-25-2011, 11:31 AM
Well, the first thing to do is figure out which file(s) creates/edits/deletes a post/thread. Once you have that, at a very simple level you can create a new table and insert some of the desired data you have into it. From there, you can just select that table and display the most recent 6 posts.

Now, what if you don't want that new table to be bloated? Well, I would think the best solution to this is to do a count query and then if it is about say 100 entries, delete maybe ~85 of the oldest entries.

Another thing you can do is get only the topic id, forum id, and topic time. Then store all the topic id into an array, and implode it to use a single SELECT query that uses the WHERE `col` IN('array', 'of', 'vals') syntax. Then if you get all the data from there, you've more or less shorted your queries from "1 + 6" to "2"

kenshn111
12-25-2011, 11:49 AM
I'm not really that good in coding @_@ Could you show me an example?
-
Oh and, what's the disadvantage of queries being run on every page visit?

Apothem
12-25-2011, 12:29 PM
I cannot go over the technical details of how MySQL works as I'm not fluent with it myself. As far as I know, the disadvantage of queries being run on every page depends; if you have too many queries it can slow down your site. If it is not optimal it will slow down your site. Generally queries are run relatively fast and because of things like indexing, queries and internal caching speed up the process.

I'll "guide" you in the second method. So suppose you had a query and you collect all the data into $rows. $rows is just an array with each row found in it.

Then you can do something like:

$in = array();
foreach($rows as $row) { $in[] = $row['col']; }
$in = implode("','", array_unique($in));

And from there:

mysql_query("SELECT col, new_col FROM table WHERE col IN('{$in}')

And retrieve the results from there:

$found = array();
while($row = mysql_fetch_assoc($result)) {
$found[$row['col']] = $row['new_col'];
}

Then, add the topic title to each element of your $rows array:

for($i = 0; $i < sizeof($rows); $i++) {
$rows[$i]['new_col'] = $found[$rows[$i]['col']];
}

Now, it is your job to implement it based on what I have told you. Try to understand what is happening and not blindly copy code :)

The first method can work out too, but it requires a lot more work + research that you and I probably don't want to do.

kenshn111
12-25-2011, 01:13 PM
EDIT:
okay so i think i got the first part:

<?php
mysql_connect ($dbhost, $dbuser, $dbpasswd);
mysql_select_db("forum");
$rows = mysql_query ("SELECT * FROM phpbb_topics WHERE (forum_id='2') OR (forum_id='3') ORDER BY topic_time DESC LIMIT 6");
$rows_**** = mysql_fetch_assoc($rows);
while ($recent_row = mysql_fetch_assoc($recent))
$in = array();
foreach($rows_**** as $row) { $in[] = $row['topic_id']; }
$in = implode("','", array_unique($in));
echo $in;
?>

And here's the output that I get which I do not understand why it's like this:

1','3','0','F','2','k','A','

Apothem
12-25-2011, 07:02 PM
I am not sure about how much you've altered that code snippet. So assuming it is literally what you have...

A few things to note:
mysql_query (http://php.net/manual/en/function.mysql-query.php) returns a handler, not an array of each row.

mysql_fetch_assoc (http://www.php.net/manual/en/function.mysql-fetch-assoc.php) can return an associative array of a single (next) row.

Your while (http://us3.php.net/manual/en/control-structures.while.php) is just reconstructing the in array.

Because of the previous problems, your foreach (http://us3.php.net/manual/en/control-structures.foreach.php) loop is not getting each row, but each column of your first row from result set and appending that column into the in array.

array_unique (http://us3.php.net/manual/en/function.array-unique.php) removes any duplicate elements in array.

implode (http://us3.php.net/manual/en/function.implode.php) basically combines an array into one string (with some separator between each element). It essentially does something like this:

$array = array("foo", "bar", "baz", "quz");
implode($array); // returns: foobarbazquz
implode(" ", $array); // returns: foo bar baz quz
implode(" [=_=] ", $array); // returns: foo [=_=] bar [=_=] baz [=_=] quz



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum