Shawn, Mel

>I am not aware of any SQL dialect that supports a query of the kind you
>are asking about. If there were such a query it _might_ look something
>like this

>SELECT <column list>
>FROM <table reference>
>WHERE ANY_COLUMN(<column name list>) LIKE <search parameter>

Right, but Mel can emulate your ANY_COLUMN with something like

  SELECT CONCAT( col1, ..., colN ) AS txt
  FROM tbl
  WHERE txt LIKE '%foobar%';

PB

-----

SGreen@unimin.com wrote:
I am not aware of any SQL dialect that supports a query of the kind you 
are asking about. If there were such a query it _might_ look something 
like this

SELECT <column list>
FROM <table reference>
WHERE ANY_COLUMN(<column name list>) LIKE <search parameter>

but like I said, I can't think of any SQL server that has a function like 
ANY_COLUMN() or its equivalent. Sorry! I think you will need to do a 
column-by-column comparison (especially if you want to use LIKE or RLIKE).

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

"mel list_php" <list_php@hotmail.co.uk> wrote on 03/23/2005 11:08:56 AM:

  
I can find the data, I was just wondering if mysql provides a kind of 
generic scan of a whole table.

I could provide an advanced search, and ask the user what kind of info 
    
he 
  
wants to retrieve but I first would like a quickSearch that may retrieve 
    
too 
  
much info but is more intuitive.

As I said, my first idea was to create a script to go through all my 
    
tables 
  
and scan the relevant columns (I don't want to scan the id keys for 
example), but I was just wondering if given a table it is possible to 
    
use 
  
mysql to scan all its columns:
*************************** 1. row ***************************
       Table: dbLab
Create Table: CREATE TABLE `dbLab` (
  `labId` tinyint(5) NOT NULL auto_increment,
  `name` varchar(50) NOT NULL default '',
  `fullName` varchar(250) default NULL,
  `adress` varchar(150) NOT NULL default '',
  `city` varchar(50) NOT NULL default '',
  `country` varchar(50) NOT NULL default '',
  `url` varchar(100) NOT NULL default '',
  PRIMARY KEY  (`labId`)
) TYPE=MyISAM COMMENT='list of partners'
1 row in set (0.00 sec)

If I search for "Smith" I want to retrieve the name Smith, the fullname 
Smith, the address or city containing Smith....basically look for the 
    
word 
  
in all the columns, a shortener for select * from dbLab where name 
like'%Smith%' or fullName like '%Smith%' or adress like '%Smith%'.....

But maybe this is not a good way to do things?

Melanie


    
From: SGreen@unimin.com
To: "mel list_php" <list_php@hotmail.co.uk>
CC: Alec.Cawley@Quantel.Com,mysql@lists.mysql.com
Subject: Re: search through one/several tables
Date: Wed, 23 Mar 2005 09:55:23 -0500

"mel list_php" <list_php@hotmail.co.uk> wrote on 03/23/2005 06:00:08 
      
AM:
  
Unfortunatly they are not, I have something like 30 tables, with I 
        
would
  
say
      
10 to 15 fields per table.
The number of row per table is quite low, i think it won't exceed
500-1000/table. But I may sometimes have to search into dna 
        
sequences
  
(around 5000 atcg characters in any order), so that is quite heavy.
        
Maybe
      
for that field a fulltext index would be helpful?

Thanks for your help,
Melanie


        
From: Alec.Cawley@Quantel.Com
To: list_php@hotmail.co.uk
CC: mysql@lists.mysql.com
Subject: Re: search through one/several tables
Date: Wed, 23 Mar 2005 10:44:53 +0000

"mel list_php" <list_php@hotmail.co.uk> wrote on 23/03/2005 
          
10:14:07:
  
Hi list,

I would like to search for something into one or several tables.
My first idea was to retrieve the tables' names, then for each 
            
of
  
them
      
retrieve the columns' name and have a look in each of this 
            
column.
  
Is there a more elegant (fast) way to do that with mysql?

Somebody has some tips/doc where I could look for search 
            
engines?My
  
problem
          
is that I don't have one big table with all the data but several
            
little
      
ones
          
with few fields, so I don't think solutions like Lucene could 
            
work.
  
If your tables are all identical, which it sounds like, you want to
          
create
      
a Merge Table: see
http://dev.mysql.com/doc/mysql/en/merge-storage-engine.html

        Alec

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/mysql?unsub=list_php@hotmail.co.uk

          
_________________________________________________________________
Express yourself with cool new emoticons
        
http://www.msn.co.uk/specials/myemo
      
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=sgreen@unimin.com

        
I agree in priciple with Alec. Good database design can and should 
      
promote
  
good data retreival. However, it sounds from your original question 
      
that
  
you need to find all records on any table with some bit of information 
      
in
  
any column.  That problem is just too generic to provide specific help
for.
If you can't find certain pieces of data, it _may_ be that you haven't
organized your data to the point that there is a place for everything 
      
and
  
everything is in its place. There is always (except for the most 
      
trivial
  
cases) more than one way to organize any set of data, however different
schemas offer different advantages (size, speed, ease of use, etc). 
      
Which
  
one will work best for you depends on the nature of your data, your
database server's limitations (hardware, software, and operating system
limits), and what you need to get from the data once it's organized. 
      
Some
  
schemas make it harder to add or update data but make finding it a 
      
breeze;
  
others are just the opposite. This sounds like a good time to honestly
review your current schema to make sure it's going to meet your usage
needs not just your data storage requirements.  Ask the people who need 
      
to
  
use the data you are storing what they need to find and adjust your
schemas to fit. Sometimes it means asking them to look by using a
different method as a compromise (you give a little, they give a 
      
little).
  
Eventually you can work it out.

If you have a schema design issue or if you have a more specific set of
tables (please post their SHOW CREATE TABLE... results) and a sample 
      
query
  
that you cannot solve (even if it's only a description of what you need 
      
to
  
find), I am sure the list will be happy to help. Many of us really 
      
enjoy
  
those kinds of puzzles.

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

      
_________________________________________________________________
Stay in touch with absent friends - get MSN Messenger 
http://www.msn.co.uk/messenger

    

  

No virus found in this incoming message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.8.1 - Release Date: 3/23/2005