Searching a large mysql database

[karaktie]

Hello all as the title says it, i am trying to search a large database for a keyword.

I understand that you can use the LIKE '% %' to search for a keyword. But it only works on one column at a time. My database has around 30 coloumns and i wish to search every single one without having to type each column out.

I was wondering if there was a wildcard to use on a data to say like
[code]("select id from table_name where (wildcard which selects every coloumn) LIKE '%keyword%'")[/code]

Any help would be [b]GREATLY[/b] appreciated!

[jworisek]

well you could do something like:

[code]
select id from tablename were col1 like %something% or col2 like %something% or col3........  etc.
[/code]

I would suggest you rethink your database layout though... If you have to search every single column then you do not have it designed properly.

[karaktie]

i am not the desiogner of the database, i am mearly transferring the database form an offline database fomr one company to MySQL to go with a website i am making for yet another company.

Very complicated but i need a keyword search page, which searches the table for a keyword, how would that be done easily?

I am not allowed to change the design of the database for some obscure reason >.<

[fractil]

Are you attempting to search the entire database for the "keyword", or a specific table in the database?

Cheers!

[karaktie]

Just a specific table for instance say 'table01'

So i have a database with 5 tables in, i only want it to search one whole table.

[jvrothjr]

I had to do what your asking with multi Keyword search any place say the keyword was used across multi field in one table. Its not a function of how the DB was designed its a function of how the user wishes to find something...... Say the user wishes to find every place a product name was used in say a test request DB.

[code]
$querystring = "select * from tbldata
where
Test_Description like '%$Keywords[0]%'
or
TestName like '%$Keywords[0]%'
or
Reason like '%$Keywords[0]%'
or
Comments like '%$Keywords[0]%'
or
Product like '%$Keywords[0]%'";
[/code]

I have shorted this query for you (above) I have it search for multi keywords with in the same record(below).

Say here failed%product are used together. The program splits the keyword at % and searchs for both with in each record.

[code]
$querystring = "select * from tbldata
where
Test_Description like '%$Keywords[0]%' and
Test_Description like '%$Keywords[1]%'
or
TestName like '%$Keywords[0]%' and
TestName like '%$Keywords[0]%'
or
Reason like '%$Keywords[0]%' and
Reason like '%$Keywords[0]%'
or
Comments like '%$Keywords[0]%' and
Comments like '%$Keywords[0]%'
or
Product like '%$Keywords[0]%' and
Product like '%$Keywords[0]%'";
[/code]

I use MS Access to layout SQL queries and what your asking I could to get it to do. I had to list each field I wished to Search and how I wished to search them I could not get it to mass search the table for a keyword with out listing every field name.....

[karaktie]

ok thanks for that :)

I will try what you said. I feared i would have to do it this way I will tell u if it works for me or not.

[craygo]

Instead of typing every field, you could run a loop which will get all field names then run a query to search all those fields

Query to get all the fields in a table
[code]<?
// Start field count
$i=0;
// Start field names array
$fnames = array();
// Query the table
$res = mysql_query('select * from yourtablename') or die (mysql_error());;
while ($i < mysql_num_fields($res)) {
   $meta = mysql_fetch_field($res, $i);
   $fnames[] = $meta->name;
$i++;
}
?>[/code]

Query to loop thru each field and search
[code]<?
// Start array for row id's
$ids = array();
// Loop through the previos query
foreach($fnames as $key => $val){
$sql = "SELECT DISTINCT(id) from testing where $val LIKE '%searchstring%'";
  $result = mysql_query($sql) or die (mysql_error());
  while($r = mysql_fetch_array($result)){
  $ids[] = $r['id'];
  }
  }
?>[/code]

Now your row id's will be in an array and you can get the information with one last query
[code]
// implode your array
$table_ids = implode(", ", $ids);
// Query to retrieve details from table based on the id's found
$sql1 = "SELECT * FROM tablename WHERE id IN ($table_ids)";
  $sres = mysql_query($sql1) or die (mysql_error());
  while ($row = mysql_fetch_array($sres)){
  print ''.$row['fieldnames'].'<br>';
  }
?>[/code]

Ray