ya definitely you can create another table for all other information (OR u can use same table for storing this info) ! And then u can relate them with ID of main property.
I hope u got my point. Regards, Shahu. On Fri, May 21, 2010 at 10:22 PM, pirate_lenny <piratele...@gmail.com>wrote: > > > 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 <php_mysql%40yahoogroups.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> <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><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] > > > > > -- 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/
