[RESOLVED] Search mySQL DB with SQL

This is more of a SQL question than a PHP one but I'm using PHP so I posted here. I'm fairly new to SQL and web programming in general. I have a 10 column DB with a few thousand rows. Right now I have a simple PHP page where you can enter a phone number to search the records.

Code:

---

$query = 'SELECT * FROM data WHERE phone="'.$phone.'"';

---

This works fine, but I would like the user to be able to input any search term in this one box, and have it search all columns. So no matter if they enter a phone number, a name, or an address, the record will still be found. What is the best way to do this?

I'll assume that you're using the MyISAM engine for this problem, in which case you could use MATCH AGAINST to look at all of the text/varchar/char fields in your table. this has advantages and disadvantages. I'll explain a bit:

first of all, however, you'll need to make sure that all of the appropriate fields have a FULLTEXT index created so that the MATCH function works. MATCH AGAINST can also be used without these indexes in boolean mode, but then the searches will be slowed considerably (so I wouldn't suggest this). so, how do you create FULLTEXT indexes? you can either create them when you're creating your table, or modify your table.

you're creating these indexes on the table itself (rather than the columns), so the syntax would look like the following for when you're creating a table:

Code:

---

CREATE TABLE test (

  col1 TEXT,
  col2 VARCHAR(10),
  col3 CHAR(5),
  col4 INT,

  FULLTEXT(col1, col2, col3)

) ENGINE=MyISAM;

---

in this case, we're creating a fulltext index for col1, col2, and col3. if your table already exists, however, then you just need to create a simple alter table query:

Code:

---

ALTER TABLE test ADD FULLTEXT(col1, col2, col3)

---

this can all be done within phpMyAdmin, too. when creating a table, under index select FULLTEXT. when modifying a table (under the structure tab), there is a "FULLTEXT" button that will add an index to a field (or multiple fields at once).

so, now that your table has these indexes, we can start to use these indexes with a MATCH AGAINST. we can take your current query and morph it (variables removed for clarity):

Code:

---

SELECT * FROM data WHERE MATCH(phone) AGAINST('780-555-5454');

---

as you might be able to tell, this is pretty simple to use now. we can match all three of our new table's columns against one value, too:

Code:

---

SELECT * FROM test WHERE MATCH(col1, col2, col3) AGAINST('search terms');

---

one thing to note is that MySQL will also return these values based on how relevant they are. for example, if (in the previous example) the words search and terms are both found in one record then that result will be more relevant than if only one of those words were found in a result.

another thing to note is that MySQL has a list of stopwords -- this is a list of common words that are completely ignored by MySQL. this list can be modified, but in a shared host environment I don't think you'd be able to do so. MySQL will also ignore words if they are below the minimum length for a word (4 characters), and will not match partial words.

this is a very general explanation, however, and if you are still interested then you may want to just consult the manual about fulltext searches.

------

an alternative to using MATCH AGAINST queries (especially useful if you're using InnoDB, where this function is not available, but also a good alternative if you want to be able to match partial words) would be to just extend your query with a bunch of OR statements, using the LIKE operator, with an expression using wildcards:

Code:

---

SELECT * FROM test WHERE col1 LIKE '%searchterms%' OR col2 LIKE '%searchterms%' OR col3 LIKE '%searchterms%';

---

the percent sign ("%") is the wildcard in this case, but other characters can also act as wildcards in MySQL as well (which may or may not be a problem, so you could always escape them using a regular expression if you needed to). the LIKE operator takes care of case sensitivity, so if you were searching for "david," you would also find "David."

hope that helps.

Wow, this helps tons! Very thorough reply. Better than I expected. Thanks very much.