full text search
Hi, I've always done conventional searches Where ( title like '%$key1%') or(isynopsis like '%$key2%') etc etc But the client has increased the complexity of the search and especially the size of his database and the search has really slowed. (particularly now that I have to search a longtext field.) So should I consider fulltext searching? Do I have to build an index first and how often is it updated etc? -- zzapper Success for Techies and Vim,Zsh tips http://SuccessTheory.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Calling MySQL often risk of performance problems?
Hi, When I started out I used to do a single query and store the data in a Perl/PHP datastructure. I've noticed with time that I'm treating MySQL as though it were part of PHP/Perl. ie I call a MySQL primitive everytime I need to read a table/lookup table etc. I develop Shoppingbaskets/CMS systems for smaller client websites etc where speed is not the top criteria. What are your views/experience. -- zzapper Success for Techies and Vim,Zsh tips http://SuccessTheory.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Comparing Numbers in Text Fields
Hi, Recently had a few problems with comparing numbers stored in text mysql fields char(2), which I solved by chnging the field to int(1) Now my question is why do you sometimes appear to get away with this? -- zzapper Success for Techies and Vim,Zsh tips http://SuccessTheory.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Comparing Numbers in Text Fields
On Wed, 12 Oct 2005 13:27:52 +0200, wrote: zzapper wrote: Hi, Recently had a few problems with comparing numbers stored in text mysql fields char(2), which I solved by chnging the field to int(1) Now my question is why do you sometimes appear to get away with this? My guess is that if the numbers are of the same length (number of characters) then the alpha sorting works ok. -- mack / Mack, I guess you are right, as coincidentally, I recently had the same problem with MS-Access DB where a search for largest number in a column worked until the largest number exceeded 999, thereafter 999 was seen as greater than 1000.. -- zzapper Success for Techies and Vim,Zsh tips http://SuccessTheory.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Avoiding complex mysql selects
On Tue, 12 Jul 2005 18:34:38 -0400, wrote: H, how about using full text indexing? It will be a lot easier to implement and a lot quicker. Can you give more details? -- zzapper vim -c :%s%s*%Cyrnfr)fcbafbe[Oenz(Zbbyranne%|:%s)[[()])-)Ig|norm Vg? http://www.rayninfo.co.uk/tips/ vim, zsh success tips -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Conditions in selects
On Wed, 13 Jul 2005 00:34:15 -0400, wrote:
Scott Haneda wrote:
I can not get this to work how I would think it should be formatted:
SELECT IF(NOW() BETWEEN sale_start AND sale_end, 'yes', 'no')
That seems to work just fine
It should, as that is correct syntax, as documented in the manual
http://dev.mysql.com/doc/mysql/en/comparison-operators.html.
However, I was under the impression that BETWEEN was to be formatted
BETWEEN(va1, v2)
Where did you get that idea?
Which leads me to:
SELECT IF(NOW() BETWEEN(sale_start AND sale_end), 'yes', 'no')
Which gives me a error of:
You have an error in your SQL syntax. Check the manual that corresponds to
your MySQL server version for the right syntax to use near ' 'yes', 'no')
FROM products WHERE id = 75
This is invalid syntax, hence the error message. Mysql expects BETWEEN
min_value AND max_value. Your parentheses mean that min_value is the result
of (sale_start AND sale_end), which will be 0 (false) if either sale_start or
sale_end is 0, otherwise it will be 1 (true). Then you are missing the 'AND
max_value' part.
Michael
Could we have the corrected sql here pls?
I guess
SELECT IF(NOW()) BETWEEN sale_start AND sale_end,'no','yes')
another example
SELECT IF(STRCMP('test','test1'),'no','yes');
- 'no'
--
zzapper
vim -c :%s%s*%Cyrnfr)fcbafbe[Oenz(Zbbyranne%|:%s)[[()])-)Ig|norm Vg?
http://www.rayninfo.co.uk/tips/ vim, zsh success tips
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Avoiding complex mysql selects
On Wed, 13 Jul 2005 09:24:20 -0400, wrote:
Create a full text index that encompasses the fields you want to
search in (synopsis, title, keywords).
Then format your select to take advantage of the full text index:
select * from dbname where match(synopsis, title, keywords) against
('word1 word2 phrase one etc' in boolean mode)
You should read the manual on full text indexing since there are a
lot of options. You can do relevance ranking, adjust the importance
of the words you are searching on, etc. There are also limitations
you need to be aware of, like no relevance when searching in boolean
mode, can't be used on InnoDB tables, etc.
O gosh my learning curve
Thanks
--
zzapper
vim -c :%s%s*%Cyrnfr)fcbafbe[Oenz(Zbbyranne%|:%s)[[()])-)Ig|norm Vg?
http://www.rayninfo.co.uk/tips/ vim, zsh success tips
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
myisam or innodb (chestnut)
Hi, I created the same database on two different servers (with different versions of mysql). I found one db had been created as innodb and the myisam without me apparently having a say in the matter. I've Googled and found that Innodb has record locking, roll back, but that MYISAM is quicker. Is myisam still dominant for web applications? -- zzapper vim -c :%s%s*%Cyrnfr)fcbafbe[Oenz(Zbbyranne%|:%s)[[()])-)Ig|norm Vg? http://www.rayninfo.co.uk/tips/ vim, zsh success tips -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Avoiding complex mysql selects
Hi I have a piece of code which will search a series of records for the presence of two keywords $key1 and $key2 (see below) The client now wants more keywords $key1,2,3,4 etc But is there any alternative to mushrooming the the following select??? $wherekeyword =__SQL__ and ( ( (synopsis like '%$key1%') or (titlelike '%$key1%') or (keywords like '%$key1%') ) and ( (synopsis like '%$key2%') or (titlelike '%$key2%') or (keywords like '%$key2%') ) ) __SQL__; } -- zzapper vim -c :%s%s*%Cyrnfr)fcbafbe[Oenz(Zbbyranne%|:%s)[[()])-)Ig|norm Vg? http://www.rayninfo.co.uk/tips/ vim, zsh success tips -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Records which link to other records in same table
On Thu, 30 Jun 2005 16:47:43 +0100, wrote: Hi, I'm designing a simple family tree db which is at present just a flat table In which each record everyone has a father mother, a variable number of wives,and variable number of children. The links to other family tree members is always the record_id of another record. At present I'm manually inserting the links eg my father is record_id 52, my mother recordid 60 my children records 100,101,102 I can write queries to display everyone's father mother children etc no problem. * But My question is Is it possible/useful to define any sort of relationships/linking the wife/father/mother/children fields and the recordid of other records I'm fogging on this, your advice pls My experience so far is that everything is done at the mysql query level and nothing required/possible at the data-design level. The only thing open for me is whether the children links (up to 20 for 1 father) shouldn't be in a separate pairs link table; what's the name of this type of table? eg parent-children table eg father 234 child 450 father 234 child 451 father 234 child 453 mother 235 child 450 mother 235 child 451 mother 235 child 453 Now would it be useful to have a couple identifier? to simplify the above -- zzapper vim -c :%s%s*%Cyrnfr)fcbafbe[Oenz(Zbbyranne%|:%s)[[()])-)Ig|norm Vg? http://www.rayninfo.co.uk/tips/ vim, zsh success tips -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Update query to set random data
Hi, In order to generate some useful test data eg randomnly setting the flag select_normal to 0 or 1 use the following update tbl_products set select_normal=round(rand()); I've actually answered my own question (as usual in formulating the question you often can workout the answer) But anyone got any other useful queries in this line? -- zzapper vim -c :%s%s*%Cyrnfr)fcbafbe[Oenz(Zbbyranne%|:%s)[[()])-)Ig|norm Vg? http://www.rayninfo.co.uk/tips/ vim, zsh success tips -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Records which link to other records in same table
Hi, I'm designing a simple family tree db which is at present just a flat table In which each record everyone has a father mother, a variable number of wives,and variable number of children. The links to other family tree members is always the record_id of another record. At present I'm manually inserting the links eg my father is record_id 52, my mother recordid 60 my children records 100,101,102 I can write queries to display everyone's father mother children etc no problem. * But My question is Is it possible/useful to define any sort of relationships/linking the wife/father/mother/children fields and the recordid of other records I'm fogging on this, your advice pls -- zzapper vim -c :%s%s*%Cyrnfr)fcbafbe[Oenz(Zbbyranne%|:%s)[[()])-)Ig|norm Vg? http://www.rayninfo.co.uk/tips/ vim, zsh success tips -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Using MySQL to store/archive code/html etc?
On Fri, 27 May 2005 15:39:36 -0400, wrote: news [EMAIL PROTECTED] wrote on 05/27/2005 02:46:09 PM: It is possible to do just what you propose. You could even FT index your code so that you could find certain key phrases (assuming that the keyphrase you want is at least 4 (or 3) characters long and does not appear in over 50% of the documents. HOWEVER, it is more proper to store files in a file system and just store the files path information in a database (along with other information like who it was for, who wrote it, etc.) Once you start doing that, though, you might as well take that last itty-bitty step and move into a full version-control system. There are several out there, many of them are free. Not only do they keep your code well organized (and protected, usually) but it allows you to do all sorts of interesting things (like reverting to previous versions). That way if you modified the same file 5 times for 5 different clients, you wouldn't have to have 5 copies floating around. Your VCS software would allow you to regress your current version to any prior version. In my humble opinion, that's the appropriate class of tool for the task you mention. Some sample names of VCS software: BitKeeper, Visual Source Safe. Shawn Green Shawn, I maintain, update dozens of websites, and many more sites that have been archived. Many of them have hundreds of pages but I'm only involved in a few of them etc. What I'm looking for is a way of storing,identifying code nuggets. I currently do this in flat text files but these are better suited to recording say a cute mysql query etc than a wodge of html. and already use Googles Desktop Search, plus my own Intranet Search grep,find, etc. Now amongst other problems these pages have non-unique names index.html etc. I feel the question of whether I should be using VCS is another issue, probably should be! I 'm searching for another level of retrieval sophistication. Sad that a database does not appear ideal. Welcome any lateral ideas -- zzapper vim -c :%s%s*%Cyrnfr)fcbafbe[Oenz(Zbbyranne%|:%s)[[()])-)Ig|norm Vg? http://www.rayninfo.co.uk/tips/ vim, zsh success tips -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Using MySQL to store/archive code/html etc?
Hi, Today I designed a fairly cute error404.php page, which I'd like to reuse in future. Currently I rely on remembering that I created such a page for say ACME Carpets. Now and again my memory fails to remember which site I developed a piece of HTML,PHP,Perl,Javascript for and I spend more time going thru CD backups etc than it would take to start again. I've postulated using MySql as a store for such pages. but is a database suitable for storing, searching for and retrieving such information.??? Your eminent opinions and experiences desired!! -- zzapper vim -c :%s%s*%Cyrnfr)fcbafbe[Oenz(Zbbyranne%|:%s)[[()])-)Ig|norm Vg? http://www.rayninfo.co.uk/tips/ vim, zsh success tips -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Cleaning muck out of data fields
On Fri, 13 May 2005 08:29:46 +0200, wrote: Hi, if the first characters are numerics, no need to use regexp, since mysql does implicit conversion if you do calculations : mysql select '10.95 tiitti' from dual; +--+ | 10.95 tiitti | +--+ | 10.95 tiitti | +--+ 1 row in set (0.09 sec) mysql select '10.95 tiitti'+0 from dual; +--+ | '10.95 tiitti'+0 | +--+ |10.95 | +--+ 1 row in set (0.02 sec) Mathais Can I write the clean value back? , (i will give it a try) -- zzapper vim -c :%s%s*%Cyrnfr)fcbafbe[Oenz(Zbbyranne%|:%s)[[()])-)Ig|norm Vg? http://www.rayninfo.co.uk/tips/ vim, zsh success tips -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: query to lowercase database columns names
On Wed, 11 May 2005 15:08:50 +0200, wrote: Selon Jay Blanchard [EMAIL PROTECTED]: [snip] I've got a converted from Excel spreadsheet to mysql database, which has mixed case column names and With advice from this thread, what I ended up doing was show create table tbl_products; To get syntax such `01_Desc` varchar(255) default NULL, `01_Lcode` varchar(255) default NULL, I then used a text editor www.vim.org to generate the following mysql command using VIM regexp substitutes alter table tbl_products change `01_Desc` `p01_desc` varchar(255) default NULL, change `01_lLcode` `p01_lcode` varchar(255) default NULL, etc eg VIM commands :%s#^[^ ]\+#change p\L :%s# p`# `p# This worked very well -- zzapper vim -c :%s%s*%Cyrnfr)fcbafbe[Oenz(Zbbyranne%|:%s)[[()])-)Ig|norm Vg? http://www.rayninfo.co.uk/tips/ vim, zsh success tips -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Cleaning muck out of data fields
Hi,
I wanted to clean up some numeric currency data fields which had some
non-numeric values which took
the first two characters of the field (they were some kind of garbage
characters) anyway the
following did the trick
update tbl_products set p10_price=mid(p10_price,2) where p10_price regexp
('[^0-9.]');
Which worked because the mucky characters were always the first two digits but
it's still cludgy.
What I really wanted to do was just filter out the good any currency numerics
of form \d+.\d\d
10.95 but as mysql only supports regexp when matching, I couldn't think of a
way. I have the same
problem if I try to Locate I cant AFAIK say locate first digit.
Comments/Ideas?
Q2)
Can I match mucky non-alphanumerics ?
--
zzapper
vim -c :%s%s*%Cyrnfr)fcbafbe[Oenz(Zbbyranne%|:%s)[[()])-)Ig|norm Vg?
http://www.rayninfo.co.uk/tips/ vim, zsh success tips
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: query to lowercase database columns names
On Mon, 09 May 2005 17:32:29 +0100, wrote: Hi, Is there a query which will alter a table to rename all the columns to lowercase eg Price to price, Quantity to quantity Funny how sometimes a UseNet query is answered in minutes, and or exites a flurry of responses, and othertimes like here zilch! I've got a converted from Excel spreadsheet to mysql database, which has mixed case column names and also columns beginning with a digit eg 01_name, 02_address etc what upsets PHP considerably. So I'd like to have a way to generically rename all columns beginning with a numeric form ^\d.* to someprefix_ Is it not possible to write such a query, or is this best done by Perl or PHP script? -- zzapper vim -c :%s%s*%Cyrnfr)fcbafbe[Oenz(Zbbyranne%|:%s)[[()])-)Ig|norm Vg? http://www.rayninfo.co.uk/tips/ vim, zsh success tips -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: query to lowercase database columns names
On Wed, 11 May 2005 12:22:21 +0200, wrote: Hi, this is the idea, mysql create table t (01_t varchar(10)); ERROR 1046 (3D000): No database selected mysql mysql use world Database changed mysql create table t (01_t varchar(10)); Query OK, 0 rows affected (0.20 sec) mysql mysql mysql desc t - ; +---+-+--+-+-+---+ | Field | Type| Null | Key | Default | Extra | +---+-+--+-+-+---+ | 01_t | varchar(10) | YES | | NULL| | +---+-+--+-+-+---+ 1 row in set (0.01 sec) mysql alter table t change 01_t something_01_t varchar(15); Query OK, 0 rows affected (0.17 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql desc t - ; ++-+--+-+-+---+ | Field | Type| Null | Key | Default | Extra | ++-+--+-+-+---+ | something_01_t | varchar(15) | YES | | NULL| | ++-+--+-+-+---+ 1 row in set (0.00 sec) Scripting this query on all tables, and all columns from a perl or php script can help you Mathias Yes of course I can use Perl to generate a multiple MySQL Alter script rather than actually doing it via say Perl:DBI::MySQL -- zzapper vim -c :%s%s*%Cyrnfr)fcbafbe[Oenz(Zbbyranne%|:%s)[[()])-)Ig|norm Vg? http://www.rayninfo.co.uk/tips/ vim, zsh success tips -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Data Design : Numeric or keyword lookup values?
On Sat, 7 May 2005 09:35:21 +0100, wrote: One issue you have to consider is how the data is entered. If the user selects from a drop down list then this is ok but you need to plan how the drop down list is compiled. If not then you need to thinks about spelling errors, language issues etc. Charles Walmsley Charles, The data-entry needs to be controlled whether numeric or keyword, usually by drop-down. I will come to some conlusion with my present complex, where I will designing my database from scratch. -- zzapper vim -c :%s%s*%Cyrnfr)fcbafbe[Oenz(Zbbyranne%|:%s)[[()])-)Ig|norm Vg? http://www.rayninfo.co.uk/tips/ vim, zsh success tips -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
query to lowercase database columns names
Hi, Is there a query which will alter a table to rename all the columns to lowercase eg Price to price, Quantity to quantity -- zzapper vim -c :%s%s*%Cyrnfr)fcbafbe[Oenz(Zbbyranne%|:%s)[[()])-)Ig|norm Vg? http://www.rayninfo.co.uk/tips/ vim, zsh success tips -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Data Design : Numeric or keyword lookup values?
hi, When designing data it is common to have lookup tables such animal_type : dog=1, cat=2,bird=3 etc And then in other tables to refer to animals by their number 1, 2 or 3. This is memory and presumably speed efficient. Howver not much fun for humans who are reading/debugging the data. Alternatively it's possible to have a lookup table containing animal types as text strings cat dog, bird and actually then use the actual names where ever required in other tables. If table size speed are not top priority is there anything wrong with using 2nd method? Views/opinions please -- zzapper vim -c :%s%s*%Cyrnfr)fcbafbe[Oenz(Zbbyranne%|:%s)[[()])-)Ig|norm Vg? http://www.rayninfo.co.uk/tips/ vim, zsh success tips -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Multi Table Delete in 3.23.47
On Wed, 27 Apr 2005 09:50:37 +0100, wrote: zzapper wrote: On Tue, 26 Apr 2005 23:22:46 +0100, wrote: Hi, The following query runs fine in recent versions of mysql 4.1x etc delete from t2 using tbl_User as t1,tbl_UserTopic as t2 where (t1.txtemail='[EMAIL PROTECTED]') and (t1.intID = t2.intID); But not in 3.23.47 I can't find any reference in the manuals to why this might be. Is there any change in syntax which will make the query work in 3.23.47? I mean how did you do this before 4.x?? No trick will make it work with a 3.23. You'll need to program it yourself with your favorite langage (PHP, Perl, Templeet, C...). I know it's painful... (but it's great to have it in 4.0 as well as multi-table updates) -- Philippe Poelvoorde COS Trading Ltd. Thanx Philippe that stops me scratching about looking for a non-existant solution.I'm happy to write a perl script just wished I tried it sooner! It's so wretched when your ISP won't upgrade their S/W -- zzapper vim -c :%s%s*%Cyrnfr)fcbafbe[Oenz(Zbbyranne%|:%s)[[()])-)Ig|norm Vg? http://www.rayninfo.co.uk/tips/ vim, zsh success tips -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Multi Table Delete in 3.23.47
Hi, Final whinge: I wish the mysql website made it a little clearer which features are recent additions, I suggested color coding. BTW regards multi table deletes there's something nasty there between 4.0 and 4.1 -- zzapper vim -c :%s%s*%Cyrnfr)fcbafbe[Oenz(Zbbyranne%|:%s)[[()])-)Ig|norm Vg? http://www.rayninfo.co.uk/tips/ vim, zsh success tips -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Multi Table Delete in 3.23.47
On Wed, 27 Apr 2005 09:51:41 -0400, wrote: My subject is a bit of a misnomer in fact the Where refers to multiple tables but the delete is just from one table, nevertheless my query worked super-dandy in 4.1 (on my test rig) and failed on the live server 3.23.47 . that's why I couldn't work out which bit the doc refered to. BTW Whinge is a rather a mild term in the UK, -- zzapper vim -c :%s%s*%Cyrnfr)fcbafbe[Oenz(Zbbyranne%|:%s)[[()])-)Ig|norm Vg? http://www.rayninfo.co.uk/tips/ vim, zsh success tips -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Multi Table Delete in 3.23.47
Hi, The following query runs fine in recent versions of mysql 4.1x etc delete from t2 using tbl_User as t1,tbl_UserTopic as t2 where (t1.txtemail='[EMAIL PROTECTED]') and (t1.intID = t2.intID); But not in 3.23.47 I can't find any reference in the manuals to why this might be. Is there any change in syntax which will make the query work in 3.23.47? -- zzapper vim -c :%s%s*%Cyrnfr)fcbafbe[Oenz(Zbbyranne%|:%s)[[()])-)Ig|norm Vg? http://www.rayninfo.co.uk/tips/ vim, zsh success tips -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Multi Table Delete in 3.23.47
On Tue, 26 Apr 2005 23:22:46 +0100, wrote: Hi, The following query runs fine in recent versions of mysql 4.1x etc delete from t2 using tbl_User as t1,tbl_UserTopic as t2 where (t1.txtemail='[EMAIL PROTECTED]') and (t1.intID = t2.intID); But not in 3.23.47 I can't find any reference in the manuals to why this might be. Is there any change in syntax which will make the query work in 3.23.47? I mean how did you do this before 4.x?? -- zzapper vim -c :%s%s*%Cyrnfr)fcbafbe[Oenz(Zbbyranne%|:%s)[[()])-)Ig|norm Vg? http://www.rayninfo.co.uk/tips/ vim, zsh success tips -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
General substring update query required
Hi, I want to replace every occurence of a string with another. The following query works fine (appears to anyway) update tbl_websitepages set page_content=replace (page_content,'develop','bucket'); But is there any way to get any Regexp into the substitution? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Repairing Data packing with zeros
Hi, I needed to repair some data that had been entered inconsistently, I was in a rush so I did it manually via SqlYOG. (too much of a rush to consider how to do it quickly!) The bad data in one column propertyID was of kind \w\w\w\d eg ONE1 \w\w\w\d\d eg ABC23 \w\w\w\d\d\d eg FGH123 when they all needed to packed with zeros as necessary \w\w\w\d\d\d\d so ONE1- ONE0001 ABC23 - ABC0023 FGH123 - FGH0123 What Update Query should/could I have used? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Repairing Data packing with zeros
On Fri, 25 Mar 2005 12:03:26 -0500, wrote:
zzapper wrote:
ONE1- ONE0001
ABC23 - ABC0023
FGH123 - FGH0123
What Update Query should/could I have used?
Something like this perhaps?
UPDATE table_name SET propertyID = CONCAT(LEFT(propertyID, 3),
LPAD(SUBSTRING(propertyID, 4), 4, '0'))
WHERE propertyID REGEXP '^[A-Z]{3}[0-9]{1,3}$';
Keith,
Thanx that works just fine
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Why are my tables read only?
Hi, After upgrading to mysql 4.1.10nt I am being told that some of my tables are read-only I cannot find where or how you might specify that a table is read/write to whatever -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Upgrading to MySQL 4.1X Win32 Horror
Hi Yesterday upgraded from 4.0.2 to to 4.1.10 Stages of Horror 1) You have uninstall old version (does not delete your databases fortunately, but backup any way 2a) B4 installing Either also remove references to mysql in the registry OR during installation name choose Service name mysql41 (not mysql) 2b) New installation defaults to c:\program files\mysql rather than c:\mysql I advise you to accept this, I tried to load it elsewhere w/o success OK Mysql now runs phew, but now your real problems begin None of your db apps will now run! It's something to do with a new password algorithm You are supposed to be able to fix this by specifying old passwords in your new my.ini You are also advised to upgrade Perl,PHP, Drivers etc This didn't work for me I'm temporarily using:- #in my.ini skip-grant-tables THIS WORKED but I can do this because I work only a test server, but it's no solution to others. IAC best read (real carefully) http://dev.mysql.com/doc/mysql/en/upgrading-from-4-0.html http://dev.mysql.com/doc/mysql/en/windows-upgrading.html Hey I now read that I should have upgraded from 4.0.2 to 4.0.15 and then upgraded my grant tables On Windows systems, there isn't an easy way to update the grant tables until MySQL 4.0.15. From version 4.0.15 on, MySQL distributions include a mysql_fix_privilege_tables.sql SQL script that you can run using the mysql client. If your MySQL installation is located at C:\mysql, the commands look like this: C:\ C:\mysql\bin\mysql -u root -p mysql mysql SOURCE C:\mysql\scripts\mysql_fix_privilege_tables.sql Just ran this made no difference! YUK YUK YUK zzapper (vim, cygwin, wiki zsh) -- vim -c :%s%s*%CyrnfrTfcbafbeROenzSZbbyranne%|:%s)[R-T]) )Ig|:norm G1VGg? http://www.vim.org/tips/tip.php?tip_id=305 Best of Vim Tips -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Is there a Library of complex queries/inserts/queries?
On Sun, 13 Feb 2005 10:34:39 +, wrote: Hi, Sometimes an example is worth a 1000 words. Does anyone know of a website with lists of mysql statement examples? ie a list of queries, a list of updates, list of inserts from simple examples to joins regexps etc From Shantanu http://en.wikibooks.org/wiki/Appendix mysql statement examples zzapper (vim, cygwin, wiki zsh) -- vim -c :%s%s*%CyrnfrTfcbafbeROenzSZbbyranne%|:%s)[R-T]) )Ig|:norm G1VGg? http://www.vim.org/tips/tip.php?tip_id=305 Best of Vim Tips -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Filtering non-ascii characters from mysql data, null, tab etc
On Fri, 11 Feb 2005 18:41:38 -0500, wrote:
zzapper wrote:
On Fri, 11 Feb 2005 12:46:29 +0100 (CET), wrote:
Tom adapting your script,
create table test (txt varchar(255)) Type=MyISAM;
insert into test values('Some Text\nand some more');
update test set txt = replace(txt,'\n','');
BTW
\n = null
\0 seems to be something else
Turns out my rotten character (they all seem to display as a hollow box) was
a \r
thanx
zzapper (vim, cygwin, wiki zsh)
--
No. \n is a newline, \r is a return, and \0 is the null character C uses to
terminate strings. Continuing your example:
mysql CREATE TABLE test (id INT, txt VARCHAR(255));
Query OK, 0 rows affected (0.01 sec)
mysql INSERT INTO test VALUES (1, 'Some Text\0 and some more'),
- (2, 'Some Text\nand some more');
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql SELECT * FROM test;
+--+--+
| id | txt |
+--+--+
|1 | Some Text|
|2 | Some Text
and some more |
+--+--+
2 rows in set (0.00 sec)
mysql UPDATE test SET txt = REPLACE(txt, '\0', '');
Query OK, 1 row affected (0.13 sec)
Rows matched: 2 Changed: 1 Warnings: 0
mysql UPDATE test SET txt = REPLACE(txt, '\n', ' ');
Query OK, 1 row affected (0.00 sec)
Rows matched: 2 Changed: 1 Warnings: 0
mysql SELECT * FROM test;
+--+-+
| id | txt |
+--+-+
|1 | Some Text and some more |
|2 | Some Text and some more |
+--+-+
2 rows in set (0.00 sec)
Michael
Michael,
Thanx got it sussed now!
Any ideas on a more generic non-ascii filter, that could remove a range of
characters?
zzapper (vim, cygwin, wiki zsh)
--
vim -c :%s%s*%CyrnfrTfcbafbeROenzSZbbyranne%|:%s)[R-T]) )Ig|:norm G1VGg?
http://www.vim.org/tips/tip.php?tip_id=305 Best of Vim Tips
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Is there a Library of complex queries/inserts/queries?
Hi, Sometimes an example is worth a 1000 words. Does anyone know of a website with lists of mysql statement examples? ie a list of queries, a list of updates, list of inserts from simple examples to joins regexps etc zzapper (vim, cygwin, wiki zsh) -- vim -c :%s%s*%CyrnfrTfcbafbeROenzSZbbyranne%|:%s)[R-T]) )Ig|:norm G1VGg? http://www.vim.org/tips/tip.php?tip_id=305 Best of Vim Tips -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Filtering non-ascii characters from mysql data, null, tab etc
On Fri, 11 Feb 2005 12:46:29 +0100 (CET), wrote:
Tom adapting your script,
create table test (txt varchar(255)) Type=MyISAM;
insert into test values('Some Text\nand some more');
update test set txt = replace(txt,'\n','');
BTW
\n = null
\0 seems to be something else
Turns out my rotten character (they all seem to display as a hollow box) was a
\r
thanx
zzapper (vim, cygwin, wiki zsh)
--
vim -c :%s%s*%CyrnfrTfcbafbeROenzSZbbyranne%|:%s)[R-T]) )Ig|:norm G1VGg?
http://www.vim.org/tips/tip.php?tip_id=305 Best of Vim Tips
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Filtering non-ascii characters from mysql data, null, tab etc
On Fri, 11 Feb 2005 12:46:29 +0100 (CET), wrote:
Tom adapting your script,
create table test (txt varchar(255)) Type=MyISAM;
insert into test values('Some Text\nand some more');
update test set txt = replace(txt,'\n','');
BTW
\n = null
\0 seems to be something else
Turns out my rotten character (they all seem to display as a hollow box) was a
\r
thanx
zzapper (vim, cygwin, wiki zsh)
--
vim -c :%s%s*%CyrnfrTfcbafbeROenzSZbbyranne%|:%s)[R-T]) )Ig|:norm G1VGg?
http://www.vim.org/tips/tip.php?tip_id=305 Best of Vim Tips
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Filtering non-ascii characters from mysql data, null, tab etc
Hi, I've successfully used the following update-replace statement to replace strings in mysql data update tbl_county_lookup set countyname=replace(countyname,'amp;','and') ; However I've had problems trying to replace a null character 0x00h , I'd be interested to know the syntax to filter null characters. Secondly I'd be interested in a general filter for non-ascii. cheers zzapper (vim, cygwin, wiki zsh) -- vim -c :%s%s*%CyrnfrTfcbafbeROenzSZbbyranne%|:%s)[R-T]) )Ig|:norm G1VGg? http://www.vim.org/tips/tip.php?tip_id=305 Best of Vim Tips -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Filtering non-ascii characters from mysql data, null, tab etc
On Thu, 10 Feb 2005 10:17:00 +, wrote: Hi, I've successfully used the following update-replace statement to replace strings in mysql data update tbl_county_lookup set countyname=replace(countyname,'amp;','and') ; However I've had problems trying to replace a null character 0x00h , I'd be interested to know the syntax to filter null characters. Secondly I'd be interested in a general filter for non-ascii. cheers Can't believe I'm the only one who's ever had this problem, I've googled and just found a few fellow searchers!! (I will probably have to dump the db and use a perl script!) zzapper (vim, cygwin, wiki zsh) -- vim -c :%s%s*%CyrnfrTfcbafbeROenzSZbbyranne%|:%s)[R-T]) )Ig|:norm G1VGg? http://www.vim.org/tips/tip.php?tip_id=305 Best of Vim Tips -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Perl Script to transform a MySql insert into an update
Hi, Does anyone have a Perl Script (or other) to transform a MySql insert into an update (or vice-versa)??? zzapper (vim, cygwin, wiki zsh) -- vim -c :%s%s*%CyrnfrTfcbafbeROenzSZbbyranne%|:%s)[R-T]) )Ig|:norm G1VGg? http://www.vim.org/tips/tip.php?tip_id=305 Best of Vim Tips -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Perl Script to transform a MySql insert into an update
On Fri, 14 Jan 2005 18:42:41 +, wrote:
Hi,
Does anyone have a Perl Script (or other) to transform a MySql insert into an
update (or
vice-versa)???
Just wrote a QAD (Quick and dirty) script see below, has no error handling
#!perl
# sedit.pl
# description : Transform a mysql insert into an update statement
# zzapper [EMAIL PROTECTED]
# NB does not handle any commas in strings etc
$insert_str=q|(fred,joe) values ('$fred','$joe')|;
$_=$insert_str;
s#^\s*\(([^)]+)\)##;
$set_str=$1;
s#values\s+\(([^)]+)\)##;
$values_str=$1;
@set_array=split(/,/,$set_str);
@values_array=split(/,/,$values_str);
$update_str.=qq|set |;
for $setv (@set_array)
{
$valv=$values_array[$cnt++];
$update_str.=qq|$setv=$valv,|;
}
$update_str=~s/,$//;
print \ninsert :: $insert_str\n;
print \nupdate :: $update_str\n;
zzapper (vim, cygwin, wiki zsh)
--
vim -c :%s%s*%CyrnfrTfcbafbeROenzSZbbyranne%|:%s)[R-T]) )Ig|:norm G1VGg?
http://www.vim.org/tips/tip.php?tip_id=305 Best of Vim Tips
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
A query to swap the data in two fields
Hi, I want to swap the data in two fields eg t1.beds to t1.beds2 and t2.beds2 to t1.beds Do i need to temporarily store one of the fields (thinking like a programmer)? Can you suggest what query I need here pls? zzapper (vim, cygwin, wiki zsh) -- vim -c :%s%s*%CyrnfrTfcbafbeROenzSZbbyranne%|:%s)[R-T]) )Ig|:norm G1VGg? http://www.vim.org/tips/tip.php?tip_id=305 Best of Vim Tips -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: A query to swap the data in two fields
On Thu, 23 Sep 2004 11:03:08 +0100, wrote: Hi, I want to swap the data in two fields eg t1.beds to t1.beds2 and t2.beds2 to t1.beds do I need to store one of the fields temporarily? Hey chums you normally get me an answer in minutes, is my question too easy? zzapper (vim, cygwin, wiki zsh) -- vim -c :%s%s*%CyrnfrTfcbafbeROenzSZbbyranne%|:%s)[R-T]) )Ig|:norm G1VGg? http://www.vim.org/tips/tip.php?tip_id=305 Best of Vim Tips -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: A query to swap the data in two fields
On Thu, 23 Sep 2004 13:31:03 -0500, wrote: At 10:04 AM 9/23/2004, you wrote: On Thu, 23 Sep 2004 11:03:08 +0100, wrote: Hi, I want to swap the data in two fields eg t1.beds to t1.beds2 and t2.beds2 to t1.beds do I need to store one of the fields temporarily? Hey chums you normally get me an answer in minutes, is my question too easy? zzapper (vim, cygwin, wiki zsh) -- zzapper, Gee, have you paid your bill yet?g The only thing I've been able to come up with is: update table1 a, table1 b set a.beds1=b.beds2, a.beds2=b.beds1 where a.rcd_id=b.rcd_id You need to create a transitory value for one of the variables and this is done by joining the table onto itself (the b table won't get updated as table a gets updated). This solution should work on any column type. There is probably a faster more efficient way of doing it (without a table join), but only if we knew what the column types were. If they were String then perhaps subscripting out the strings would be possible. Mike P.S. Now about your bill... :) Mike, cheque in the post! Sorry about my impertinence, I think I'm going to be a coward and create an extra column for the transitory value, as it's a one off operation. Just had a thought (dangerous I know) but couldn't I just rename the fields as that would do the same thing? zzapper (vim, cygwin, wiki zsh) -- vim -c :%s%s*%CyrnfrTfcbafbeROenzSZbbyranne%|:%s)[R-T]) )Ig|:norm G1VGg? http://www.vim.org/tips/tip.php?tip_id=305 Best of Vim Tips -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Understanding Joins : Baffled
On Fri, 3 Sep 2004 15:47:26 -0400, wrote: A. Any record in tbl_development can be associated with up to 8 URLs that represent virtual tours of the dwelling types in that particular development. B. tbl_urldetails is a table acting as a master list of the virtual tours you have ready to use. The records in tbl_development should reference these URLs. This is what we call a many-to-many relationship and is commonly modeled in a relational database with a 3-table structure. It is a many-to-many because: 1) Each development can associate itself to several (up to 8, you say) URLs 2) Each URL can be associated with any of several (or none) developments. You already have two of the tables you need, what you are missing is the association or relationship table. It is usually just a two column table but more columns can be added if you need to store information about the combination of a development to a virtual tour URL. I will make up a column that fits that last category and explain why it fits on this third table. Here is what your association table could look like: CREATE TABLE development_URL ( tbl_development_intDevID mediumint(9) not null, tbl_urldetails_intID int(11) not null, dateAvailable datetime null, PRIMARY KEY(tbl_development_intDevID, tbl_urldetails_intID) ) INSERT development_URL VALUES(50,7,'2004-11-01'); Let's say that property 50 also needs to show the URLs for tours 1,2, and 4 right now. INSERT development_URL VALUES (50,1,null), (50,2,null), (50,4,null) Shawn, I wrote this query using your relation table. select t1.tbl_development_intdevid,r1.* from development_url as t1 inner join tbl_urldetails as r1 on r1.intid = t1.tbl_urldetails_intid where tbl_development_intdevid='50'; Thanks very much for your help, I still struggle to think database, but my understanding will now (hopefully) leapfrog. It's always a struggle when you are at such a low level of your learning curve that you cannot easily formulate your pleas for help!! zzapper (vim, cygwin, wiki zsh) -- vim -c :%s%s*%CyrnfrTfcbafbeROenzSZbbyranne%|:%s)[R-T]) )Ig|:norm G1VGg? http://www.vim.org/tips/tip.php?tip_id=305 Best of Vim Tips -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Is it possible to have Undeletable Records?
Hi Y'All Is it possible to have undeletable/unmodifiable records in a table of otherwise modifiable deleteable createable records? At present I do it at the Update/Delete level where I have clauses which prevent certain records being changed. I suppose I could also have an extra boolean field Record Read only, but is there anyway to specify at the data level? zzapper (vim, cygwin, wiki zsh) -- vim -c :%s%s*%CyrnfrTfcbafbeROenzSZbbyranne%|:%s)[R-T]) )Ig|:norm G1VGg? http://www.vim.org/tips/tip.php?tip_id=305 Best of Vim Tips -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Understanding Joins : Baffled
Hi, I have a main query which can reads a record tbl_development This has a varying number of the following fields IDurl1 IDurl2 ... IDurln The fields contain indexes into another table tbl_urldetails where each record contains details about each URL eg hyperlink, description Currently I preload tbl_urldetails into a (coldFusion) array and use the IDurl1, IDurl2 etc to get the required URL details, I know this is cock-eyed way of doing this, but cannot see how to use a JOIN What I GUESS it should like is SELECT *FROM tbl_development as dev INNER JOIN tbl_urldetails AS url ON dev.IDurl1 = url.intID WHERE intDevID = #intDevID# But that only Joins one record. and I cant understand how I would access the results of the query Baffled help!!! zzapper (vim, cygwin, wiki zsh) -- vim -c :%s%s*%CyrnfrTfcbafbeROenzSZbbyranne%|:%s)[R-T]) )Ig|:norm G1VGg? http://www.vim.org/tips/tip.php?tip_id=305 Best of Vim Tips -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Understanding Joins : Baffled
On Fri, 3 Sep 2004 13:06:45 -0400, wrote: I don't understand what do you mean by trim out the irrelevant? If certain columns aren't important then why are they in your tables? I don't need any data, only the structures. You do know that you don't need to post to an actual NG to get your message to the list, right?. You only need to CC your emails to: [EMAIL PROTECTED] Shawn Green Database Administrator Unimin Corporation - Spruce Pine David Rayner [EMAIL PROTECTED] wrote on 09/03/2004 01:02:01 PM: Shawn, Will reply directly to NG (just had an access problem today) reason am delating post is that one table is huge and I need to trim out the irrelevant Thanks so far!!! You will normally get better responses if you always CC the list in each of your replies (unless you are intentionally going off-list). Yes, it sounds like you are talking about a relationship to me, too. If the we had your existing table structures in front of us we could help you to understand just how easy this is. This is a very important topic of database design. Please post as soon as you are able. Shawn Green Database Administrator Unimin Corporation - Spruce Pine David Rayner [EMAIL PROTECTED]wrote on 09/03/2004 11:15:41 AM: Shawn, Will post email table info later. tbl_urldetails is essentially a lookup table containg a list of 30 or so urls. each record in tbl_development references up to 8 of these URLS by storing the id of the particular record in tbl_urldetails. When I retrieve a record from tbl_development, I read the index and then want to get the full record, I suppose I could do this by doing a new query of tbl_urldetails (for each referenced URLid) , but that seems wasteful or is it? I suppose I'm talking about is a relationship? Surely this a bread and butter thing to do for a database (but I cant see it?) You say you have a table that changes how many columns it has? Please post the results of: SHOW CREATE TABLE tbl_developent\G SHOW CREATE TABLE tbl_urldetails\G (lookup table) CREATE TABLE `tbl_urldetails` ( intID` int(11) NOT NULL auto_increment, `vtEhouseName` varchar(50) default NULL, `vtBarrattName` varchar(50) default NULL, `vtDescription` text, `vtInternalNote` varchar(50) default NULL, `vtHyperlink` varchar(50) default NULL, `dtmVTLastUpdate` timestamp(14) NOT NULL, `vtRandom` int(3) default NULL, PRIMARY KEY (`intID`)) TYPE=MyISAM CREATE TABLE `tbl_development` ( `intDevID` mediumint(9) unsigned NOT NULL auto_increment, `txtBarrattID` varchar(20) default NULL, `txtDevName` varchar(60) default NULL, `txtDevWebSiteName` varchar(80) default NULL, `dtmDevDate` datetime default NULL, `dtmDevDateLastUpdate` datetime default NULL, `txtDevDivision` varchar(30) default NULL, `txtDevAddress` text, `intDevStage` smallint(6) default NULL, `txtDevPostCode` varchar(20) default NULL, `txtDevCity` varchar(40) default NULL, `txtDevRegion` varchar(40) default NULL, `txtDevPropertyType` varchar(15) default NULL, `txtDevVirtualTourURL` varchar(60) default NULL, `txtDevVirtualTourURL1` varchar(60) default NULL, `txtDevVirtualTourURL2` varchar(60) default NULL, `txtDevVirtualTourURL3` varchar(60) default NULL, `txtDevVirtualTourURL4` varchar(60) default NULL, `txtDevVirtualTourURL5` varchar(60) default NULL, `txtDevVirtualTourURL6` varchar(60) default NULL, `txtDevVirtualTourURL7` varchar(60) default NULL, `txtDevVirtualTourURL8` varchar(60) default NULL, PRIMARY KEY (`intDevID`)) TYPE=MyISAM zzapper (vim, cygwin, wiki zsh) -- vim -c :%s%s*%CyrnfrTfcbafbeROenzSZbbyranne%|:%s)[R-T]) )Ig|:norm G1VGg? http://www.vim.org/tips/tip.php?tip_id=305 Best of Vim Tips -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Using REGEXP
On 30 Jun 2004 17:45:06 +0200, wrote: In article [EMAIL PROTECTED], SGreen writes: SELECT t1.* FROM ytbl_development t1 INNER JOIN tmpShortCodes sc ON INSTR(t1.txtDevPostCode, sc.short_code) =1 This is the same as SELECT t1.* FROM ytbl_development t1 INNER JOIN tmpShortCodes sc ON t1.txtDevPostCode LIKE concat(sc.short_code, '%') and this query would use indexes on txtDevPostCode and short_code. Thnx to all for this mysql primer, will let you know how it goes, but have been dragged off to another crisis zzapper (vim, cygwin, wiki zsh) -- vim -c :%s/^/WhfgTNabgureRIvzSUnpxre/|:%s/[R-T]/ /Ig|:normal ggVGg? http://www.vim.org/tips/tip.php?tip_id=305 Best of Vim Tips -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Using REGEXP
Michael
Ignoring my attempt at a query, I'll restate the problem
T1.devtxtpostcode contains full UK Postcodes eg OX14 5RA, OX14 5BH, Se1 1AH, etc
I want to check if a particular postcode is within a list of postcode areas, these
postcode areas
are naturally shorter ie ox14 5,ox14 6 etc. So I need to write a query that will check
if OX14 5RA
matches one of the postcode areas
If UK Postcodes had a fixed structure I could write
select * from ytbl_development as t1
where mid(t1.txtDevPostCode,1,5) in ('ox14 1','ox14 2','se1 1')
unfortunately I can't use mid as I can't guarantee that the length of a short postcode
is 5 chars
How would you solve this problem
(The list of short Area Postcodes is generated by an earlier query)
zzapper (vim, cygwin, wiki zsh)
--
vim -c :%s/^/WhfgTNabgureRIvzSUnpxre/|:%s/[R-T]/ /Ig|:normal ggVGg?
http://www.vim.org/tips/tip.php?tip_id=305 Best of Vim Tips
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Using REGEXP
Hi,
select * from ytbl_development as t1
where (t1.txtDevPostCode REGEXP ^[[:alpha:]]{1,2}[[:digit:]]{1,2}[[:alpha:]]{0,1} in
#QuotedValueList(qryRadius.shortpostcode)#)
The above Where clause doesn't work , it just seems you can't use REGEXP this way
qryRadius.shortpostcode contains a list of short postcodes OX14 1,OX14 2 etc
whereas t1.postcode contains full postcodes OX14 5RA
(If qryRadius.shortpostcode wasn't a list I'd be able to use LIKE)
How can I write a Where clause that gets round this
MySql 4.018
zzapper (vim, cygwin, wiki zsh)
--
vim -c :%s/^/WhfgTNabgureRIvzSUnpxre/|:%s/[R-T]/ /Ig|:normal ggVGg?
http://www.vim.org/tips/tip.php?tip_id=305 Best of Vim Tips
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Using REGEXP
Hi,
select * from ytbl_development as t1
where (t1.txtDevPostCode REGEXP ^[[:alpha:]]{1,2}[[:digit:]]{1,2}[[:alpha:]]{0,1} in
#QuotedValueList(qryRadius.shortpostcode)#)
The above Where clause doesn't work , it just seems you can't use REGEXP this way
qryRadius.shortpostcode contains a list of short postcodes OX14 1,OX14 2 etc
whereas t1.postcode contains full postcodes OX14 5RA
(If qryRadius.shortpostcode wasn't a list I'd be able to use LIKE)
How can I write a Where clause that gets round this
MySql 4.018
zzapper (vim, cygwin, wiki zsh)
--
vim -c :%s/^/WhfgTNabgureRIvzSUnpxre/|:%s/[R-T]/ /Ig|:normal ggVGg?
http://www.vim.org/tips/tip.php?tip_id=305 Best of Vim Tips
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Using REGEXP
On Tue, 29 Jun 2004 15:13:10 -0400, wrote:
zzapper:
I could be reading it wrong, but it looks like you're looking for the
result of your REGEXP in a list. REGEXP returns only a 0 or 1, not the
expression resulting from performing a REGEXP.
Wes
On Jun 29, 2004, at 9:25 AM, zzapper wrote:
Hi,
select * from ytbl_development as t1
where (t1.txtDevPostCode REGEXP
^[[:alpha:]]{1,2}[[:digit:]]{1,2}[[:alpha:]]{0,1} in
#QuotedValueList(qryRadius.shortpostcode)#)
The above Where clause doesn't work , it just seems you can't use
REGEXP this way
qryRadius.shortpostcode contains a list of short postcodes OX14
1,OX14 2 etc
whereas t1.postcode contains full postcodes OX14 5RA
(If qryRadius.shortpostcode wasn't a list I'd be able to use LIKE)
How can I write a Where clause that gets round this
Shawn,
Your solution doesn't work unfortunately because I need to operate on t1.postcode
before making the
comparison (IT'S USUALLY the OTHER WAY ROUND) , I think this must be a generic
problem, so I think
an eventual solution will be interesting
zzapper (vim, cygwin, wiki zsh)
--
vim -c :%s/^/WhfgTNabgureRIvzSUnpxre/|:%s/[R-T]/ /Ig|:normal ggVGg?
http://www.vim.org/tips/tip.php?tip_id=305 Best of Vim Tips
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysql for Family History (genealogy)
On Tue, 27 Apr 2004 15:47:19 -0400 (EDT), wrote: Hi, Anyone designed a MySql database for family history? Any ideas,recommendations, problems ? You can try asking the GRAMPS team (http://www.gnu.org/directory/gramps.html). I seem to remember that they are playing around with using a database (right now, only in CVS). Don't know if it's specifically MySQL or not. The current release of GRAMPS stores its information in an XML file. I'm looking for something a little simpler. I just thought somebody might have designed a mysql datastructure. zzapper (vim, cygwin, wiki zsh) -- vim -c :%s/^/WhfgTNabgureRIvzSUnpxre/|:%s/[R-T]/ /Ig|:normal ggVGg? http://www.vim.org/tips/tip.php?tip_id=305 Best of Vim Tips -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
What is your mysql debugging strategy?
Hi, mysql 4.018 script ColdFusion MX I've got the rather miserable job of porting a MsAccess ODBC SQL application to Mysql. Most seems to work OK, but some of the queries fail ; Mysql seems to be stricter . Even though I solved the following problem myself, I'd like to know what debugging strategy people use to solve problems when they get the dreaded Error in Mysql look in the manual The following for instance SELECT tbl_invoice.SubProjectID, * FROM tbl_subproject AS sp INNER JOIN tbl_projectstatus AS ps ON sp.ProjectStatus = ps.ProjectStatus LEFT JOIN tbl_invoice ON sp.SubProjectID = tbl_invoice.SubProjectID WHERE (tbl_invoice.SubProjectID Is Null) AND (sp.ProjectID=1) AND (sp.Deleted=0) AND (ps.Invoice=1) ORDER BY sp.OrderBy DESC required the more specific sp.* rather than * (which access had tolerated) SELECT tbl_invoice.SubProjectID,sp. * FROM tbl_subproject AS sp INNER JOIN tbl_projectstatus AS ps ON sp.ProjectStatus = ps.ProjectStatus LEFT JOIN tbl_invoice ON sp.SubProjectID = tbl_invoice.SubProjectID WHERE (tbl_invoice.SubProjectID Is Null) AND (sp.ProjectID=1) AND (sp.Deleted=0) AND (ps.Invoice=1) ORDER BY sp.OrderBy DESC zzapper (vim, cygwin, wiki zsh) -- vim -c :%s/^/WhfgTNabgureRIvzSUnpxre/|:%s/[R-T]/ /Ig|:normal ggVGg? http://www.vim.org/tips/tip.php?tip_id=305 Best of Vim Tips -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Mysql for Family History (genealogy)
Hi, Anyone designed a MySql database for family history? Any ideas,recommendations, problems ? zzapper (vim, cygwin, wiki zsh) -- vim -c :%s/^/WhfgTNabgureRIvzSUnpxre/|:%s/[R-T]/ /Ig|:normal ggVGg? http://www.vim.org/tips/tip.php?tip_id=305 Best of Vim Tips -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: storing
On Tue, 27 Apr 2004 17:06:59 -0400 (EDT), wrote: hi, i have mysql on my local machine.and i have created tables in my database. if i need to copy these tables onto a cd and transfer them , which folder to i copy? is it the data directory under mysql??? i am not sure abt this. Thanks, liz Liz, You can do this, but I'd rather store them as a script eg with mysqldump zzapper (vim, cygwin, wiki zsh) -- vim -c :%s/^/WhfgTNabgureRIvzSUnpxre/|:%s/[R-T]/ /Ig|:normal ggVGg? http://www.vim.org/tips/tip.php?tip_id=305 Best of Vim Tips -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: What is your mysql debugging strategy?
On Tue, 27 Apr 2004 22:26:16 +0100, wrote: I only use mysql with php so all I need is php code: $result = mysql_query($sql) or die(mysql_error()); This always tells me what I did wrong in the query. You could easily put together a very short script into which you just drop you query. THis would output the problem to the page. Dead simple and quick. Rich Joshua J. Kugler wrote: On Tuesday 27 April 2004 04:26 am, zzapper said something like: Even though I solved the following problem myself, I'd like to know what debugging strategy people use to solve problems when they get the dreaded Error in Mysql look in the manual Fire up MySQL CC and paste the SQL in there, and see what error it gives me. As in 'You have an error near' type messages. j- k- When I've got a horrible query with joins etc, I don't find the your error near .. very useful or am I giving up to easy? zzapper (vim, cygwin, wiki zsh) -- vim -c :%s/^/WhfgTNabgureRIvzSUnpxre/|:%s/[R-T]/ /Ig|:normal ggVGg? http://www.vim.org/tips/tip.php?tip_id=305 Best of Vim Tips -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Sub-select Inner Joins
Hi Here's a complicated bit sql that I'm trying to Post from MSAccess to MySql I searched UseNet and read that in MySQL you cannot have a sub-select, but I'm lost as to how to split this into two selects? Any help gratefully received!!! SELECT distinct p.ProjectID ,p.ProjectName FROM tbl_project AS p INNER JOIN ( ( (tbl_UnitType AS ut INNER JOIN tbl_RateType AS r ON ut.RateType = r.RateType) INNER JOIN (tbl_SubProject AS sp INNER JOIN (Select * FROM tbl_ProjectResource WHERE UserID = '#rptUserID#' AND Deleted = 0 AND ((ActivityDate) Between #datDateFrom# And #datDateTo#) ) AS pr ON sp.SubProjectID = pr.SubProjectID) ON ut.Unit = pr.Unit ) INNER JOIN tbl_RateTypeCategoryCharge AS rt ON pr.RateTypeCategoryChargeID = rt.RateTypeCategoryChargeID ) ON p.ProjectID = sp.ProjectID WHERE ut.reportDisplay = 1 zzapper (vim, cygwin, wiki zsh) -- vim -c :%s/^/WhfgTNabgureRIvzSUnpxre/|:%s/[R-T]/ /Ig|:normal ggVGg? http://www.vim.org/tips/tip.php?tip_id=305 Best of Vim Tips -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Complicated Inner Joins
Here's another rotter! I searched UseNet and read that in MySQL you cannot have a sub-select, but I'm lost as to how to split this into two selects? SELECT distinct p.ProjectID ,p.ProjectName FROM tbl_project AS p INNER JOIN ( ( (tbl_UnitType AS ut INNER JOIN tbl_RateType AS r ON ut.RateType = r.RateType) INNER JOIN (tbl_SubProject AS sp INNER JOIN (Select * FROM tbl_ProjectResource WHERE UserID = '#rptUserID#' AND Deleted = 0 AND ((ActivityDate) Between #datDateFrom# And #datDateTo#) ) AS pr ON sp.SubProjectID = pr.SubProjectID) ON ut.Unit = pr.Unit ) INNER JOIN tbl_RateTypeCategoryCharge AS rt ON pr.RateTypeCategoryChargeID = rt.RateTypeCategoryChargeID ) ON p.ProjectID = sp.ProjectID WHERE ut.reportDisplay = 1 zzapper (vim, cygwin, wiki zsh) -- vim -c :%s/^/WhfgTNabgureRIvzSUnpxre/|:%s/[R-T]/ /Ig|:normal ggVGg? http://www.vim.org/tips/tip.php?tip_id=305 Best of Vim Tips -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Complicated Inner Joins
Hi,
I've moved a Database Application from MSAccess to Mysql, most of the
application seems to work fine, but I'm having trouble with Inner
Joins. (The scripting language is ColdFusion MX 6.1) mySql 4.018.
Any help desperately welcome, including explanation of the principles
SELECT i.*, p.ProjectName, si.OrderBy, si.editable,
tbl_SubProject.SubProjectName
FROM (tbl_Project AS p INNER JOIN
(tbl_InvoiceStatus AS si
INNER JOIN tbl_Invoice AS i
ON si.InvoiceStatus = i.InvoiceStatus)
ON p.ProjectID = i.ProjectID)
LEFT JOIN tbl_SubProject
ON i.SubProjectID = tbl_SubProject.SubProjectID
WHERE i.InvoiceStatus = '#strInvoiceStatus#'
AND i.ProjectID =#intProjectID#
AND i.SubProjectID =#intSubProjectID#
AND i.Deleted = 0
Order BY si.Orderby,i.InvoiceNumber
General error: You have an error in your SQL syntax near
'(tbl_invoicestatus AS si INNER JOIN tbl_invoice AS i ON si.Invoice'
at line 3
SELECT i.*, p.ProjectName, si.OrderBy, si.editable,
tbl_subproject.SubProjectName FROM (tbl_project AS p INNER JOIN
(tbl_invoicestatus AS si INNER JOIN tbl_invoice AS i ON
si.InvoiceStatus = i.InvoiceStatus) ON p.ProjectID = i.ProjectID) LEFT
JOIN tbl_subproject ON i.SubProjectID = tbl_subproject.SubProjectID
WHERE i.InvoiceStatus Not IN('Show All') AND i.ProjectID =-1 AND
i.Deleted = 0 Order BY si.Orderby ,i.InvoiceNumber
zzapper (vim, cygwin, wiki zsh)
--
vim -c :%s/^/WhfgTNabgureRIvzSUnpxre/|:%s/[R-T]/ /Ig|:normal ggVGg?
http://www.vim.org/tips/tip.php?tip_id=305 Best of Vim Tips
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Complicated Inner Joins
On Wed, 14 Apr 2004 16:59:31 -0400, wrote: You need to drop the parenthesis. It looks you are trying to do a join on a sub-join, and you appear to be missing a join condition. Or you're trying to create a join with multiple conditions, which if fine. Near as I can tell, you've made this a lot more complicated than it should. I'm not sure I know what you are trying to do. Try this: SELECT i.*, p.ProjectName, si.OrderBy, si.editable, tbl_SubProject.SubProjectName FROM tbl_Project AS p INNER JOIN tbl_Invoice AS i ON p.ProjectID = i.ProjectID) INNER JOIN tbl_InvoiceStatus AS si ON si.InvoiceStatus = i.InvoiceStatus LEFT JOIN tbl_SubProject ON i.SubProjectID = tbl_SubProject.SubProjectID WHERE i.InvoiceStatus = '#strInvoiceStatus#' AND i.ProjectID =#intProjectID# AND i.SubProjectID =#intSubProjectID# AND i.Deleted = 0 Order BY si.Orderby,i.InvoiceNumber I moved your tbl_InvoiceStatus join to after the tbl_Invoice join. I think using alias names for your tables is throwing you off since it's affecting your join order. Meaning, to read it properly you need to alias the table before you reference it in any join condition. To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 Brent, Will try that out, I inherited the code, and am also a Join Newbie, it did work on MSAccess that's all I know zzapper (vim, cygwin, wiki zsh) -- vim -c :%s/^/WhfgTNabgureRIvzSUnpxre/|:%s/[R-T]/ /Ig|:normal ggVGg? http://www.vim.org/tips/tip.php?tip_id=305 Best of Vim Tips -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Table/Column Name Completion
Hi Ya I believe the Dos Mysql Client has limited table name completion, but do any of the GUI Clients have this feature?? zzapper (vim cygwin zsh) -- vim -c :%s/^/WhfgTNabgureRIvzSUnpxre/|:%s/[R-T]/ /Ig|:normal ggVGg? http://www.vim.org/tips/tip.php?tip_id=305 Best of Vim Tips -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Please help with syntax for mysqldump
On Tue, 13 Jan 2004 14:07:38 +, Matthew Stuart [EMAIL PROTECTED] wrote: I am really struggling with a mysqldump. I am trying to create a dump of a complete database called csi_db01 and I am trying to save the dump file to My Documents on the C drive. I am sure I am doing right, but could somebody email me the full syntax to use which comes after the mysql mysqldump -udavidrayner -pdavidrayner eeetic eeetic.sql mysqldump -udavidrayner -pdavidrayner -A all.sql zzapper (vim cygwin zsh) -- vim -c :%s/^/WhfgTNabgureRIvzSUnpxre/|:%s/[R-T]/ /Ig|:normal ggVGg? http://www.vim.org/tips/tip.php?tip_id=305 Best of Vim Tips -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Please help with syntax for mysqldump
On Tue, 13 Jan 2004 14:07:38 +, Matthew Stuart [EMAIL PROTECTED] wrote: Similarly. SELECT intDEVID,txtDEVPOSTCODE INTO OUTFILE c:/aaa/dump.sql from ytbl_development; (dump.sql file must NOT already exist) zzapper (vim cygwin zsh) -- vim -c :%s/^/WhfgTNabgureRIvzSUnpxre/|:%s/[R-T]/ /Ig|:normal ggVGg? http://www.vim.org/tips/tip.php?tip_id=305 Best of Vim Tips -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Automatic Updating of Timestamp
Hi, I've just found out by accident, that MYSQL will update TimeStamps automatically, that saves a lot of coding timestamps etc. http://www.mysql.com/doc/en/DATETIME.html TIMESTAMP behavior when not running in MAXDB mode The TIMESTAMP column type provides a type that you can use to automatically mark INSERT or UPDATE operations with the current date and time. If you have multiple TIMESTAMP columns, only the first one is updated automatically. zzapper (vim cygwin zsh) -- vim -c :%s/^/WhfgTNabgureRIvzSUnpxre/|:%s/[R-T]/ /Ig|:normal ggVGg? http://www.vim.org/tips/tip.php?tip_id=305 Best of Vim Tips -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Converting an Access Table to MySQL
Hi Ya,
How to this has always been somewhat fuzzy in my head. I looked around
for tools unsuccessfully . So I thought I'd share my solution.
I've just written a simple Perl script to do this, this uses DBI:mysql
to write to mysql and DBI:ODBC to read from Access. I suppose it could
be rewritten in PHP as well.
The tables map practically one to one, but the script could easily be
adapted for a more complex conversion. I presume it could be adapted
to do a reverse conversion as well.
Any suggestions/improvements welcome
#!/usr/local/bin/perl
# convert.pl
# description : Copy Access Database via DBI:ODBC to MySQL
# V1.0 07/01/2004
my $dsn=group;
my $dsn_mysql=group;
my $table=group;
my $table_dest=ytbl_agents;
my $db; # database handle
my $db_dest; # database handle
$delete_sql=qq|DROP TABLE IF EXISTS ytbl_agents|;
$recreate_table =qq|
CREATE TABLE ytbl_agents (
intID mediumint(9) unsigned NOT NULL auto_increment,
txtRegion varchar(60) default NULL,
txtCompany varchar(60) default NULL,
txtContact varchar(60) default NULL,
txtAddress text,
txtTown varchar(60) default NULL,
txtCounty varchar(60) default NULL,
txtPostCode varchar(20) default NULL,
txtPhone varchar(60) default NULL,
txtFax varchar(50) default NULL,
txtEmail varchar(60) default NULL,
txtWeb varchar(60) default NULL,
dtmDate timestamp(14) default NULL,
PRIMARY KEY (intID)
) TYPE=MyISAM;
|;
# MMDDHHMMSS
($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) =
localtime(time);
$year+=1900; $mon++;
$mysql_timestamp = sprintf(
%04d%02d%02d%02d%02d%02d,$year,$mon,$mday,$hour,$min,$sec);
#-#
open_access_db();
open_mysql_db();
$cnt=fn_read_access_db();
fn_write_to_mysql();
print \n$cnt records imported to mysql database $dsn_mysql (table
$table_dest)\n;
$db-disconnect();
$db_dest-disconnect();
#-#
sub open_access_db()
{
use DBI;
use DBD::ODBC;
my $emsg=Could not access the Database\n Could not open DSN $dsn;
$db = DBI-connect( dbi:ODBC:$dsn, , ,
{RaiseError = 1,
PrintError = 1,
AutoCommit = 1,
LongReadLen = 4000} ) or
do
{
print ($emsg: . $DBI::errstr .__LINE__.\n);
exit;
}
}
#-#
sub open_mysql_db()
{
use DBI;
use DBD::MYSQL;
my $emsg=Could not access the Database\n Could not open DSN
$dsn_mysql;
$db_dest = DBI-connect( dbi:mysql:$dsn_mysql, , ,
{RaiseError = 1,
PrintError = 1,
AutoCommit = 1,
LongReadLen = 4000} ) or
do
{
print ($emsg: . $DBI::errstr .__LINE__.\n);
exit;
};
### delete table
$sel_dest = $db_dest-prepare( $delete_sql );
$sel_dest-execute() or webdie(\n$delete_sql \n: .$DBI::errstr.
line .__LINE__);
### recreate table
my $sel_dest = $db_dest-prepare( $recreate_table );
$sel_dest-execute() or webdie(\n$recreate_table \n:
.$DBI::errstr. line .__LINE__);
}
#-#
sub fn_read_access_db
{
my $sql=qq|select * from $table |;
my $emsg=$sql;
my $sel = $db-prepare( $sql );
$sel-execute() or webdie($emsg : .$DBI::errstr. line
.__LINE__);
@results=();
while (my $ref=$sel-fetchrow_hashref)
{
push @results, {%$ref}; # array of hashes
undef $ref;
}
return $#results+1;
}
#-#
sub fn_write_to_mysql()
{
my $cols =qq|txtRegion, txtCompany, txtContact, txtAddress, txtTown,
txtCounty, txtPostCode|;
$cols.=qq|, txtPhone, txtFax, txtEmail, txtWeb, dtmDate|;
for my $resid (0 .. $#results)
{
my $ID= $results[$resid]{ID};
my $region=escsql($results[$resid]{region});
my $company=escsql($results[$resid]{company});
my $contact=escsql($results[$resid]{contact});
my $address=escsql($results[$resid]{address});
my $town=escsql($results[$resid]{town});
my $county=escsql($results[$resid]{county});
my $postcode=escsql($results[$resid]{postcode});
my $tel=escsql($results[$resid]{tel});
my $fax=escsql($results[$resid]{fax});
my $email=escsql($results[$resid]{email});
my $web=escsql($results[$resid]{web});
my $vals
=qq|'$region','$company','$contact','$address','$town','$county','$postcode','$tel','$fax','$email','$web'|;
$vals.=qq|,'$mysql_timestamp'|;
#print $cols; print \n.$vals; exit;
my $sql=qq|insert into $table_dest |;
$sql.=qq| ($cols) values ($vals) |;
my $sel_dest = $db_dest-prepare( $sql );
$sel_dest-execute() or webdie(\n$sql \n: .$DBI::errstr. line
.__LINE__);
}
}
#-#
sub escsql()
{
my ($inputstr) = @_ ;
return if (!defined($inputstr)) ;
$inputstr =~ s/\\//g;
$inputstr =~ s/\/\\/g;
$inputstr =~ s/\'/\\'/g;
return($inputstr) ;
}
#-#
zzapper (vim cygwin zsh)
--
vim -c :%s/^/WhfgTNabgureRIvzSUnpxre/|:%s/[R-T]/ /Ig|:normal ggVGg?
http://www.vim.org/tips/tip.php?tip_id=305
Re: Converting an Access Table to MySQL
On Fri, 9 Jan 2004 20:40:44 +0530, Nitin Mehta [EMAIL PROTECTED] wrote: you need not write any scripts, just use MySQL Front to Import/Export dat from M$ Access Are you using MySqlfront2.5 (the original) or the new 3.x? BTW I already use/love 2.5. That's why I post to Usenet to be told there's a better way; thanx. The only advantage of my script is that I could do a fairly complex query to transform the data if that were necessary zzapper (vim cygwin zsh) -- vim -c :%s/^/WhfgTNabgureRIvzSUnpxre/|:%s/[R-T]/ /Ig|:normal ggVGg? http://www.vim.org/tips/tip.php?tip_id=305 Best of Vim Tips -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Converting an Access Table to MySQL
On Fri, 09 Jan 2004 11:48:42 -0400, Victor Medina [EMAIL PROTECTED] wrote: hey hey hey! wait a second there! is there a new mysqlfront? :o where can i download it? :) 3.0??? I already love and worship mysqlfront 2.5, i thought it was dead :( do you have a link? Victor, It's at the same URL www.mysqlfront.de I'm not sure it's stable yet zzapper (vim cygwin zsh) -- vim -c :%s/^/WhfgTNabgureRIvzSUnpxre/|:%s/[R-T]/ /Ig|:normal ggVGg? http://www.vim.org/tips/tip.php?tip_id=305 Best of Vim Tips -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Control Center!!!
On Sat, 3 Jan 2004 09:24:05 -0500, Kirti S. Bajwa [EMAIL PROTECTED] wrote: Hello: I have been reading MySQL Control Center. There are screen shots but I have not been able to find documentation. Is the documentation is hidden or not available? If there is documentation, kindly direct me to the URL. try gmane.comp.db.mysql.mycc (but I don't think there's any doc) zzapper (vim cygwin zsh) -- vim -c :%s/^/WhfgTNabgureRIvzSUnpxre/|:%s/[R-T]/ /Ig|:normal ggVGg? http://www.vim.org/tips/tip.php?tip_id=305 Best of Vim Tips -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem With Creating Table
On Wed, 31 Dec 2003 19:19:32 -0800 (PST), Caroline Jen [EMAIL PROTECTED] wrote: Hi, I tried to create a table. This table has seventeen fields. My create table syntax gets too long and I was only able to specify 5 fields at the mysql prompt in the DOS window (DOS does not accept a command beyond certain length). How do I put the rest 12 fields in the table I just created? mysqlCREATE TABLE message_thread (thread_id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, message_receiver VARCHAR(79) NOT NULL, message_sender VARCHAR(79) NOT NULL, article_title VARCHAR(255) NOT NULL, last_post_member_name VARCHAR(79) NOT NULL); Caroline, Maybe you should be putting long sql statements into an external script eg $ mysql -D mydb -u test -ptest create_table.sql zzapper (vim cygwin zsh) -- vim -c :%s/^/WhfgTNabgureRIvzSUnpxre/|:%s/[R-T]/ /Ig|:normal ggVGg? http://www.vim.org/tips/tip.php?tip_id=305 Best of Vim Tips -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Comparing strings containing possible quotes
On Mon, 08 Dec 2003 19:07:43 +, zzapper [EMAIL PROTECTED] wrote: Hi Ya, I have a typical select as follows SELECT * FROM ytbl_development AS t1 WHERE (t1.txtDevName LIKE '%#form.searchtext#%') B) Now I can filter any quotes from form.searchtext easy enough, but how do I filter them the Left Hand Side eg from txtDevName?) Is there no mysql solution to this? Most Where clauses are thus WHERE LHS Like/= RHS Now MySQL provides lots of operators for the RHS eg WHERE LHS RLIKE RHS But what I want to do is preprocess/filter the LHS before doing the comparison, I can find no information on this. In the past I have cheated by creating an additional column in my database eg txtDevNameClean which has all non-alphanumerics removed. am I barking up the wrong tree?? zzapper -- vim -c :%s/^/WhfgTNabgureRIvzSUnpxre/|:%s/[R-T]/ /Ig|:normal ggVGg? http://www.vim.org/tips/tip.php?tip_id=305 Best of Vim Tips -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Comparing strings containing possible quotes
Hi Ya. I seem to have solved my problem if I clean out any punctuation from my search string (see below (ColdFusion script)) cfset form.searchtext=replace(form.searchtext,'[[:punct:]]','','all') When I perform the WHERE LHS-Containing-Quote LIKE RHS-without-quotes The LIKE appears to ignore the quotes (which is what I want) BUT IS THAT A FEATURE OF LIKE??? zzapper -- vim -c :%s/^/WhfgTNabgureRIvzSUnpxre/|:%s/[R-T]/ /Ig|:normal ggVGg? http://www.vim.org/tips/tip.php?tip_id=305 Best of Vim Tips -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Comparing strings containing possible quotes
On Tue, 09 Dec 2003 13:24:03 -0500, Michael Stassen [EMAIL PROTECTED] wrote: You don't want to filter the quotes from the input string, because they exist in the data. (Well, you could filter from both sides, but that's inefficient.) What you need to do is escape the quotes with backslashes before sending them to mysql. Then your query will look like this: Michael, thanks for answering I've solved some of my quote problems (which were just down to mistakes).(BTW LIKE does NOT ignore quotes) How would you filter say non-alphanumerics from the input string? What is the syntax? I've previously wanted to do a regexp on the input string but this not seem to be permitted zzapper -- vim -c :%s/^/WhfgTNabgureRIvzSUnpxre/|:%s/[R-T]/ /Ig|:normal ggVGg? http://www.vim.org/tips/tip.php?tip_id=305 Best of Vim Tips -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Comparing strings containing possible quotes
Hi Ya, I have a typical select as follows SELECT * FROM ytbl_development AS t1 WHERE (t1.txtDevName LIKE '%#form.searchtext#%') However users a complaining that if they search for for a name that contains a quote eg a development named King's Reach it is not found. A) Now it simply won't match King's Reach whether I include the quote or not. WHY?? (Have the quotes been converted ie to URL Encoding)) B) Now I can filter any quotes from form.searchtext easy enough, but how do I filter them the Left Hand Side eg from txtDevName?) I look forward to seeing how you solve this. zzapper -- vim -c :%s/^/WhfgTNabgureRIvzSUnpxre/|:%s/[R-T]/ /Ig|:normal ggVGg? http://www.vim.org/tips/tip.php?tip_id=305 Best of Vim Tips -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How do test unique values in a MySql column?
Hi Y'All I need to test if any values in a column are the same (or are unique) eg (my made up syntax Unique) SELECT * from tbl_addresses WHERE txtName is UNIQUE; How should this be done??? zzapper -- vim -c :%s/^/WhfgTNabgureRIvzSUnpxre/|:%s/[R-T]/ /Ig|:normal ggVGg? http://www.vim.org/tips/tip.php?tip_id=305 Best of Vim Tips -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysqlfront versus mysqlcc
I currently use the excellent mysqlfront which is sadly no longer supported. I've tried mysqlcc but it seems non-intuitive, and missing loads of features. It also has a problem working with old versions of mysql 3.23.47 Or have I missed something, does anyone recommend mysqlcc??? zzapper -- vim -c :%s/^/WhfgTNabgureRIvzSUnpxre/|:%s/[R-T]/ /Ig|:normal ggVGg? http://www.vim.org/tips/tip.php?tip_id=305 Best of Vim Tips -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How can I become an Expert MySql User?
HiY'll I've been using MYSQL for a couple of years now. And can write some pretty complex WHERE statements. I've become aware that's really only tip of the iceberg stuff. Can readers suggest a gentle path to moving onto JOINS and a more fundamental understanding of DBs No rude answers please g zzapper -- vim -c :%s/^/WhfgTNabgureRIvzSUnpxre/|:%s/[R-T]/ /Ig|:normal ggVGg? http://www.vim.org/tips/tip.php?tip_id=305 Best of Vim Tips -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Why is there no REGEXP()?
On Sun, 19 Oct 2003 22:08:30 -0400, Kelley Lingerfelt
[EMAIL PROTECTED] wrote:
WHERE lookup RLIKE this|that|other|^starts like this|ends like this$|it
contains.*something.*like.*this
I am not yet expert enough in mySQL to even specify my question
accurately (so I will have another go)
In the following where_condtion I can use functions such as
left(),right()
where left(t1.txtDevPostCode,5) in
(#QuotedValueList(qryRadius.postcode)#)
but unfortunately UK postcodes are variable length so i need a REGEXP
SO I really want something like
where REGEXP(t1.txtDevPostCode,^[a-z]{1,2}[0-9]0,2}) in
(#QuotedValueList(qryRadius.postcode)#) ##NOT POSSIBLE##
You can see what complicates the above is using the in
QuotedValueList.
But what's REALLY unusual about this query is that I want to
manipulate the contents of a TABLE COLUMN (txtdevPostCODE) before
doing the comparison, I seem to need some kind of inner query.
can U help?
zzapper
--
vim -c :%s/^/WhfgTNabgureRIvzSUnpxre/|:%s/[R-T]/ /Ig|:normal ggVGg?
http://www.vim.org/tips/tip.php?tip_id=305 Best of Vim Tips
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Why is there no REGEXP()?
Hi
I can write a query such
where left(t1.txtDevPostCode,5) in
(#QuotedValueList(qryRadius.postcode)#)
but unfortunately UK postcodes are variable length
SO I really want something like
where REGEXP(t1.txtDevPostCode,^[a-z]{1,2}[0-9]0,2}) in
(#QuotedValueList(qryRadius.postcode)#)
This use of REGEXP doesnt exist AFAIK , what work arounds exist, or
how would you do it?
BTW the QuotedValueList contains Short Postcodes of type AA11 or A11
or AA1
zzapper
--
vim -c :%s/^/WhfgTNabgureRIvzSUnpxre/|:%s/[R-T]/ /Ig|:normal ggVGg?
http://www.vim.org/tips/tip.php?tip_id=305 Best of Vim Tips
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
