mySQL WHERE clause difficulties.....

message from msa on 19 Jul 2004

I have spent the day developing a search/results/detail page set and am
quite happy!

The search page has three entry elements: Author, Title and Publication.

Here is my SELECT statement from the results page:

$query_rsResults = sprintf("SELECT * FROM articles WHERE ((articles.title
LIKE '%%%s%%') AND (articles.author01 LIKE '%%%s%%' OR articles.author02
LIKE '%%%s%%' OR articles.author03 LIKE '%%%s%%' OR articles.author04 LIKE
'%%%s%%' OR articles.author05 LIKE '%%%s%%' OR articles.author06 LIKE
'%%%s%%' OR articles.author07 LIKE '%%%s%%' OR articles.author08 LIKE
'%%%s%%' OR articles.author09 LIKE '%%%s%%' OR articles.author10 LIKE
'%%%s%%' OR articles.author11 LIKE '%%%s%%' OR articles.author12 LIKE
'%%%s%%' OR articles.author13 LIKE '%%%s%%' OR articles.author14 LIKE
'%%%s%%' OR articles.author15 LIKE '%%%s%%' OR articles.author16 LIKE
'%%%s%%' OR articles.author17 LIKE '%%%s%%') AND (articles.journal LIKE
'%%%s%%'))",

One obvious problem.....I need the search to go through all 17 author
columns and cannot figure out a wildcard code solution to eliminate all the
repetitive text.

Second problem is that, as it is, you can only enter one author name into
the search box. I would like to be able to enter more than that. i have
fooled around with AND and OR....not getting the results that I need.

Thanks for any help!!

Christopher Seymour replied to msa on 19 Jul 2004

Do you really need 17 authors fields? Would it be easier to have
another author table then that would hold the author field and link back
to your articles table?

It might look like autID, articleID, authorID.

Then if article1 had 12 authors, you would have twelve records.

Just a thought. Let me know if you want some more examples.

Thanks.

Chris

msa replied to Christopher Seymour on 19 Jul 2004

But even though I have done this, my searches are not allowing me a
boolean..... only one word or exact phrases. how do i fix that?

"Christopher Seymour" <cseymour@seyware.com> wrote in message
news:cdh079$3oj$1@forums.macromedia.com...

Michael Fesser replied to msa on 19 Jul 2004

.oO(msa)

Uh oh ... bad db design, _very_ bad db design. Whenever you think you
need multiple columns sharing the same type of information you should
know that something's wrong. A layout like that not only contains
redundancy and wastes resources, it'll also cause _lots_ of trouble.
I strongly recommend to drop it and think it over completely.

What about articles with 18 authors? Add another column? What about
articles with only a few authors - more than a dozen NULL fields? What
if you want to fetch all articles from a certain author? What if an
author changes his name? To be continued ...

A "standard" solution is to use at least three(!) tables: articles,
authors and one that connects them. They could look like this:

table articles

msa replied to Michael Fesser on 19 Jul 2004

any ideas for the second issue....still have it, even with new db
structure....

thanks!!!!!

________________
Second problem is that, as it is, you can only enter one author name into
the search box. I would like to be able to enter more than that. i have
fooled around with AND and OR....not getting the results that I need.

Michael Fesser replied to msa on 20 Jul 2004

.oO(msa)

Haven't done such kind of search before (but maybe I also need it some
day), so I've played around a bit and set up a simple test script:

http://www.mfesser.de/test/search/search.html (all source codes
available on that page)

I used artists/genres instead of authors/articles, but the idea is the
same. Since I'm a fan of regular expressions, I've done it like that:

* Take the submitted value and remove all leading and trailing blanks.

* Separate the values with '|' (logical OR) without any blanks between.
A submitted value of

' foo bar blubb '

then becomes

'foo|bar|blubb'

* In the query use RLIKE instead of LIKE to perform a regular
expression search. With the example values above the regex becomes

'.*(foo|bar).*'

This matches all strings with either 'foo' or 'bar' in it and any
kind of other characters in any number before and after it (.*).

Just for clarification - the following clauses do the same (but in
different ways):

... LIKE '%foo%'
... RLIKE '.*foo.*'

In my script this is implemented in the function performSearch(), all
other funtions are more or less just helper functions.

One drawback of this solution: Regular expression searches in databases
are probably not that efficient than other methods, but for small sites
it should be OK. Of course there are other ways to achieve the same (for
example splitting the search values and create a "field LIKE %foo% OR
field LIKE %bar%" clause from it).

But maybe it's of some help.

Micha

Christopher Seymour replied to msa on 19 Jul 2004

What Database and scripting language are you using?

Thanks.

Chris

Archived message: mySQL WHERE clause difficulties..... (Macromedia Dreamweaver Web Design)