我为我的网站使用
PHP,MysqL,Smarty,jQuery,AJAX等.目前,我从MysqL数据库中获取大量数据(匹配问题ID),对其进行处理,将此数据分配给Smarty模板并将其打印在网页上.由于要获取的数据量太大,而且正在进行进一步处理,所以在获取最终输出数据方面花费太多时间.反过来,花费太多时间向用户显示整个数据.
我在我心里有一种方法,但是无法实现它.我的方法是运行获取单个匹配的question_id并将其同时显示到浏览器的两个过程,并重复此循环,直到获取并显示所有匹配的问题ID.当单行的加载数据显示时,加载程序映像应显示在该显示记录下.当所有的数据都被打印出来时,装载程序的图像应该消失.
但是我面临的主要问题是如何将数据连续分配给Smarty模板,并显示模板,因为Smarty模板引擎首先加载所有内容,并且只有在将其打印出来的内容完全载入浏览器之后.
为了您的参考,我把所有现有的代码放在Controller,Model和View中：
Controller(match_question.PHP)的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") ?>@H_502_10@Model(QuestionMatch.PHP)的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; } } ?>@H_502_10@View(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>@H_502_10@感谢您花了一些宝贵的时间来了解我的问题.
我相信瓶颈是循环SQL查询.有一种标准的方式来对MysqL的搜索结果进行排名.您可以简单地实现全文搜索.
首先,您需要创建一个像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;@H_502_10@您必须将这些问题表(包括问题,主题,答案以及您想通过它们搜索的任何内容)中的所有有用数据插入到这里的result_title和result_content中(还需要更新时更新此表).还有一个返回跟踪到result_resource_id的对应表的原始记录.使用预定义的URI result_uri指向您网站中定义的结果URL,您可以更快地完成所有操作.您每次都不需要创建URL.
现在,您可以为搜索查询’问题’创建一个简单的SQL查询？在自然语言模式中：
sql：
SELECT `result_title`,`result_content`,`result_uri` FROM `search_results` WHERE MATCH(result_title,result_content) AGAINST('question?');@H_502_10@您还可以将相关性度量添加到查询字符串中.还有其他搜索模式,如布尔值.阅读the documents here,找到最好的解决方案.
Full-text indexing在这些用例中更快,也更准确.
