[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 312
  • Last Modified:

PHP Search?

Good afternoon experts.

I have been programming in PHP for a couple of years now so I have a pretty basic understanding of the language and how it works. However I have come into a little road block.

Basically, I am dealing with a database that has over 3 million rows in it, there are 4 main columns in the database, they are POST_TITLE, POST_DATE, POST_CONTENT & POST_AUTHOR.

I need to be able to search quickly through this database for key words.

For example if a user wants to search through the post_title column of the database, and display results i.e. if they searched for Bon Jovi and I had 300 rows in that database with Bon Jovi in the title, then It would find them.

I know that I could use something like SELECT * FROM DB WHERE POST_TITLE LIKE '%Bon Jovi%' and be shown some results, but I never really thing the "Like" in MySQL was very acurate, and I have no doubt that that would be slow.

So do I need to create an index of key words in a seperate table for each post or something???


How do I quickly and easily search through a database that size?
  • 2
2 Solutions
Roger BaklundCommented:
The easiest solution is to use fulltext indexing:

Roger BaklundCommented:
If you create your own keywords table, use a normalized approach: the keywords are unique in the keywords table, and you have a keyword_post table linking keywords to each post.

Note that "Bon Jovi" is two words:
select kp1.post_id 
  keyword_post kp1,
  keyword_post kp2,
  keyword k1,
  keyword k2
  k1.word = 'Bon' and k2.word = 'Jovi' and 
  k1.word_id = kp1.word_id and 
  k2.word_id = kp2.word_id and
  kp1.post_id = kp2.post_id

Open in new window

If you run of mysql, look into dbsight. (dbsight.net)

It is a java webapp, but can be integrated very easily with your php webapp.

Configure the indexing process, then use a xml format to wire your php search through dbsight and process the response to your view.
billy_howardAuthor Commented:

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now