php+mysql实现全文索引程序出错

Question

php:<p>
Search the employee database:<br />
<form action="<?php htmlspecialchars($_SERVER['PHP_SELF']);?>" method="post">
Keyword:<br />
<input type="text" name="lastname" size="20" maxlength="40" value=""/><br />

<input type="submit" name="Submit" value="Search!"/>
</form>
</p>
<?php
if (isset($_POST['Submit'])) {
if (isset($_POST['lastname'])) {
$mysqldb = new mysqli('localhost', 'QQAA2233', '3322QQAA', 'corporate');

$keyword = mysqli_real_escape_string($mysqldb, $_POST['lastname']);

$result = mysqli_query($mysqldb, "SELECT first_name, email FROM employees WHERE MATCH(last_name) AGAINST('$keyword')");


if (mysqli_num_rows($result) > 0) {
while (($row = mysqli_fetch_object($result)) == true)
echo "$row->first_name ($row->email)<br />";
}
else
echo 'No results found.';
}
}
?>
数据库corporate：表employees:
| id | employee_id | first_name | last_name | email               | phone      |salary |
+----+-------------+------------+-----------+---------------------+------------+--------+
|  1 | 1           | Bob        | Connors   | [email protected]     | 2125559945 |100.00 |
|  2 | 2           | Jason      | Gilmore   | [email protected]   | 2125551212 |200.00 |
|  3 | 3           | Matt       | Wade      | [email protected]    | 2125559999 |300.00 |
|  4 |             | Mike       | Ailmore   | [email protected] |            |0.00   |
其中last_name中有两个有相同ilmore，Gilmore与Ailmore。但我在PHP中输入ilmore为什么不能显示Jason与Mike的列？提示
Warning: mysqli_num_rows() expects parameter 1 to be mysqli_result, boolean given in D:\xampp\htdocs\Project\newfile3.php on line 20
No results found.

Answer 1

查询失败！你在 last_name 上建立了全文索引吗？$result = mysqli_query($mysqldb, "SELECT first_name, email FROM employees WHERE MATCH(last_name) AGAINST('$keyword')") or die(mysql_error());
看看都是什么错

Answer 2

"SELECT first_name, email, last_name FROM employees WHERE MATCH(last_name) AGAINST('$keyword')"

Answer 3

我已经在last_name上建立了全文索引了，不过始终还是No results found.我在mysql中select match(last_name) against('ilmore') from employees;但显示
+------------------------------------+
| match(last_name) against('ilmore') |
+------------------------------------+
|                                  0 |
|                                  0 |
|                                  0 |
|                                  0 |
+------------------------------------+
全部为0，是不是我的用法错误呢

Answer 4

+-------------+----------------------+------+-----+---------+----------------+
| Field       | Type                 | Null | Key | Default | Extra          |
+-------------+----------------------+------+-----+---------+----------------+
| id          | smallint(5) unsigned | NO   | PRI | NULL    | auto_increment |
| employee_id | char(8)              | NO   |     | NULL    |                |
| first_name  | varchar(25)          | NO   |     | NULL    |                |
| last_name   | varchar(35)          | NO   | MUL | NULL    |                |
| email       | varchar(55)          | NO   |     | NULL    |                |
| phone       | char(10)             | NO   |     | NULL    |                |
| salary      | decimal(8,2)         | NO   |     | NULL    |                |
+-------------+----------------------+------+-----+---------+----------------+
另外我添加了索引fulltext(last_name)

Answer 5

表是不是MyISAM?ALTER TABLE employees ADD FULLTEXT last_name (last_name);

Answer 6

mysql> show table status like '%employees%'\G;
*************************** 1. row ***************************
           Name: employees
         Engine: MyISAM
        Version: 10
     Row_format: Dynamic
           Rows: 4
 Avg_row_length: 52
    Data_length: 208
Max_data_length: 281474976710655
   Index_length: 3072
      Data_free: 0
 Auto_increment: 5
    Create_time: 2012-10-26 08:48:19
    Update_time: 2012-10-26 08:48:19
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.00 sec)

Answer 7

不是ALTER TABLE employees ADD FULLTEXT(last_name);这样子的吗？

Answer 8

一般我都用  ALTER TABLE employees ADD FULLTEXT last_name (last_name); 这样的形式添加explain 一下你的查询语句，看看有没有 type 为 FULLTEXT 的 ？explain SELECT first_name, email FROM employees WHERE MATCH(last_name) AGAINST('+wade')