Mysql, Php - Search Feature Design

[Sparco]

Hi Everyone,

 

Long time reader, first time poster! Please tell me if i've posted this in the wrong section too ..

 

I've just started web-design and am looking to broaden my skills and start to create a few of the ideas I have. Through a bit of research and learning I've come up with a fairly non-technically specific plan on how I may design a search feature for my site. I believe it will involve the use of Mysql and PHP, and am commited to continuing my learning into these 2 areas. However, before I begin pulling my hair out over this I would like any guidance anybody can provide into whether this is the right way of approaching such a project.

 

A simple breakdown of what I would like to happen here is:

1. USER WILL ENTER 3 OPTIONS THROUGH DROP DOWN BOXES ON A WEBSITE

2. RESULTS FOR EACH INPUT BOX ARE COLLECTED AND CONVERTED TO 1 VARIABLE

3. THAT VARIABLE IS LOOKED UP AS A PRE-EXISTING COLUMN IN A DATABASE

4. RESULTS ARE RETURNED FROM THAT COLUMN AND DISPLAYED, SORTED BY NUMBER RANKING.

 

To put this into simple context,

(Im only using horses here as an example)

 

3 Input Boxes:

BOX1:

(input) HORSE RUNS WELL IN WET - output(W)

(input) HORSE RUNS WELL IN DRY - output(D)

(input) HORSE RUNS WELL IN BOTH - output(B )

BOX2:

(input) HORSE RUNS WELL OVER SHORT DISTANCE - output(S)

(input) HORSE RUNS WELL OVER MED DISTANCE - output(M)

(input) HORSE RUNS WELL OVER LONG DISTANCE - output(L)

BOX3:

(input) HORSE IS BLACK - output(BK)

(input) HORSE IS BROWN - output(BN)

(input) HORSE IS GREY - output(GY)

 

SO there are overall 27 possible variable outcomes, which will be automatically combined from the user input.

(WSBK,WSBN,WSGY,WMBK,WMBN,WMGY,WLBK.. so on..)

 

I would then have my database setup with 28 columns (1 for horse ID numbers and 27 others each variable) with the unique horse ID down each row. Depending on which attributes each horse qualifies for, they will recieve a ranking for that column(variable)

 

For example 2 inputs are WSBK (Wet Run, Small Distance, Black) Horse"A" qualifies for this column and we percieve it to be better than Horse"B" so we give horse "A" the number 2, and Horse#B a number 1. This means that when our column WSBK is searched, it can return the results from highest to lowest.

Pending that the horses colours or running preferences will never change, the initial data loaded into the database will not need to change either,So overall this would be a search feature allowing the results of the best horses suited to the best conditions depending on what kind of colour they are.

Are there better way's of approaching this?

 

Thanks heaps!!

Edited December 19, 2012 by Sparco

[Barand]

Don't create a table with 27 columns, create a second table with 27 rows.

 

Don't combine WSGY, keep them as 3 separate fields W | S | GY. That will make it easier for queries such as "What is the best horse over middle distance regardless of colour?".

 

So now you will have two tables

 

Horse : horseID, name, height etc

Scores : id, horseID, conditions, distance, colour, score