On Fri, May 21, 2010 at 8:39 PM, pirate_lenny <piratele...@gmail.com> 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 <shaha...@...> 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 <piratele...@...>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] ------------------------------------ The php_mysql group is dedicated to learn more about the PHP/MySQL web database possibilities through group learning. Yahoo! Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/php_mysql/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/php_mysql/join (Yahoo! ID required) <*> To change settings via email: php_mysql-dig...@yahoogroups.com php_mysql-fullfeatu...@yahoogroups.com <*> To unsubscribe from this group, send an email to: php_mysql-unsubscr...@yahoogroups.com <*> Your use of Yahoo! Groups is subject to: http://docs.yahoo.com/info/terms/
