[sumogray]

hi all, ive had this problem for a few days now and cant solve it, ive tried various searches but come up with nothing... im trying to search multiple fields with one search term (names and addresses) so if i have the following record:

name: bill gates
company: microsoft
street: 1 microsoft way
town: sometown
county: somecounty
postcode: lkfggj1

.. i can search for "gate" and the script would look through name, company, street, town, etc and if it found "gate" would display the full record... Im using the following script but i get no results no matter what search terms i use...

PHP Code:

<?



include ("includes/connect.php");

$getContent = mysql_query("SELECT * FROM clients,contacts 

                            WHERE (clients.client_id = contacts.client_id) AND

                            company_name LIKE '%$dosearch%' OR

                            company_name IS NULL OR

                            street LIKE '%$dosearch%' OR

                            street IS NULL OR

                            town LIKE '%$dosearch%' OR

                            town IS NULL OR

                            postcode LIKE '%$dosearch%' OR

                            postcode IS NULL

                            ") or

    die (mysql_error());

    while ($row = mysql_fetch_array($getContent)){

        echo $row['contact_name'];

        echo "<br />";

        echo $row['company_name'];

        echo "<br />";

        echo $row['street'];

        echo "<br />";

        echo $row['town'];

        echo "<br />";

        echo $row['postcode'];

        echo "<br />";

        echo $row['contact_directline'];

        echo "<br />";

        echo $row['contact_mobile'];

        echo "<br /><br /><p>______________________________________________</p>";



} 

?>

I'm setting $dosearch in the form using method="get"

thanks for any help!
gray

[sufyan]

See if this works:

PHP Code:

SELECT * FROM clients,contacts WHERE (clients.client_id = contacts.client_id) AND (contacts.company_name LIKE '%$dosearch%' OR contacts.street LIKE '%$dosearch%' OR contacts.town LIKE '%$dosearch%' OR contacts.postcode LIKE '%$dosearch%') 

An alternative is to use MySQL's Full-Text Search Functions: http://dev.mysql.com/doc/mysql/en/Fulltext_Search.html

[sumogray]

ok cool thanks, ive converted to fulltext, and i think the problem must be something to do with the way im displaying the results, cos its still just a blank page.... cant see whats wrong though

[sufyan]

Interesting... I can't see what's wrong with it either...

The pervious query I gave should work, and your output code looks ok.

Maybe if you posted some sample data (SQL dump) I could test it out for you.

[sumogray]

i think the problem lies in the fact im searching 2 seperate tables.. i changed the search to one table and got results.

is there anything to consider when doing fulltext searches on two tables?

thanks for your help!

'clients' holds company names and addresses
'contacts' holds peoples names and telephone numbers

[blaw]

Hello,

So what does your SQL statement look like now? Are you using MATCH...AGAINST... or something else? How do you join two tables? I see you are using INNER JOIN, and in that case, are you aware that you will get no rows returned if the corresponding table does not have entries? (i.e. if clients table does not have related records in contacts table, it will not come up, even if it matches your search terms)

Thought it was an interesting question. If you already solved this, would you care to post what was wrong?

Cheers. =)

[sumogray]

The only way i could figure to do it was to split the searches... so i now have 2 seperate searches, one by person one by company - i had to also resort back to LIKE statements, because i need to use wildcards, and i dont think you can using FULLTEXT

Code:

SELECT * FROM contacts WHERE (contact_name) LIKE ('%$keyword%') OR
							(department) LIKE ('%$keyword%') OR
							(job_title) LIKE ('%$keyword%')