Search a string through all columns/tables

[Sleidia]

Good morning.

Here is a piece of code (beginner level) I've made
in order to search for text strings through all the
columns of all the tables of a given Database.
Of course the code could be changed in order to scan
all tables of all databases, but I didn't need such
a thing.

I hope it will be useful to someone...


$to_search_array = explode(" ",$to_search);
$to_search_num = sizeof($to_search_array);

for($a=0; $a < $to_search_num; $a++ ) {


$connect=mysql_connect(&quot;host&quot;,&quot;user&quot;,&quot;pass&quot;
$tables=mysql_list_tables(&quot;db&quot;,$connect);
while($table_found=mysql_fetch_row($tables))

$table_array[] = $table_found[0];

for($h=0; $h < sizeof($table_array); $h++ ) {

$sql = &quot;SELECT * FROM &quot; . $table_array[$h] . &quot; WHERE &quot;;

$connect=mysql_connect(&quot;host&quot;,&quot;user&quot;,&quot;pass&quot;
$fields = mysql_list_fields(&quot;db&quot;, $table_array[$h], $connect);
$columns = mysql_num_fields($fields);

for ($j = 0; $j < $columns; $j++) {
$col_name = mysql_field_name($fields, $j);

if($j == ($columns - 1)) {
$end_line = &quot;&quot;;
} else {
$end_line = &quot; OR &quot;;
}

$sql .= $col_name . &quot; LIKE '%&quot; . $to_search_array[$a] . &quot;%'&quot; . $end_line;

}

$sql .= &quot; ORDER BY ID ASC&quot;;

$db = mysql_connect(&quot;host&quot;, &quot;user&quot;, &quot;pass&quot;
mysql_select_db(&quot;db&quot;,$db);


$result=mysql_query($sql,$db);

$num = mysql_num_rows($result);
$cur = 1;

while ($num >= $cur) {

$row = mysql_fetch_array($result);

$ID = $row[&quot;ID&quot;];



echo &quot;

!RESULT HERE!--> $ID <br>

&quot;;


$cur++;
}

}


}

 

[Sleidia]

BTW.... If anyone knows how to do the same
thing with a single mysql query, please be kind
enough to share it in this thread.

Thanks.

 

[1jochem]

not at all sure of the details (other than to know that whether you can do this depends on the version of MySQL you use.) but there is something in the newer implementations called FULLTEXT which allows you to create an index over multiple text fields

check out:

http://www.mysql.com/doc/F/u/Fulltext_Search.html

hope it helps.
Peace, Love & Open Source.

Namasté,

Jochem