List:General Discussion« Previous MessageNext Message »
From:SGreen Date:March 23 2005 4:14pm
Subject:Re: search through one/several tables
View as plain text  
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
> 

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