Hi Shahu - See? I didn't want to bore you with the overall scope of my project and it turns out I'm giving too little info!
I think what you suggest is what I'm looking for. And then I would create another table for all information about the play (such as synopsis, etc) that would relate to mainproperty in your example? Jeremy --- In php_mysql@yahoogroups.com, Shahu <shaha...@...> wrote: > > On Fri, May 21, 2010 at 8:39 PM, pirate_lenny <piratele...@...> wrote: > > > > > > > Okay. There are some other things going on. It's a site similar to IMDB. > > When I say property, I mean a play. So, for example, let's take the play > > Hamlet. > > > > Hamlet is commonly known as Hamlet. But there is an alternate title of The > > Tragedy of Hamlet, Prince of Denmark. > > > > So, Hamlet would go in the "properties" table and The Tragedy of... would > > go in the subproperties table. > > > > But, then in the database, there are productions of Hamlet. Some may have > > the title Hamlet and some may have used the alternate title. Do you suggest > > adding a title field where the data would be repeated? > > > > And then within those productions are credit lists which contain people. > > Those people can have multiple names. So, in one production, Joe Schmoe may > > be credited. In another production, he may have used the name Joe S. Schmoe. > > > > The only way that I can think of achieving what you suggest is to do the > > following: > > > > PROPERTIES > > propertyid > > property title > > > > SUBPROPERTIES (alt. titles) > > propertyid (related to above) > > alt. property titles > > > > PRODUCTIONS > > productionid > > propertyid (relates to properties) > > production title (var char with full title) > > > > CREDITS LIST > > credit id > > credit variable (relating to bio table) > > credited name (var char with name) > > > > BIO TABLE > > bioid (related to credit variable) > > bioname (var char with name) > > > > BIO ALT NAMES > > bionameid > > bioname (var char with name) > > bioid (related to bio table) > > > > Is this your recommendation? It seems like a lot of info is repeated. It > > would be easier to work with because I wouldn't have to have all of these > > join statements. But it would inflate my database quite a bit. > > > > > ohh!!! this is the real scene? I was thinking about only those two tables. > anyway. I think we need to change the tables little bit. > first of all I would like to give u an idea about the main & sub property ( > and of course their search). > Just check it out if this can be nearer to what you are looking for. If yes > then we will proceed otherwise again brainstorming . > > there will be only one table for property. Which will accommodate both main > & sub property names. > > PROPERTIES > propertyid > property title > parentid > > > In this table Hamlet will have parentid 0. And The Tragedy of Hamlet, > Prince of Denmark will have the parent id which is property id of hemlet. > e.g. try this one - > INSERT INTO `mainproperty` (propertyid, title, `parentid`) VALUES > (1, 'one', 0), > (2, 'two', 1), > (3, 'three', 0), > (4, 'four', 3), > (5, 'five', 3), > (6, 'six', 1); > > Now lets check your question which u initially asked where you want to > search by the title & want to get the main & sub titles for given property. > here is the query - > SELECT m.pname as mastername, c.pname as childname > FROM mainproperty m inner join mainproperty c on > c.parent = m.pid where m.pname like '%tw%' or c.pname like '%tw%'; > (tw is string to search). > > If this seems OK then you can use same logic for the name of people where a > single person can have multiple names. > > I am trying my best to understand ur question & to answer it. > > Regards, > shahu. > > In php_mysql@yahoogroups.com <php_mysql%40yahoogroups.com>, Shahu > <shahajip@> > wrote: > > > > yes i am suggesting to place main propeabrty in one one table and its sub > > properties in another table and relate these tables with the id of main > > property. That would be much better. > > > > > > On Thu, May 20, 2010 at 11:45 PM, pirate_lenny <piratelenny@>wrote: > > > > > > > > > > > > > > > > > --- In php_mysql@yahoogroups.com <php_mysql%40yahoogroups.com><php_mysql% > 40yahoogroups.com>, Shahu > > > > <shahajip@> wrote: > > > > > > > > > > > On Thu, May 20, 2010 at 7:55 PM, pirate_lenny <piratelenny@> wrote: > > > > > > > > > > > > > > > > > > > I've got two tables that I'd like to search. Here is how I've got it > > > set > > > > > up: > > > > > > > > > > There is a property table. Each property can have multiple names, > with > > > one > > > > > name designated as the main name. So there is a property name table > as > > > well. > > > > > But I want the user to be able to search by property names and the > > > result to > > > > > display as: > > > > > > > > > > Main Property Name > > > > > a.k.a. Non-Main Property Name 1 > > > > > a.k.a. Non-Main Property Name 2 > > > > > > > > > > I'm having problems doing this without multiple queries. > > > > > > > > > > Right now, I've got one query that searches the names. Then another > > > query > > > > > that takes the found property ID and finds that property. The > property > > > is > > > > > displayed with the main property name. Then there is another query > to > > > find > > > > > the aka's. > > > > > > > > > > I have to repeat this type of thing on the search results page as > well > > > for > > > > > people and then people with multiple names. That makes 8 queries to > > > display > > > > > the search results. Plus more in the future as we add different > options > > > for > > > > > searching the site. > > > > > > > > > > Is there a better way to do this? Can someone point me in the right > > > > > direction? > > > > > > > > > > do u mean this? -- > > > > > > > > tables - > > > > CREATE TABLE `mainproperty` ( > > > > `pid` int(11) NOT NULL AUTO_INCREMENT, > > > > `pname` varchar(255) NOT NULL, > > > > PRIMARY KEY (`pid`) > > > > ) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=latin1 > > > > > > > > CREATE TABLE `subproperty` ( > > > > `subid` int(11) NOT NULL AUTO_INCREMENT, > > > > `pid` int(11) NOT NULL, > > > > `subname` varchar(255) NOT NULL, > > > > PRIMARY KEY (`subid`) > > > > ) ENGINE=MyISAM AUTO_INCREMENT=10 DEFAULT CHARSET=latin1 > > > > > > > > inserting data - > > > > > > > > INSERT INTO `mainproperty` (`pid`, `pname`) VALUES > > > > (1, 'one'), > > > > (2, 'two'), > > > > (3, 'three'), > > > > (4, 'four'); > > > > > > > > > > > > INSERT INTO `subproperty` (`subid`, `pid`, `subname`) VALUES > > > > (1, 1, 'one-one'), > > > > (2, 1, 'one-two'), > > > > (3, 1, 'one-three'), > > > > (4, 2, 'two-one'), > > > > (5, 2, 'two-two'), > > > > (6, 2, 'two-three'), > > > > (7, 3, 'three-one'), > > > > (8, 3, 'three-two'), > > > > (9, 4, 'four-one'); > > > > > > > > > > > > query - > > > > select m.pname,s.subname from mainproperty m inner join subproperty s > on > > > > m.pid=s.pid; > > > > > > > > regards, > > > > shahu > > > > > > > > > > > > > > > > > > > > > > > > > > > > > -- > > > > http://phpinterviewanswers.blogspot.com/ > > > > > > > > > > > > [Non-text portions of this message have been removed] > > > > > > > > > > Shahu - Thanks for the answer. Unfortunately, I don't think that that > will > > > work. My properties/names tables are set up like > > > > > > PROPERTIES > > > propid (int) > > > propname (int) > > > > > > PROP NAMES > > > propnameid (int) > > > propname (var char) > > > propno (int) > > > > > > propid is the primary key in Properties. Propname is the main name > assigned > > > to that property. > > > > > > propnameid is the primary key for Prop Names (and is placed in propname > to > > > indicate which name is primary). It's then displayed using a join. > > > > > > propno is used to relate all of the names to the property. > > > > > > Are you suggesting that I should be placing the main property name in > the > > > properties table? That would be easier to search? > > > > > > Jeremy > > > > > > > > > > > > > > > > > -- > > http://phpinterviewanswers.blogspot.com/ > > > > > > [Non-text portions of this message have been removed] > > > > > > > > -- > http://phpinterviewanswers.blogspot.com/ > > > [Non-text portions of this message have been removed] >
