|
PHP - Fulltext Indexing
In phpmyadmin, once I do a fulltext on a field, is that the only time I have to do it or does it have to be indexed anytime new data is added or altered?
Similar TutorialsI have a MySql database (Server Version 5.6.12) with a fulltext index on the SectionText field in the FD table with this structu
CREATE TABLE `FD` ( `FTS_DOC_ID` bigint(20) unsigned NOT NULL, `ReadingNo` int(11) DEFAULT NULL, `SequenceNo` int(11) DEFAULT NULL, `SectionTypeID` int(11) DEFAULT NULL, `SectionText` longtext, `FullDocNo` varchar(11) CHARACTER SET utf8 NOT NULL, `DocType` varchar(11) CHARACTER SET utf8 DEFAULT NULL, UNIQUE KEY `FTS_DOC_ID_INDEX` (`FTS_DOC_ID`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1;I am able to do a very accurate fulltext proximity search (92 documents found) in PHPMyAdmin using this syntax:
SELECT SectionText
FROM fd
WHERE DocType = "text" AND
MATCH (SectionText) AGAINST ('"liver hepatic" @3' IN BOOLEAN MODE)
ORDER BY FTS_DOC_ID
But when I try to use this query on a webpage I get this error message:
Parse error: syntax error, unexpected 'liver' (T_STRING) in C:\wamp\www\ecr\proximity.php on line 77
When I remove the quotes like this:
MATCH (SectionText) AGAINST ('liver hepatic @3' IN BOOLEAN MODE)
There is no error, but there are no documents found.
Realistically, to do searches on the web, I need to use search variables provided via an input form (and preferably a parameterized query) such as:
MATCH (SectionText) AGAINST (':search1 :search2 @:proximity' IN BOOLEAN MODE)
But this also returns no documents found.
I need help getting this implemented on a web page. If this needs to be posted in another forum (PHP), please advise. But since the proximity search feature seems to be relatively new and somewhat obscure (couldn’t find anything about implementing it on a webpage via Google), I thought it best to start here. Any help in solving this is greatly appreciated.
Hi guys, i'm trying to search from a form and use php to display the results of the FullText Search from a MySQL database, but I don't get any results from the query. This is the query: $query="SELECT * FROM tutorials WHERE MATCH(title, tags) AGAINST ('$searchform')"; I think the query is right but it doesn't give me any results.... Does anyone know what could be wrong? I think it might be something wrong with my table (database). my table should have 5 columns: id, title, category, content, tags The form should search throught the title and tags columns to see if it finds anything matching the $searchform , but I don't think it is doing it. Can anyone help please? Hello everybody! I have a fulltext search script that orders the results by relevance to the keywords. But I want that the order process takes into consideration column `views` as well. So basically what I want is that relevance is 80% significant but views respectively - 20%. I tried to figure out some ways, but as I am amateur there were only errors that came out. Here ismMy code which is working and ordering the results ONLY 100% by revelance: Code: [Select] $mq = mysql_query("SELECT *, (MATCH(articles) AGAINST ('briniskigi housez lacitis' IN BOOLEAN MODE)) AS relevance FROM results WHERE ( MATCH(articles) AGAINST ('briniskigi housez lacitis' IN BOOLEAN MODE) ) ORDER BY relevance DESC"); while($row = mysql_fetch_array($mq)) { echo $row['id'] . '<br />'; } Thank you in advance. I hope that you understood me Hello everyone. First post here. very new to php and html. Im working on my uncles website which is a fabric store. I created a mysql database and a search to use. Im trying to make this a fulltext search because right now you have to match the pattern name exactly to get any results! And if somebody searches for the pattern name and the color they will get no results! In fact, if there is more than one search term, there is no results. -This is the first part where it counts so the pagination can be built- $sql = "SELECT COUNT(*) FROM rapatterns WHERE Pattern= '$keyword' OR Color= '$keyword' OR Fabric_Use= '$keyword'"; $result = mysql_query($sql, $conn) or trigger_error("SQL", E_USER_ERROR); -here is the query to get results- $sql = "SELECT * FROM rapatterns WHERE Pattern= '$keyword' OR Color= '$keyword' OR Fabric_Use= '$keyword' LIMIT $offset, $rowsperpage"; $result = mysql_query($sql, $conn) or trigger_error("SQL", E_USER_ERROR); Ive looked into full text searching, and ive created a fulltext index on the three columns being searched here. The problem im having is when I try to change the queries to fulltext searches I get syntax errors. -this is what i was trying to do- -this is the first one that counts for pagination- $sql = "SELECT COUNT(*) FROM rapatterns WHERE MATCH(Pattern,Color,Fabric_Use) AGAINST ('$keyword') LIMIT $offset, $rowsperpage"; -here is the second one to get results- $sql = "SELECT * FROM rapatterns WHERE MATCH(Pattern,Color,Fabric_Use) AGAINST ('$keyword') LIMIT $offset, $rowsperpage"; $result = mysql_query($sql, $conn) or trigger_error("SQL", E_USER_ERROR); Im wondering if I can get a little help with the syntax or if what im trying to do wont work at all. Go easy on me. I am extremely new to any of this. fulltext in boolean mode is not matching ignored words in mysql.
ENGINE is set for myisam. mysql collation is utf8_general_ci.
i have altered table using...
ALTER TABLE users ADD FULLTEXT(userName);when i view mysql indexes, userName is username instead. I am not sure if this matter as there is not an error message when searching.
SELECT * FROM users WHERE MATCH (userName) AGAINST ('$keywords' IN BOOLEAN MODE)
I have restarted mysql and still does not match some 5 character words. in the mysql config, word length is set to 4. have i missed something?
The issue I am having is that everytime I click the next button, it takes me to the index page. My question is, how do I set up a proper search results link in "href="?page=1"
index.php
<form action="search" method="GET"> <input type="search" name="search" placeholder="Find products, services ..."> <input type="submit" name="submit" value=""> </form>search.php
if(isset($_GET['submit']) && !empty($_GET['search'])) {
$value = escape(trim(Input::get('search')));
try {
// Find out how many items are in the table
$q = DB::getInstance()->query("SELECT * FROM records WHERE MATCH(title) AGAINST('$value' IN BOOLEAN MODE)");
$total = $q->count();
// How many items to list per page
$limit = 10;
// How many pages will there be
$pages = ceil($total / $limit);
// What page are we currently on?
$page = min($pages, filter_input(INPUT_GET, 'page', FILTER_VALIDATE_INT, array(
'options' => array(
'default' => 1,
'min_range' => 1,
),
)));
// Calculate the offset for the query
$offset = ($page - 1) * $limit;
// Some information to display to the user
$start = $offset + 1;
$end = min(($offset + $limit), $total);
// Prepare the paged query
$stmt = DB::getInstance()->query("SELECT users.*, records.* FROM records
LEFT JOIN users ON records.user_id = users.user_id
WHERE MATCH(title) AGAINST('$value' IN BOOLEAN MODE) ORDER BY relevance DESC LIMIT {$limit} OFFSET ".$offset);
if($stmt->count()) {
// Display the results
foreach($stmt->results() as $row) {
$date = escape($row->posted);
$record_date = escape(Input::facebook_date_format($date));
$record_id = escape($row->record_id);
$title = trim($row->title);
$slug_title = $row->slug_title;
$view_count = escape(intval(number_format($row->views + 1)));
$username = escape($row->username);
require 'snippets/record_widget.php';
}
} else {
?><div class="message"><?php echo 'No records found.';?></div><?php
}
<?php
// The "back" link
$prevlink = ($page > 1) ? '<a href="?page=1" title="First page"><img src="images/left-page-arrow-end.png"/></a> <a href="?page=' . ($page - 1) . '" title="Previous page"><img src="images/left-page-arrow-start.png"/></a>' : '<span class="disabled"><img src="images/left-page-arrow-end-disabled.png"/></span> <span class="disabled"><img src="images/left-page-arrow-start-disabled.png"/></span>';
// The "forward" link
$nextlink = ($page < $pages) ? '<a href="?page=' . ($page + 1) . '" title="Next page"><img src="images/right-page-arrow-start.png"/></a> <a href="?page=' . $pages . '" title="Last page"><img src="images/right-page-arrow-end.png"/></a>' : '<span class="disabled"><img src="images/right-page-arrow-start-disabled.png"/></span> <span class="disabled"><img src="images/right-page-arrow-end-disabled.png"/></a></span>';
// Display the paging information
?>
<div class="pages">
<span class="pages-arrows"><?php echo $prevlink; ?></span> <span class="pages-numbers"><?php echo $page; ?> of <?php echo $pages; ?></span> <span class="pages-arrows"><?php echo $nextlink; ?></span>
</div>
} catch (Exception $e) {
echo '<p>', $e->getMessage(), '</p>';
}
} else {
Redirect::to('index');
}
Edited by man5, 20 August 2014 - 10:27 PM. Hello everyone.
I have a large table consisting of 135497 rows
I have found that by indexing the table, it will take less time in quering the results.
In some site it shows that Using "Explain" before the queries will show that how much rows it has to travel to get the desired results.
SO I have doen and it shows like below...
mysql> EXPLAIN select * FROM ip2country WHERE 3084727327 BETWEEN begin_long_ip AND end_long_ip; +----+-------------+------------+------+---------------------------------------------------------------------------------+------+---------+------+--------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+------------+------+---------------------------------------------------------------------------------+------+---------+------+--------+-------------+ | 1 | SIMPLE | ip2country | ALL | ip_adress,begin_long_ip,end_long_ip,begin_long_ip_3,count_index,begin_long_ip_2 | NULL | NULL | NULL | 135497 | Using where | +----+-------------+------------+------+---------------------------------------------------------------------------------+------+---------+------+--------+-------------+The above results shows 135497. means it has to travel all the rows to get the result. So i have done index on the column ("begin_long_ip and end_long_ip) mysql> CREATE INDEX count_index ON ip2country(begin_long_ip,end_long_ip); Query OK, 135497 rows affected, 1 warning (0.87 sec) Records: 135497 Duplicates: 0 Warnings: 1But it still shows the same result. mysql> EXPLAIN select * FROM ip2country WHERE 3084727327 BETWEEN begin_long_ip AND end_long_ip; +----+-------------+------------+------+---------------------------------------------------------------------------------+------+---------+------+--------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+------------+------+---------------------------------------------------------------------------------+------+---------+------+--------+-------------+ | 1 | SIMPLE | ip2country | ALL | ip_adress,begin_long_ip,end_long_ip,begin_long_ip_3,count_index,begin_long_ip_2 | NULL | NULL | NULL | 135497 | Using where | +----+-------------+------------+------+---------------------------------------------------------------------------------+------+---------+------+--------+-------------+Ahy help will be greatly apprecaited... This topic has been moved to Miscellaneous. http://www.phpfreaks.com/forums/index.php?topic=351279.0 Is there such thing? I designed a while back a rudimentary form based app for my users. We receive from our suppliers hardware manufacturing data in XML files: file name is made of eleven fields separated by tildes, with each field having its own meaning. R&D guys wanted to be able to search each field of the file names so I used regex() with decent results. Problem is that we have now in the upwards of 2.5 million files. And my app can't hack it anymore. I looked at Apache Lucene & Solr. Though it seemed like the best solution to my problem, the fields in the filenames are not peers to the file content. Big no-no with Solr. What is the best way to implement a PHP app with indexing and search capability with such large number of files? Do I have to buy Zend and use Zend_Search? Is it the only way? Thanks for your input. |
|||||||