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...@gmail.com>wrote: > > > > > --- In php_mysql@yahoogroups.com <php_mysql%40yahoogroups.com>, Shahu > <shaha...@...> wrote: > > > > > On Thu, May 20, 2010 at 7:55 PM, pirate_lenny <piratele...@...> 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] ------------------------------------ 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/
