[PHP-DB] Creating an INDEX on multiple tables?
Is it possible to create one index on multiple tables? I am trying to create a
search function for my web site. The data the user needs to be able to search
is stored in multiple tables. I would like to be able to use MATCH /
AGAINST, like the query below I found online.
SELECT firstname, lastname,comments FROM users WHERE
MATCH(firstname,lastname,comments) AGAINST ('$searchterm')
Ron
Re: [PHP-DB] Creating an INDEX on multiple tables?
I dont think you can create such index across tables.
If you are interested read up on sphinx. Im pretty sure you would be
able to create what you need.
Alternatively ... a super simplistic solution . create one extra
search table with copy of the data and create index there? ;P hehehe
+ would let you do what you need
- would require a lot more IO to support the extra writes (to keep copy in sync)
It would be cool if a fulltext index could be created on a view :)
Art
On 21 October 2010 09:43, Ron Piggott ron.pigg...@actsministries.org wrote:
Is it possible to create one index on multiple tables? I am trying to create
a search function for my web site. The data the user needs to be able to
search is stored in multiple tables. I would like to be able to use MATCH /
AGAINST, like the query below I found online.
SELECT firstname, lastname,comments FROM users WHERE
MATCH(firstname,lastname,comments) AGAINST ('$searchterm')
Ron
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Creating an INDEX on multiple tables?
Alternatively, you could try setting that index on each table and use a UNION
to join multiple queries together provided each query returns the identical
data set structures ( or the column types have to match)
Bastien Koert
905-904-0334
Sent from my iPhone
On 2010-10-21, at 7:06 AM, Artur Ejsmont ejsmont.ar...@gmail.com wrote:
I dont think you can create such index across tables.
If you are interested read up on sphinx. Im pretty sure you would be
able to create what you need.
Alternatively ... a super simplistic solution . create one extra
search table with copy of the data and create index there? ;P hehehe
+ would let you do what you need
- would require a lot more IO to support the extra writes (to keep copy in
sync)
It would be cool if a fulltext index could be created on a view :)
Art
On 21 October 2010 09:43, Ron Piggott ron.pigg...@actsministries.org wrote:
Is it possible to create one index on multiple tables? I am trying to
create a search function for my web site. The data the user needs to be
able to search is stored in multiple tables. I would like to be able to use
MATCH / AGAINST, like the query below I found online.
SELECT firstname, lastname,comments FROM users WHERE
MATCH(firstname,lastname,comments) AGAINST ('$searchterm')
Ron
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Creating an INDEX on multiple tables?
Excuse me for intruding, but wouldn't this be a Foreign Key solution?
If one field is the same on all tables, can't you just set a Foreign Key
that all tables with that field get their data from?
Then you only have to query the master table??
Or an I not grasping the idea behind Foreign Keys.
I am still learning.
TIA
HTHS
Karl
On Oct 21, 2010, at 6:12 AM, Bastien wrote:
Alternatively, you could try setting that index on each table and
use a UNION to join multiple queries together provided each query
returns the identical data set structures ( or the column types
have to match)
Bastien Koert
905-904-0334
Sent from my iPhone
On 2010-10-21, at 7:06 AM, Artur Ejsmont ejsmont.ar...@gmail.com
wrote:
I dont think you can create such index across tables.
If you are interested read up on sphinx. Im pretty sure you would be
able to create what you need.
Alternatively ... a super simplistic solution . create one extra
search table with copy of the data and create index there? ;P hehehe
+ would let you do what you need
- would require a lot more IO to support the extra writes (to keep
copy in sync)
It would be cool if a fulltext index could be created on a view :)
Art
On 21 October 2010 09:43, Ron Piggott
ron.pigg...@actsministries.org wrote:
Is it possible to create one index on multiple tables? I am
trying to create a search function for my web site. The data the
user needs to be able to search is stored in multiple tables. I
would like to be able to use MATCH / AGAINST, like the query
below I found online.
SELECT firstname, lastname,comments FROM users WHERE MATCH
(firstname,lastname,comments) AGAINST ('$searchterm')
Ron
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Karl DeSaulniers
Design Drumm
http://designdrumm.com
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Creating an INDEX on multiple tables?
FKs are constraints. They really are there to ensure that the data in the FK
exists in the other table. They do not operate like a conventional index that
is there to aid in selecting data.
Bastien Koert
905-904-0334
Sent from my iPhone
On 2010-10-21, at 7:24 AM, Karl DeSaulniers k...@designdrumm.com wrote:
Excuse me for intruding, but wouldn't this be a Foreign Key solution?
If one field is the same on all tables, can't you just set a Foreign Key
that all tables with that field get their data from?
Then you only have to query the master table??
Or an I not grasping the idea behind Foreign Keys.
I am still learning.
TIA
HTHS
Karl
On Oct 21, 2010, at 6:12 AM, Bastien wrote:
Alternatively, you could try setting that index on each table and use a
UNION to join multiple queries together provided each query returns the
identical data set structures ( or the column types have to match)
Bastien Koert
905-904-0334
Sent from my iPhone
On 2010-10-21, at 7:06 AM, Artur Ejsmont ejsmont.ar...@gmail.com wrote:
I dont think you can create such index across tables.
If you are interested read up on sphinx. Im pretty sure you would be
able to create what you need.
Alternatively ... a super simplistic solution . create one extra
search table with copy of the data and create index there? ;P hehehe
+ would let you do what you need
- would require a lot more IO to support the extra writes (to keep copy in
sync)
It would be cool if a fulltext index could be created on a view :)
Art
On 21 October 2010 09:43, Ron Piggott ron.pigg...@actsministries.org
wrote:
Is it possible to create one index on multiple tables? I am trying to
create a search function for my web site. The data the user needs to be
able to search is stored in multiple tables. I would like to be able to
use MATCH / AGAINST, like the query below I found online.
SELECT firstname, lastname,comments FROM users WHERE
MATCH(firstname,lastname,comments) AGAINST ('$searchterm')
Ron
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Karl DeSaulniers
Design Drumm
http://designdrumm.com
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Creating an INDEX on multiple tables?
I see.
Thanks for the explanation Bastien.
Best,
Karl
On Oct 21, 2010, at 6:56 AM, Bastien wrote:
FKs are constraints. They really are there to ensure that the data
in the FK exists in the other table. They do not operate like a
conventional index that is there to aid in selecting data.
Bastien Koert
905-904-0334
Sent from my iPhone
On 2010-10-21, at 7:24 AM, Karl DeSaulniers k...@designdrumm.com
wrote:
Excuse me for intruding, but wouldn't this be a Foreign Key solution?
If one field is the same on all tables, can't you just set a
Foreign Key
that all tables with that field get their data from?
Then you only have to query the master table??
Or an I not grasping the idea behind Foreign Keys.
I am still learning.
TIA
HTHS
Karl
On Oct 21, 2010, at 6:12 AM, Bastien wrote:
Alternatively, you could try setting that index on each table and
use a UNION to join multiple queries together provided each query
returns the identical data set structures ( or the column types
have to match)
Bastien Koert
905-904-0334
Sent from my iPhone
On 2010-10-21, at 7:06 AM, Artur Ejsmont
ejsmont.ar...@gmail.com wrote:
I dont think you can create such index across tables.
If you are interested read up on sphinx. Im pretty sure you
would be
able to create what you need.
Alternatively ... a super simplistic solution . create one
extra
search table with copy of the data and create index there? ;P
hehehe
+ would let you do what you need
- would require a lot more IO to support the extra writes (to
keep copy in sync)
It would be cool if a fulltext index could be created on a view :)
Art
On 21 October 2010 09:43, Ron Piggott
ron.pigg...@actsministries.org wrote:
Is it possible to create one index on multiple tables? I am
trying to create a search function for my web site. The data
the user needs to be able to search is stored in multiple
tables. I would like to be able to use MATCH / AGAINST, like
the query below I found online.
SELECT firstname, lastname,comments FROM users WHERE MATCH
(firstname,lastname,comments) AGAINST ('$searchterm')
Ron
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Karl DeSaulniers
Design Drumm
http://designdrumm.com
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Karl DeSaulniers
Design Drumm
http://designdrumm.com
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Creating an INDEX on multiple tables?
Sphinx can work and is fairly easy to install. If you don't want that
create a summary temp.
a table has a foreign key to the result row and columns for your search
terms.
On Thu, Oct 21, 2010 at 5:00 AM, Karl DeSaulniers k...@designdrumm.comwrote:
I see.
Thanks for the explanation Bastien.
Best,
Karl
On Oct 21, 2010, at 6:56 AM, Bastien wrote:
FKs are constraints. They really are there to ensure that the data in the
FK exists in the other table. They do not operate like a conventional index
that is there to aid in selecting data.
Bastien Koert
905-904-0334
Sent from my iPhone
On 2010-10-21, at 7:24 AM, Karl DeSaulniers k...@designdrumm.com wrote:
Excuse me for intruding, but wouldn't this be a Foreign Key solution?
If one field is the same on all tables, can't you just set a Foreign Key
that all tables with that field get their data from?
Then you only have to query the master table??
Or an I not grasping the idea behind Foreign Keys.
I am still learning.
TIA
HTHS
Karl
On Oct 21, 2010, at 6:12 AM, Bastien wrote:
Alternatively, you could try setting that index on each table and use a
UNION to join multiple queries together provided each query returns the
identical data set structures ( or the column types have to match)
Bastien Koert
905-904-0334
Sent from my iPhone
On 2010-10-21, at 7:06 AM, Artur Ejsmont ejsmont.ar...@gmail.com
wrote:
I dont think you can create such index across tables.
If you are interested read up on sphinx. Im pretty sure you would be
able to create what you need.
Alternatively ... a super simplistic solution . create one extra
search table with copy of the data and create index there? ;P hehehe
+ would let you do what you need
- would require a lot more IO to support the extra writes (to keep copy
in sync)
It would be cool if a fulltext index could be created on a view :)
Art
On 21 October 2010 09:43, Ron Piggott ron.pigg...@actsministries.org
wrote:
Is it possible to create one index on multiple tables? I am trying to
create a search function for my web site. The data the user needs to be
able to search is stored in multiple tables. I would like to be able to
use
MATCH / AGAINST, like the query below I found online.
SELECT firstname, lastname,comments FROM users WHERE
MATCH(firstname,lastname,comments) AGAINST ('$searchterm')
Ron
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Karl DeSaulniers
Design Drumm
http://designdrumm.com
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Karl DeSaulniers
Design Drumm
http://designdrumm.com
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Creating an INDEX on multiple tables?
I have created FULLTEXT indexes on each of the tables that needs to be
searched. I have had to break each of the tables up into a maximum of 16
fields or less for the FULLTEXT index to be created (I received a mySQL
error to tell me this).
The reason I am sending this message is I wonder if you would help me with
the WHERE MATCH ( ) This is the first time I have done something like this.
Specifically I am wondering if the FULLTEXT index names may be used
(search_contact, search_details, search_activity, search_categories) or do I
need to specify each of the fields from each table?
Here is the beginning of the mySQL query with all the INNER JOIN's.
SELECT `ministry_profiles`.`reference`, `ministry_profiles`.`organization`
FROM ( ( ( `ministry_profiles` INNER JOIN
`ministry_profiles_listing_details` ON `ministry_profiles`.`reference` =
`ministry_profiles_listing_details`.`ministry_profile_reference` ) INNER
JOIN `ministry_profiles_activity` ON `ministry_profiles`.`reference` =
`ministry_profiles_activity`.`ministry_profiles_reference` ) INNER JOIN
`ministry_profile_categories` ON = `ministry_profiles`.`reference` =
`ministry_profile_categories`.`ministry_profiles_reference` ) INNER JOIN
`ministry_categories` ON
`ministry_profile_categories`.`ministry_categories_reference` =
`ministry_categories`.`reference` WHERE MATCH( ) AGAINST ('$search') AND
`ministry_profiles`.`live` =1 AND `ministry_profiles_activity`.`live` =1
The fields I need to be searched are in the FULLINDEX which is part of each
table description.
Here are the tables:
Table structure for table `ministry_profiles`
NOTE: This is the base table. The common field between this table and the
others is `ministry_profiles`.`reference`
--
CREATE TABLE IF NOT EXISTS `ministry_profiles` (
`reference` int(10) NOT NULL AUTO_INCREMENT,
`organization` varchar(250) NOT NULL,
`address_line_1` varchar(100) NOT NULL,
`address_line_2` varchar(100) NOT NULL,
`city` varchar(100) NOT NULL,
`province_state` varchar(100) NOT NULL,
`postal_zip_code` varchar(25) NOT NULL,
`country` varchar(2) NOT NULL,
`telephone` varchar(50) NOT NULL,
`toll_free` varchar(50) NOT NULL,
`fax` varchar(20) NOT NULL,
`email` varchar(250) NOT NULL,
`website` varchar(250) NOT NULL,
`live` int(1) NOT NULL DEFAULT '0',
PRIMARY KEY (`reference`),
FULLTEXT KEY `search_contact`
(`organization`,`address_line_1`,`address_line_2`,`city`,`province_state`,`postal_zip_code`,`country`,`telephone`,`toll_free`,`fax`,`email`,`website`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1199 ;
Table structure for table `ministry_profiles_listing_details`
--
CREATE TABLE IF NOT EXISTS `ministry_profiles_listing_details` (
`reference` int(10) NOT NULL AUTO_INCREMENT,
`ministry_profile_reference` int(10) NOT NULL,
`contact` varchar(100) NOT NULL,
`year_founded` varchar(10) NOT NULL,
`volunteer_opportunities` varchar(1000) NOT NULL,
`employment_opportunities` varchar(1000) NOT NULL,
`members_of` varchar(500) NOT NULL,
`major_events` varchar(1000) NOT NULL,
`assoiciate_member_of:` varchar(500) NOT NULL,
`registration_number` varchar(100) NOT NULL,
`fundraising` varchar(100) NOT NULL,
PRIMARY KEY (`reference`),
FULLTEXT KEY `search_details`
(`contact`,`year_founded`,`volunteer_opportunities`,`employment_opportunities`,`members_of`,`major_events`,`assoiciate_member_of:`,`registration_number`,`fundraising`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
Table structure for table `ministry_profiles_activity`
--
CREATE TABLE IF NOT EXISTS `ministry_profiles_activity` (
`reference` int(20) NOT NULL AUTO_INCREMENT,
`ministry_profiles_reference` int(10) NOT NULL,
`activity` varchar(1500) NOT NULL,
`live` int(1) NOT NULL DEFAULT '0',
PRIMARY KEY (`reference`),
FULLTEXT KEY `search_activity` (`activity`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
Now the listing categories. Categories are in their own table
(ministry_categories) and then links created with INNER JOINS in a second
table (ministry_profile_categories)
Table structure for table `ministry_categories`
--
CREATE TABLE IF NOT EXISTS `ministry_categories` (
`reference` int(4) NOT NULL AUTO_INCREMENT,
`category` varchar(100) NOT NULL,
PRIMARY KEY (`reference`),
UNIQUE KEY `category` (`category`),
FULLTEXT KEY `search_categories` (`category`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=320 ;
Table structure for table `ministry_profile_categories`
--
CREATE TABLE IF NOT EXISTS `ministry_profile_categories` (
`reference` int(100) NOT NULL AUTO_INCREMENT,
`ministry_profiles_reference` int(10) NOT NULL,
`ministry_categories_reference` int(4) NOT NULL,
PRIMARY KEY (`reference`),
UNIQUE KEY `ministry_profiles_activity_reference`
(`ministry_profiles_reference`,`ministry_categories_reference`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1568 ;
Ron
--
From: Bastien
Re: [PHP-DB] Creating an INDEX on multiple tables?
I am still struggling with this query still although I have made great
progress. The following query (below) executes successfully.
Right now the query returns no rows. I believe this is because there isn't
automatically a record in the following three tables.
`ministry_profiles_activity`
`ministry_profile_categories`
`ministry_profiles_listing_details`
The bare minimum for a listing is only a record in he table
`ministry_profiles`
Is there a way to modify this query to accommodate only a record in the
table `ministry_profiles`
Ron?
SELECT `ministry_profiles`.`reference`, `ministry_profiles`.`organization`
FROM
(
(
( `ministry_profiles` INNER JOIN `ministry_profiles_listing_details` ON
`ministry_profiles`.`reference` =
`ministry_profiles_listing_details`.`ministry_profile_reference` )
INNER JOIN `ministry_profiles_activity` ON `ministry_profiles`.`reference` =
`ministry_profiles_activity`.`ministry_profiles_reference` )
INNER JOIN `ministry_profile_categories` ON `ministry_profiles`.`reference`
= `ministry_profile_categories`.`ministry_profiles_reference` )
INNER JOIN `ministry_categories` ON
`ministry_profile_categories`.`ministry_categories_reference` =
`ministry_categories`.`reference`
WHERE
MATCH( `ministry_profiles`.`organization`,
`ministry_profiles`.`address_line_1`, `ministry_profiles`.`address_line_2`,
`ministry_profiles`.`city`, `ministry_profiles`.`province_state`,
`ministry_profiles`.`postal_zip_code`, `ministry_profiles`.`country`,
`ministry_profiles`.`telephone`, `ministry_profiles`.`toll_free`,
`ministry_profiles`.`fax`, `ministry_profiles`.`email`,
`ministry_profiles`.`website` )
AGAINST
('$search')
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Creating an INDEX on multiple tables?
On 2010-10-21, at 7:38 PM, Ron Piggott ron.pigg...@actsministries.org wrote:
I am still struggling with this query still although I have made great
progress. The following query (below) executes successfully.
Right now the query returns no rows. I believe this is because there isn't
automatically a record in the following three tables.
`ministry_profiles_activity`
`ministry_profile_categories`
`ministry_profiles_listing_details`
The bare minimum for a listing is only a record in he table
`ministry_profiles`
Is there a way to modify this query to accommodate only a record in the table
`ministry_profiles`
Ron?
SELECT `ministry_profiles`.`reference`, `ministry_profiles`.`organization`
FROM
(
(
( `ministry_profiles` INNER JOIN `ministry_profiles_listing_details` ON
`ministry_profiles`.`reference` =
`ministry_profiles_listing_details`.`ministry_profile_reference` )
INNER JOIN `ministry_profiles_activity` ON `ministry_profiles`.`reference` =
`ministry_profiles_activity`.`ministry_profiles_reference` )
INNER JOIN `ministry_profile_categories` ON `ministry_profiles`.`reference` =
`ministry_profile_categories`.`ministry_profiles_reference` )
INNER JOIN `ministry_categories` ON
`ministry_profile_categories`.`ministry_categories_reference` =
`ministry_categories`.`reference`
WHERE
MATCH( `ministry_profiles`.`organization`,
`ministry_profiles`.`address_line_1`, `ministry_profiles`.`address_line_2`,
`ministry_profiles`.`city`, `ministry_profiles`.`province_state`,
`ministry_profiles`.`postal_zip_code`, `ministry_profiles`.`country`,
`ministry_profiles`.`telephone`, `ministry_profiles`.`toll_free`,
`ministry_profiles`.`fax`, `ministry_profiles`.`email`,
`ministry_profiles`.`website` )
AGAINST
('$search')
Ron,
Try using a left outer join from the table that has records. The inner join
won't return any rows if one record in one table is null. A left outer join
will take all records from the left (first) table regardless of data being null
in the other rows.
Does that make sense?
Bastien
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Creating an INDEX on multiple tables?
Very much so. Thank you Bastien. I have never used an OUTER join before
and didn't know it would be required for this project. Ron
--
From: Bastien phps...@gmail.com
Sent: Thursday, October 21, 2010 8:03 PM
To: Ron Piggott ron.pigg...@actsministries.org
Cc: Artur Ejsmont ejsmont.ar...@gmail.com; php-db@lists.php.net
Subject: Re: [PHP-DB] Creating an INDEX on multiple tables?
On 2010-10-21, at 7:38 PM, Ron Piggott ron.pigg...@actsministries.org
wrote:
I am still struggling with this query still although I have made great
progress. The following query (below) executes successfully.
Right now the query returns no rows. I believe this is because there
isn't automatically a record in the following three tables.
`ministry_profiles_activity`
`ministry_profile_categories`
`ministry_profiles_listing_details`
The bare minimum for a listing is only a record in he table
`ministry_profiles`
Is there a way to modify this query to accommodate only a record in the
table `ministry_profiles`
Ron?
SELECT `ministry_profiles`.`reference`,
`ministry_profiles`.`organization` FROM
(
(
( `ministry_profiles` INNER JOIN `ministry_profiles_listing_details` ON
`ministry_profiles`.`reference` =
`ministry_profiles_listing_details`.`ministry_profile_reference` )
INNER JOIN `ministry_profiles_activity` ON
`ministry_profiles`.`reference` =
`ministry_profiles_activity`.`ministry_profiles_reference` )
INNER JOIN `ministry_profile_categories` ON
`ministry_profiles`.`reference` =
`ministry_profile_categories`.`ministry_profiles_reference` )
INNER JOIN `ministry_categories` ON
`ministry_profile_categories`.`ministry_categories_reference` =
`ministry_categories`.`reference`
WHERE
MATCH( `ministry_profiles`.`organization`,
`ministry_profiles`.`address_line_1`,
`ministry_profiles`.`address_line_2`, `ministry_profiles`.`city`,
`ministry_profiles`.`province_state`,
`ministry_profiles`.`postal_zip_code`, `ministry_profiles`.`country`,
`ministry_profiles`.`telephone`, `ministry_profiles`.`toll_free`,
`ministry_profiles`.`fax`, `ministry_profiles`.`email`,
`ministry_profiles`.`website` )
AGAINST
('$search')
Ron,
Try using a left outer join from the table that has records. The inner
join won't return any rows if one record in one table is null. A left
outer join will take all records from the left (first) table regardless of
data being null in the other rows.
Does that make sense?
Bastien=
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Creating an INDEX on multiple tables?
Bastien (and others) I am still having one problem with this query I don't
know how to resolve:
When I add the category column: `ministry_categories`.`category` as part of
the MATCH () I receive the error:
Incorrect arguments to MATCH
I create the LEFT OUTER JOINs to allow additional columns to be part of the
MATCH. I have made this category column a FULLTEXT index in the
ministry_categories table. The query that produces the error is below.
Is there a way this will work?
Ron
===
SELECT `ministry_profiles`.`reference`, `ministry_profiles`.`organization`
FROM
(
(
( `ministry_profiles` LEFT OUTER JOIN `ministry_profiles_listing_details` ON
`ministry_profiles`.`reference` =
`ministry_profiles_listing_details`.`ministry_profile_reference` )
LEFT OUTER JOIN `ministry_profiles_activity` ON
`ministry_profiles`.`reference` =
`ministry_profiles_activity`.`ministry_profiles_reference` )
LEFT OUTER JOIN `ministry_profile_categories` ON
`ministry_profiles`.`reference` =
`ministry_profile_categories`.`ministry_profiles_reference` )
LEFT OUTER JOIN `ministry_categories` ON
`ministry_profile_categories`.`ministry_categories_reference` =
`ministry_categories`.`reference`
WHERE
MATCH( `ministry_profiles`.`organization`,
`ministry_profiles`.`address_line_1`, `ministry_profiles`.`address_line_2`,
`ministry_profiles`.`city`, `ministry_profiles`.`province_state`,
`ministry_profiles`.`postal_zip_code`, `ministry_profiles`.`country`,
`ministry_profiles`.`telephone`, `ministry_profiles`.`toll_free`,
`ministry_profiles`.`fax`, `ministry_profiles`.`email`,
`ministry_profiles`.`website`, `ministry_categories`.`category` )
AGAINST
('$search')
AND
`ministry_profiles`.`live` =1
GROUP BY `ministry_profiles`.`reference`
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Creating an INDEX on multiple tables?
On 22/10/10 13:31, Ron Piggott wrote:
Bastien (and others) I am still having one problem with this query I
don't know how to resolve:
When I add the category column: `ministry_categories`.`category` as part
of the MATCH () I receive the error:
Incorrect arguments to MATCH
I create the LEFT OUTER JOINs to allow additional columns to be part of
the MATCH. I have made this category column a FULLTEXT index in the
ministry_categories table. The query that produces the error is below.
You can't do a full text match across tables.
One of the previous suggestions was to union the results, you end up
with something like
select field from t1 where match(field1, field2) against ('search term')
union all
select field from t2 where match(field3, field4) against ('search term')
etc
though I don't know if that would work (I've never tried it).
--
Postgresql php tutorials
http://www.designmagick.com/
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Creating an INDEX on multiple tables?
The query works using UNION ALL and MATCH ( ) AGAINST --- one table at a
time for the MATCH. The down side is that each SELECT is giving it's own
search results. The directory listings are being displayed multiple times
when one of the SELECTS produces it as a result. How do I limit the
results? Can I do this some how with results.reference and
results.organization (I made the UNION ALLs sub queries)
Thanks for the help.
Ron
SELECT reference, organization FROM (
SELECT `ministry_profiles`.`reference`, `ministry_profiles`.`organization`
FROM
(
(
( `ministry_profiles` LEFT OUTER JOIN `ministry_profiles_listing_details` ON
`ministry_profiles`.`reference` =
`ministry_profiles_listing_details`.`ministry_profile_reference` )
LEFT OUTER JOIN `ministry_profiles_activity` ON
`ministry_profiles`.`reference` =
`ministry_profiles_activity`.`ministry_profiles_reference` )
LEFT OUTER JOIN `ministry_profile_categories` ON
`ministry_profiles`.`reference` =
`ministry_profile_categories`.`ministry_profiles_reference` )
LEFT OUTER JOIN `ministry_categories` ON
`ministry_profile_categories`.`ministry_categories_reference` =
`ministry_categories`.`reference`
WHERE
MATCH( `ministry_profiles`.`organization`,
`ministry_profiles`.`address_line_1`, `ministry_profiles`.`address_line_2`,
`ministry_profiles`.`city`, `ministry_profiles`.`province_state`,
`ministry_profiles`.`postal_zip_code`, `ministry_profiles`.`country`,
`ministry_profiles`.`telephone`, `ministry_profiles`.`toll_free`,
`ministry_profiles`.`fax`, `ministry_profiles`.`email`,
`ministry_profiles`.`website` )
AGAINST
('$search')
AND
`ministry_profiles`.`live` =1
GROUP BY `ministry_profiles`.`reference`
UNION ALL
SELECT `ministry_profiles`.`reference`, `ministry_profiles`.`organization`
FROM
(
(
( `ministry_profiles` LEFT OUTER JOIN `ministry_profiles_listing_details` ON
`ministry_profiles`.`reference` =
`ministry_profiles_listing_details`.`ministry_profile_reference` )
LEFT OUTER JOIN `ministry_profiles_activity` ON
`ministry_profiles`.`reference` =
`ministry_profiles_activity`.`ministry_profiles_reference` )
LEFT OUTER JOIN `ministry_profile_categories` ON
`ministry_profiles`.`reference` =
`ministry_profile_categories`.`ministry_profiles_reference` )
LEFT OUTER JOIN `ministry_categories` ON
`ministry_profile_categories`.`ministry_categories_reference` =
`ministry_categories`.`reference`
WHERE
MATCH( `ministry_categories`.`category` )
AGAINST
('$search')
GROUP BY `ministry_profiles`.`reference`
UNION ALL
SELECT `ministry_profiles`.`reference`, `ministry_profiles`.`organization`
FROM
(
(
( `ministry_profiles` LEFT OUTER JOIN `ministry_profiles_listing_details` ON
`ministry_profiles`.`reference` =
`ministry_profiles_listing_details`.`ministry_profile_reference` )
LEFT OUTER JOIN `ministry_profiles_activity` ON
`ministry_profiles`.`reference` =
`ministry_profiles_activity`.`ministry_profiles_reference` )
LEFT OUTER JOIN `ministry_profile_categories` ON
`ministry_profiles`.`reference` =
`ministry_profile_categories`.`ministry_profiles_reference` )
LEFT OUTER JOIN `ministry_categories` ON
`ministry_profile_categories`.`ministry_categories_reference` =
`ministry_categories`.`reference`
WHERE
MATCH( `ministry_profiles_activity`.`activity` )
AGAINST
('$search')
AND `ministry_profiles_activity`.`live` =1
GROUP BY `ministry_profiles`.`reference`
UNION ALL
SELECT `ministry_profiles`.`reference`, `ministry_profiles`.`organization`
FROM
(
(
( `ministry_profiles` LEFT OUTER JOIN `ministry_profiles_listing_details` ON
`ministry_profiles`.`reference` =
`ministry_profiles_listing_details`.`ministry_profile_reference` )
LEFT OUTER JOIN `ministry_profiles_activity` ON
`ministry_profiles`.`reference` =
`ministry_profiles_activity`.`ministry_profiles_reference` )
LEFT OUTER JOIN `ministry_profile_categories` ON
`ministry_profiles`.`reference` =
`ministry_profile_categories`.`ministry_profiles_reference` )
LEFT OUTER JOIN `ministry_categories` ON
`ministry_profile_categories`.`ministry_categories_reference` =
`ministry_categories`.`reference`
WHERE
MATCH( `ministry_profiles_listing_details`.`contact`,
`ministry_profiles_listing_details`.`year_founded`,
`ministry_profiles_listing_details`.`volunteer_opportunities`,
`ministry_profiles_listing_details`.`employment_opportunities`,
`ministry_profiles_listing_details`.`members_of`,
`ministry_profiles_listing_details`.`major_events`,
`ministry_profiles_listing_details`.`associate_member_of`,
`ministry_profiles_listing_details`.`registration_number`,
`ministry_profiles_listing_details`.`fund_raising` )
AGAINST
('$search')
GROUP BY `ministry_profiles`.`reference`
) AS results
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Creating an INDEX on multiple tables?
On 22/10/10 15:24, Ron Piggott wrote: The query works using UNION ALL and MATCH ( ) AGAINST --- one table at a time for the MATCH. The down side is that each SELECT is giving it's own search results. The directory listings are being displayed multiple times when one of the SELECTS produces it as a result. How do I limit the results? Can I do this some how with results.reference and results.organization (I made the UNION ALLs sub queries) Get rid of the group by references inside each bit and move it to the outer: SELECT reference, organization FROM ( SELECT `ministry_profiles`.`reference`, `ministry_profiles`.`organization` FROM ... UNION ALL SELECT `ministry_profiles`.`reference`, ... ) GROUP BY reference, organization; You'll get one reference per organization. -- Postgresql php tutorials http://www.designmagick.com/ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
