On Thu, May 20, 2010 at 7:55 PM, pirate_lenny <[email protected]> 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]
------------------------------------
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:
[email protected]
[email protected]
<*> To unsubscribe from this group, send an email to:
[email protected]
<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/
