List:General Discussion« Previous MessageNext Message »
From:Peter Brawley Date:March 23 2005 4:46pm
Subject:Re: search through one/several tables
View as plain text  
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@stripped 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@stripped> 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@stripped
>>>To: "mel list_php" <list_php@stripped>
>>>CC: Alec.Cawley@stripped,mysql@stripped
>>>Subject: Re: search through one/several tables
>>>Date: Wed, 23 Mar 2005 09:55:23 -0500
>>>
>>>"mel list_php" <list_php@stripped> 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@stripped
>>>>>To: list_php@stripped
>>>>>CC: mysql@stripped
>>>>>Subject: Re: search through one/several tables
>>>>>Date: Wed, 23 Mar 2005 10:44:53 +0000
>>>>>
>>>>>"mel list_php" <list_php@stripped> 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=1
>>>>>
>>>>>          
>>>>>
>>>>_________________________________________________________________
>>>>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=1
>>>>
>>>>        
>>>>
>>>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
>  
>

Attachment: [text/html]
No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.8.1 - Release Date: 3/23/2005
Thread
search through one/several tablesmel list_php23 Mar
  • Re: search through one/several tablesAlec.Cawley23 Mar
    • Re: search through one/several tablesmel list_php23 Mar
      • Re: search through one/several tablesAlec.Cawley23 Mar
      • Re: search through one/several tablesSGreen23 Mar
        • Re: search through one/several tablesmel list_php23 Mar
          • Re: search through one/several tablesSGreen23 Mar
            • Re: search through one/several tablesPeter Brawley23 Mar
              • Re: search through one/several tablesmel list_php23 Mar
                • Re: search through one/several tablesSGreen23 Mar
                • Re: search through one/several tablesPeter Brawley23 Mar
RE: search through one/several tablesPat 006 Adams23 Mar