如何使用PHP和Smarty同时获取数据并在浏览器中显示?

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.

我的网站使用PHP、MySQL、Smarty、jQuery、AJAX等。目前，我正在从MySQL数据库中获取大量数据(匹配问题id)，对其进行处理，将这些数据分配给Smarty模板并将其打印到网页上。由于要获取的数据量太大，并且正在进行进一步的处理，所以获取最终的输出数据要花费太多的时间。反过来，向用户显示整个数据需要花费太多的时间。

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.

我脑子里有一种方法，但不能实现它。我的方法是运行获取单个匹配的question_id并同时显示给浏览器的两个过程，并重复这个循环，直到获取并显示所有匹配的问题id。当单行加载的数据被显示时，加载器映像应该在显示的记录下显示。当所有的数据被打印出来时，加载器映像应该消失。

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.

但是，我面临的主要问题是，我应该如何不断地将数据分配给Smarty模板，并显示模板，因为Smarty模板引擎首先加载所有内容，并在将内容全部打印到浏览器之后。

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:

Controller的PHP代码(match_question.php)如下:

<?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代码(QuestionMatch.php)如下:

<?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:

查看代码(match-question.tpl)如下:

<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.

感谢您花了宝贵的时间来理解我的问题。

5 个解决方案

#1

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

我认为瓶颈在于SQL查询的循环。有一种标准的方法可以对MySQL上的搜索结果进行排名。您可以简单地实现全文搜索。

First, you need to create a table like search_results:

首先，需要创建一个类似search_results的表:

SQL:

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 useful data from table of questions (including the questions, subjects, answers, and whatever you want to search through them) into result_title and result_content here, (also update this table when ever it needs to be update). There is also a back track to original record of 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 URL each time.

您必须将问题表中的所有有用数据(包括问题、主题、答案，以及您想要通过它们搜索的任何内容)插入到result_title和result_content中，(当这个表需要更新时也要更新它)。还有一个关于result_resource_id上对应表的原始记录的回溯。使用一个预定义的URI result_uri指向您网站中结果的已定义URL，可以使一切都更快。您不需要每次都创建URL。

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

现在，您可以为搜索查询的问题创建一个简单的SQL查询吗?“自然语言模式:

SQL:

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 in to your query string. There are other modes for 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.

全文索引在这些用例中更快，也更准确。

#2

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.

一般来说，模板引擎不会逐块加载内容——您需要手动将数据以块的形式发送给浏览器，并在每个位之间刷新数据。模板库通常在内存中编写整个文档，然后一次性将其转储到浏览器中。不过，还是有必要检查一下Smarty手册，以防万一。

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.

作为一种替代方法，您可以在不需要大量数据的情况下呈现页面，然后通过AJAX以分段方式加载它。虽然连续地进行10个AJAX连接会增加少量额外开销，但与当前的呈现时间相比，这似乎是最小的开销。尽管您的总呈现时间可能稍长一些，但是用户的感知呈现时间将会快得多，当然，他们也有好处，可以看到数据到达。

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.

在domready之后，我将启动第一个jQuery AJAX操作，当每个操作完成时，它将触发另一个请求。如果您的服务器可以以JSON而不是HTML来回答，它将允许服务器返回一个more_available Boolean标志，您可以使用它来确定是否需要进行另一个fetch。

#3

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.

您可以在“根据”一节中看到编写快速加载HTML页面和了解表的技巧。

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.

如果浏览器可以立即确定图像和表格的高度和/或宽度，那么它就可以显示一个web页面，而不必重新考虑内容。这不仅加快了页面的显示速度，而且在页面完成加载时，还可以防止页面布局中的恼人更改。因此，应该尽可能为图像指定高度和宽度。

And:

和:

Tables should use the CSS selector:property combination:

表应该使用CSS选择器:属性组合:

table-layout: fixed;

表布局:固定;

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

…并且应该使用COL和COLGROUP HTML标记指定列的宽度。

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.

您可能还想了解PHP的流输出方法。

See this question for details.

详情请参见这个问题。

#4

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

您当前的数据库查询和随后的smarty->分配将不允许延迟加载数据以加快进程。

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.

在这种情况下，您可以从查询中识别一组最大的行，这些行可以快速地显示给用户。一旦您确定了可以显示并保持快速响应时间的最大行集，您就可以修改查询和模板系统以反映多个查询设置。这就是分页。您将执行初始的行加载，而不是分页，然后通过jquery加载后一组行，直到成功加载所有“页面”数据。

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.
• 例如:假设您的测试产生100行作为最佳的最快响应。您将对预期的数据集执行计数(*)，该数据集将返回2021年。您可以将行数除以100的最优结果，这将产生20.21或21“页面”，或者在您的情况下，总共有21个查询。您的第一个初始查询，以及另外20个ajax查询。

• 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.
• 在html元素中输出“page_count”值，可能是一个数据页面html5值，并将其分配给具有唯一id的元素。
• On page load have your ajax initialize and grab the data-pages value.
• 在页面加载中，让ajax初始化并获取数据页面值。
• call your php document via ajax with the ?page=&subject_id=&topic_id=
• 使用?page=&subject_id=&topic_id=通过ajax调用php文档
• loop through the ajax call utilizing the data-pages amount starting at page=2 until you've queried the max pages.
• 使用从page=2开始的数据页数量对ajax调用进行循环，直到查询了最大页面。
• append the returned html where appropriate on each iteration.
• 在每次迭代中适当地附加返回的html。

Hope this idea helps you find a solution. Cheers!

希望这个想法能帮你找到解决方法。干杯!

#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.

在不涉及代码的具体细节的情况下，您所寻找的似乎是类似于Facebook所使用的名为BigPipe的系统，在这篇关于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.

基本上，他们试图做的是尽快向浏览器发送一个回复，其中包含页面的基本布局，以及占位符元素，这些元素稍后将包含需要更长的加载时间的内容——他们调用这些pagelets。在刷新初始响应之后，依次加载每个pagelet，包括从数据库或类似的数据库加载数据，并将其发送到客户端——仍然是相同的HTTP请求的一部分。Javascript用于将内容插入到正确的占位符中。

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.

在我工作的公司，我们做了一段时间的实验，取得了很好的结果。GitHub上有一个开源的第三方PHP/Javascript BigPipe实现，我们将它作为起点。虽然这远不是一件小事，更重要的是，要把工作做好，但我相信这是解决你所面临的问题的好办法。