mySQL WHERE clause difficulties.....

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

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!!

actually, just decided to put all authors in one field. after discussion,
we realized there is no reason to separate them....

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 (AT) seyware (DOT) com> wrote

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

.oO(msa)

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
[...]

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.

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.

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
--------------
articleID
title
description
....

table authors
-------------
authorID
firstname
lastname
....

table written
-------------
authorID
articleID

Such a layout eliminates unnecessary redundancy and avoids insert/update
anomalies (hint: normalized database).


Additionally you should avoid SELECT * in queries, it's better to
explicitly list the columns you want to retrieve for certain reasons.
SELECT * should only be used while developing or for quick tests.

Micha

thanks for the db input....i am still newbie at db.....

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.

What Database and scripting language are you using?

Thanks.

Chris

php/mysql

"Christopher Seymour" <cseymour (AT) seyware (DOT) com> wrote

What Database and scripting language are you using?

Thanks.

Chris

.oO(msa)

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

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