From: Peter Brawley Date: March 23 2005 5:46pm Subject: Re: search through one/several tables List-Archive: http://lists.mysql.com/mysql/181633 Message-Id: <4241AB8A.8030208@earthlink.net> MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Right, my mistake, you would have to use HAVING. PB mel list_php wrote: > Unfortunatly I can't use the alias txt in the where clause: > Standard SQL doesn't allow you to refer to a column alias in a WHERE > clause. This is because when the WHERE code is executed, the column > value may not yet be determined. For example, the following query is > illegal: > > SELECT id, COUNT(*) AS cnt FROM tbl_name WHERE cnt > 0 GROUP BY id; > > http://dev.mysql.com/doc/mysql/en/problems-with-alias.html > > > >> From: Peter Brawley >> Reply-To: peter.brawley@stripped >> To: SGreen@stripped >> CC: mel list_php , mysql@stripped >> Subject: Re: search through one/several tables >> Date: Wed, 23 Mar 2005 10:46:10 -0600 >> >> 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 >> >FROM >> >WHERE ANY_COLUMN() LIKE >> >> 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 >>> FROM

>>> WHERE ANY_COLUMN() LIKE >>> >>> 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" 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" >>>>> 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" 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" 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@stripped >>>>>>> >>>>>>> >>>>>>> >>>>>> _________________________________________________________________ >>>>>> 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@stripped >>>>>> >>>>>> >>>>>> >>>>> 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 >>> >>> >> 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 >> >> -- >> MySQL General Mailing List >> For list archives: http://lists.mysql.com/mysql >> To unsubscribe: >> http://lists.mysql.com/mysql?unsub=list_php@stripped > > > _________________________________________________________________ > Want to block unwanted pop-ups? Download the free MSN Toolbar now! > http://toolbar.msn.co.uk/ > > > -- 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