Hello,
I wrote a search query for the administrative backend section of a website that goes through and looks for keywords in every column of a relational database. I didnt go with full-text search as i need it to go through every column and there are nearly 30 columns for this table. I quickly found this query to be very slow as the number of columns for the table increased.
Here is my convoluted bit of code which basically makes one big long 'column LIKE keyword' list for the query:
$fields = array();
foreach(array('music','ms_locations','ms_artists','ms_producers') as $table)
{
$result = mysql_query("SHOW COLUMNS FROM `$table`", getDBConnection());
for($i=0;$i<mysql_num_rows($result);$i++)
{
$tmp = mysql_result($result, $i);
$tmp = "$table.$tmp";
if($tmp != "ms_locations.id" && $tmp != "ms_artists.id" && $tmp != "ms_producers.id" && $tmp != "music.status")
array_push($fields,$tmp);
}
}
$string = '';
foreach($fields as $field)
$string .= " $field LIKE '%$query%' OR";
$string = rtrim($string," OR");
$result = mysql_query("SELECT music.*, ms_locations.*, ms_artists.*, ms_producers.* FROM music, ms_locations, ms_artists, ms_producers WHERE (ms_locations.musicid = music.musicid AND ms_artists.musicid = music.musicid AND ms_producers.musicid = music.musicid) AND ($string) GROUP BY music.musicid", getDBConnection()) or handleDieError(__FILE__,__LINE__,mysql_error());
My question then is: What is the best way to search every column in a relational db? I suppose i could mash all the data into another text field and just search that one, but i find that to be unappealing...
What do you guys think?
Thanks in advance!
