Jump to content


Photo

Searching a large mysql database


  • Please log in to reply
7 replies to this topic

#1 karaktie

karaktie
  • New Members
  • Pip
  • Newbie
  • 4 posts

Posted 27 June 2006 - 11:17 AM

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
("select id from table_name where (wildcard which selects every coloumn) LIKE '%keyword%'")

Any help would be GREATLY appreciated!

#2 jworisek

jworisek
  • Members
  • PipPipPip
  • Advanced Member
  • 112 posts

Posted 27 June 2006 - 01:24 PM

well you could do something like:

select id from tablename were col1 like %something% or col2 like %something% or col3........  etc.

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.

#3 karaktie

karaktie
  • New Members
  • Pip
  • Newbie
  • 4 posts

Posted 27 June 2006 - 01:26 PM

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 >.<

#4 fractil

fractil
  • Members
  • PipPip
  • Member
  • 10 posts

Posted 27 June 2006 - 03:29 PM

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

Cheers!
"One of the main causes of the fall of the Roman Empire was that, lacking zero, they had no way to indicate successful termination of their C programs"

#5 karaktie

karaktie
  • New Members
  • Pip
  • Newbie
  • 4 posts

Posted 27 June 2006 - 03:33 PM

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.

#6 jvrothjr

jvrothjr
  • Members
  • PipPipPip
  • Advanced Member
  • 300 posts
  • LocationOhio

Posted 27 June 2006 - 05:02 PM

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.

$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]%'";

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.

$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]%'";

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.....


If you understand everything you know nothing!

http://rcchjr.awardspace.com/

#7 karaktie

karaktie
  • New Members
  • Pip
  • Newbie
  • 4 posts

Posted 27 June 2006 - 05:33 PM

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.

#8 craygo

craygo
  • Staff Alumni
  • Advanced Member
  • 1,973 posts
  • LocationRhode Island

Posted 27 June 2006 - 08:32 PM

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
<?
// 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++;
}
?>

Query to loop thru each field and search
<?
// 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'];
  }
  }
?>

Now your row id's will be in an array and you can get the information with one last query
// 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>';
  }
?>

Ray




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users