Hi,
I'm pretty much a newbie to SQL query construction and database design, but I do know how to use SQL and all it's various commands. Here is the problem:
I'm writing a simple search engine where I take a bunch of keywords (inputted by user in an HTML form) and use those to search all the fields of my table. For example, let my table have these fields:
name
company
location
url
description
keywords
Now, lets say the user enters the following: "abc def ghi". What I want to do is search the table to find any OR all of the keywords. Here is my query (BTW: I'm using MySQL):
SELECT * FROM mytable WHERE name LIKE '%abc%' OR company LIKE '%abc%' OR url LIKE '%abc%' ....all the way to.... OR keywords LIKE '%ghi%'
Now obviously, the abc, def, ghi parts in the SQL query are done using a foreach statement:
foreach word in keywords {
append "WHERE name LIKE '%$word%' OR ..."
} // you get the point
Now, my question: is there anyway to simplify this? Meaning, is there any query I can issue that will do this:
SELECT FROM table WHERE anyfile LIKE '%keyword1%' OR anyfield LIKE '%keyword2%'
I'm sure I'm doing this search engine the long way. So if anyone can help me construct a simpler query, I'ld greatly appreciate it.
Right now, my query executes in ample time, but soon the table is going to become huge with thousands of entries and I want to make sure the search results come up very very fast.
Thanks,
-sridhar
I'm pretty much a newbie to SQL query construction and database design, but I do know how to use SQL and all it's various commands. Here is the problem:
I'm writing a simple search engine where I take a bunch of keywords (inputted by user in an HTML form) and use those to search all the fields of my table. For example, let my table have these fields:
name
company
location
url
description
keywords
Now, lets say the user enters the following: "abc def ghi". What I want to do is search the table to find any OR all of the keywords. Here is my query (BTW: I'm using MySQL):
SELECT * FROM mytable WHERE name LIKE '%abc%' OR company LIKE '%abc%' OR url LIKE '%abc%' ....all the way to.... OR keywords LIKE '%ghi%'
Now obviously, the abc, def, ghi parts in the SQL query are done using a foreach statement:
foreach word in keywords {
append "WHERE name LIKE '%$word%' OR ..."
} // you get the point
Now, my question: is there anyway to simplify this? Meaning, is there any query I can issue that will do this:
SELECT FROM table WHERE anyfile LIKE '%keyword1%' OR anyfield LIKE '%keyword2%'
I'm sure I'm doing this search engine the long way. So if anyone can help me construct a simpler query, I'ld greatly appreciate it.
Right now, my query executes in ample time, but soon the table is going to become huge with thousands of entries and I want to make sure the search results come up very very fast.
Thanks,
-sridhar