homepage Welcome to WebmasterWorld Guest from 54.204.94.228
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member

Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
Forum Library, Charter, Moderators: coopster & jatar k

PHP Server Side Scripting Forum

    
Search String Challenge...
Multiple SQL SELECT statement problem
alcheme

10+ Year Member



 
Msg#: 6635 posted 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

10+ Year Member



 
Msg#: 6635 posted 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

10+ Year Member



 
Msg#: 6635 posted 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

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved