Having probs with fulltext search query

Dev Articles Community Forums Sponsor:

January 21st, 2003, 03:32 PM

funkyM

Junior Member

Join Date: Jan 2003

Location: Australia

Posts: 13

Time spent in forums: < 1 sec
Reputation Power: 0

Having probs with fulltext search query

I have been reading the tutorial by Mitchell Harper on this site

Getting Started With MySQL's Full-Text Search Capabilities

I have given it a go and have come up trumps. I keep getting a mysql error message. But if I do a query on the table in phpMyAdmin it works great. Any help would be appreciated.

Here's the link

http://www.tnmarketing.com.au/fulltext/

Here's the html

<html>
<body>
<form method=post action=biz_listing_display.php>
<input type="text" size="20" name="keysearch">
<input type="submit" value=SEARCH>

</form>

</body>
</html>

Here's the php that processes it

<?

$database = "tnm";
$user = "tnm";
$pass ="booger";

@ $db = mysql_pconnect("localhost", "$user", "$pass");

if (!$db)
{
echo "Error: Could not connect to database. Please try again later.";
exit;
}

$query = "select firstName from testTable where match(firstName, lastName, details) against('ben')";
$result = mysql_query($query);
$num_results = mysql_num_rows($result);

if(!$num_results == "0")
{

echo "";
echo "No. of results found: ".$num_results;
echo "";

for ($i=0; $i <$num_results; $i++)
{

$row = mysql_fetch_array($result);

echo "First Name: ".stripslashes($row[firstName])." ";
echo "Last Name: ".stripslashes($row[lastName])." ";
echo "Details: ".stripslashes($row[details])." ";
echo "";
}

}
else
{
echo "<center>SORRY, NO MATCHING ENTRIES FOUND TRY SEARCHING AGAIN!</ceter>";
}
?>

Last edited by funkyM : January 21st, 2003 at 03:52 PM.

January 21st, 2003, 06:33 PM

FrankieShakes

Frank The Tank!

Dev Articles Beginner (1000 - 1499 posts)

Join Date: Jun 2002

Location: Toronto, Canada

Posts: 1,246

Time spent in forums: < 1 sec
Reputation Power: 8

Re: Having probs with fulltext search query

Quote:

Originally posted by funkyM
if(!$num_results == "0")

Try this instead:

PHP Code:


		
			
if ($num_results != 0)

You can keep it the way you had it, just remove the double quotes around the 0... You're treating it as a String. I would, however, use the method above, as it's easier to read.

Hope that helps!

__________________
____________________________________________
Developer Shed Weekly Writer | DevArticles Forum Moderator
Build Your Own KlipFolio Klip With PHP
FrankManno.com - Under Construction
Design Interactive Group - Under Construction

January 21st, 2003, 06:53 PM

funkyM

Junior Member

Join Date: Jan 2003

Location: Australia

Posts: 13

Time spent in forums: < 1 sec
Reputation Power: 0

no go

Thanks for the hint I tried doing what you said but still no go I am getting the smae error

Regards

funky

January 21st, 2003, 09:25 PM

Jeb.

"l33t? What's l33t?"

Join Date: Aug 2002

Posts: 51

Time spent in forums: < 1 sec
Reputation Power: 7

$result = mysql_query($query)

Because you're getting an invalid result error, it means your query isn't perfoming correctly. To find out what's up with your query, change that above line to this:

PHP Code:


		
			
 $result = mysql_query($query) or die(mysql_error());

That should allow you to correct whatever is wrong.

-J

__________________
Jeb.

AIM: JebediahMc - PM Me - E-Mail Me

January 21st, 2003, 10:10 PM

funkyM

Junior Member

Join Date: Jan 2003

Location: Australia

Posts: 13

Time spent in forums: < 1 sec
Reputation Power: 0

Problem exposed

Thankyou to all who have helped me especially you Jeb your little bit of error handling has mede me see the light.

Somehow I had forgotten the mysql_select_db() function and that's what was causing the problem.

Thankyou very much sometimes no matter how hard you look you just can't see the answer.

Kind regards to all

FunkyM

January 21st, 2003, 10:32 PM

funkyM

Junior Member

Join Date: Jan 2003

Location: Australia

Posts: 13

Time spent in forums: < 1 sec
Reputation Power: 0

Next Problem

Kind of got it working

except that it is now only displaying the business name only

to test it for yourself go to

http://www.tnmarketing.com.au and search for "total" go down the bottom of the page and do a search it's right next to the weather and you'll see what I mean.

Also just found that if I search for anything else I get an error

see code below

<?
$database = "";
$user = "";
$pass ="";

@ $db = mysql_pconnect("localhost", "$user", "$pass");

if (!$db)
{
echo "Error: Could not connect to database. Please try again later.";
exit;
}

mysql_select_db($database);
$query = "select biz_name, description, address, phone, fax, email, url from biz_listing where match biz_name, description against $keyword_search";
$result = mysql_query($query) or die(mysql_error());
$num_results = mysql_num_rows($result);

if($num_results != 0)
{

echo "";
echo "No. of results found: ".$num_results;
echo "";

for ($i=0; $i <$num_results; $i++)
{

$row = mysql_fetch_array($result);

$listing_no = ($i + 1);
echo "<big>$listing_no.</big> ";
echo "Name: ".stripslashes($row[biz_name])." ";
echo "Description: ".stripslashes($row[description])." ";
echo "Address: ".stripslashes($row[address])." ";
echo "Phone: ".stripslashes($row[phone]);
echo " Fax: ";

if(stripslashes($row[fax]) == "")
echo "N/A ";
else
echo stripslashes($row[fax]);

echo " Email: ";
if(stripslashes($row[email]) == "")
echo "N/A   ";
else
echo "<a href=mailto:".stripslashes($row[email]).">".stripslashes($row[email])."</a> ";

echo " URL: ";
if(stripslashes($row[url]) == "")
echo "No URL Available ";
else
echo "<a href=http://".stripslashes($row[url]).">".stripslashes($row[url])."</a>";

echo "";
}

}
else
{
echo "<center>SORRY, NO MATCHING ENTRIES FOUND TRY SEARCHING AGAIN!</ceter>";
}

?>

Last edited by funkyM : January 21st, 2003 at 10:40 PM.

January 21st, 2003, 11:35 PM

Jeb.

"l33t? What's l33t?"

Join Date: Aug 2002

Posts: 51

Time spent in forums: < 1 sec
Reputation Power: 7

Re: Next Problem

Quote:

Originally posted by funkyM
Also just found that if I search for anything else I get an error

I haven't done anything with FT Searching before, so I can't answer your first question. However, I can throw a guess at the 2nd.

It appears that when doing a full text search, if no matches are found, MySQL doesn't return a standard 0 rows as it would with any other select query. (Don't quote me on that

)

What you might want to do is add the error-supressing operator (@) on the beginning of your mysql_num_rows() function. That will remove your error - but be careful, it'll also hide any other errors you get with that function!

January 21st, 2003, 11:43 PM

funkyM

Junior Member

Join Date: Jan 2003

Location: Australia

Posts: 13

Time spent in forums: < 1 sec
Reputation Power: 0

No change still doing it

It made no difference damn it. I hate when this happens it is very frustrating.

January 22nd, 2003, 01:23 AM

Jeb.

"l33t? What's l33t?"

Join Date: Aug 2002

Posts: 51

Time spent in forums: < 1 sec
Reputation Power: 7

$num_results = mysql_num_rows($result);

To

$num_results = @mysql_num_rows($result);

And that still gives you the mysql_num_rows() error? That can't be right.

#10

January 22nd, 2003, 02:59 PM

funkyM

Junior Member

Join Date: Jan 2003

Location: Australia

Posts: 13

Time spent in forums: < 1 sec
Reputation Power: 0

My mistake

OK I added the @ and it now works except that it is only printing the name of the business.

http://www.tnmarketing.com.au

search for: total boulevarde

and you'll see

by the way if you can help me sort this out I'll send you a bottle of nice red. If you drink wine that is.

New Code:

<?
$database = "";
$user = "";
$pass ="";

@ $db = mysql_pconnect("localhost", "$user", "$pass");

if (!$db)
{
echo "Error: Could not connect to database. Please try again later.";
exit;
}

mysql_select_db($database);

// $query = "select biz_listing where match biz_name, description against $keyword_search as relevance from test table";

$query = "select biz_name, description match biz_name, description against $keyword_search as relevance from testTable where match biz_name, description against $keyword_search";

$result = mysql_query($query) or die(mysql_error());
$num_results = @mysql_num_rows($result);

if($num_results != 0)
{

echo "";
echo "No. of results found: ".$num_results;
echo "";

for ($i=0; $i <$num_results; $i++)
{

$row = mysql_fetch_array($result);

$listing_no = ($i + 1);
echo "<big>$listing_no.</big> ";
echo "Name: ".stripslashes($row[biz_name])." ";
echo "Description: ".stripslashes($row[description])." ";
echo "Address: ".stripslashes($row[address])." ";
echo "Phone: ".stripslashes($row[phone]);
echo " Fax: ";

if(stripslashes($row[fax]) == "")
echo "N/A ";
else
echo stripslashes($row[fax]);

echo " Email: ";
if(stripslashes($row[email]) == "")
echo "N/A   ";
else
echo "<a href=mailto:".stripslashes($row[email]).">".stripslashes($row[email])."</a> ";

echo " URL: ";
if(stripslashes($row[url]) == "")
echo "No URL Available ";
else
echo "<a href=http://".stripslashes($row[url]).">".stripslashes($row[url])."</a>";

echo "";
}

}
else
{
echo "<center>SORRY, NO MATCHING ENTRIES FOUND TRY SEARCHING AGAIN!</ceter>";
}

?>
Funky

Last edited by funkyM : January 22nd, 2003 at 03:33 PM.

#11

January 22nd, 2003, 07:03 PM

Jeb.

"l33t? What's l33t?"

Join Date: Aug 2002

Posts: 51

Time spent in forums: < 1 sec
Reputation Power: 7

Ok, I've done a bit of reading on FullText searching, and here's what I'm thinking:

Code:

select biz_name, description match biz_name, description against $keyword_search as relevance from testTable where match biz_name, description against $keyword_search

Perhaps you could try and write it syntactically correct, and see if that makes any difference:

Code:

SELECT biz_name, description FROM textTable WHERE MATCH(biz_name,description) AGAINST('$keyword_search')

See if that works as expected.

#12

January 22nd, 2003, 07:47 PM

funkyM

Junior Member

Join Date: Jan 2003

Location: Australia

Posts: 13

Time spent in forums: < 1 sec
Reputation Power: 0

same error

when I tried that it gave me the same MYSQL error as usual

#13

January 22nd, 2003, 11:50 PM

Jeb.

"l33t? What's l33t?"

Join Date: Aug 2002

Posts: 51

Time spent in forums: < 1 sec
Reputation Power: 7

echo() the value of $result and see if it's actually a resource ID or not.

#14

January 23rd, 2003, 12:03 AM

funkyM

Junior Member

Join Date: Jan 2003

Location: Australia

Posts: 13

Time spent in forums: < 1 sec
Reputation Power: 0

how

how do I do that

echo $result;

wright or wrong

#15

January 23rd, 2003, 01:19 AM

Jeb.

"l33t? What's l33t?"

Join Date: Aug 2002

Posts: 51

Time spent in forums: < 1 sec
Reputation Power: 7

That's correct. While you're at it, use print_r($row) to print the value of the $row array from MySQL.

Then try searching for total boulevard and see what it says.