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.

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.