need help: search query on two separate database tables

[jepler]

Hello,

I am receiving unwanted cartesian results in my query. I'm hoping someone might be able to explain the best way to do what I'm trying to do, because I obviously don't how to do it correctly! :bemused:

I'm trying to build a search query over two separate databases on my server. The connections are open and working, but I'm not sure how to output the results into an array, etc.

Here is an example of the code. assume there are no parse errors even if code below doesn't reflect it:

<?php require_once('Connections/database_1.php'); ?>
<?php require_once('Connections/database_2.php'); ?>

$colname_search = "1"; if (isset($HTTP_GET_VARS['searchsite'])) { $colname_search = (get_magic_quotes_gpc()) ? $HTTP_GET_VARS['searchsite'] :
addslashes($HTTP_GET_VARS['searchsite']); }

$search1 = mysql_query("SELECT * FROM database_1.table_1 WHERE MATCH (column_1, column_2) AGAINST ('$colname_search' IN BOOLEAN MODE)") or die(mysql_error());
$row_search1 = mysql_fetch_assoc($search1); $totalRows_search1 = mysql_num_rows($search1);

$search2 = mysql_query("SELECT * FROM database_1.table_2 WHERE MATCH (column_3, column_4) AGAINST ('$colname_search' IN BOOLEAN MODE)") or die(mysql_error());
$row_search2 = mysql_fetch_assoc($search2); $totalRows_search2 = mysql_num_rows($search2);

body

<?php do { ?>

       [these results would be in a table]

             echo $row_search['column_1']; <br>
             echo $row_search['column_2']; <br>
             echo $row_search2['column_3']; <br>
             echo $row_search2['column_4']; <br>

<?php } while ($row_search1 = mysql_fetch_assoc($search1) || $row_search2 = myslql_fetch_assoc($search2); 

?>

[bogu]

U need to open one connection make the search put the results in your array, close the connection, opent the other connection make the search put the results in your array close connection ...

[jepler]

thanks for your reply. I think I understand what you are suggesting, but why do I need to close the one connection before opening the other?

I modified my query from what I wrote above; here is the new one:

$search = mysql_query("SELECT DISTINCT * FROM database_1.table_1 JOIN database_2.table_2 ON MATCH (column_1, column_2) AGAINST ('$colname_search' IN BOOLEAN MODE) WHERE MATCH (column_3, column_4) AGAINST ('$colname_search' IN BOOLEAN MODE)") or die(mysql_error());

$row_search = mysql_fetch_assoc($search); $totalRows_search = mysql_num_rows($search);

This seemed to work better, but I am still having trouble with repeating results during output between my <? do { ... } while ?> loop.

Will opening and closing connections solve this?