[RESOLVED] Search DB with PHP - Good Design?

Yari

Hello,

I am building a simple photo gallery for a client. A large part of this is a good search engine for the photos... do display photos by Theme, Artist, Style and the like. I would like some advice on the best way to structure / search this website.

Each photo can be categorised in a many ways, it may be Black & White (a Style), a Portrait (again a Style), by the Artist Alberto (Artist) and also be in Feel Good (a Theme).

In order to do this I have spent some time designing a database that handles these functions in a fast, user-friendly manner. Record names have been exagerated for simplicity here.

Photo DB is:

photo_id
photo_artist_id
photo_name
photo_src

There is a corresponding Artist DB with id, name that can be referenced from this photo (or more likely, Photos from the Artist). I feel this structure is quite sound, and am happy with it.

What I would like to know is if I am handling my Themes and Styles databases well.

I will give the Theme DB as an example, there are two DB's for each Theme:

THEME DB:

theme_id
theme_name

which might consist of:

theme_id: 1
theme_name: Feeling Good

Then a second DB that matches a Photo to a Theme:

THEME_PHOTOS DB:

theme_id
photo_id

This way I can easily match a photo to a theme, or several themes.

First question: is this a good way of structuring my db?
Second question: what is the best way to search all of my themes, artists and styles for a keyword?
What I started doing was a search for the keyword in THEME db, then matching that with photo id etc. However should I lump this search with the ARTIST and STYLE search in one big array, or is it better to search each DB, send results, search next DB?

Thank you for your time to read this long message, I appreciate any advice you could give me.

Cheers,

Yari

rheinen

Yari wrote:
First question: is this a good way of structuring my db?

Yes, there's nothing wrong with the organization of your tables. You might want to consider calling the fields in THEMES_PHOTOS theme_fk and photo_fk as they are really foreign keys referring to records in other tables but that's not a big deal.

Yari wrote:
Second question: what is the best way to search all of my themes, artists and styles for a keyword?
What I started doing was a search for the keyword in THEME db, then matching that with photo id etc. However should I lump this search with the ARTIST and STYLE search in one big array, or is it better to search each DB, send results, search next DB?

I would put it all in one query using joins:

You will have to replace the ... in the field list with the fields that you actually want and you will also need to set and escape (using mysql_real_escape_string or addslashes()) the keywords variable.

Placing it all in one query reduces amount of times that SQL code will have to be compiled and executed and it will certainly reduce the complexity of your php code. These sorts of operations are better performed all at once in the database as that's one of the reasons of using a database in the first place.

I hope this is helpful,

Ryan

Yari

Ah, much appreciated!

Will take your advice on the naming of feilds...

Cheers.

Yari

Hello Again,

Sorry to re-open this post, but I realised there was one question that remains un-answered.

"what is the best way to search all of my themes, artists and styles for a keyword?"

I can search a Theme successfully, however I want to be able to search for a Style, also the Artists name...

Would this be best in one large array, or to break it up when I search each different DB table, ie. one search for Themes, one for Styles, one for Artist etc.??

There must be a standard way of searching many different tables in a database, and collating that info.

Thanks

rheinen

Yari wrote:
"what is the best way to search all of my themes, artists and styles for a keyword?"

I can search a Theme successfully, however I want to be able to search for a Style, also the Artists name...

Would this be best in one large array, or to break it up when I search each different DB table, ie. one search for Themes, one for Styles, one for Artist etc.??
Thanks

Yes SQL does have support for what you are looking for:

Add another set of joins for each extra table you want to search/retrieve:

[FONT=Courier New]SELECT photo_id, photo_name, ... FROM photos
JOIN themes_photos ON photos.photo_id = themes_photos.photo_id
JOIN themes ON themes_photos.theme_id = themes.theme_id
JOIN styles_photos ON photos.photo_id = styles_photos.photo_id
JOIN styles ON styles_photos.style_id = styles.style_id
...
WHERE theme_name LIKE '{$keyword}' AND/OR style_name LIKE '{$keyword}' ...;[/FONT]

You can use either AND or OR in that last part, depending on your preference but in this case since you probably want to return the result if either the style or the theme or whatever else matches the keyword you should probably use an OR.

Ryan

Yari

Wow, thats great!

I had no idea such complex queries could be run in MySQL... this makes it soo much easier for me.

bye