Php > Searching

Table Searcher

Table Searcher

<?php 
function searchtable($database,$tablename,$userquery,$host,$user,$pass){ 
    $link   = mysql_connect($host, $user, $pass) or die("Could not connect: " . mysql_error()); 
    $db     = mysql_select_db($database, $link) or die(mysql_error()); 
    $fields = mysql_list_fields($database, $tablename, $link); 
    $cols   = mysql_num_fields($fields); 
    $content = ""; 
    for ($i = 0; $i < $cols; $i++) { 
        $allfields[] = mysql_field_name($fields, $i); 
    } 
    foreach ($allfields as $myfield) { 
        $result = mysql_query("SELECT * FROM $tablename WHERE $myfield like '%$userquery%' "); 
        if (mysql_num_rows($result) > 0){ 
            $content .= "<h3>search <i>$database</i> for <i>$userquery</i>, found match(es) in <i>$myfield</i>: </h3>\n"; 
            $content .= "<table border=1 align=\"center\">\n\t<tr>\n"; 
            for ($i = 0; $i < $cols; $i++) { 
                $content .= "\t\t<th"; 
                if ($myfield == mysql_field_name($fields, $i)){ 
                    $content .= " bgcolor=\"orange\" "; 
                                } 
                $content .= ">" . mysql_field_name($fields, $i) . "</th>\n"; 
            } 
            $content .= "\t</tr>\n"; 
            $myrow = mysql_fetch_array($result); 
            do { 
                $content .= "\t<tr>\n"; 
                for ($i = 0; $i < $cols; $i++){ 
                    $content .= "\t\t<td"; 
                    if (preg_match("/$userquery/i", $myrow[$i])){ 
                        $mysplit = preg_split("/($userquery)/i", $myrow[$i], -1,PREG_SPLIT_DELIM_CAPTURE); 
                        $content .= " bgcolor=\"orange\">"; 
                        for ($si=0;$si<count($mysplit);$si++){ 
                            if (preg_match("/$userquery/i", $mysplit[$si])){ 
                                $content .= "<font color=\"green\"><b>$mysplit[$si]</b></font>"; 
                            }else{ 
                                $content .= "$mysplit[$si]"; 
                            } 
                        } 
                    } else { 
                        $content .= ">$myrow[$i]"; 
                    } 
                    $content .= " </td>\n"; 
                } 
                $content .= "\t</tr>\n"; 
            } while ($myrow = mysql_fetch_array($result)); 
            $content .= "</table>\n"; 
        } 
    } 
    mysql_close($link); 
    return $content; 
} 

$page_content = searchtable(yourdb,your table,$userquery,yourhost,youruser,yourpass); 
echo $page_content; 


?> 


//Database used as demo on my site 

# 
# Table structure for table `thistest` 
# 

CREATE TABLE thistest ( 
  firstname varchar(20) NOT NULL default '', 
  lastname varchar(20) NOT NULL default '', 
  phone varchar(20) NOT NULL default '', 
  city varchar(20) NOT NULL default '' 
) TYPE=MyISAM;