In keeping with good database practices, I've rededsigned an older application so that the data is very atomic.
For example, before we simply had a filed called "name", now we've expanded it to "title", "name", and "lname" so that when sorting the data we can organize it a little more logically.
I feel that this is the right thing to do, but it makes some of my free text queries more difficult.
I use postgresql as my database to hold some content such as author name and the text of an article. I used to do a query like this:
$q = "search string";
$sql = "SELECT FROM table WHERE name ~ '$q' OR article ~* '$q'";
This made the assumption that the string was either all or part of an author's name or a phrase found in the article.
Now, to do the same thing I fear I'll have to do something more complex. I know that the below will not do what I want, but it's the closest thing to what I need.
$q = explode(' ', "search string");
$q = "'" . implode("','", $q). "'";
// take an array of search words and format it like 'word1','word2'
$sql = "SELECT * FROM table WHERE ($q) IN name OR ($q) IN lname OR ($q) IN article";
I don't think the above will work for a couple of reasons. First, I loose my case insensitivity, second, can you switch the parameters of "IN" like that?
Can anyone give some pointers?
Thanks,
Matt Nuzum
