Match() returns a graded "relevance rating", as a floating point number
(when called as you call it) for matches. Words present in more than half
the rows are ignored, presence of a word in most rows lowers relevance,
presence of a word in just a few rows increases it, weights are then
combined to compute the return value. Match() doesn't sound quite like what
you are looking for, though what you're looking for isn't entirely clear
from your post.
The first problem is, extracting multiple keys from text, for matching
against keys in another table, is usually the sort of thing that is best
done before the data comes into the database. The reason is that the
assumptions underpinning SQL are not compatible with your use of the text
column as a key bank: your use violates one of Codd's rules, that a single
column should yield only one value. So you might want to consider breaking
out the key-containing text into rows of data that can be processsed by
ordinary SQL logic.
The second problem is, if you must find rows in tableB having key values
which have been found in a text column from certain rows in tableA, is a
match to be computed without regard to context, for example the text '123'
is to be accepted as a match no matter where it is found, and no matter
whether it occurs inside strings like '01234'? If so, have a look at the
Locate() function. OTOH, if there are context rules, you will probably need
to use regular expression logic to find the matches you want, so have a look
at <str REGEXP pattern> in the manual.
Once you reslve these two problems, probably simple JOIN syntax will get you
the SQL result you need.
HTH.
PB
----- Original Message -----
From: leegold
To: mySQL mailinglist
Sent: Wednesday, July 14, 2004 9:06 AM
Subject: Search one table, then display another table where keys match ?
If you would entertain a MYSQL/PHP, hope not too off-topicIt's
probably not difficult to solve - but you would be helping me
with some SQL logic.
The only way I can think of to explain what I want to do
is to give you my working newbie MSQL/PHP code that I'm learning
MYSQL/PHP with, and at a certain point in the code below I'll state
exactly as I can what I want to try to do. It's probably
quite simple but I can't get it- Thanks:
...
<pre>
<?php
$dblink = mysql_connect ( 'localhost', "guest", "password" );
mysql_select_db( "balloon", $dblink );
// Doing a FULLTEXT search
// Re the SELECT: I indexed both fields together, so seemed like
// I should put them both in the MATCH...OK, it works.
$query="SELECT * FROM balloon_txt WHERE MATCH(access_no, recs_txt)
AGAINST ('robin')";
$result = MySQL_query($query);
/////////////////////////////////
OK, right here - next below I'm gonna display/loop $result from table
balloon_txt. But, what I really want to do is take the "result set"
access_no fields from the search above and (access_no is a Key in all
my tables) and use it to generate results (ie. matching records) from
another table called balloon_rec and dispaly/loop the results from
balloon_rec. So I'm searching balloon_txt, getting results, but I want
to display matching records from another table - balloom_rec. Is there
a way to do a join or something in the SELECT above? Or do I process
$result? Seems a join in the SELECT above or some SQL above is cleaner
- but not sure how(?) Thanks, Lee G.
///////////////////////////////
while ( $row = mysql_fetch_row( $result ) ) {
for ( $i=0; $i<mysql_num_fields( $result ); $i++ )
{echo $row[$i] . " ;}
echo"\n\n\n";
}
// Close the db connection
mysql_close ( $dblink );
?>
</pre>
...
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
