Page 1 of 2
results from full text help
Posted: Sun Jan 16, 2005 1:28 am
by C_Calav
hi guys,
been playing around with the full text search in mysql and got that to work. now i want to use the search on my web page. so i wrote a little script to test things out and now im a little stuck!
how do i list the results that my query brings back?
heres what i have so far:
Code: Select all
include("db.php");
if ($_SERVERї'REQUEST_METHOD'] == "POST")
{
$keyword = ($_POSTї"keyword"]);
}
$query = "SELECT P_Name, MATCH (P_Desc) AGAINST ('$keyword') FROM planes";
$result = mysql_query($query) or die (mysql_error()."<br />Couldn't execute query: $query");
$num_results = mysql_num_rows($result);
echo '<p>Number of searches found: '.$num_results.'</p>';
echo '<p>query string is: '.$query.'</p>';
echo '<p>Result is: '.$result.'</p>';
echo '<p>keyword is: '.$keyword.'</p>';
the output is: Resource id #4
just researching Resource id #4, looks like i have to add some more code.
can anyone help me with this Resource id #4 thing?
thanx!
Posted: Sun Jan 16, 2005 5:34 am
by scorphus
Take a look at these:
-- Scorphus
Posted: Sun Jan 16, 2005 11:14 am
by Cronikeys
Code: Select all
$i=0;
while ($i < $num_results) {
$P_Name=mysql_result($result,$i,"P_Name");
$P_Desc=mysql_result($result,$i,"P_Desc");
echo $P_Name." ".$P_Desc;
$i++;
}
Posted: Mon Jan 17, 2005 12:53 am
by C_Calav
thanx for your help scorphus & Cronikeys!
Cronikeys:
your code works great, but it just lists all the planes i have in my database.
how do i get just the planes that have a match for example in mysql to be displayed?
MATCH (P_Desc) AGAINST
('army')
0
0
3.0292830467224
0
2.0944106578827
here is my code so far:
Code: Select all
<?php
include("db.php");
if ($_SERVERї'REQUEST_METHOD'] == "POST")
{
$keyword = ($_POSTї"keyword"]);
}
$query = "SELECT P_Name, MATCH (P_Desc) AGAINST ('$keyword') FROM planes";
$result = mysql_query($query) or die (mysql_error()."<br />Couldn't execute query: $query");
$num_results = mysql_num_rows($result);
$row = mysql_fetch_row($result);
$i=0;
while ($i < $num_results) {
$P_Name=mysql_result($result,$i,"P_Name");
echo $P_Name." ".$P_Desc;
echo "<br />";
$i++;
}
?>
Posted: Mon Jan 17, 2005 1:26 am
by feyd
might want to use something more like:
Code: Select all
SELECT * FROM `table` WHERE
MATCH( `column` ) AGAINST ('your stuff')
the "result" returned from mysql_query is merely a pointer to mysql result data. It is not a specific result per se.
You need to use one of the fetch functions, either in or out of a loop to get data:
mysql_fetch_array()
Posted: Mon Jan 17, 2005 1:44 am
by C_Calav
thanx feyd!
changed my sql to yours and works great with my code!

Posted: Tue Jan 18, 2005 3:05 am
by C_Calav
hey guys, just adding another field to search on..
this works
Code: Select all
SELECT * FROM planes WHERE MATCH (P_Desc) AGAINST ('$keyword')
this works
Code: Select all
SELECT * FROM planes WHERE MATCH (P_Name) AGAINST ('$keyword')
but this does not work.. why is that?
Code: Select all
SELECT * FROM planes WHERE MATCH (P_Name,P_Desc) AGAINST ('$keyword')
all tried with quotes around colum feilds etc
thanx very much!
Posted: Tue Jan 18, 2005 8:04 am
by feyd
maybe it's this

Full-Text Search Functions wrote:For natural-language full-text searches, it is a requirement that the columns named in the MATCH() function be the same columns included in some FULLTEXT index in your table. For the preceding query, note that the columns named in the MATCH() function (title and body) are the same as those named in the definition of the article table's FULLTEXT index. If you wanted to search the title or body separately, you would need to create FULLTEXT indexes for each column.
Posted: Tue Jan 18, 2005 1:53 pm
by C_Calav
what does that mean?!

Posted: Tue Jan 18, 2005 2:22 pm
by feyd
if you look at the linked page, it shows an example table. The table has 2 full-text fields, both of which are declared in the same instance. Your table structure may need the same.. it's a total guess.
Posted: Tue Jan 18, 2005 2:34 pm
by C_Calav
hey feyd,
you mean
FULLTEXT (title,body)
this bit here? when they create the table?
becuase i created the table first, then added FULLTEXT after by clicking the icons
Posted: Tue Jan 18, 2005 2:37 pm
by feyd
right.. which may have screwed up the ability to search both fields at the same time. I don't know.. it's a thought.
Posted: Tue Jan 18, 2005 4:33 pm
by C_Calav
ok i see what you mean now.. how can i go back and make them the correct way? or make them both FULLTEXT in one instance?
thanx!
Posted: Tue Jan 18, 2005 4:37 pm
by feyd
ALTER TABLE maybe or recreate the table..

Posted: Tue Jan 18, 2005 4:39 pm
by C_Calav
cheers man will do when get home!