Questions about a site search

Not for 'how-to' coding questions but PHP theory instead, this forum is here for those of us who wish to learn about design aspects of programming with PHP.

Moderator: General Moderators

Post Reply
User avatar
social_experiment
DevNet Master
Posts: 2793
Joined: Sun Feb 15, 2009 11:08 am
Location: .za

Questions about a site search

Post by social_experiment »

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".
“Don’t worry if it doesn’t work right. If everything did, you’d be out of a job.” - Mosher’s Law of Software Engineering
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: Questions about a site search

Post by requinix »

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.
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: Questions about a site search

Post by Celauran »

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. site.com/foods/almonds-roasted conveys more meaning than site.com/foods/678326
User avatar
social_experiment
DevNet Master
Posts: 2793
Joined: Sun Feb 15, 2009 11:08 am
Location: .za

Re: Questions about a site search

Post by social_experiment »

requinix wrote: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.
Yea I need the name so I'm retrieving that; the JOIN option is something that I didn't think about, thanks for the tip.
“Don’t worry if it doesn’t work right. If everything did, you’d be out of a job.” - Mosher’s Law of Software Engineering
Post Reply