Jump to content

Archived

This topic is now archived and is closed to further replies.

karaktie

Searching a large mysql database

Recommended Posts

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!

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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 >.<

Share this post


Link to post
Share on other sites
Are you attempting to search the entire database for the "keyword", or a specific table in the database?

Cheers!

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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.....

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.