How would I implement a simple site search with php and mySQL?

by

Everyone is suggesting MySQL fulltext search, however you should be aware of a HUGE caveat. The Fulltext search engine is only available for the MyISAM engine (not InnoDB, which is the most commonly used engine due to its referential integrity and ACID compliance).

So you have a few options:

1. The simplest approach is outlined by Particle Tree. You can actaully get ranked searches off of pure SQL (no fulltext, no nothing). The SQL query below will search a table and rank results based off the number of occurrences of a string in the search fields:

SELECT
    SUM(((LENGTH(p.body) - LENGTH(REPLACE(p.body, 'term', '')))/4) +
        ((LENGTH(p.body) - LENGTH(REPLACE(p.body, 'search', '')))/6))
    AS Occurrences
FROM
    posts AS p
GROUP BY
    p.id
ORDER BY
    Occurrences DESC

edited their example to provide a bit more clarity

Variations on the above SQL query, adding WHERE statements (WHERE p.body LIKE ‘%whatever%you%want’), etc. will probably get you exactly what you need.

2. You can alter your database schema to support full text. Often what is done to keep the InnoDB referential integrity, ACID compliance, and speed without having to install plugins like Sphinx Fulltext Search Engine for MySQL is to split the quote data into it’s own table. Basically you would have a table Quotes that is an InnoDB table that, rather than having your TEXT field “data” you have a reference “quote_data_id” which points to the ID on a Quote_Data table which is a MyISAM table. You can do your fulltext on the MyISAM table, join the IDs returned with your InnoDB tables and voila you have your results.

3. Install Sphinx. Good luck with this one.

Given what you described, I would HIGHLY recommend you take the 1st approach I presented since you have a simple database driven site. The 1st solution is simple, gets the job done quickly. Lucene will be a bitch to setup especially if you want to integrate it with the database as Lucene is designed mainly to index files not databases. Google custom site search just makes your site lose tons of reputation (makes you look amateurish and hacked), and MySQL fulltext will most likely cause you to alter your database schema.

More Related Contents:

  1. Order results from two queries based on time in second query [closed]
  2. Query database then email posts from where user is subbed to
  3. Using PHP data in MySQL [closed]
  4. Save the data into a database and return another value from database using PHP
  5. Checking the availability of rooms
  6. How can I do ‘insert if not exists’ in MySQL?
  7. How to properly set up a PDO connection
  8. Getting raw SQL query string from PDO prepared statements
  9. Correct way to use LIKE ‘%{$var}%’ with prepared statements?
  10. MySQL Prepared statements with a variable size variable list
  11. Loading .sql files from within PHP
  12. PDO with INSERT INTO through prepared statements [closed]
  13. SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens on line 102 [closed]
  14. Comma separated values in MySQL “IN” clause
  15. MySQL “NOT IN” query 3 tables
  16. SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax — PHP — PDO [duplicate]
  17. PHP: multiple SQL queries in one mysql_query statement
  18. Doing a while / loop to get 10 random results
  19. PHP – Import CSV file to mysql database Using LOAD DATA INFILE
  20. Why does this return Resource id #2? [duplicate]
  21. Simple PHP SQL login troubleshooting
  22. Change date format (in DB or output) to dd/mm/yyyy – PHP MySQL
  23. MySql PHP select count of distinct values from comma separated data (tags)
  24. How to SELECT DEFAULT value of a field
  25. Laravel Eloquent get results grouped by days
  26. how do I get month from date in mysql
  27. Do I have to use mysql_real_escape_string if I bind parameters?
  28. PDO and MySQL ‘between’
  29. MySQL Join Multiple Rows as Columns
  30. Aggregated query without GROUP BY

Leave a Comment