使用mysql的书籍数据库的php搜索脚本

I have a database of about 3000 books and i want to be able to search my database for books either by titles, subjects or authors.. I've done my best trying to write a script using php and mysql but i still didn't get it right. can anyone assist please. Below is how far I've come with the script.. and an example of the table in my mysql database

我有一个约3000本书的数据库,我希望能够通过标题,主题或作者在我的数据库中搜索书籍。我已经尽力使用php和mysql编写脚本,但我仍然没有修正它。请有人帮忙。下面是我带来脚本的程度..以及我的mysql数据库中的表的示例

<?php

if (@$find == "")




// Otherwise we connect to our Database

mysql_connect("localhost", "root", "erhun") or die(mysql_error());


mysql_select_db("books") or die(mysql_error());


// We perform a bit of filtering
@$find = strtoupper($find);
@$find = strip_tags($find);
@$find = trim ($find);

@$search = strip_tags(stripslashes(mysql_real_escape_string(@$db, @$_POST["search"])));

//query the database
@$query = ("SELECT * FROM `project` WHERE (`author1` LIKE '%$search%' OR `main_title`               `LIKE '%$search%' OR `subj1` LIKE '%$search%')");  


//displaying the data
@$results=mysql_query(@$query) or die(mysql_error ());

if(mysql_num_rows(@$results) >= 1)

//here the table starts
echo "<table id='results'>";
while($row=mysql_fetch_array($results))


{
echo "<tr><td><img src='image1/".$row['image']."' height='100px' width='90px'></td><td   valign='top'>
<b><a href='details.php?id=".$row['book_id']."'>".$row['main_title']."<br/>
By: ".$row['author1']."</a></b><br/>
<b>Call no:</b> ".$row['call_no']."<br/>
<b>Type:</b> ".$row['item_type']."<br/>
<b>Year:</b> ".$row['publdate']."</td></tr>";
}
echo "</table>";

?>

my table contains these different fields

我的表包含这些不同的字段

Full texts
book_id image main_title author1 call_no item_type publdate publplace publshr item_home item_status subj1 subj2

全文book_id image main_title author1 call_no item_type publdate publplace publshr item_home item_status subj1 subj2

2 个解决方案

#1

Try like

@$query = "SELECT * FROM `project` WHERE (`author1` LIKE '%$search%' OR `main_title` LIKE '%$search%' OR `subj1` LIKE '%$search%')";

You have an extra quote ` after "main_title"

在“main_title”之后你有一个额外的引用`

#2

UPDATED :

Try changing this line :

尝试更改此行:

@$search = strip_tags(stripslashes(mysql_real_escape_string($db, @$_POST["search"])));

to :

@$search = strip_tags(stripslashes(mysql_real_escape_string( @$_POST["search"])));

The only thing I needed to change was dropping the $db variable, just passing in the search string, and it seems to work fine. It might be that $db was needed for some other reason so I recommend going back to the documentation you got that from and investigate further.

我唯一需要改变的是删除$ db变量,只是传入搜索字符串,它似乎工作正常。可能因为某些其他原因需要$ db所以我建议回到你从中获得的文档并进一步调查。

P.S. I'd still recommend using MATCH AGAINST. If your subject, title and author fields have an appropriate FULLTEXT index you can use MATCH AGAINST e.g. :

附:我仍然建议使用MATCH AGAINST。如果您的主题,标题和作者字段具有适当的FULLTEXT索引,您可以使用MATCH AGAINST,例如:

@$query = "SELECT * FROM `project` WHERE MATCH (subj1, main_title, author) AGAINST ('$search' IN NATURAL LANGUAGE MODE)";

This query would require that you have a fulltext index over all three columns:

此查询要求您在所有三列上都有全文索引:

ALTER TABLE project ADD FULLTEXT(subj1, main_title, author);

The list of columns in the index must match the list of columns you want to MATCH AGAINST in the query for the sake of optimisation I believe. You'd have to create other indexes if you wanted to be able to match against individual columns separately. That query also worked on my test page and it should better support normal search queries with a bit of experimentation.

为了优化,索引中的列列表必须与您希望在查询中匹配的列的列表相匹配,我相信。如果您希望能够单独匹配各个列,则必须创建其他索引。该查询也适用于我的测试页面,它应该通过一些实验更好地支持普通的搜索查询。

P.P.S. If you have a choice of DB I would recommend using something with more support for different types of text searching. The only relational databases I'm really familiar with are PostgreSQL and MySQL and PostgreSQL has a lot better support for text searching.

P.P.S.如果您可以选择DB,我建议您使用更多支持不同类型文本搜索的内容。我真正熟悉的唯一关系数据库是PostgreSQL和MySQL,PostgreSQL对文本搜索有更好的支持。

#1