Creating a search page

[grantus]

I've seen some articles on creating a FULLTEXT search on my database, but that looks like it's only for 1 table.

So I'm looking to build my own search page, where users can search the whole database, not just 1 table. How can I do this? Sorry if this is too complex, but I figured it can't be THAT hard since lots of people have search boxes on their sites!

Thanks in advance!

[richie]

This is too broad for a definite answer.

Creating a search system is about writing database queries to JOIN tables.

[grantus]

Well the thing is, I have a nice search page setup with FULLTEXT, but since it's only for searching 1 table, it's got me stuck.

Let's say I have 5 tables and I run a FULLTEXT on each one. How would I run a query so that it searches all 5 tables? I've trying JOIN but maybe I'm doing something wrong, or maybe it's just not possible with FULLTEXT.

Any suggestions?

Thanks.

[kakki]

what about union( i dont know, if there is such thing in mysql ... ), like
select id from table1 where whatever like '%mySearch%'
UNION
select id from table2 where whatever like '%mySearch%'
UNION
select id from table3 where whatever like '%mySearch%'
UNION
select id from table4 where whatever like '%mySearch%'
.....

[kakki]

or you do that whith php :

$allResults=array();
$q=myqsl_query ( "select * from table1 where whatever like '%mySearch%' " )
while ( $line=myqsl_fetch_array( $q ) )
{
   $allResults[]=$line;
}
$q=myqsl_query ( "select * from table2 where whatever like '%mySearch%' " )
while ( $line=myqsl_fetch_array( $q ) )
{
   $allResults[]=$line;
}
$q=myqsl_query ( "select * from table3 where whatever like '%mySearch%' " )
while ( $line=myqsl_fetch_array( $q ) )
{
   $allResults[]=$line;
}

[TobyRT]

How would you separate the results from that?

[grantus]

I figured it out!

I'm using the FULLTEXT search, and I just did a separate query for each table, it works great. If anyone wants the code, just reply and I'll post it.

[kakki]

TobyRT wrote:

How would you separate the results from that?

select 1,id from table1 where whatever like '%mySearch%'
UNION
select 2,id from table2 where whatever like '%mySearch%'
UNION
select 3,id from table3 where whatever like '%mySearch%'
UNION
select 4,id from table4 where whatever like '%mySearch%'

....
you get a static col, that is set to 1-4 for the table being searched

[grantus]

Ok slight problem with my code! For some reason, no matter what my results are (if it's 1 page or many others), it always show the numbered links and Next link! Can someone help me out here? It's driving me nuts!

<?php
// Connect to the database.
$cnx = mysql_connect('localhost', 'root', 'pass') or die ("Could not connect");
mysql_select_db('main',  $cnx) or die (mysql_error());

// Create the search function:

function searchForm()
{
  // Re-usable form

  // variable setup for the form.
  $searchwords = (isset($_GET['words']) ? htmlspecialchars(stripslashes($_REQUEST['words'])) : '');

  echo '<form method="get" action="'.$_SERVER['PHP_SELF'].'">';
  echo '<input type="hidden" name="cmd" value="search" />';
  echo 'Search for: <input type="text" name="words" value="'.$searchwords.'" />&nbsp;';
  echo '<input type="submit" value="Search" />';
  echo '</form>';
}


// Create the navigation switch
$cmd = (isset($_GET['cmd']) ? $_GET['cmd'] : '');

switch($cmd)
{
  default:
    echo '<h1>Search Database!</h1>';
    searchForm();

  break;


  case "search":
    searchForm();
    echo '<h3>Search Results:</h3><br />';

$searchstring = mysql_escape_string($_GET['words']);

//Interviews query
// If current page number, use it
// if not, set one!

if(!isset($_GET['page'])){
    $page = 1;
} else {
    $page = $_GET['page'];
}

// Define the number of results per page
$max_results = 5;

// Figure out the limit for the query based
// on the current page number.
$from = (($page * $max_results) - $max_results);

{
    $sql = "SELECT name, date, description, content, url,
           MATCH(name, date, description, content, url) 
           AGAINST ('$searchstring' IN BOOLEAN MODE) AS score FROM interviews 
           WHERE MATCH(name, date, description, content, url) 
           AGAINST ('$searchstring' IN BOOLEAN MODE) ORDER BY score DESC LIMIT $from, $max_results"; 
} 

//Echo Interviews query

$result = mysql_query($sql) or die (mysql_error());

while($row = mysql_fetch_object($result))
{
  echo '<a href="'. $row->url .'">'.stripslashes(htmlspecialchars($row->name)).'</a><br />';
  echo '<span class="text-small">';
  echo date('F j, Y',strtotime($row->date)).'<br />';
  echo '</span>';
  echo stripslashes(htmlspecialchars($row->description)).'<br />';
}

  	// Figure out the total number of results in DB:
$total_results = mysql_result(mysql_query("SELECT COUNT(*) as Num FROM interviews"),0);

// Figure out the total number of pages. Always round up using ceil()
$total_pages = ceil($total_results/$max_results);

// Build Page Number Hyperlinks
echo "<center><br />";

// Build Previous Link
if ($page != 1) {
    $prev = ($page - 1);
    echo "<a href=\"".$_SERVER['PHP_SELF']."?page=$prev&cmd=$cmd&words=$searchstring\"><<</a> ";
}

for ($i = 1; $i <= $total_pages; $i++){
    if ($page == $i){
        echo "$i ";
        } else {
            echo "<a href=\"".$_SERVER['PHP_SELF']."?page=$i&cmd=$cmd&words=$searchstring\">$i</a> ";
    }
}

// Build Next Link
if($page < $total_pages){
    $next = ($page + 1);
    echo "<a href=\"".$_SERVER['PHP_SELF']."?page=$next&cmd=$cmd&words=$searchstring\">>></a> ";
}
echo "</center>";

  break;
}
?>