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.

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:

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

"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

    johanafm;10941129 wrote:

    And as long as you want to match against the same search pattern, why not use one match against expression instead of several?
    http://dev.mysql.com/doc/refman/5.1/en/fulltext-search.html

    That's what I ended up doing. But, now I've got to figure out the best way to use this in a search. Since, full text doesn't pull up exact results at times - like if I type in Tom, I get anything with that word in it. Not just that one person's name.

    I'm looking into VIEWs, but that is sounding confusing! I've gotta read through the tutorial more.

    Qadoshyah

      Usually it makes sense for Tom to match part of words. Let's say I'm looking for material referencing Tom, whose friends call him Tommy. If Tom only matched the stand alone word Tom, I'd miss out all the juicy stuff they've written about Tommy.

      One possible solution to circumvent the above would be to make the match against query a subquery of one using reg exp to weed out partial word matches. Reg exp matching over string matching since you probably want to allow
      'Tom.'
      'Tom,'
      'Tom,'
      and not just 'Tom '...

        Yes, I understand for it to match certain parts of words. But, I want exact matches to return. Like when I search a full name (last, then first) like "Carlin, Tom." I want just his information to pull up, not his information and every other data entry for a "Tompson" or "Tommy", etc.

        I don't quite follow the example you gave here:

        "One possible solution to circumvent the above would be to make the match against query a subquery of one using reg exp to weed out partial word matches. Reg exp matching over string matching since you probably want to allow"

        Qadoshyah

          What I mean is, take your existing query and make that a subquery of one that uses regexp to get those exact matches.

          SELECT *
FROM (
	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')";
) AS sub
	WHERE sub.Account REGEXP ' $search[ .,;:]'

          If $search contains more than one keyword, you have to split them up for the regexp with implode like this

          '(' . implode('|', $search) . ")[.,;:]'"

            Oh, I see! Thanks, I will have to see if that'll work. Thank you very much!!

              It just struck me that I made a little error in my example code. implode is to create a string from an array, but you have a string in $search. chance the implode to
              str_replace(' ', '|', $search)

                I tried that and changed the implode and this is the error message I get:

                "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'str_replace, '|', 730000778)' at line 9: SELECT * FROM ( 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('730000778') ) AS sub WHERE sub.Account REGEXP '('str_replace, '|', 730000778)"

                This is the code I put in:

                ""SELECT *
                FROM (
                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')
                ) AS sub
                WHERE sub.Account REGEXP '('str_replace, '|', $search)"; "

                Thanks!
                Qadoshyah

                  str_replace is a php function, just like implode. Which is why I used php string concatenation to build the SQL query string.

                    Write a Reply...