Creating an INDEX on multiple tables?

------=_NextPart_000_0003_01CB70DA.9008FA40
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

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. =20

SELECT firstname, lastname,comments FROM users WHERE =
MATCH(firstname,lastname,comments) AGAINST ('$searchterm')

Ron
------=_NextPart_000_0003_01CB70DA.9008FA40--

Re: 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 wrot=
e:
> Is it possible to create one index on multiple tables? =A0I am trying to =
create a search function for my web site. =A0The data the user needs to be =
able to search is stored in multiple tables. =A0I 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,last=
name,comments) AGAINST ('$searchterm')
>
> Ron

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Creating an INDEX on multiple tables?

Alternatively, you could try setting that index on each table and use a UNIO=
N to join multiple queries together provided each query returns the identica=
l 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 wrote:

> I dont think you can create such index across tables.
>=20
> If you are interested read up on sphinx. Im pretty sure you would be
> able to create what you need.
>=20
> 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)
>=20
> It would be cool if a fulltext index could be created on a view :)
>=20
> Art
>=20
> On 21 October 2010 09:43, Ron Piggott wro=
te:
>> Is it possible to create one index on multiple tables? I am trying to cr=
eate a search function for my web site. The data the user needs to be able t=
o search is stored in multiple tables. I would like to be able to use "MATC=
H / AGAINST", like the query below I found online.
>>=20
>> SELECT firstname, lastname,comments FROM users WHERE MATCH(firstname,last=
name,comments) AGAINST ('$searchterm')
>>=20
>> Ron
>=20
> --=20
> PHP Database Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>=20

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: 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
> 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
>> 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: 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 th=
at is there to aid in selecting data.=20

Bastien Koert
905-904-0334
Sent from my iPhone

On 2010-10-21, at 7:24 AM, Karl DeSaulniers 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
>=20
> Karl
>=20
> On Oct 21, 2010, at 6:12 AM, Bastien wrote:
>=20
>> Alternatively, you could try setting that index on each table and use a U=
NION to join multiple queries together provided each query returns the ident=
ical data set structures ( or the column types have to match)
>>=20
>> Bastien Koert
>> 905-904-0334
>> Sent from my iPhone
>>=20
>> On 2010-10-21, at 7:06 AM, Artur Ejsmont wrote:=

>>=20
>>> I dont think you can create such index across tables.
>>>=20
>>> If you are interested read up on sphinx. Im pretty sure you would be
>>> able to create what you need.
>>>=20
>>> 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 i=
n sync)
>>>=20
>>> It would be cool if a fulltext index could be created on a view :)
>>>=20
>>> Art
>>>=20
>>> On 21 October 2010 09:43, Ron Piggott w=
rote:
>>>> Is it possible to create one index on multiple tables? I am trying to c=
reate 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 "MA=
TCH / AGAINST", like the query below I found online.
>>>>=20
>>>> SELECT firstname, lastname,comments FROM users WHERE MATCH(firstname,la=
stname,comments) AGAINST ('$searchterm')
>>>>=20
>>>> Ron
>>>=20
>>> --=20
>>> PHP Database Mailing List (http://www.php.net/)
>>> To unsubscribe, visit: http://www.php.net/unsub.php
>>>=20
>>=20
>> --
>> PHP Database Mailing List (http://www.php.net/)
>> To unsubscribe, visit: http://www.php.net/unsub.php
>>=20
>=20
> Karl DeSaulniers
> Design Drumm
> http://designdrumm.com
>=20
>=20
> --=20
> PHP Database Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>=20

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: 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
> 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
>>> 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
>>>> 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: Creating an INDEX on multiple tables?

--20cf300fb1ef68ed380493220925
Content-Type: text/plain; charset=ISO-8859-1

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 wrote:

> 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 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
>>>> 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
>>>>> 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
>
>

--20cf300fb1ef68ed380493220925--

Re: 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_refere nce` ) 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`,`pr ovince_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`,`employm ent_opportunities`,`members_of`,`major_events`,`assoiciate_m ember_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_referenc e`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1568 ;

Ron

--------------------------------------------------
From: "Bastien"
Sent: Thursday, October 21, 2010 7:12 AM
To: "Artur Ejsmont"
Cc: "Ron Piggott" ;
Subject: 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 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
>> 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: 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_refere nce` )
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: Creating an INDEX on multiple tables?

On 2010-10-21, at 7:38 PM, "Ron Piggott" wr=
ote:

> I am still struggling with this query still although I have made great pro=
gress. The following query (below) executes successfully.
>=20
> 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`
>=20
> The bare minimum for a listing is only a record in he table `ministry_prof=
iles`
>=20
> Is there a way to modify this query to accommodate only a record in the ta=
ble `ministry_profiles`
>=20
> Ron?
>=20
> SELECT `ministry_profiles`.`reference`, `ministry_profiles`.`organization`=
FROM
> (
> (
> ( `ministry_profiles` INNER JOIN `ministry_profiles_listing_details` ON `m=
inistry_profiles`.`reference` =3D `ministry_profiles_listing_details`.`minis=
try_profile_reference` )
> INNER JOIN `ministry_profiles_activity` ON `ministry_profiles`.`reference`=
=3D `ministry_profiles_activity`.`ministry_profiles_reference` )
> INNER JOIN `ministry_profile_categories` ON `ministry_profiles`.`reference=
` =3D `ministry_profile_categories`.`ministry_profiles_reference` )
> INNER JOIN `ministry_categories` ON `ministry_profile_categories`.`ministr=
y_categories_reference` =3D `ministry_categories`.`reference`
> WHERE
> MATCH( `ministry_profiles`.`organization`, `ministry_profiles`.`address_li=
ne_1`, `ministry_profiles`.`address_line_2`, `ministry_profiles`.`city`, `mi=
nistry_profiles`.`province_state`, `ministry_profiles`.`postal_zip_code`, `m=
inistry_profiles`.`country`, `ministry_profiles`.`telephone`, `ministry_prof=
iles`.`toll_free`, `ministry_profiles`.`fax`, `ministry_profiles`.`email`, `=
ministry_profiles`.`website` )
> AGAINST
> ('$search')=20

Ron,

Try using a left outer join from the table that has records. The inner join w=
on't return any rows if one record in one table is null. A left outer join w=
ill take all records from the left (first) table regardless of data being nu=
ll 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: 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"
Sent: Thursday, October 21, 2010 8:03 PM
To: "Ron Piggott"
Cc: "Artur Ejsmont" ;
Subject: Re: [PHP-DB] Creating an INDEX on multiple tables?

>
>
> On 2010-10-21, at 7:38 PM, "Ron Piggott"
> 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_refere nce` )
>> 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: 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_refere nce` )
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: 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: 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_refere nce` )
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_refere nce` )
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_refere nce` )
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_refere nce` )
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_opportunitie s`,
`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: 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

Re: Creating an INDEX on multiple tables?

I have moved the GROUP BY outside now Chris.

In the queries that make up the UNION ALL (such as below) is there a way to
only SELECT matches that are 70% or greater based on the users input? Right
now each and every listing is a search result.

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_refere nce` )
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




--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Creating an INDEX on multiple tables?

On 22/10/10 21:00, Ron Piggott wrote:
> I have moved the GROUP BY outside now Chris.
>
> In the queries that make up the UNION ALL (such as below) is there a way
> to only SELECT matches that are 70% or greater based on the users input?
> Right now each and every listing is a search result.

http://dev.mysql.com/doc/refman/5.0/en/fulltext-natural-lang uage.html

has info about doing that sort of thing.

--
Postgresql & php tutorials
http://www.designmagick.com/


--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php