我有一个由几个字段组成的表格（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的新手，这是我的第一个代码:)
...
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>';
}
...
在您的情况下，您需要获取找到的值并执行更新语句，将 +1 添加到search_count列
$search_query = "SELECT id, firstname, surname, username, search_count FROM `users` WHERE `firstname` = '".mysql_real_escape_string($keyword)."' OR `surname` = '".mysql_real_escape_string($keyword)."' ORDER BY `search_count` DESC";
$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 = ;
$update_search = "UPDATE users SET search_count = search_count + 1 WHERE id = {$search_result_information['id']}"; // so every `id` will increment its search_count with 1. You will need to select the rows once again, to take this count, or to manually increment in PHP
mysql_query($update_search);
echo '<li>'.$search_result_information['username'].'. This user has been searched: '.$search_result_information['search_count'].' times before.</li>';
PS：强烈建议不要使用 mysql_* lib。从官方文档的红框中可以看出 http://www.php.net/manual/en/function.mysql-query.php 您应该选择当前实际支持的 API 之一