Need help & ideas on searching multiple tables - full text search or is another way??
Hi all,
I'm fairly new to PHP & MySQL, so I need some help!
I have 9 tables joined into my search. At the moment I'm using the code below to return the results.
Code:
WHERE asmnt_parcel.Account LIKE '{$search}' OR asmnt_parcel.OwnersName LIKE '{$search}' OR asmnt_parcel.ParcelID LIKE '{$search}' OR asmnt_legal.Legal LIKE '{$search}'
I have more tables that I need to be able to pull records from in a search though, so obviously the above method won't work too well. It will only let me do 4 tables with the above method and then I can't add in anything else.
So, I'm looking into full text searching.
I can full text search from one table, but when I try to add in a second table to be full text searched from, it won't work.
This query works perfect:
Code:
"SELECT appr_agland.Account, appr_agland.Acres, appr_resident.Account, appr_resident.YearBuilt
FROM appr_agland
LEFT JOIN appr_resident
ON appr_agland.Account=appr_resident.Account
WHERE MATCH (appr_agland.Account) AGAINST('$search')";
But, as soon as I try to add in the second table, kind of like this query below (note bolded part), it doesn't work:
Code:
"SELECT appr_agland.Account, appr_agland.Acres, appr_resident.Account, appr_resident.YearBuilt
FROM appr_agland
LEFT JOIN appr_resident
ON appr_agland.Account=appr_resident.Account
WHERE MATCH (appr_agland.Account) AGAINST('$search') OR MATCH (appr_resident.Account) AGAINST('$search')";
How can I make it so that I can full text search from multiple tables? Or, is there a better way to search from multiple tables?
Any & all help, is greatly appreciated! I haven't found the most efficient way to search from multiple tables.
Just add your other tables with inner joins and place their fields into the place as the other fields such as:
Code:
SELECT t.id
FROM Table1
WHERE MATCH(name, text) AGAINST ('search*');
-- Becomes
SELECT t.id
FROM Table1 as t
INNER JOIN Table2 as t2
ON (t2.table1_id=t.id)
WHERE MATCH(t.name, t.text, t2.text2) AGAINST ('search*');
The article gets pretty in depth on returning a good search result, so you may want to stop just after the first part since you are new to php and mysql. Anyways I hope this helps and doesn't confuse you... (which chances are that it will hehehe)
Just add your other tables with inner joins and place their fields into the place as the other fields such as:
Code:
SELECT t.id
FROM Table1
WHERE MATCH(name, text) AGAINST ('search*');
-- Becomes
SELECT t.id
FROM Table1 as t
INNER JOIN Table2 as t2
ON (t2.table1_id=t.id)
WHERE MATCH(t.name, t.text, t2.text2) AGAINST ('search*');
The article gets pretty in depth on returning a good search result, so you may want to stop just after the first part since you are new to php and mysql. Anyways I hope this helps and doesn't confuse you... (which chances are that it will hehehe)
Thank you! I just tried that and this is the error message I got back
Quote:
Incorrect arguments to MATCH: SELECT appr_agland.Account, appr_agland.Acres, appr_resident.Account, appr_resident.YearBuilt FROM appr_agland INNER JOIN appr_resident ON (appr_agland.Account=appr_resident.Account) WHERE MATCH (appr_agland.Account, appr_resident.Account) AGAINST('730000037')
And, this is the code I tried to use:
Code:
"SELECT appr_agland.Account, appr_agland.Acres, appr_resident.Account, appr_resident.YearBuilt
FROM appr_agland
INNER JOIN appr_resident
ON (appr_agland.Account=appr_resident.Account)
WHERE MATCH (appr_agland.Account, appr_resident.Account) AGAINST('$search')";
Did I do something wrong?
I will take a look at the other post sometime here. I thought I'd try this first though, in case I'd get myself all confused by reading the post :P.
If appr_agland.Account=appr_resident.Account are joined on each other then there is no reason to add both to the search. Try this real quick and let me know what it does.
Code:
SELECT a.Account, a.Acres, r.Account, r.YearBuilt
FROM appr_agland as a
INNER JOIN appr_resident as r
ON (a.Account=r.Account)
WHERE MATCH(a.Account) AGAINST ('$search*' IN BOOLEAN MODE);
If appr_agland.Account=appr_resident.Account are joined on each other then there is no reason to add both to the search. Try this real quick and let me know what it does.
Code:
SELECT a.Account, a.Acres, r.Account, r.YearBuilt
FROM appr_agland as a
INNER JOIN appr_resident as r
ON (a.Account=r.Account)
WHERE MATCH(a.Account) AGAINST ('$search*' IN BOOLEAN MODE);
That worked!!!! And I was able to search with an account number directly from the appr_agland table and appr_resident table. This is awesome!
Now, I have 9 tables that need to be in this search. And, there are about 4 or 5 other search options that I need to tie into this. For example, I also need to be able to search by OwnersName and that is in a completely different table. So, do I just do a variation of what you've done above and it should work?
Thank you so much!! I've been trying to figure this out for days!
Qadoshyah
P.S. this is the search code I have right now with all the tables that I need joined into it:
Code:
"SELECT asmnt_parcel.Account, asmnt_parcel.OwnersName, asmnt_parcel.ParcelID, asmnt_parcel.Township, asmnt_parcel.Range, asmnt_parcel.Section, asmnt_parcel.LotSize, asmnt_parcel.LotSizeType, asmnt_parcel.TaxAreaCode, asmnt_parcel.TotalValue, asmnt_legal.Legal, cmn_name.Address2, cmn_name.City, cmn_name.State, cmn_name.ZipCode, asmnt_situs.Situs, appr_resident.TotalArea, appr_resident.YearBuilt, appr_miscimpr.Description, appr_miscimpr.Year, appr_miscimpr.Size, appr_miscimpr.Value, appr_commercial.CostValue, appr_commercial.BldgDescription, sale_parcel.SaleDate, sale_parcel.SalePrice, sale_parcel.InstrumentNumber
FROM asmnt_parcel
INNER JOIN asmnt_legal
ON asmnt_parcel.Account=asmnt_legal.Account
INNER JOIN cmn_name
ON asmnt_parcel.OwnersName=cmn_name.OwnersName
INNER JOIN asmnt_situs
ON asmnt_parcel.Account=asmnt_situs.Account
INNER JOIN appr_resident
ON asmnt_parcel.Account=appr_resident.Account
INNER JOIN appr_miscimpr
ON asmnt_parcel.Account=appr_miscimpr.Account
LEFT JOIN appr_commercial
ON asmnt_parcel.Account=appr_commercial.Account
LEFT JOIN sale_parcel
ON asmnt_parcel.Account=sale_parcel.Account
WHERE asmnt_parcel.Account = '{$search}' OR asmnt_parcel.OwnersName = '{$search}' OR asmnt_parcel.ParcelID = '{$search}' OR asmnt_legal.Legal = '{$search}'
ORDER BY asmnt_parcel.Account ASC";
SELECT p.Account, p.OwnersName, p.ParcelID, p.Township, p.Range, p.Section, p.LotSize, p.LotSizeType, p.TaxAreaCode, p.TotalValue, l.Legal, n.Address2, n.City, n.State, n.ZipCode, s.Situs, r.TotalArea, r.YearBuilt, m.Description, m.Year, m.Size, m.Value, c.CostValue,c.BldgDescription, sp.SaleDate, sp.SalePrice, sp.InstrumentNumber
FROM asmnt_parcel as p
INNER JOIN asmnt_legal as l
ON p.Account=l.Account
INNER JOIN cmn_name as n
ON p.OwnersName=n.OwnersName
INNER JOIN asmnt_situs as s
ON p.Account=s.Account
INNER JOIN appr_resident as r
ON p.Account=r.Account
INNER JOIN appr_miscimpr as m
ON p.Account=m.Account
LEFT JOIN appr_commercial as c
ON p.Account=c.Account
LEFT JOIN sale_parcel as sp
ON p.Account=sp.Account
WHERE MATCH(p.Account, p.OwnersName, p.ParselID, l.legal, ) AGAINST ('$search*' IN BOOLEAN MODE)
ORDER BY p.account ASC;
Thank you so much for the help!! You've been so helpful . I put in the code that you wrote back with. It is working, for the most part.
It will pull up any account number that I search by. But, if I try to search by Name, Legal or ParcelID, nothing comes up. It just keeps searching, but never pulls anything up, so I just click cancel.
I've made the OwnersName, Legal & ParcelID all full text indexes. I was supposed to do that, to be able to search from them, right? Or, should I take the full text indexes of those off?
You do not have to have them for a search IN BOOLEAN MODE, but it will be helpful if they are indexed. It's kind of hard for me to figure out why it's not working 100%... It should be retrieving some sort of result. It may have to do with the data being searched for... certain types of fields can not be searched by full text... They need to be VARCHAR or TEXT fields (if I remember right).
You do not have to have them for a search IN BOOLEAN MODE, but it will be helpful if they are indexed. It's kind of hard for me to figure out why it's not working 100%... It should be retrieving some sort of result. It may have to do with the data being searched for... certain types of fields can not be searched by full text... They need to be VARCHAR or TEXT fields (if I remember right).
They are all VARCHAR fields that I have indexed right now.
Well, I searched by a name and it did end up pulling up results. Just took a little longer than it does when searching by account number.
I searched by "Carlin, Tom" - a name which is in our DB. And it did return results, but it didn't only return "Carlin, Tom" information. It also returned anything with the word Tom in it - Tommy, Tompson, etc. You can see what I'm referring to here (this is the page I'm working on):
If you search by the name above, you should see what I'm talking about. I'm trying to search another name right now and it's just searching and searching though.
You can see how well it works when searching by just account number by using this "730015926."
If you want to get better results you need to sort by a relevancy field as mentioned in the link I sent you earlier... One of the short comings of full text searches is that it doesn't automatically sort in a good order.
Try this:
Code:
SELECT p.Account, p.OwnersName, p.ParcelID, p.Township, p.Range, p.Section, p.LotSize, p.LotSizeType, p.TaxAreaCode, p.TotalValue, l.Legal, n.Address2, n.City, n.State, n.ZipCode, s.Situs, r.TotalArea, r.YearBuilt, m.Description, m.Year, m.Size, m.Value, c.CostValue,c.BldgDescription, sp.SaleDate, sp.SalePrice, sp.InstrumentNumber, MATCH(p.Account, p.OwnersName, p.ParselID, l.legal, ) AGAINST ('$search*' IN BOOLEAN MODE) as relevance
FROM asmnt_parcel as p
INNER JOIN asmnt_legal as l
ON p.Account=l.Account
INNER JOIN cmn_name as n
ON p.OwnersName=n.OwnersName
INNER JOIN asmnt_situs as s
ON p.Account=s.Account
INNER JOIN appr_resident as r
ON p.Account=r.Account
INNER JOIN appr_miscimpr as m
ON p.Account=m.Account
LEFT JOIN appr_commercial as c
ON p.Account=c.Account
LEFT JOIN sale_parcel as sp
ON p.Account=sp.Account
WHERE MATCH(p.Account, p.OwnersName, p.ParselID, l.legal, ) AGAINST ('$search*' IN BOOLEAN MODE)
ORDER BY relevance DESC, p.account ASC;
Basically it returns it's value from the full text search as a column then you sort by the highest number of that column. Any ties will then revert to the account name. If you add in my two functions from that link it will return even better results but you performance may take a slight hit.
Hmm I don't believe the search should take as long as it does, but it seems to be pulling too many records at once... Use a limit like
Code:
SELECT p.Account, p.OwnersName, p.ParcelID, p.Township, p.Range, p.Section, p.LotSize, p.LotSizeType, p.TaxAreaCode, p.TotalValue, l.Legal, n.Address2, n.City, n.State, n.ZipCode, s.Situs, r.TotalArea, r.YearBuilt, m.Description, m.Year, m.Size, m.Value, c.CostValue,c.BldgDescription, sp.SaleDate, sp.SalePrice, sp.InstrumentNumber, MATCH(p.Account, p.OwnersName, p.ParselID, l.legal, ) AGAINST ('$search*' IN BOOLEAN MODE) as relevance
FROM asmnt_parcel as p
INNER JOIN asmnt_legal as l
ON p.Account=l.Account
INNER JOIN cmn_name as n
ON p.OwnersName=n.OwnersName
INNER JOIN asmnt_situs as s
ON p.Account=s.Account
INNER JOIN appr_resident as r
ON p.Account=r.Account
INNER JOIN appr_miscimpr as m
ON p.Account=m.Account
LEFT JOIN appr_commercial as c
ON p.Account=c.Account
LEFT JOIN sale_parcel as sp
ON p.Account=sp.Account
WHERE MATCH(p.Account, p.OwnersName, p.ParselID, l.legal, ) AGAINST ('$search*' IN BOOLEAN MODE)
ORDER BY relevance DESC, p.account ASC
LIMIT 0, 10;
Then use a variable in the url to select which page you are on to get that pages results. It will be way faster.
Haha funny side note... I see you're working in Oklahoma. I'm from Oklahoma City. I just moved out to California last year in April. I still run my web design company back home and will probably be doing that full time later this summer if things go right.
Thanks again!! That worked to help reduce the results that were returned. It's not quite as slow as it was, but it's still slightly slow. But, it's still returning anything with let's say "Tom" in it or "Howard" in it. Not, just that specific name.
How do I fix that? Or, is that something that just has to be put up with in full text searching? I have to take a look at that link you gave still and hope to in just a bit.
Haha, that's funny! I actually moved from California in the summer of 2008 . Lived there for 19 years!
Ya that's one of the issues with full text search and that I worked around. If you are wanting to retrieve exact results every time create a view and then query that view for exact values. Don't use full text or like.
That's too funny. http://coyote6graphx.com or http://coyote6.com are both my sites... They are the exact same (It's just forwarded for the time being). Not much on there cause I'm working on other's sites and mine hasn't been updated for a while.
Before you post, read our: 
. I put in the code that you wrote back with. It is working, for the most part. 
. Lived there for 19 years!
