Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    New Coder
    Join Date
    Feb 2010
    Posts
    39
    Thanks
    2
    Thanked 0 Times in 0 Posts

    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.

    Thanks for any & all help!!
    Qadoshyah

  • #2
    Regular Coder
    Join Date
    May 2009
    Location
    Moore, OK
    Posts
    282
    Thanks
    11
    Thanked 41 Times in 41 Posts
    Here is a post I wrote a while back that will get you started on Full Text Searches on Multiple fields

    http://codingforums.com/showthread.php?p=899499

    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)

  • #3
    New Coder
    Join Date
    Feb 2010
    Posts
    39
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Coyote6 View Post
    Here is a post I wrote a while back that will get you started on Full Text Searches on Multiple fields

    http://codingforums.com/showthread.php?p=899499

    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

    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.

    Qadoshyah

  • #4
    Regular Coder
    Join Date
    May 2009
    Location
    Moore, OK
    Posts
    282
    Thanks
    11
    Thanked 41 Times in 41 Posts
    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);

  • #5
    New Coder
    Join Date
    Feb 2010
    Posts
    39
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Coyote6 View Post
    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";

  • #6
    Regular Coder
    Join Date
    May 2009
    Location
    Moore, OK
    Posts
    282
    Thanks
    11
    Thanked 41 Times in 41 Posts
    Great... try this one now...
    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
    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;

  • #7
    Regular Coder
    Join Date
    May 2009
    Location
    Moore, OK
    Posts
    282
    Thanks
    11
    Thanked 41 Times in 41 Posts
    If you want to add more words to you search you need to add the asterisk (*) behind each word before putting it into the search.

  • #8
    New Coder
    Join Date
    Feb 2010
    Posts
    39
    Thanks
    2
    Thanked 0 Times in 0 Posts
    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?

    Thanks!
    Qadoshyah

  • #9
    Regular Coder
    Join Date
    May 2009
    Location
    Moore, OK
    Posts
    282
    Thanks
    11
    Thanked 41 Times in 41 Posts
    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).

  • #10
    New Coder
    Join Date
    Feb 2010
    Posts
    39
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Coyote6 View Post
    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."

    Thank you for the help!!

    Qadoshyah
    Last edited by WA; 07-14-2010 at 08:26 PM.

  • #11
    Regular Coder
    Join Date
    May 2009
    Location
    Moore, OK
    Posts
    282
    Thanks
    11
    Thanked 41 Times in 41 Posts
    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.

  • #12
    Regular Coder
    Join Date
    May 2009
    Location
    Moore, OK
    Posts
    282
    Thanks
    11
    Thanked 41 Times in 41 Posts
    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.

  • #13
    Regular Coder
    Join Date
    May 2009
    Location
    Moore, OK
    Posts
    282
    Thanks
    11
    Thanked 41 Times in 41 Posts
    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.

  • #14
    New Coder
    Join Date
    Feb 2010
    Posts
    39
    Thanks
    2
    Thanked 0 Times in 0 Posts
    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!

    Thanks so much!
    Qadoshyah
    www.countrygirlwebdesign.com

  • #15
    Regular Coder
    Join Date
    May 2009
    Location
    Moore, OK
    Posts
    282
    Thanks
    11
    Thanked 41 Times in 41 Posts
    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.

    Here is one of the sites I'm currently working on
    http://www.allamericansportsusa.com/
    But that's going through a redesign to look like this...
    http://www.allamericansportsusa.com/test


  •  
    Page 1 of 2 12 LastLast

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •