Search several tables at the same time

[PF2G]

Hello,

I did this search engine just for one table:

<?php

$button = $_GET ['submit'];
$search = $_GET ['search']; 

if(strlen($search)<=1)
echo "Search term too short";
else{
echo "You searched for <b>$search</b> <hr size='1'></br>";



mysql_connect("localhost","root","");
mysql_select_db("rmworshipers");



$search_exploded = explode (" ", $search);
$x=1;

foreach($search_exploded as $search_each)
{
$x++;    
}

$constructs ="SELECT * FROM events WHERE event_name LIKE '%$search_each%'";
$run = mysql_query($constructs) or die(mysql_error());

$foundnum = mysql_num_rows($run);

if ($foundnum==0)
echo "Sorry, there are no matching result for <b>$search</b>.";
else
{ 

echo "$foundnum results found !<p>";

$per_page = 10;
if(isset($_GET['start'])){
$start = $_GET['start'];
}
$max_pages = ceil($foundnum / $per_page);
if(!isset($_GET['start'])){
$start=0; 
}
$getquery = mysql_query("SELECT * FROM events WHERE event_name LIKE '%$search_each%' LIMIT $start, $per_page");
$runrows = mysql_fetch_assoc($getquery);

do{
    $id = $runrows ['event_id'];
    $nome = $runrows ['event_name'];
    echo "$id - <a href='$id'>$nome</a><p>";
}while($runrows = mysql_fetch_assoc($getquery));

//Pagination Starts
$prev = $start - $per_page;
$next = $start + $per_page;

$adjacents = 3;
$last = $max_pages - 1;

if($max_pages > 1)
{   
//previous button
if (!($start<=0)) 
echo " <a href='search.php?search=$search&submit=Search+source+code&start=$prev'>Prev</a> ";    

//pages 
if ($max_pages < 7 + ($adjacents * 2))   //not enough pages to bother breaking it up
{
$i = 0;   
for ($counter = 1; $counter <= $max_pages; $counter++)
{
if ($i == $start){
echo " <a href='search.php?search=$search&submit=Search+source+code&start=$i'><b>$counter</b></a> ";
}
else {
echo " <a href='search.php?search=$search&submit=Search+source+code&start=$i'>$counter</a> ";
}  
$i = $i + $per_page;                 
}
}
elseif($max_pages > 5 + ($adjacents * 2))    //enough pages to hide some
{
//close to beginning; only hide later pages
if(($start/$per_page) < 1 + ($adjacents * 2))        
{
$i = 0;
for ($counter = 1; $counter < 4 + ($adjacents * 2); $counter++)
{
if ($i == $start){
echo " <a href='search.php?search=$search&submit=Search+source+code&start=$i'><b>$counter</b></a> ";
}
else {
echo " <a href='search.php?search=$search&submit=Search+source+code&start=$i'>$counter</a> ";
} 
$i = $i + $per_page;                                       
}

}
//in middle; hide some front and some back
elseif($max_pages - ($adjacents * 2) > ($start / $per_page) && ($start / $per_page) > ($adjacents * 2))
{
echo " <a href='search.php?search=$search&submit=Search+source+code&start=0'>1</a> ";
echo " <a href='search.php?search=$search&submit=Search+source+code&start=$per_page'>2</a> .... ";

$i = $start;                 
for ($counter = ($start/$per_page)+1; $counter < ($start / $per_page) + $adjacents + 2; $counter++)
{
if ($i == $start){
echo " <a href='search.php?search=$search&submit=Search+source+code&start=$i'><b>$counter</b></a> ";
}
else {
echo " <a href='search.php?search=$search&submit=Search+source+code&start=$i'>$counter</a> ";
}   
$i = $i + $per_page;                
}

}
//close to end; only hide early pages
else
{
echo " <a href='search.php?search=$search&submit=Search+source+code&start=0'>1</a> ";
echo " <a href='search.php?search=$search&submit=Search+source+code&start=$per_page'>2</a> .... ";

$i = $start;                
for ($counter = ($start / $per_page) + 1; $counter <= $max_pages; $counter++)
{
if ($i == $start){
echo " <a href='search.php?search=$search&submit=Search+source+code&start=$i'><b>$counter</b></a> ";
}
else {
echo " <a href='search.php?search=$search&submit=Search+source+code&start=$i'>$counter</a> ";   
} 
$i = $i + $per_page;              
}
}
}

//next button
if (!($start >=$foundnum-$per_page))
echo " <a href='search.php?search=$search&submit=Search+source+code&start=$next'>Next</a> ";    
}   
} 
} 
?>

and now i want to Select the tables: Guests, News, Interviews. And also how do i do to if i search for a some guest or event it gives me the results of every news/interviews where the guest and/or event appears...

Thank you for your help

[Szabi Zsoldos]

Please try the UNION function for this.

If you want more fields, the selected fields have to be equal as a number in all your union selects.

Please note that this can be done in multiple ways, this is not the only option.

(SELECT your_fields FROM yourTable1 WHERE yourField LIKE '%yourSearch%' LIMIT yourLimit) 
UNION (SELECT your_fields FROM yourTable2 WHERE yourField LIKE '%yourSearch%' LIMIT yourLimit)
UNION (SELECT your_fields FROM yourTable3 WHERE yourField LIKE '%yourSearch%' LIMIT yourLimit)

[Webville312]

Also, you can consider left or right joins. Only problem with that is; your query will take a while to execute.

[diafol]

I'd go UNION for this as data sees unrelated.