PHP Developers Network

Questions about a site search
Page 1 of 1

Author:  social_experiment [ Tue Jan 27, 2015 2:58 am ]
Post subject:  Questions about a site search

I'm writing search code and I'm conflicted about the process of getting the results back to the user.

At the moment I have 2 tables, food_tbl and food_tag_tbl. The first table will house information about a specific food while the second will be the table that is used for the search.

Columns for the food_tag_tbl are as follows:
1. id
2. food_name
3. food_tag

Entries into the tag table will be determined by the name of the food, i.e Beef, Fillet, Lean, Raw. In the tag table i would have the following entries:

Beef, Fillet, Lean, Raw | Beef
Beef, Fillet, Lean, Raw | Fillet
Beef, Fillet, Lean, Raw | Lean
Beef, Fillet, Lean, Raw | Raw

The conflict comes on how to retrieve the information that will be used to display the result. food_tbl has an id column, auto-increment and PK, that i will in the query string to retrieve more specific information from the database. My thoughts at the moment:

1. Do i put a foreign key column into the tag table, which ties together with the id key in the food table, and on finding a match return that id value to be used in a hyperlink?
2. Do i retrieve the name of the item from the tag table, and then retrieve the id from the food table to be used in a hyperlink?

I am leaning towards option 1 at this stage because I don't want there to be unnecessary code that goes back and forth between the two tables. If possible I'd like table 1 to be the "information table" while table 2 is strictly a "search table".

Author:  requinix [ Tue Jan 27, 2015 3:55 am ]
Post subject:  Re: Questions about a site search

1. Your table should definitely be using the ID instead of the name of the food.
2. If you're searching for a particular tag then you can get the ID back, yes, but you could also JOIN in the food table and get its information immediately. At the very least you'll want to display the name of the food in the search results, right? So you kinda have to.

Author:  Celauran [ Tue Jan 27, 2015 7:31 am ]
Post subject:  Re: Questions about a site search

I definitely agree with the foreign key and possibly returning joined information from the food table. I say possibly because you already have the food name in the tag table and additional information may or may not be needed on the search results page. Another nice option would be to slugify the food names and return that rather than an ID. conveys more meaning than

Author:  social_experiment [ Tue Jan 27, 2015 3:19 pm ]
Post subject:  Re: Questions about a site search

Page 1 of 1 All times are UTC - 5 hours
Powered by phpBB® Forum Software © phpBB Group