Member Avatar for Stefano Mtangoo

Now, heading might be a bit misleading. If that is the case forgive me as I cannot think of best heading!

i have database of books, that is e-books. Each table have page number and contents (to simulate true book). The problem is, I have to search for a word in the book and return page number and contents. I know LIKE '%mykeyword%' helps to some extent but it returns much unneeded stuffs. Eg if the keyword is Goa it will bring goalkeeper, goat et al. I want to be able to get pages which contains exactly word Goa and ignore others. Which query am I supposed to use. I have run out of Ideas so, I need your help

  • 3 Contributors
  • 6 Replies
  • 87 Views
  • 14 Hours Discussion Span
  • Latest Post Latest Post by Stefano Mtangoo
Member Avatar for pritaeas

How about searching with REGEX.

$query = "SELECT * FROM table WHERE column REGEXP '[[:<:]]" . $word . "[[:>:]]'"
Edited by pritaeas because: n/a
Member Avatar for mschroeder

Assuming your table is a MyISAM and the particular column has a full-text index on it:

SELECT * FROM table WHERE MATCH (col1, col2, col3) AGAINST ('+Goa' IN BOOLEAN MODE);

http://dev.mysql.com/doc/refman/5.1/en/fulltext-boolean.html

***Apparently this does not REQUIRE a fulltext index, but will take a major performance hit

Edited by mschroeder because: n/a
Member Avatar for Stefano Mtangoo

I didn't said that I use SQLite3, however I will search to see if those solutions are available with SQLite

Member Avatar for pritaeas

From the sqlite site:
The like() function is used to implement the "Y LIKE X [ESCAPE Z]" expression. If the optional ESCAPE clause is present, then the like() function is invoked with three arguments. Otherwise, it is invoked with two arguments only. Note that the X and Y parameters are reversed in the like() function relative to the infix LIKE operator. The sqlite3_create_function() interface can be used to override the like() function and thereby change the operation of the LIKE operator. When overriding the like() function, it may be important to override both the two and three argument versions of the like() function. Otherwise, different code may be called to implement the LIKE operator depending on whether or not an ESCAPE clause was specified.

I do not know about SQLite, so can't give an example, but perhaps you can find one (looks like a function in C).

Edited by pritaeas because: n/a
Member Avatar for mschroeder

Not sure what the environment limitations are, but SQLite3 has fulltext searching capabilities, but they are not enabled by default. Very Similar to mysql syntax it appears.

http://www.sqlite.org/fts3.html

Member Avatar for Stefano Mtangoo

Thanks guys!
theoretically my problem is solved. I will post if I hit a wall.
Again thanks a lot!

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.