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

The problem with an OR condition is that the DB has to evaluate everything.
So, as it must parse the whole tables, it skips the indexes, and goes in a table scan.

As you have noticed, table scan are baaaaaaad.

Try to use an union rather than OR:

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')
    UNION
    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_resident.Account) AGAINST('$search')
) AS global

and extend the union with your tables as needed.
Each query in the UNION is evaluated independently, and their results are associated.
For that, each query must have the same number of columns in the select, and they must be of the same types.
You cannot have a query that have a varchar filed in the second position, and a timestamp in another and use a union between the two.

A simple "UNION" merge duplicates. If you want to have them, use a "UNION ALL" keyword.
Just a side node, some DB transform implicitly an LEFT JOIN into a LEFT OUTER JOIN, which can be very costly if the table grows.
I personally likes to specify that I want an INNER JOIN or an LEFT INNER JOIN or a LEFT OUTER JOIN.
At least I'm certain of what the db does.

Now, about the query up there, it's surely is not optimal, but it should be much better than using OR in the WHERE clause.
Give it a try...

Thank you! I will have to give that a try!

Qadoshyah

I'm not able to get that code to work at all, for some reason. I tried taking out a few things, making sure everything was written right and nothing will pull up. Not even the search page, it's just a blank page.

Not sure why.

Qadoshyah

Without an error message, there is not much we can do.
Did you enabled error reporting ?

PHP Code:

error_reporting(E_ALL); 


And error display ?

PHP Code:

ini_set('display_errors',true); 


Yeah, I have the code to show error messages in my script. The page isn't even loading. It's just showing up as a white page. Like something is wrong with the code. I haven't looked at it super close yet to see for sure, but I didn't see anything right off the top.

Qadoshyah

Then it means that either you are hiding the error message, or that you reporting level is too low.

Check that please.
Without those informations, nothing I can do.