<soapbox>
If all you need from your query is one column, then ask for just that one
column. Don't overuse SELECT *. You wind up filling your data transfer
channels with unnecessary and unused data.
</soapbox> (sorry about that, one of my pet peeves)
You can store the results of your fulltext search in a temporary table and
JOIN that to your other table. I think it will take 3 separate calls to
MySQL_query() but I am not sure (I work for a Win32 company and I don't get
to work with PHP very often).
call 1:
CREATE TEMPORARY TABLE tmpSearch
SELECT access_no FROM ballon_txt WHERE MATCH (access_no, recs_txt)
AGAINST('robin')
call 2 (this one will give you your matching records:
SELECT <field list here>
FROM balloon_rec r
INNER JOIN tmpSearch s
ON s.access_no = r.access_no
call 3 (I always try to clean up after myself):
DROP TABLE tmpSearch
Depending on what version of MySQL you have , you might be able to use the
first query as an "unnamed view" and avoid the need for a temp table:
SELECT <field list here>
FROM balloon_rec r
INNER JOIN (SELECT access_no FROM ballon_txt WHERE MATCH (access_no,
recs_txt) AGAINST('robin')) s
ON s.access_no = r.access_no
Yours,
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
"leegold"
<[EMAIL PROTECTED] To: "mySQL mailinglist" <[EMAIL
PROTECTED]>
.fm> cc:
Fax to:
07/14/2004 10:06 Subject: Search one table, then
display another table where keys match
AM ?
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]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
