Search string only searches last word in string

alcheme

Hello,

The MySQL search string only searches last word in search string.

i.e.: search string: (john smith chicago)

ONLY shows the search results for the word chicago from the city column of the table directory.

-------------> Begin Code

include("inc/*****.php");

if(isset($POST['search']) &&!empty($POST['search'])) {

mysql_connect() or die ("Problem connecting to Database");

$search = trim($_POST['search']);

$search = explode(" ",$search);
foreach($search as $searches) {

$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 Code

~Any ideas on how to get it to return search results for all words in the string?

devinemke

it only searches the last keyword because you are running a loop that keeps overwriting the result returned. thus, only the last iteration of the loop is used.

you should really look at Full-Text Searching.

alcheme

I tried writing FULL TEXT SEARCHES many times but they have not worked.

I would like to know how to do it right... My Current Full Text that does not work.

--- >BEGIN FULL TEXT

include("inc/db.inc.php");

if(isset($POST['search']) && !empty($POST['search'])) {

mysql_connect() or die ("Problem connecting to Database");

$search = trim($_POST['search']);
$search = explode(" ",$search);
foreach($search as $searches) {

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

--- >END FULL TEXT

AM I writing the FULL-Text search wrong?

~Shane

devinemke

the point is you do not need the loop to do this.

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

tomhath

devinemke told you what's wrong. Variable $sql is overwritten every time through the loop.

Presumably (although you didn't post it) you use whatever value it had when the FOR loop ends in a call to mysql_query; try printing $sql out at that point.

alcheme

Originally posted by devinemke
the point is you do not need the loop to do this.
$sql= mysql_query("SELECT * WHERE MATCH (wname,title,keywords,description,city,country,category,url) AGAINST ('" . $_POST['search'] . "') FROM directory", $db);
[/B]

Understood. So this is what I tried but it came back with these errors:

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /home/deep1/public_html/search_results.php on line 234

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/deep1/public_html/search_results.php on line 243

Warning: mysql_free_result(): supplied argument is not a valid MySQL result resource in /home/deep1/public_html/search_results.php on line 264

--- >BEGIN FULL TEXT

include("inc/db.inc.php");

if(isset($POST['search']) && !empty($POST['search'])) {

mysql_connect() or die ("Problem connecting to Database");

$search = trim($_POST['search']);

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

[LINE 234] $num_rows = mysql_num_rows($sql); // Number of search results

if  ($search) 
{ 
mysql_connect() or die ("Problem connecting to Database");
echo "<b>&nbsp;&nbsp;Found <u>".$num_rows."</u> results to match search string: ".$search."</b><br><br>";
echo "<table width=100% border=0 cellpadding=0 cellspacing=5 class=border_playlist_table><tr>";     

    $counter = 0;

[LINE 243] while ($myrow = mysql_fetch_array($sql)){

$wname = $myrow["wname"];
$url = $myrow["url"];
$title = $myrow["title"];
$city = $myrow["city"];
$country = $myrow["country"];
$category = $myrow["category"];
$keywords = $myrow["keywords"];
$description = $myrow["description"];

              echo "<tr class=sub_1><td colspan=4>".++$counter." $wname : $category</td></tr>
                    <tr bgcolor=#55556F>
					    <td><p><a href=$url target=_blank>$url</a></p></td> 
                        <td><p>$city</p></td> 
                        <td><p>$country</p></td></tr>
					<tr bgcolor=#55556F><td colspan=4>$keywords</td></tr>
					<tr bgcolor=#55556F><td colspan=4>$description</td></tr>
				    <tr><td colspan=4>&nbsp;</td></tr>";
    }

[LINE 264] mysql_free_result ($sql); // end while
echo "</tr></table>";
} else { echo "problems...."; }
} else {

echo "  Search form is empty. Go back and type a new search.<br><br>  <a href=\"javascript:history.go(-1);\">(click here to try again.)</a>";
}
?>

<--------- END

devinemke

use [man]mysql_error[/man] to see what's going on. a blind guess would be that you forgot to create the full-text index on those columns.

alcheme

If I was using phpmyadmin, do you mean like:

description (LONGTEXT)

~Shane

devinemke

Originally posted by alcheme
If I was using phpmyadmin, do you mean like:

description (LONGTEXT)

as i said, use [man]mysql_error[/man] to see exactly what mySQL is telling you what the problem is (basic debugging).

alcheme

This is what it said:

echo mysql_error ($db);

You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE MATCH (wname,title,keywords,description,city,country,cat

It cut off there....

~Shane

tsinka

Hi,

replace

SELECT * WHERE

with

SELECT * FROM directory WHERE

Some info about full-text searches:
http://dev.mysql.com/doc/mysql/en/fulltext-search.html

Thomas

alcheme

Originally posted by tsinka
Hi,

replace

SELECT * WHERE

with

SELECT * FROM directory WHERE

Thomas

Thank you thomas.

I tried that and also noticed a space between 'ca' and 'tegory'. Anyway here is the result:

Can't find FULLTEXT index matching the column list

----------> BEGIN SELECT statement

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

<---------- END SELECT statement

DO I need to convert the database fields to LONGTEXT as opposed to VARCHAR?

Edited... !! UPDATE !!

I still get the same errors from before to:

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /home/deep1/public_html/search_results.php on line 234

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/deep1/public_html/search_results.php on line 243

Warning: mysql_free_result(): supplied argument is not a valid MySQL result resource in /home/deep1/public_html/search_results.php on line 264

AND I tried changing all the search fields (columns) from VARCHAR to LONGTEXT and it did not help.

~Shane

tsinka

You need to create a fulltext index over all columns that are in the match column list. VARCHAR is ok.

EDIT: read the manual page about full-text searches. Besides the index problem there are some restrictions regarding full-text indexes and how/when they are used.

alcheme

Originally posted by tsinka
You need to create a fulltext index over all columns that are in the match column list. VARCHAR is ok.

EDIT: read the manual page about full-text searches. Besides the index problem there are some restrictions regarding full-text indexes and how/when they are used.

Hello,

I have read the information on FULLTEXT but I cannot find out how to create a FULLTEXT index in my table.

Does creating a FULLTEXT index mean changing the MATCH list columns to VARCHAR or LONGTEXT?

If so I have already done that and received the error in my previous post.

------- Shane

tsinka

Use either phpMyAdmin (if available at all) to create that index or execute e.g. the following query:

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

But that index can grow rather large. Keep in mind that the search query only works with search words with a length >=4 (default).

Thomas

alcheme

Originally posted by tsinka
Use either phpMyAdmin (if available at all) to create that index or execute e.g. the following query:
CREATE FULLTEXT INDEX fullindex ON directory (wname,title,keywords,description,city,country,category,url)
But that index can grow rather large. Keep in mind that the search query only works with search words with a length >=4 (default).

Thomas [/B]

Thomas, you are the man!

I have been beating myself over the head for two weeks now and finally a solution has been made with your help and all the others I have asked about this.

Thank you.

What I did:

1. Created a FullText Search:

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

2. Logged onto phpmyadmin and performed the following query in the 'directory' table:

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

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

Thank You & Thank You & Thank You Thomas (aka tsinka), devinemke, and tomhath.

~Shane

alcheme

With regards to this SQL statement.... It works great for multi-word searches but those searches have to be spelled correctly.

I have discovered two FLAWS:

1. It does not allow for partial matches: i.e. 'shane' if the search is spelled 'shan' it will not find it. I cannot figure out how to get the '%' Wildcard into this statement so it will work.

2. It will ony show results for the first column the search word(s) are found in.

For example, I type in 'shane' and I will get all listings for 'shane' only from the 'wname' column. The search query stops at the first column 'wname' in the SQL statement below and ignores that 'shane' is also located in several other columns and records.

-------> Begin Code

<?

include("inc/db.inc.php");

if(isset($POST['search']) && !empty($POST['search'])) {

mysql_connect() or die ("Problem connecting to Database");

$search = trim($_POST['search']);

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

<------- End Code

Any ideas?

% WILDCARD: These did not work:

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

NOR THESE

('%" . $_POST['search'] . "%')

%('" . $_POST['search'] . "')%

'%'('" . $_POST['search'] . "')'%'