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
