How to fetch the data and display it in a browser simultaneously using PHP and Smarty?

Question

I'm using PHP, MySQL, Smarty, jQuery, AJAX, etc. for my website. Currently, I'm fetching a large amount of data (matching question IDs) from the MySQL database, do processing on it, assigning this data to the Smarty template and printing it on a webpage. As the amount of data to be fetched is too large and it's going under further processing, it's taking too much time in getting the final output data. In turn, it takes too much time to display whole data to the user.

I have one approach in my mind but not able to implement it. My approach is to run the two processes of fetching the single matching question_id and displaying it to the browser simultaneously and repeat this cycle until all the matching question ids are fetched and displayed. As the loaded data of single row is getting displayed a loader image should get display under that displayed record. When all the data gets printed the loader image should vanish.

But the major issue I'm facing is how I should continuously assign the data to the Smarty template and display the template as the Smarty Template Engine first loads all the content and only after completely having the content it prints it to the browser.

For your reference I'm putting below all my existing code from Controller, Model and View:

The PHP code of Controller (match_question.php) is as follows:

<?php 
  require_once("../../includes/application-header.php");

  $objQuestionMatch  = new QuestionMatch();

  $request = empty( $_GET ) ? $_POST : $_GET ;


  if($request['subject_id']!="") 
    $subject_id = $request['subject_id'];
  if($request['topic_id']!="") 
    $topic_id = $request['topic_id'];

  if($subject_id !='' && $topic_id !='')
    $all_match_questions = $objQuestionMatch->GetSimilarQuestionsBySubjectIdTopicId($subject_id, $topic_id);

  $smarty->assign('all_match_questions', $all_match_questions);
  $smarty->display("match-question.tpl")
?>

The PHP code of Model(QuestionMatch.php) is as follows:

<?php
  class QuestionMatch {

    var $mError = "";
    var $mCheck;
    var $mDb;
    var $mValidator;
    var $mTopicId;
    var $mTableName;

    function __construct() {
      global $gDb;
      global $gFormValidation;

      $this->mDb        = $gDb; 
      $this->mValidator = $gFormValidation;
      $this->mTableName = TBL_QUESTIONS;
    }
/**
     * This function is used to get all the questions from the given subject id and topic id
         */
    function GetSimilarQuestionsBySubjectIdTopicId($subject_id, $topic_id) {

            /*SQL query to find out questions from given subject_id and topic_id*/
            $sql  = " SELECT * FROM ".TBL_QUESTIONS." WHERE question_subject_id=".$subject_id;
            $sql .= " AND question_topic_id=".$topic_id;

            $this->mDb->Query($sql);
            $questions_data = $this->mDb->FetchArray(); 
            /*Same array $questions_data is assigned to new array $questions to avoid the reference mismatching*/
            $questions      = $questions_data;

      /*Array of words to be excluded from comparison process
       *For now it's a static array but when UI design will be there the array would be dynamic
            */
            $exclude_words = array('which','who','what','how','when','whom','wherever','the','is','a','an','and','of','from');  

      /*This loop removes all the words of $exclude_words array from all questions and converts all 
       *converts all questions' text into lower case
      */
      foreach($questions as $index=>$arr) {
        $questions_array = explode(' ',strtolower($arr['question_text']));
        $clean_questions = array_diff($questions_array, $exclude_words);
        $questions[$index]['question_text'] = implode(' ',$clean_questions);
      }      

      /*Now the actual comparison of each question with every other question stats here*/
            foreach ($questions as $index=>$outer_data) {

        /*Logic to find out the no. of count question appeared into tests*/
        $sql  = " SELECT count(*) as question_appeared_count FROM ".TBL_TESTS_QUESTIONS." WHERE test_que_id=";
        $sql .= $outer_data['question_id'];

        $this->mDb->Query($sql);
        $qcount = $this->mDb->FetchArray(MYSQL_FETCH_SINGLE); 

        $question_appeared_count = $qcount['question_appeared_count'];
        $questions_data[$index]['question_appeared_count'] = $question_appeared_count;
        /*Crerated a new key in an array to hold similar question's ids*/
        $questions_data[$index]['similar_questions_ids_and_percentage'] = Array(); 

        $outer_question = $outer_data['question_text'];

        $qpcnt = 0;     
        //foreach ($questions as $inner_data) {
        /*This foreach loop is for getting every question to compare with outer foreach loop's 
        question*/
        foreach ($questions as $secondIndex=>$inner_data) { 
            /*This condition is to avoid comparing the same questions again*/
          if ($secondIndex <= $index) {
            /*This is to avoid comparing the question with itself*/
              if ($outer_data['question_id'] != $inner_data['question_id']) {

              $inner_question = $inner_data['question_text'];  

                /*This is to calculate percentage of match between each question with every other question*/
                similar_text($outer_question, $inner_question, $percent);
                $percentage = number_format((float)$percent, 2, '.', '');

                /*If $percentage is >= $percent_match only then push the respective question_id into an array*/
                if($percentage >= 85) {
                $questions_data[$index]['similar_questions_ids_and_percentage'][$qpcnt]['question_id']       = $inner_data['question_id'];
                $questions_data[$index]['similar_questions_ids_and_percentage'][$qpcnt]['percentage']        = $percentage;
                /*$questions_data[$secondIndex]['similar_questions_ids_and_percentage'][$qpcnt]['question_id'] = $outer_data['question_id'];
                $questions_data[$secondIndex]['similar_questions_ids_and_percentage'][$qpcnt]['percentage']    = $percentage;*/

                /*Logic to find out the no. of count question appeared into tests*/
                $sql  = " SELECT count(*) as question_appeared_count FROM ".TBL_TESTS_QUESTIONS." WHERE test_que_id=";
                $sql .= $inner_data['question_id'];

                $this->mDb->Query($sql);
                $qcount = $this->mDb->FetchArray(MYSQL_FETCH_SINGLE); 

                $question_appeared_count = $qcount['question_appeared_count'];
                $questions_data[$index]['similar_questions_ids_and_percentage'][$qpcnt]['question_appeared_count'] = $question_appeared_count;
                $qpcnt++;
            }
          }
        }   
      }
    }    //}    
    /*Logic to create the return_url when user clicks on any of the displayed matching question_ids*/
    foreach ($questions_data as $index=>$outer_data) {
      if(!empty($outer_data['similar_questions_ids_and_percentage'])) { 
        $return_url  = ADMIN_SITE_URL.'modules/questions/match_question.php?';
        $return_url .= 'op=get_question_detail&question_ids='.$outer_data['question_id'];

        foreach($outer_data['similar_questions_ids_and_percentage'] as $secondIndex=>$inner_data) {
          $return_url = $return_url.','.$inner_data['question_id'];
        }      
        $questions_data[$index]['return_url'] = $return_url.'#searchPopContent';
      }
    }     
          /*This will return the complete array with matching question ids*/
      return $questions_data;
      }
}
?>

The code of View(match-question.tpl) is as follows:

<table width="100%" class="base-table tbl-practice" cellspacing="0" cellpadding="0" border="0">
  <tr class="evenRow">
    <th width="33%" style="text-align:center;" class="question-id">Que ID</th>
    <th width="33%" style="text-align:center;" class="question-id">Matching Que IDs</th>
    <th width="33%" style="text-align:center;" class="question-id">Percentage(%)</th>
  </tr>
{if $all_match_questions}
  {foreach from=$all_match_questions item=qstn key=key}   
    {if $qstn.similar_questions_ids_and_percentage}
      {assign var=counter value=1}
  <tr class="oddRow">
    <td class="question-id" align="center" valign="top">
      <a href="{$qstn.return_url}" title="View question" class="inline_view_question_detail">QUE{$qstn.question_id}</a>{if $qstn.question_appeared_count gt 0}-Appeared({$qstn.question_appeared_count}){/if}
    </td>
      {foreach from=$qstn.similar_questions_ids_and_percentage item=question key=q_no}
        {if $counter gt 1}
    <tr class="oddRow"><td class="question-id" align="center" valign="top"></td>
        {/if}
    <td class="question" align="center" valign="top">

        {if $question.question_id!=''}
      <a href="{$qstn.return_url}" title="View question" class="inline_view_question_detail">QUE{$question.question_id}</a>{if $question.question_appeared_count gt 0}-Appeared({$question.question_appeared_count}){/if}
        {if $question.question_appeared_count eq 0}
      <a id ="{$question.question_id}" href="#" class="c-icn c-remove delete_question"  title="Delete question"> Delete</a>{/if}
        {/if}

    </td>

    <td class="question" align="center" valign="top">
        {if $question.percentage!=''}{$question.percentage}{/if}
        {assign var=counter value=$counter+1}
    </td>
  </tr>
      {/foreach}               
    {/if}
  {/foreach}
{else}
  <tr>
    <td colspan="2" align="center"><b>No Questions Available</b></td>
  </tr>
{/if}
</table>

Thanks for the spending some of your valuable time in understanding my issue.

Inextinguishable

If the problem is that the script times out you could try to use set_time_limit(): php.net/manual/en/function.set-time-limit.php – Hygeia

@KristerAndersson:The script is not timing out. The user has to wait for long time until all the data gets loaded. And I want to display the data step by step and not to make the user wait. My issue is how to print the data step by step so that user should not wait until whole data(all records) gets loaded. – Inextinguishable

I suggest you look into using COL and COLGROUP and a fixed table layout so that the browser knows how to layout the table even before it has been completely loaded. You can test if this is your culprit by changing your tables to a DIV-based setup and testing how it loads. Please note that buffer flushing (as @halfer suggested) may be crucial. – Induline

Answer 1

I believe the bottleneck is on the looping over SQL queries. There is a standard way to rank search results on MySQL. You can simply implement full-text search.

First, you need to create a table like search_results:

SQL:

CREATE TABLE `search_results` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `result_title` varchar(128) CHARACTER SET utf8 NOT NULL,
  `result_content` text CHARACTER SET utf8 NOT NULL,
  `result_short_description` text CHARACTER SET utf8,
  `result_uri` varchar(255) CHARACTER SET utf8 NOT NULL DEFAULT '',
  `result_resource_id` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`),
  FULLTEXT KEY `result_title` (`result_title`,`result_content`)
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

You have to insert all relevant data from the table of questions (including questions, subjects, answers, and whatever you want to search through them) into result_title and result_content here, (also update this table whenever it needs to be updated). There is also backtrack pointer to the original record of the corresponding table on result_resource_id. With a pre-defined URI result_uri pointing to the defined URL of the result in your website, you make everything faster. You don't need to create a URL each time.

Now, you can create a simple SQL query for a search query 'question?' in NATURAL LANGUAGE MODE:

SQL:

SELECT `result_title`, `result_content`, `result_uri`
FROM `search_results` WHERE MATCH(result_title, result_content) AGAINST('question?');

You can also add the relevance measurement into your query string. There are other modes for a search like boolean. Read the documents here and find the best solution.

Full-text indexing is faster and also more accurate in these use-cases.

Groceries

Answer 2

Assuming you want your content to load in the browser while it is still being streamed from the server to the client, if you are using tables - as you do - you may run into the problem of the browser (due to layout issues) not being able to render the table until all data is loaded.

You can see these tips for authoring fast-loading HTML pages and learn about tables in the according section.

Some crucial points:

If the browser can immediately determine the height and/or width of your images and tables, it will be able to display a web page without having to reflow the content. This not only speeds the display of the page but prevents annoying changes in a page's layout when the page completes loading. For this reason, height and width should be specified for images, whenever possible.

And:

Tables should use the CSS selector:property combination:

table-layout: fixed;

... and should specify widths of columns using the COL and COLGROUP HTML tags.

As well as:

Tables are still considered valid markup, but should be used for displaying tabular data. To help the browser render your page quicker, you should avoid nesting your tables.

You might also want to look into methods of streaming output from PHP.

See this question for details.

Induline

Answer 3

In general, templating engines do not load content piecemeal - you'd need to send data to the browser in chunks manually, and flush between each bit. Template libraries usually compose the whole document in memory, and then dump it to the browser in one go. It's worth checking the Smarty manual though, just in case.

As an alternative, you could render the page without the large amount of data, and then load it in sections via AJAX. Whilst making, say, 10 AJAX connections serially adds a small extra overhead, it sounds like that will be minimal in comparison to your current rendering time. Even though your total rendering time may be slightly longer, the perceived rendering time for the user will be much faster, and of course they have the benefit that they can see data arriving.

I would kick off the first AJAX operation in jQuery upon domready, and when each completes, it can fire off another request. If your server can answer in JSON rather than HTML, it will allow the server to return a more_available Boolean flag, which you can use to determine if you need to do another fetch.

Exegetics

Answer 4

Your current database query and subsequent smarty->assign will not allow for lazy loading of data to speed up the process.

In this situation you can identify a maximum set of rows from your query that can be rapidly displayed to the user. Once you identify a maximum set of rows you can display and still maintain a fast response time you can modify your query and template system to reflect a multiple query setup. This is essentially pagination. Instead of paginating you will perform an initial load of rows, and then via jquery load the latter set of rows until all "pages" of data have been successfully loaded.

For match_question.php

• First query your dataset to see how many total rows of data you have.
• Divide those rows by the total amount of rows you can display while maintaining a fast application. This will give you your total number of "pages" or "queries" you will run.
• For example: Say your tests yield 100 rows as the optimal fastest response. You would perform a COUNT(*) on the expected data set which returns 2021. You would divide that row count by your optimal 100 results, which would yield 20.21 or 21 "pages", or in your case 21 total queries. Your first initial query, and 20 additional ajax queries.

• This will cause a larger amount of queries to your database, but it will cause the page load time to be more efficient to the end user. So you should measure the load on the machine vs the ease of use for the end user.

  $limit = 100;
  $page = 1;
  ...
  
  if($request['page'] != '')
     $page = $request['page'];
  
  ...
  
  if($subject_id !='' && $topic_id !=''){
     $count_matched_questions = $objQuestionMatch->GetSimilarQuestionsBySubjectIdTopicId($subject_id, $topic_id, true);
  
     $page_count = ceil($count/$limit) //round up if decimal for last page;
  
     $paged_match_questions = $objQuestionMatch->GetSimilarQuestionsBySubjectIdTopicId($subject_id, $topic_id, false, $limit, $page)
  }
  
  $smarty->assign( 'all_match_questions', $paged_match_questions
                  ,'page_count', $page_count);
  //cache each result page separately to support multiple subject/topic/page combinations to properly utilize the cache mechanism
  $smarty->display("match-question-".$subject_id."-".$topic_id."-".$page.".tpl")
  

For QuestionMatch.php

• Adjust your query function (an example):

  function GetSimilarQuestionsBySubjectIdTopicId($subject_id, $topic_id, $count = false, $limit = 0, $page = 0 ) {
      if($count)
      {
         $sql  = " SELECT COUNT(*) FROM ".TBL_QUESTIONS." WHERE question_subject_id=".$subject_id;
      }
      else
      {
         $sql  = " SELECT * FROM ".TBL_QUESTIONS." WHERE question_subject_id=".$subject_id;
      }
      $sql .= " AND question_topic_id=".$topic_id;
  
      if($page > 0 && $limit > 0)
      {
         $sql .= " LIMIT = " . ($limit*$page)-$limit . ", " . ($limit*$page);
      } 
  
  } 
  

For View(match-question.tpl)

• output the 'page_count' value in an html element, maybe a data-pages html5 value and assign it to an element with a unique id.
• On page load have your ajax initialize and grab the data-pages value.
• call your php document via ajax with the ?page=&subject_id=&topic_id=
• loop through the ajax call utilizing the data-pages amount starting at page=2 until you've queried the max pages.
• append the returned html where appropriate on each iteration.

Hope this idea helps you find a solution. Cheers!

Ovenware

I'm not familiar with Smarty, but good and substantial answer, Pynt. – Exegetics

Answer 5

Without getting into the specific details of your code, it sounds like what you are looking for is something similar to a system used by Facebook called BigPipe, described in reasonable detail in this note on Facebook Engineering.

Basically, what they attempt to do is send a reply to the browser as soon as possible, containing the basic layout of the page, and placeholder elements that will later contain the content that takes longer to load - they call these pagelets. After the initial response is flushed, each pagelet is loaded in turn, including loading data from databases or similar, and also sent to the client - still part of the same HTTP request. Javascript is used to insert the content into the right placeholder.

At the company I work at, we experimented with this for a while and got great results. There is an open source third party PHP/Javascript BigPipe implementation on GitHub that we used as a starting point. While far from trivial to set up and, more importantly, get working really well, I believe it's a great solution for exactly the kind of problem you face.

Vehemence