Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 6 of 6
  1. #1
    New Coder
    Join Date
    May 2010
    Posts
    79
    Thanks
    15
    Thanked 2 Times in 2 Posts

    Minimize the number of query?

    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 Code:
    <?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!
    Last edited by kenshn111; 12-27-2011 at 09:03 AM.

  • #2
    Regular Coder Apothem's Avatar
    Join Date
    Mar 2008
    Posts
    380
    Thanks
    36
    Thanked 25 Times in 25 Posts
    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.

    Edit: 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"
    Last edited by Apothem; 12-25-2011 at 10:37 AM.

  • #3
    New Coder
    Join Date
    May 2010
    Posts
    79
    Thanks
    15
    Thanked 2 Times in 2 Posts
    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?

  • #4
    Regular Coder Apothem's Avatar
    Join Date
    Mar 2008
    Posts
    380
    Thanks
    36
    Thanked 25 Times in 25 Posts
    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:
    Code:
    $in = array();
    foreach($rows as $row) { $in[] = $row['col']; }
    $in = implode("','", array_unique($in));
    And from there:
    Code:
    mysql_query("SELECT col, new_col FROM table WHERE col IN('{$in}')
    And retrieve the results from there:
    Code:
    $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:
    Code:
    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.
    Last edited by Apothem; 12-25-2011 at 11:37 AM.

  • Users who have thanked Apothem for this post:

    kenshn111 (12-26-2011)

  • #5
    New Coder
    Join Date
    May 2010
    Posts
    79
    Thanks
    15
    Thanked 2 Times in 2 Posts
    EDIT:
    okay so i think i got the first part:
    PHP Code:
    <?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','
    Last edited by kenshn111; 12-25-2011 at 12:48 PM.

  • #6
    Regular Coder Apothem's Avatar
    Join Date
    Mar 2008
    Posts
    380
    Thanks
    36
    Thanked 25 Times in 25 Posts
    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 returns a handler, not an array of each row.

    mysql_fetch_assoc can return an associative array of a single (next) row.

    Your while is just reconstructing the in array.

    Because of the previous problems, your foreach 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 removes any duplicate elements in array.

    implode basically combines an array into one string (with some separator between each element). It essentially does something like this:
    Code:
    $array = array("foo", "bar", "baz", "quz");
    implode($array); // returns: foobarbazquz
    implode(" ", $array); // returns: foo bar baz quz
    implode(" [=_=] ", $array); // returns: foo [=_=] bar [=_=] baz [=_=] quz
    Last edited by Apothem; 12-25-2011 at 06:04 PM.

  • Users who have thanked Apothem for this post:

    kenshn111 (12-26-2011)


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •