| Search String Challenge...
Multiple SQL SELECT statement problem |
alcheme
msg:1270129 | 1:23 am on Jan 23, 2005 (gmt 0) |
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
msg:1270130 | 1:49 am on Jan 23, 2005 (gmt 0) |
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
msg:1270131 | 10:21 am on Jan 23, 2005 (gmt 0) |
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
|
|
|
