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 ***************************
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?
>To: "mel list_php" <list_php@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
> > 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.
> > 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
> > > > 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
> > >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
> > >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
> > --
> > 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
>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.
>Unimin Corporation - Spruce Pine
Stay in touch with absent friends - get MSN Messenger