DATABASE SEARCH USING SPHINX SEARCH ENGINE IN PHP

METHOD 1: Using WEB Search Mechanism.

Have Two Activities – 1. While creating new thread and responding thread.

2. Searching while typing on title or description field .

First activity:

when creating new forum thread and responding thread.

Step 1: After save or responding forum thread, get current page URL.

Step 2: From URL ,download HTML file to local machine using

Ex:

wget -r "URL" -o log 

 

Step 3: Downloaded files paths (Local and global) store it in one text file.

Step 4: Mapping local and global(http url) paths and pass to database table

“sphinx_data”

using shell script or PHP.

Step 5: Configure Sphinx.conf file (/usr/local/sphinx/etc/sphinx.conf).

Ex: configure source - mysql connections,table details and query

index ,indexer,searchd deamon.

Step 6: Create Sphinx index to sphinx_data table using Sphinx indexer

Like,

$ cd /usr/local/sphinx/etc

$ /usr/local/sphinx/bin/indexer –all

Second Activity:

Search on Forum thread creation form(Title and description Field).

Step 1: Start Sphinx searchd daemon.

Ex: Start: /usr/local/sphinx/bin/searchd

Stop : open /usr/local/sphinx/var/log/searchd.pid

get searchd pid number

kill -9 pid 

 

Step 2: When typing title or description field ,get search keyword and pass to

sphinx engine.

Ex: $ cd /usr/local/sphinx/etc

$ /usr/local/sphinx/bin/search search text.

Step 3: Sphinx engine returns global url's from sphinx_data table(MySQL

database).

Step 4: Get forum thread's title and description from output url's.

Step 5: Display search result on our portal.

METHOD 2: Search directly from database(Forum table) using sphinx engine.
                           
                               Table creation & Insert some data

CREATE TABLE IF NOT EXISTS `forum` (
  `topic_id` int(4) NOT NULL AUTO_INCREMENT,
 `title` varchar(255) NOT NULL,
  `description` longtext NOT NULL,
   PRIMARY KEY (`topic_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=132 ;

INSERT INTO `forum` ( `title`, `description`) VALUES
('Post Prandial Blood Sugar Test', 'What is the current and right practice of doing the Post Prandial Blood Sugar test'),
('Health Benefit Foods', 'What are the foods for health benefits'),
( 'sddfsdfs', 'sdfsdfsdf'),
('Laser Surgery', 'What is a laser?'),
( 'what is ur name', 'what do you want to do?'),
('cvcxcv', 'xcvxcv'),
('Sneezing', 'Can a pregnant woman can sneeze?'),
('Cold Effects', 'what are the cold Effects'),
('oncology', 'oncology'),
('Implementation sphinxapi on python,twisted', 'Hi Community! I write for self asynchronous python driver for sphinxYet').
                       
                                     STEPS

Step 1: Configure Sphinx.conf file (/usr/local/sphinx/etc/sphinx.conf) –

Manually .

Ex: configure source - mysql connections,table details and query -

Select * from forum table index ,indexer.

 

Step 2 : Set only search on title and description column from forum table in

sphinx.conf file.

Step 3 : Start Client Searchd demon server.

/usr/local/sphinx/bin/searchd

Step 4: Create Sphinx index to forum table using Sphinx indexer

Like,

$ cd /usr/local/sphinx/etc

$ /usr/local/sphinx/bin/indexer –all

Re-indexing:

$ /usr/local/sphinx/bin/indexer –all --rotate

Step 5 : When typing title or description field on portal ,get search keyword

and pass to sphinx engine client PHP API. (sphinxapi.php class).

Step 6 : Sphinx engine returns forum_id (primary key) from forum

table(MySQL database).

Step 7 : From forum_id get title and description and Display it on our portal.

                                                                                       
                                                                                        -PAVANKUMAR JOSHI