PHP SQL - Search in MySql database

Author

Search in MySql database

Morten Westby

2005-01-26, 3:57 pm

Hello.

I have some questions about searching for images (tagged with metadata) in a
SQL database.

What I want to do is to have several metadata fields attached to each
picture. One of the fields should be a "general expression" field with
several words.

A typical pictures metadata would look like this:

Filename: shi0000001.jpg
Photographer: Name of the photographer
Category: Ship
Object name: Name of ship
Keywords: Ship, sea, waves, transportation, passenger ship, sun

I want to be able to narrow down the search by typing several words in to
one search field. If I search for the word "ship" I would get thousands of
hits.

If I search for the words: ship, passenger, sun, name of ship
I get even more hits because I get all pictures containing the word "ship"
or "passenger" or "sun" and so on.

I want to be able to search in all metadata fields with an "and" between all
words. That way I can narrow down the search instead of expanding it.

Does anyone have knowledge on this issue or a web address to where this
issue is described?

Thank you for listening!

Regards, Morten Westby
Norway

J.O. Aho

2005-01-26, 3:57 pm

Morten Westby wrote:
> Hello.
>
> I have some questions about searching for images (tagged with metadata) in a
> SQL database.
>
> What I want to do is to have several metadata fields attached to each
> picture. One of the fields should be a "general expression" field with
> several words.
>
> A typical pictures metadata would look like this:
>
> Filename: shi0000001.jpg
> Photographer: Name of the photographer
> Category: Ship
> Object name: Name of ship
> Keywords: Ship, sea, waves, transportation, passenger ship, sun
>
> I want to be able to narrow down the search by typing several words in to
> one search field. If I search for the word "ship" I would get thousands of
> hits.
>
> If I search for the words: ship, passenger, sun, name of ship
> I get even more hits because I get all pictures containing the word "ship"
> or "passenger" or "sun" and so on.
>
> I want to be able to search in all metadata fields with an "and" between all
> words. That way I can narrow down the search instead of expanding it.
>
> Does anyone have knowledge on this issue or a web address to where this
> issue is described?

You could concat the coulmns and make a search in that new field, this way you
could sear with AND only and should generate less hits. You would need to
search for substr in it.

//Aho

phptn

2005-01-27, 8:57 pm

Morten Westby wrote:
> Hello.
>
> I have some questions about searching for images (tagged with metadata) in a
> SQL database.
>
> What I want to do is to have several metadata fields attached to each
> picture. One of the fields should be a "general expression" field with
> several words.
>
> A typical pictures metadata would look like this:
>
> Filename: shi0000001.jpg
> Photographer: Name of the photographer
> Category: Ship
> Object name: Name of ship
> Keywords: Ship, sea, waves, transportation, passenger ship, sun
>
> I want to be able to narrow down the search by typing several words in to
> one search field. If I search for the word "ship" I would get thousands of
> hits.
>
> If I search for the words: ship, passenger, sun, name of ship
> I get even more hits because I get all pictures containing the word "ship"
> or "passenger" or "sun" and so on.
>
> I want to be able to search in all metadata fields with an "and" between all
> words. That way I can narrow down the search instead of expanding it.
>
> Does anyone have knowledge on this issue or a web address to where this
> issue is described?
>
> Thank you for listening!
>
> Regards, Morten Westby
> Norway
>
hiyou should use AND for your search
exemple:

(category) OR (Object name) OR (word1 AND word2 AND word3 in Keywords)

I think it will retrun less results

Sponsored Links