Search String Challenge...

Multiple SQL SELECT statement problem

[alcheme]

This SQL SELECT statement does not work because it only shows results for the last word in a search query string (i.e. word1 word2 word3)

I learned from another website that it does this because it loops through the SQL SELECT statement one string word at a time and ends with the last string word and only shows the last string word (word3) in the results.

---------> Start

$sql= mysql_query("SELECT wname, title, keywords, description, city, country, category, url FROM directory WHERE wname LIKE '%$searches%' OR title LIKE '%$searches%' OR keywords LIKE '%$searches%' OR description LIKE '%$searches%' OR city LIKE '%$searches%' OR country LIKE '%$searches%' OR category LIKE '%$searches%' OR url LIKE '%$searches%'", $db);

<--------- END

I tried to get around this by creating multiple SQL SELECT statements but it only shows the last field (URL) in the last SQL statement below.

How can I join these SQL statements so it shows results for all fields?

----------> Start

$sql= mysql_query("SELECT wname FROM directory WHERE wname LIKE '%$searches%'", $db);
$sql= mysql_query("SELECT title FROM directory WHERE title LIKE '%$searches%'", $db);
$sql= mysql_query("SELECT keywords FROM directory WHERE keywords LIKE '%$searches%'", $db);
$sql= mysql_query("SELECT description FROM directory WHERE description LIKE '%$searches%'", $db);
$sql= mysql_query("SELECT city FROM directory WHERE city LIKE '%$searches%'", $db);
$sql= mysql_query("SELECT country FROM directory WHERE country LIKE '%$searches%'", $db);
$sql= mysql_query("SELECT category FROM directory WHERE category LIKE '%$searches%'", $db);
$sql= mysql_query("SELECT url FROM directory WHERE url LIKE '%$searches%'", $db);

<--------- END

Ideas?

[alcheme]

I tried numbering but it only shows the first field 'wname' in the search results.

/* ONLY SHOWS FIRST FIELD IN RESULTS

$sql= mysql_query("SELECT wname FROM directory WHERE wname LIKE '%$searches%'", $db);
$sql1= mysql_query("SELECT title FROM directory WHERE title LIKE '%$searches%'", $db);
$sql2= mysql_query("SELECT keywords FROM directory WHERE keywords LIKE '%$searches%'", $db);
$sql3= mysql_query("SELECT description FROM directory WHERE description LIKE '%$searches%'", $db);
$sql4= mysql_query("SELECT city FROM directory WHERE city LIKE '%$searches%'", $db);
$sql5= mysql_query("SELECT country FROM directory WHERE country LIKE '%$searches%'", $db);
$sql6= mysql_query("SELECT category FROM directory WHERE category LIKE '%$searches%'", $db);
$sql7= mysql_query("SELECT url FROM directory WHERE url LIKE '%$searches%'", $db);

**/

It could work if I had a way to combine the $sql's together as just $SQL after this statement..

Well... maybe

[alcheme]

Situation Resolved with help from an outside source. But I wanted to post it here for those who have helped me here with other PHP questions and to contribute to the knowledge base.

The Solution:

1. Created a FullText SELECT STATEMENT:

$sql= mysql_query("SELECT * FROM directory WHERE MATCH (wname,title,keywords,description,city,country,category,url) AGAINST ('" . $_POST['search'] . "')", $db); )

2. Because my table was not already a FULLTEXT index I logged onto phpmyadmin and performed the following query in the 'directory' table:

CREATE FULLTEXT INDEX fullindex ON directory (wname,title,keywords,description,city,country,cat
egory,url)

3. Tested the search form and voila it worked...

Have a great day!

~Shane