Jump to content

Searching a large mysql database


karaktie

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!
Link to comment
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.
Link to comment
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 >.<
Link to comment
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.....

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

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

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.