php - 如何增加 php/mysql 中的搜索计数？

Question

我有一个包含多个字段的表(id、firstname、surname、username、search_count)

我构建了一个小型搜索引擎来搜索我的表以查找 firstname 或 surname 中存在的任何匹配项，我得到的结果没有问题。

现在，我要做的是每次匹配时将 search_count 字段递增 1!

例如，假设我们有下表users:

id | firstname | surname  | username | search_count 
1  | John      | Mike     | un1      | 0
2  | John      | Jeff     | un2      | 0
3  | Dale      | John     | un3      | 0
4  | Mike      | Gorge    | un4      | 0

假设我们正在搜索 Jeff 作为关键字

因此，查询将返回 1 条记录

我想做的是将匹配记录的search_count增加1

所以结果会是这样的:

id | firstname | surname  | username | search_count 
2  | John      | Jeff     | un2      | `1`

如果我们进行新的搜索(例如 John)，结果应该是这样的:

id | firstname | surname  | username | search_count 
1  | John      | Mike     | un1      | 1
2  | John      | Jeff     | un2      | 2
3  | Dale      | John     | un3      | 1

我已经尝试了几种方法但没有运气..所以我很感激任何提示和帮助

这是我的代码...

<?php
// open the HTML page
include 'html_open.php';
// require the db connection
require '/inc/db.inc.php';
// require the error messages
require '/inc/echo.inc.php';



    if (isset($_GET['keyword'])) {
        $keyword = $_GET['keyword'];
        if (!empty($keyword)) {
            // build our search query
            $search_query = "SELECT * FROM `users` WHERE `firstname` = '".mysql_real_escape_string($keyword)."' OR `surname` = '".mysql_real_escape_string($keyword)."' ORDER BY `search_count` DESC";
            // run the search query
            $search_query_run = mysql_query($search_query);
            // search results
            $search_results_num = mysql_num_rows($search_query_run);
            // check query return results
            if ($search_results_num>0) {        
                echo 'Search engine returns <strong>[ '.$search_results_num.' ]</strong> result(s) for <strong>[ '.$keyword.' ]</strong>:<br>';
                // retrieving the information found
                echo '<ol>';
                while ($search_result_information = mysql_fetch_assoc($search_query_run)) {
                    //$current_search_count = ;
                    echo '<li>'.$search_result_information['username'].'.  This user has been searched: '.$search_result_information['search_count'].' times before.</li>';
                }
                echo '</ol><hr>';
                include 'search_form.php';
            } else {
                echo '<hr>Search engine returns no result for <strong>[ '.$keyword.' ]</strong>, please try another keyword.<hr>'; // hint: no result found
                include 'search_form.php';
            }               
        } else {
            echo $err20_002; // hint: must insert input
            include 'search_form.php';
        }
    } else {
            echo $err20_001; // hint: form has not been submitted
            include 'search_form.php';
    }
// close the HTML page  
include 'html_close.php';   
?>

附言我是 PHP/MySQL 的新手，这是我的第一个代码:)

Answer 1

...
while ($search_result_information = mysql_fetch_assoc($search_query_run)) {
    # add this following line
    mysql_query('UPDATE `users` SET search_count=search_count+1 WHERE id='.$search_result_information['id']);
    # Edit. Change result to show new number
    $search_result_information['search_count']++; # this adds 1 to the value (does not affect stored data in database)
    echo '<li>'.$search_result_information['username'].'.  This user has been searched: '.$search_result_information['search_count'].' times before.</li>';
}
...