Re: Autoindexing
Under mysql 5.0.18 you can do something like: // get the current value of the auto_increment counter mysql select @@auto_increment_offset; +-+ | @@auto_increment_offset | +-+ |1105 | +-+ 1 row in set (0.00 sec) // set it to your required value mysql set @@auto_increment_offset = 201; Query OK, 0 rows affected (0.01 sec) // make sure it is set to what you want it to be mysql select @@auto_increment_offset; +-+ | @@auto_increment_offset | +-+ | 201 | +-+ 1 row in set (0.02 sec) mysql HTH Keith Roberts In theory, theory and practice are the same; in practice they are not. On Fri, 23 Jun 2006, Tom Ray [Lists] wrote: To: mysql@lists.mysql.com From: Tom Ray [Lists] [EMAIL PROTECTED] Subject: Autoindexing Hey, I have a really simple question (I hope)..I have a database that has a field in it that autoindexes the number. I screwed up and imported a bunch of wrong data. Up to row 200 it's right, beyond that it was wrong so I had delete it all. The problem now is the autoindexing is set to 1105, I want to change it so the next insert gets a number of 201 not 1105. I've tried to change it via phpMyAdmin but it keeps going back to 1105. Is there anyway I can do this? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: New to the group
Well, php was designed basically from the ground-up as a replacement for CGI programming. AFAIK you can do similar things in PERL, but there is alot more to learn. php has an easy learning curve, and seems to be alot more suitable for server sided web programming than PERL. If you are an experienced PERL programmer then you may want to use the Apache PERL module, and do server sided programming with that. If you are new to server side programming then I would recommend starting with php, due to the easy learning curve. See http://www.php.net/manual/en/introduction.php for an intro to what php is, and what it can do. HTH Keith Roberts In theory, theory and practice are the same; in practice they are not. To unsubscribe from this list, please see detailed instructions already posted at: http://marc.theaimsgroup.com/?l=php-installm=114138567814319w=2 On Thu, 22 Jun 2006, Bartis, Robert M (Bob) wrote: To: 'mos' [EMAIL PROTECTED], mysql@lists.mysql.com From: Bartis, Robert M (Bob) [EMAIL PROTECTED] Subject: RE: New to the group If you will excuse my ignorance. I have no immediate need for this, but have often asked what the pros/cons there are writing a WEB based interface in PHP vs. say Perl. Do you have any insight into that? Thanks Bob -Original Message- From: mos [mailto:[EMAIL PROTECTED] Sent: Thursday, June 22, 2006 3:39 PM To: mysql@lists.mysql.com Subject: Re: New to the group At 08:46 AM 6/22/2006, Nicholas Vettese wrote: Hello, My name is Nick, and I am a new MySQL user. My hope is not to become a PITA, so I will make sure that any question is straight and to the point with the information needed to answer the question. My skill in MySQL is pretty low, and I am looking to build a website for myself that will take information and save it to a database. At this time, I have a login, registration, change/lost password functionality working from a book that I read, but I am looking to expand my knowledge into more robust site. I am not looking to become the master programmer, just someone with enough knowledge and skill to accomplish his goals. Thanks, Nick Welcome Nick, You've come to the right place. There are a couple of books on MySQL that are quite good and I'd like to recommend. MySQL 3rd Edition by Paul Dubois and MySQL Cookbook by Paul Dubois (I think these guys are relatedvbg) If you are using PHP to build your website I found PHP and MySQL for Dynamic Web Sites : Visual QuickPro Guide (2nd Edition) (Visual Quickpro Guide) to be quite good and gets you going quite fast. There's not a lot of reading to do and they have you writing PHP code the first day. If you want a more thorough book on PHP MySQL there is: PHP and MySQL Web Development (3rd Edition) (Developer's Library) (Paperback) by Luke Welling, Laura Thomson There are also PHP/Mysql tutorials on the web but I don't know how good they are. You'll get up to speed faster by getting some of these books. Of course if you're not using PHP, then someone else can jump in with some reading suggestions. Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem With Join Syntax
Hi Chris. I cannot see how it can be done with the current table schema. Maybe you need to redeclare your table so the values in the value column are more distinct? What is value supposed to contain anyway? First name, last name and email address? What about a structure like: id | userid | ipf_1 | ipf_2 | ipf_3 1 2 JohnSmith email_addy Which will allow you to retrieve all the values you want from the table as one row without having to repeat the userid column? HTH Keith Roberts In theory, theory and practice are the same; in practice they are not. On Wed, 14 Jun 2006, Chris White wrote: To: mysql@lists.mysql.com From: Chris White [EMAIL PROTECTED] Subject: Re: Problem With Join Syntax On Wednesday 14 June 2006 10:55 am, Albert Padley wrote: A typical set of data looks like this: id | inputfieldid | userid | value 1 1 2 John 2 2 2 Smith 3 3 2 [EMAIL PROTECTED] I am trying to come up with a query to return all the `values` of a single userid in a single row. I've checked my books, the manual and tried every type of join I can think of without success. I'd appreciate some direction. This sounds like somewhat of a strange requirement. Why do they need to be in a single row? There MIGHT be a way to do it with stored procedures, I'm just not sure how.. Thanks. Albert Padley -- Chris White PHP Programmer/DB Fighter Interfuel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: my-huge.cnf quite outdated
I was getting a little OT there Gaspar. It might be better for me to file a feature request on mysl bug reporting page for any un-necessary functionality and the associated documentation to be removed from mysql in version 6.0.x, rather than hijacking this thread! Regards Keith On Sun, 11 Jun 2006, Gaspar Bakos wrote: To: Keith Roberts [EMAIL PROTECTED] From: Gaspar Bakos [EMAIL PROTECTED] Subject: Re: my-huge.cnf quite outdated Hi, Keith, RE: This seems to be the way things are with mysql nowdays. Is it not time for the developers to take a serious look into culling all the outdated and multiple ways of accomplishing the same thing from mysql and the documentation? This is a somewhat different subject. But you are right about it. On the other hand, I have been using MySQL since 2001, and I enjoy looking at the old syntax, and seeing how it changed helps me understanding what the new syntax means. Back to my-huge.cnf, I am sure there are many people reading the list who run MySQL on big-big servers, and they must have figured out how to optimize it. I am curious about their advice. Any opinions of the following : ? [mysqld] key_buffer_size=1024M myisam_sort_buffer_size=256M sort_buffer_size=256M bulk_insert_buffer_size=64M join_buffer_size=64M max_connections=5 read_buffer_size=8M read_rnd_buffer_size=8M net_buffer_length=1M max_allowed_packet=16M Cheers, Gaspar -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: my-huge.cnf quite outdated
This seems to be the way things are with mysql nowdays. Is it not time for the developers to take a serious look into culling all the outdated and multiple ways of accomplishing the same thing from mysql and the documentation? All the excess documentation for different ways of accomplishing the same outcome makes the learning curve for mysql alot harder. It doesn't make things any easier for people that want to take the MySQL certification exams either. Why should we have to remember many different ways to accomplish the same thing, when one or two ways at the most would be quite sufficient? For example, do we really need so many ways to start a server? Do we really need different ways to add or drop indexes and modify tables? With the newer versions of mysql (5.0.21+ or maybe version 6.0.x), can we not dump the old syntax that is in mysql for backward compatibility reasons? I would really like to see a slim and trim version of mysql, the SQL commands, and the supporting cli programs, that is up to date and has a very fast learning curve. Just my little gripe. Kind Regards Keith Roberts In theory, theory and practice are the same; in practice they are not. On Sun, 11 Jun 2006, Gaspar Bakos wrote: To: mysqllist mysql@lists.mysql.com From: Gaspar Bakos [EMAIL PROTECTED] Subject: my-huge.cnf quite outdated Hi, Isn't the my-huge.cnf in the MySQL 5.0.22 distribution quite outdated? It says for systems with 512Mb RAM or more. Nowdays this is pretty basic setup, and 'huge' is probably something in excess of 4Gb RAM. I wonder if anyone has a recommendation for truly huge systems. For example a dual CPU AMD opteron system with 4Gb (or 8Gb) RAM that is fully devoted to serving the mysql daemon. The config I have (see below) has been tuned to be optimal for creating indexes on a large (100Gb+) single database table. It works fine (although not satisfactory), but I worry that some parameters may have an optimal value or range, and it does not make sense to increase them like crazy. Any opinions of the following : ? [mysqld] key_buffer_size=1024M myisam_sort_buffer_size=256M sort_buffer_size=256M bulk_insert_buffer_size=64M join_buffer_size=64M max_connections=5 read_buffer_size=8M read_rnd_buffer_size=8M net_buffer_length=1M max_allowed_packet=16M # Cheers, # Gaspar -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Books on MySQL 5
Well I have the book you mentioned below - it is VERY good IMHO. Goes into alot of detail on how to design tables the right way. Covers normalisation and optimisation techniques for the server itself. These guys really know what they are talking about. I also have Beginning PHP MySQL 5, by Apress. This takes a look at the new features of MySQL 5, including triggers, views and stored procedures. Also covers MySQLi the new interface for MySQL. I've not read the whole book yet, but it seems to complement the above mentioned book very well. So I don't think you would go far wrong by getting these two books together. Regards Keith Roberts In theory, theory and practice are the same; in practice they are not. On Fri, 9 Jun 2006, Chris White wrote: To: mysql@lists.mysql.com From: Chris White [EMAIL PROTECTED] Subject: Books on MySQL 5 As always, thanks ahead of time for all responses. The company I work for is currently looking at getting literature on MySQL 5, more specifically, what's different in new MySQL features from the SQL standard. By this I mean things such as Stored Procedures, Foreign Keys, and anything else I might have missed. I know there's: Beginning MySQL Database Design and Optimization: From Novice to Professional (Apress, 2004) And it does say it covers MySQL5, I'm just not sure to what extent. -- Chris White PHP Programmer/DB Virus Interfuel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Transfer users and permissions between servers
Are you moving to a new unpopulated server, or are there already mysql users on the new server? If it is a new server with no other mysql users onboard, then I guess it is as easy as copying the mysql system database (/var/lib/mysql/mysql on my system) across to the new server, with the other databases you are moving. If both servers are connected by LAN, you could set up FTP to transfer everything you need from the old server to the new one. I find Kbear is a very easy to use GUI front end to FTP. HTH Keith In theory, theory and practice are the same; in practice they are not. On Wed, 7 Jun 2006 [EMAIL PROTECTED] wrote: To: mysql@lists.mysql.com From: [EMAIL PROTECTED] Subject: Transfer users and permissions between servers Is there an easy way to transfer users and their permissions from one server to another? We are moving databases to a new server and have a lot of users to move as well. Thanks in advance! Donna -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql edition problem
My first choice for easy installation: Linux x86 generic RPM (statically linked against glibc 2.2.5) downloads Server 5.0.22-013.9M Download | Pick a mirror MD5: a61b9aec938ec729652872bc123b7508 Max 5.0.22-02.9MDownload | Pick a mirror MD5: b67f9f948a9500cfdad5beb51c657cc9 Benchmark/test suites 5.0.22-06.0M Download | Pick a mirror MD5: 449d9bc4da4b345e0f346cd2edfd0dcf Client programs 5.0.22-06.9MDownload | Pick a mirror MD5: 611d2598227d230d21c710d6785c74e5 Libraries and header files 5.0.22-03.9M Download | Pick a mirror MD5: 56d6e0baee187efd0f8e4abc8ccb4a0e Shared client libraries 5.0.22-02.1M Download | Pick a mirror MD5: 28e78f984fa32644dd174712efa3b829 Shared compatibility libraries (3.23, 4.x, 5.x libs in same package) 5.0.22-0 3.4MDownload | Pick a mirror MD5: cc5d382c61b704454d0762611a15c076 Second choice for more flexibility: Linux (non RPM package) downloads (platform notes) Linux (x86, glibc-2.2, standard is static, gcc) Standard5.0.22 30.2M Download | Pick a mirror MD5: 0eaa7a8ec18699ce550db1713a27cda3 | Signature HTH Keith Roberts In theory, theory and practice are the same; in practice they are not. On Fri, 2 Jun 2006, yuan edit wrote: To: mysql@lists.mysql.com From: yuan edit [EMAIL PROTECTED] Subject: mysql edition problem my operating system is linux redhat 9.0. i am installing mysql 5.0.x binary distribution. Which edition is the most fit in the following editions? And would you like to tell me the difference among these editions? Thanks very much! [image: [ ]] mysql-standard-5.0.22-linux-i686-glibc23.tar.gz http://mysql.oss.eznetsols.org/Downloads/MySQL-5.0/mysql-standard-5.0.22-linux-i686-glibc23.tar.gz 27-May-2006 02:32 26M GZIP compressed docume [image: [TXT]] mysql-standard-5.0.22-linux-i686-glibc23.tar.gz.aschttp://mysql.oss.eznetsols.org/Downloads/MySQL-5.0/mysql-standard-5.0.22-linux-i686-glibc23.tar.gz.asc 27-May-2006 08:52 189 GZIP compressed docume [image: [ ]] mysql-standard-5.0.22-linux-i686-glibc23.tar.gz.md5http://mysql.oss.eznetsols.org/Downloads/MySQL-5.0/mysql-standard-5.0.22-linux-i686-glibc23.tar.gz.md5 27-May-2006 02:32 82 GZIP compressed docume [image: [ ]] mysql-standard-5.0.22-linux-i686-icc-glibc23.tar.gzhttp://mysql.oss.eznetsols.org/Downloads/MySQL-5.0/mysql-standard-5.0.22-linux-i686-icc-glibc23.tar.gz 27-May-2006 02:24 24M GZIP compressed docume [image: [TXT]] mysql-standard-5.0.22-linux-i686-icc-glibc23.tar.gz.aschttp://mysql.oss.eznetsols.org/Downloads/MySQL-5.0/mysql-standard-5.0.22-linux-i686-icc-glibc23.tar.gz.asc 27-May-2006 08:52 189 GZIP compressed docume [image: [ ]] mysql-standard-5.0.22-linux-i686-icc-glibc23.tar.gz.md5http://mysql.oss.eznetsols.org/Downloads/MySQL-5.0/mysql-standard-5.0.22-linux-i686-icc-glibc23.tar.gz.md5 27-May-2006 02:24 86 GZIP compressed docume [image: [ ]] mysql-standard-5.0.22-linux-i686.tar.gzhttp://mysql.oss.eznetsols.org/Downloads/MySQL-5.0/mysql-standard-5.0.22-linux-i686.tar.gz 27-May-2006 07:15 30M GZIP compressed docume [image: [TXT]] mysql-standard-5.0.22-linux-i686.tar.gz.aschttp://mysql.oss.eznetsols.org/Downloads/MySQL-5.0/mysql-standard-5.0.22-linux-i686.tar.gz.asc 27-May-2006 08:52 189 GZIP compressed docume [image: [ ]] mysql-standard-5.0.22-linux-i686.tar.gz.md5http://mysql.oss.eznetsols.org/Downloads/MySQL-5.0/mysql-standard-5.0.22-linux-i686.tar.gz.md5 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Keyword - MySQL 4.1
From the manual: http://dev.mysql.com/doc/refman/4.1/en/legal-names.html An identifier may be quoted or unquoted. If an identifier is a reserved word or contains special characters, you must quote it whenever you refer to it. (Exception: A word that follows a period in a qualified name must be an identifier, so it is not necessary to quote it, even if it is a reserved word.) For a list of reserved words, see Section 9.5, Treatment of Reserved Words in MySQL. Special characters are those outside the set of alphanumeric characters from the current character set, _, and $. The identifier quote character is the backtick (`): mysql SELECT * FROM `select` WHERE `select`.id 100; Or could you just use an ALTER TABLE statement to change the name in your database, and then change the div name in your application code? HTH Keith Roberts In theory, theory and practice are the same; in practice they are not. On Mon, 29 May 2006, ManojW wrote: To: mysql@lists.mysql.com From: ManojW [EMAIL PROTECTED] Subject: Keyword - MySQL 4.1 Dear All, While migrating from MySQL 4.0 to MySQL 4.1, I found that the database could not be loaded in 4.1 because MySQL 4.1 uses a reserved keyword div that was not reserved in 4.0. Is there anyway to get around the issue? Thanks in advance. Manoj -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [suse-security] fou4s for 10.1?
There is no reason why you cannot use diffeent package managers for your system. The only thing is do not run two managers at the same time, as they require exclusive access to the rpm database. Trying to use more than one package manager, or multiple instances of the same one is not a good idea, may not be possible due to locks on the rpm database, or could end up corrupting the rpm database. Smart is still under development, and hopefully the CLI version will be fully functional ASAP. Keith In theory, theory and practice are the same; in practice they are not. On Wed, 24 May 2006, Ingvar Berg wrote: To: [EMAIL PROTECTED] From: Ingvar Berg [EMAIL PROTECTED] Subject: Re: [suse-security] fou4s for 10.1? Hi, From my crontab: 23 4 * * * /usr/bin/smart upgrade -y --update /Ingvar [EMAIL PROTECTED] wrote: The smart gui seems to be decent are a package manager replacement, and I'm definitely going to using it on my workstation, but the command line version doesn't seem to be geared toward running it in a cron job at night. Apt pretty easily performs that main function of fou4s, though fou4s present the information and manages it much better. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SPAMs
Check this out, it works a treat for me: http://www.tmda.net Regards Keith In theory, theory and practice are the same; in practice they are not. On Wed, 24 May 2006, Jørn Dahl-Stamnes wrote: To: mysql@lists.mysql.com From: Jørn Dahl-Stamnes [EMAIL PROTECTED] Subject: Re: SPAMs On Wednesday 24 May 2006 14:48, George Law wrote: more than likely someone is harvesting emails off groups.google.com http://groups.google.com/group/mailing.database.myodbc Your posting shows top of the list :) Yet another reason for not using google. It seems like they help the spammers by doing this. It's OK that they show the item in the list, but the e-mail addresse should be masked... -- Jørn Dahl-Stamnes homepage: http://www.dahl-stamnes.net/dahls/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Sparse 1.0b - framework for MySQL programs
Thankyou for pointing that out Daniel. Regards Keith On Mon, 22 May 2006, Daniel Orner wrote: To: mysql@lists.mysql.com From: Daniel Orner [EMAIL PROTECTED] Subject: Re: Sparse 1.0b - framework for MySQL programs Hi, According to the CC license, Creative Commons licenses are not intended to apply to software. They should not be used for software. So I'm not entirely sure why you're recommending it. ^^; It is released under the GPL. --Daniel Keith Roberts [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] Hi Daniel. Distribute it under the CC license? http://creativecommons.org/about/licenses/index_html Keith In theory, theory and practice are the same; in practice they are not. On Fri, 19 May 2006, Daniel Orner wrote: To: mysql@lists.mysql.com From: Daniel Orner [EMAIL PROTECTED] Subject: Sparse 1.0b - framework for MySQL programs I'd like to announce the first release of Sparse, a new way to create MySQL programs without actually programming anything! Sparse takes care of handling the SQL data, navigation, displaying errors, input validation, and caching. Using a few extra HTML tags allows surprising power, yet remains easy to use and very customizable. It can save a lot of time and effort during development! It's especially well-suited for creating admin back-ends of sites that use MySQL. This means that you can actually create entire MySQL-backed programs without using a single line of PHP code. However, more complex forms and queries can be easily integrated with PHP code as well. The Sparse homepage is here: http://sparse-php.sourceforge.net/ Besides looking for beta testers, I'd also like to know the best way to spread the word about it, so if anyone has any comments or help they can offer, please do so! Thanks! --Daniel Orner -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Sparse 1.0b - framework for MySQL programs
Hi Daniel. Distribute it under the CC license? http://creativecommons.org/about/licenses/index_html Keith In theory, theory and practice are the same; in practice they are not. On Fri, 19 May 2006, Daniel Orner wrote: To: mysql@lists.mysql.com From: Daniel Orner [EMAIL PROTECTED] Subject: Sparse 1.0b - framework for MySQL programs I'd like to announce the first release of Sparse, a new way to create MySQL programs without actually programming anything! Sparse takes care of handling the SQL data, navigation, displaying errors, input validation, and caching. Using a few extra HTML tags allows surprising power, yet remains easy to use and very customizable. It can save a lot of time and effort during development! It's especially well-suited for creating admin back-ends of sites that use MySQL. This means that you can actually create entire MySQL-backed programs without using a single line of PHP code. However, more complex forms and queries can be easily integrated with PHP code as well. The Sparse homepage is here: http://sparse-php.sourceforge.net/ Besides looking for beta testers, I'd also like to know the best way to spread the word about it, so if anyone has any comments or help they can offer, please do so! Thanks! --Daniel Orner -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Looking for free MySQL Administrator
http://www.phpmyadmin.net/home_page/index.php I think phpmyadmin will allow you to make changes to multiple columns at once. For any major changes to a database such as you describe, if you have the disk space, I would advise copying the database and performing your changes on the copy, just in case you do make an irreversible mistake. At least you will still have the original tables to work with again, and not loose your database. As a matter of interest, how large is the database in MB's or GB's? Once you are sure you know EXACTLY what you are doing to your database, then it should be safe to work on the live database, and perform the changes to that. Just my 2c Kind Regards Keith In theory, theory and practice are the same; in practice they are not. On Sat, 20 May 2006, mos wrote: To: mysql@lists.mysql.com From: mos [EMAIL PROTECTED] Subject: Looking for free MySQL Administrator I'm looking for a MySQL administrator for 4.x/5.x that will allow me to make multiple changes to a table structure without reloading the data after each change. The problem is I may have to change 5 columns on a 10 million row table. As it stands now with the administrator I'm using, it will reload the table after each change (very slow because it means 5 reloads-1 for each column). So I need to batch the 5 table changes then have it alter the table. (Of course I could use Alter Table with multiple columns but if I make a mistake, there goes data for 10 million rows-Ouch!) Any suggestions? TIA Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Find invalid email formats using MySQL query.
Well, mysql has support for regex matching. The thing is though, why store invalid email addresses in you database? I think what you are doing is the right approach. Check the email address validity first in php BEFORE saving it to the database. At least you then know you are only storing valid emails in your database. If you store invalid emails in your database then how do you get the user to correct the email address from mysql? HTH Keith In theory, theory and practice are the same; in practice they are not. On Sat, 20 May 2006, Yesmin Patwary wrote: To: mysql@lists.mysql.com From: Yesmin Patwary [EMAIL PROTECTED] Subject: Find invalid email formats using MySQL query. Dear All, My contact table has email field which contains many invalid email addresses. I find them using following php function. Is there a way to do that check in MySQL query? function emailValidate ($str) { $reg = ^([a-zA-Z0-9._-]+)@([a-zA-Z0-9-])+(\.[a-zA-Z0-9-]+)+$; if (eregi($reg, $str)) { return true;//-- good email }else{ return false;//--bad email } }//--close function Thanks in advance for any comments or help. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Find invalid email formats using MySQL query.
Probably the most efficient place to do this sort of field checking would be using javascript in the browser. That would stop the bad addresses even being sent down the line to the server in the first place. Keith In theory, theory and practice are the same; in practice they are not. On Sat, 20 May 2006, fbsd wrote: To: Yesmin Patwary [EMAIL PROTECTED], mysql@lists.mysql.com From: fbsd [EMAIL PROTECTED] Subject: RE: Find invalid email formats using MySQL query. You need to use a stronger edit to check email addresses. This is what I use. Note the dns mx domain name check. $len = 0,45; if (empty ($email)) { $errors[] = font color=redYou didn't enter a Email Address./font; } elseif (!ereg(^([[:alnum:]\.\_\-]+)([EMAIL PROTECTED]:alnum:]\.\-]+\.+), $email)) { $errors[] = font color=redEmail Address format is invalid. It must be this format [EMAIL PROTECTED]/font; $errors[] = font color=redAlpha-Numeric characters plus - and . and _ and @ are valid in the address./font; } else { // good email address so far now check for good dns mx for domain name list($Username, $Domain) = split(@,$email); if(!getmxrr($Domain, $MXHost)) { $errors[] = font color=redEmail Address Domain name is invalid because it has no DNS mx records./font;} As far as I know there is no way to do this in a query command. You have to read every row's email field and then process against the php code to check/validate followed by a DB delete for all bad ones. Far better to fix code that edits the email field before adding it to db. Denying bad field content before adding it is the normal way this is done. -Original Message- From: Yesmin Patwary [mailto:[EMAIL PROTECTED] Sent: Saturday, May 20, 2006 10:48 AM To: mysql@lists.mysql.com Subject: Find invalid email formats using MySQL query. Dear All, My contact table has email field which contains many invalid email addresses. I find them using following php function. Is there a way to do that check in MySQL query? function emailValidate ($str) { $reg = ^([a-zA-Z0-9._-]+)@([a-zA-Z0-9-])+(\.[a-zA-Z0-9-]+)+$; if (eregi($reg, $str)) { return true;//-- good email }else{ return false;//--bad email } }//--close function Thanks in advance for any comments or help. - Talk is cheap. Use Yahoo! Messenger to make PC-to-Phone calls. Great rates starting at 1cent;/min. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL, PHP and Yahoo webhosting
Hi. call this function, phpinfo(); in a *.php file. That should return alot of usesfull info. Make sure the mysql extension is being loaded. If so check the value of the following php config var: ; Default socket name for local MySQL connects. If empty, ; uses the built-in ; MySQL defaults. mysql.default_socket = /var/lib/mysql/mysql.sock You may need to change the setting in php.ini to tell php to use the correct socket to talk to mysql on. HTH Keith In theory, theory and practice are the same; in practice they are not. On Sat, 13 May 2006, Steve wrote: To: mysql@lists.mysql.com From: Steve [EMAIL PROTECTED] Subject: MySQL, PHP and Yahoo webhosting I'm fairly new to all this and duing everything step by step... I made my first (test) php site - and it works... then, i have this script where i'm supposed to find out if MySQL and PHP talk with each other. and there i get the answer Error 2002: Can't connect to local MySQL server through socket '/tmp/mysql.sock' (46) and the tem-folder in my page root is empty, too. But i have PHPMyAdmin and i can log in there. i just have no clue where the files are and how i change it so that i finaly can start learning by doing... Thanks so much for the help... steve -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.392 / Virus Database: 268.5.6/338 - Release Date: 5/12/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Is there any way to search a whole database for a value?
I need to look in several different tables/columns in a database for a particular value. If I find it, I need to update it. Is there any way to search/update every table/column in one query in a particular database? -- Keith Roberts IT Manager - Laptops For Less 2995 White Star Anaheim, CA 92806 http://www.laptopsforless.com 714.224.3980 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php