Search multiple tables and display links to different files

Question

I have 2 tables first one tblactive ( ac_id, ac_title, ac_text) , second one tblnews (n_id, n_title, n_text)

Also I have 3 files

Active.php (in this file I obtain data from table active and display it through function displayac, ex. <a href=active.php?op=displayac&ac_id=$ac_id>$ac_title</a>)

News.php (in this file I obtain data from table news and display it through function displaynews, ex. <a href=news.php?op=displaynews&n_id=$n_id>$n_title</a>)

The problem

Search.php ( in this file I want to search the (searchword like ac_text and searchword like n_text in the 2 tables active & news at same time and display result of search containing links to their original files ex.

This my query

$result ="(select n_title,n_text, n_id from tblnews  where n_text LIKE '%$searchword%')
        union
        (select ac_title,ac_text, ac_id from tblactive  where ac_text LIKE '%$searchword%') limit $page, $perpage";
        $query=mysql_query($result);

If the searchword found in ac_text column the link will be <a href=active.php?op=displayac&ac_id=$ac_id>$ac_title</a>

And if searchword found in n_text column the link will be <a href=news.php?op=displaynews&n_id=$n_id>$n_title</a>

[Please, stop using mysql_* functions](http://stackoverflow.com/q/12859942/1238019) in new code, they are [officially deprecated](https://wiki.php.net/rfc/mysql_deprecation). Instead of, have a look on [prepared statements](http://dev.mysql.com/doc/refman/5.0/en/sql-syntax-prepared-statements.html), and use [Mysqli](http://php.net/manual/en/book.mysqli.php) or [PDO](http://php.net/manual/en/book.pdo.php). — zessx, Nov 18 '13 at 10:09

score 0 · Accepted Answer · answered Nov 18 '13 at 10:47

Union will not tell you from which table the data came, so manipulate your query as

(select n_title,'n' as wchtab, n_text, n_id from tblnews  where n_text LIKE '%e%')
union
(select ac_title,'a' as wchtab, ac_text, ac_id from tblactive  where ac_text LIKE '%e%')

where 'wchtab' tells you from which table the data came from, now you can check while fetching the records like

while ($row = mysql_fetch_assoc($result)) {
    if($row['wchtab'] == 'n'){
        $n_title = $row['N_TITLE'];
        $n_id    = $row['N_ID'];
        echo "<a href=news.php?op=displaynews&n_id=$n_id>$n_title</a>"
    }
    else if($row['wchtab'] == 'a'){
        $ac_title = $row['N_TITLE'];
        $a_id    = $row['N_ID'];
        echo "<a href=active.php?op=displayac&ac_id=$ac_id>$ac_title</a>";
    }

NOTE as you are using union, you will get the title of the column as the first table name column fields, so you won't get ac_title separately, but the column n_title will hold data for ac_title and wchtab will tell you from which table the data has came. }

Search multiple tables and display links to different files

1 Answers1