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. --- In php_mysql@yahoogroups.com, Shahu <shaha...@...> wrote: > > yes i am suggesting to place main property 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 <piratele...@...>wrote: > > > > > > > > > > > --- In php_mysql@yahoogroups.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] >
