List:General Discussion« Previous MessageNext Message »
From:Justin Swanhart Date:July 14 2004 4:54pm
Subject:Re: Search one table, then display another table where keys match ?
View as plain text  
Does access_no contain actual textual data, or is it
simply a key like a category or an integer?  If you
don't need to do a fulltext search against access_no
then there is no reason to include it in your fulltext
index.  You should most likely have a seperate index
for access_no in that case.

The following query assumes access_no is exactly the
same in both balloon_txt and balloon_rec for the rows
you are matching. 

SELECT br.* 
  FROM balloon_txt bt,
       balloon_rec br
       /*find the matching rows from balloon_txt*/
 WHERE MATCH(bt.access_no, bt.recs_txt)
       AGAINST ('robin')";
       /*and join them to rows in balloon_rec using
         the access_no column*/
   AND bt.access_no = br.access_no 

if access_no doesn't need to be full text indexed, you
could drop the fulltext key and add a new one just for
recs_txt and remove bt.access_no from the MATCH()

--- leegold <leegold@stripped> wrote:
> 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/mysql?unsub=1
> 
> 

Thread
Search one table, then display another table where keys match ?leegold14 Jul
  • Re: Search one table, then display another table where keys match ?Peter Brawley14 Jul
    • Re: Search one table, then display another table where keys match ?leegold15 Jul
  • Re: Search one table, then display another table where keys match ?Justin Swanhart14 Jul
Re: Search one table, then display another table where keys match ?SGreen14 Jul