[jonnekke]

Hi there,

is it possible to get the name of the database from the data?
I search my DB with full-text-search..

now I want to link from the results, but I need the name of the
table the result was found in. I use that back in the URL to link..

F.E.

search for: "Example Search" in my DB
In table 1 this is found, I need the name of table 1 to use..

Hope this comes a bit clear to someone...

_j

[Nico]

Can you post your code?

[jonnekke]

Sure.. I use the following script to search:

PHP Code:

$search_result_algemeen = mysql_query("SELECT * FROM mc_algemeen WHERE MATCH(verhaal, titel, subtitel) AGAINST($word IN BOOLEAN MODE) 
                                     UNION SELECT * FROM stp_algemeen WHERE MATCH(verhaal, titel, subtitel) AGAINST($word IN BOOLEAN MODE)
                                     UNION SELECT * FROM nl_algemeen WHERE MATCH(verhaal, titel, subtitel) AGAINST($word IN BOOLEAN MODE)
                                     UNION SELECT * FROM vb_algemeen WHERE MATCH(verhaal, titel, subtitel) AGAINST($word IN BOOLEAN MODE)
                                     ") or die('Error, query failed1'); 

Now I need to know from wich table the result comes from so I can link to (f.e.) mc_algemeen.php to show the entire story.

[End User]

As far as I know there wouldn't be any way to determine which table (or tables) produced the "hit" for a successful search using that query.

Originally Posted by jonnekke

Sure.. I use the following script to search:

PHP Code:

$search_result_algemeen = mysql_query("SELECT * FROM mc_algemeen WHERE MATCH(verhaal, titel, subtitel) AGAINST($word IN BOOLEAN MODE) 

                                     UNION SELECT * FROM stp_algemeen WHERE MATCH(verhaal, titel, subtitel) AGAINST($word IN BOOLEAN MODE)

                                     UNION SELECT * FROM nl_algemeen WHERE MATCH(verhaal, titel, subtitel) AGAINST($word IN BOOLEAN MODE)

                                     UNION SELECT * FROM vb_algemeen WHERE MATCH(verhaal, titel, subtitel) AGAINST($word IN BOOLEAN MODE)

                                     ") or die('Error, query failed1'); 

Now I need to know from wich table the result comes from so I can link to (f.e.) mc_algemeen.php to show the entire story.

[carters-site]

If you are using MySQL 5 that supports Store Procedures and Functions it would be possible.

You would need to add a function that you can call in the SELECT statement of each union that passed the table name of that union and a row from if there is a value.

This also relies upon MySQL user defined variables.

For example here is a MySQL function that would store the tblName if there are results in inp.

Code:

delimiter //
CREATE FUNCTION `testTableName`(inp VARCHAR(100), tbl VARCHAR(100)) RETURNS text CHARSET utf8
    READS SQL DATA
    DETERMINISTIC
BEGIN
  DECLARE bCheck INT;

  IF inp <> "" THEN
     SELECT @tblName LIKE CONCAT("%",tbl,"%") INTO bCheck;
     IF bCheck <> 1 THEN
        IF @tblName = "" THEN
           SET @tblName = tbl;
        ELSE 
           SET @tblName = CONCAT(@tblName, ",", tbl);
        END IF;
     END IF;
  END IF;  
  RETURN inp;
END
 
//
delimiter ;

So using your code

Code:

SELECT *, testTableName(id, mc_algemeen) FROM mc_algemeen WHERE MATCH(verhaal, titel, subtitel) AGAINST($word IN BOOLEAN MODE) 
                                     UNION SELECT *,  testTableName(id, stp_algemeen) FROM stp_algemeen WHERE MATCH(verhaal, titel, subtitel) AGAINST($word IN BOOLEAN MODE)
                                     UNION SELECT *,  testTableName(id, nl_algemeen) FROM nl_algemeen WHERE MATCH(verhaal, titel, subtitel) AGAINST($word IN BOOLEAN MODE)
                                     UNION SELECT *,  testTableName(id, vb_algemeen) FROM vb_algemeen WHERE MATCH(verhaal, titel, subtitel) AGAINST($word IN BOOLEAN MODE)

After calling the query you can do

Code:

SELECT @tblName;

To get a comma separated list of tables that had results. Just make sure you clear out your user defined value before each run.

Thre is probably a more elegant solution using Stored Procedures however there are some special client flags you must set depending on how you build your stored proc.

Was an interesting problem to tackle.