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.
Find ids that are NOT in other two tables.
Dear All, Need to find ids that are NOT in other two tables. With the help of PHP scripting and three queries I am able to do the task. Can this be done with one mysql query? Below are current quires: SELECT DISTINCT company_id FROM company_db SELECT company_id FROM person WHERE company_id=\$company_id\ SELECT company_id FROM customer WHERE company_id=\$company_id\ Thanks in advance for any help. - How low will we go? Check out Yahoo! Messengers low PC-to-Phone call rates.
Re: Find ids that are NOT in other two tables.
Hi, Greatly appreciated your kind help. Would it work, if I rewrite your query as below to remove all records from company_db that don't have any relation to other two tables? DELETE FROM company_db c LEFT JOIN person p ON c.company_id = p.company_id LEFT JOIN customer cu ON c.company_id = cu.company_id WHERE p.company_id IS NULL AND cu.company_id IS NULL; I am not sure if this join query will remove records from other tables. Jay Pipes [EMAIL PROTECTED] wrote: Yesmin Patwary wrote: Dear All, Need to find ids that are NOT in other two tables. With the help of PHP scripting and three queries I am able to do the task. Can this be done with one mysql query? Below are current quires: SELECT DISTINCT company_id FROM company_db SELECT company_id FROM person WHERE company_id=\$company_id\ SELECT company_id FROM customer WHERE company_id=\$company_id\ Use outer joins: SELECT DISTINCT c.company_id FROM company_db c LEFT JOIN person p ON c.company_id = p.company_id LEFT JOIN customer cu ON c.company_id = cu.company_id WHERE p.company_id IS NULL AMD cu.company_id IS NULL; -- Jay Pipes Community Relations Manager, North America, MySQL Inc. Roaming North America, based in Columbus, Ohio email: [EMAIL PROTECTED] mob: +1 614 406 1267 Are You MySQL Certified? http://www.mysql.com/certification Got Cluster? http://www.mysql.com/cluster - Feel free to call! Free PC-to-PC calls. Low rates on PC-to-Phone. Get Yahoo! Messenger with Voice
INSERT...SELECT Query Help Request.
Dear All, First of all, I would like to thank Shawn Green, Peter Brawley and Josh for their kind help in my previous issue. I have a table named master_list with two field customer_id and list_code. I need to insert only non existent records in master_list from a child_list. Both master_list and child_list table structure are identical but data in child_list may contain records from master_list and new records. I have constructed query using INSERT SELECT but I am unable to check and filter out records that already exist in master_list. INSERT INTO master_list (customer_id,list_code) SELECT DISTINCT customer_id,list_code FROM child_list Is there anyway to check and insert records in master_list without creating dups? Thanks in advance for any help. - Yahoo! Mail Bring photos to life! New PhotoMail makes sharing a breeze.
Re: Compare lists Query?
Dear All, I had some issues in past with timestamp fields as a result I am unable to upgrade to mysql 4.1 version. I am sure below the query recommended by Josh works with 4.1 or above. Would it be possible to rewrite this query for 3.23 version? Again, thank you Josh and all others for your kind help and comments. Josh [EMAIL PROTECTED] wrote: Here's one method: SELECT cl1.list_name, count(*) as count FROM customerList cl1 WHERE cl1.id IN (SELECT cl2.id FROM customerList cl2 WHERE cl2.list_name='CA10') and cl1.list_name != 'CA10' GROUP BY cl1.list_name --- Yesmin Patwary wrote: Good morning all, We have 12 customer lists: CA01, CA02, .,CA12. Table: customerList +---+--+ | list_name | id | +---+--+ | CA10 | 20BE | | CA07 | 20BE | | CA11 | 20BE | | CA03 | 20BE | | CA10 | NQCR | | CA04 | NQCR | | CA02 | MVYK | | CA10 | 0BEC | | AND SO ON. | +---+--+ Each list has 25 to 350 customers. Same customer_id may exist in multiple lists. We need to compare CA10 list customer_ids with other 11 lists to find matching id count by list_name. The query output should be something similar below: +--+---+ | list_name |count | +--+---+ | CA05 | 60 | | CA07 | 42 | | CA01 | 35 | | CA03 | 28 | | CA09 | 15 | | AND SO ON | +---+--+ Can this be done with a SELECT statement without using perl or php? Thanks in advance for any help. - Yahoo! Mail Bring photos to life! New PhotoMail makes sharing a breeze.
Problems with timestamp field after upgrading MySQL Server.
Dear All, First of all, I would like to thank to Josh and Peter Brawley for their kind help on previous issue. Here is another Issue: There are many tables that have timestamp field with 8 char (mmdd). I use this format all over our website and to create various reports. Below is static php code that I used numerous places: ? $year=substr($date, 0, 4); $month=substr($date, 4, 2); $day=substr($date, 6, 2); ? All timestamp dependent reports and web pages had problems, once I upgrade to MySQL 4.1.12. At the end I had to downgrade to 3.23 to bring web site operation to normal. Is there anyway to preserve old timestamp format after upgrading? MySQL - 3.23 +++-- | Field | Type | Default (mmdd) +++-- |log_date|timestamp(8)| MySQL - 4.1.12 ++---+ | Field | Type| Default ++---+ |log_date| timestamp |-00-00 00:00:00 Josh [EMAIL PROTECTED] wrote: You could rotate the output... basically get 1 row with 11 columns (CA01_count,CA02_count,...,CA12_count) (leaving out CA10) joining all 12 tables together... Or... perhaps we can help with the timestamp issues you are having and get you upgraded to later version of mysql that supports nested SELECT statements. --- Yesmin Patwary wrote: Dear All, I had some issues in past with timestamp fields as a result I am unable to upgrade to mysql 4.1 version. I am sure below the query recommended by Josh works with 4.1 or above. Would it be possible to rewrite this query for 3.23 version? Again, thank you Josh and all others for your kind help and comments. Josh wrote: Here's one method: SELECT cl1.list_name, count(*) as count FROM customerList cl1 WHERE cl1.id IN (SELECT cl2.id FROM customerList cl2 WHERE cl2.list_name='CA10') and cl1.list_name != 'CA10' GROUP BY cl1.list_name --- Yesmin Patwary wrote: Good morning all, We have 12 customer lists: CA01, CA02, .,CA12. Table: customerList +---+--+ | list_name | id | +---+--+ | CA10 | 20BE | | CA07 | 20BE | | CA11 | 20BE | | CA03 | 20BE | | CA10 | NQCR | | CA04 | NQCR | | CA02 | MVYK | | CA10 | 0BEC | | AND SO ON. | +---+--+ Each list has 25 to 350 customers. Same customer_id may exist in multiple lists. We need to compare CA10 list customer_ids with other 11 lists to find matching id count by list_name. The query output should be something similar below: +--+---+ | list_name |count | +--+---+ | CA05 | 60 | | CA07 | 42 | | CA01 | 35 | | CA03 | 28 | | CA09 | 15 | | AND SO ON | +---+--+ Can this be done with a SELECT statement without using perl or php? Thanks in advance for any help. - Yahoo! Mail Bring photos to life! New PhotoMail makes sharing a breeze. __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] - Yahoo! Mail Use Photomail to share photos without annoying attachments.
Problems with timestamp field after upgrading MySQL Server.
Dear All, First of all, I would like to thank to Josh and Peter Brawley for their kind help on previous issue. Here is another Issue: There are many tables that have timestamp field with 8 char (mmdd). I use this format all over our website and to create various reports. Below is static php code that I used numerous places: ? $year=substr($date, 0, 4); $month=substr($date, 4, 2); $day=substr($date, 6, 2); ? All timestamp dependent reports and web pages had problems, once I upgrade to MySQL 4.1.12. At the end I had to downgrade to 3.23 to bring web site operation to normal. Is there anyway to preserve old timestamp format after upgrading? MySQL - 3.23 +++-- | Field | Type | Default (mmdd) +++-- |log_date|timestamp(8)| MySQL - 4.1.12 ++---+ | Field | Type| Default ++---+ |log_date| timestamp |-00-00 00:00:00 Josh [EMAIL PROTECTED] wrote: You could rotate the output... basically get 1 row with 11 columns (CA01_count,CA02_count,...,CA12_count) (leaving out CA10) joining all 12 tables together... Or... perhaps we can help with the timestamp issues you are having and get you upgraded to later version of mysql that supports nested SELECT statements. --- Yesmin Patwary wrote: Dear All, I had some issues in past with timestamp fields as a result I am unable to upgrade to mysql 4.1 version. I am sure below the query recommended by Josh works with 4.1 or above. Would it be possible to rewrite this query for 3.23 version? Again, thank you Josh and all others for your kind help and comments. Josh wrote: Here's one method: SELECT cl1.list_name, count(*) as count FROM customerList cl1 WHERE cl1.id IN (SELECT cl2.id FROM customerList cl2 WHERE cl2.list_name='CA10') and cl1.list_name != 'CA10' GROUP BY cl1.list_name --- Yesmin Patwary wrote: Good morning all, We have 12 customer lists: CA01, CA02, .,CA12. Table: customerList +---+--+ | list_name | id | +---+--+ | CA10 | 20BE | | CA07 | 20BE | | CA11 | 20BE | | CA03 | 20BE | | CA10 | NQCR | | CA04 | NQCR | | CA02 | MVYK | | CA10 | 0BEC | | AND SO ON. | +---+--+ Each list has 25 to 350 customers. Same customer_id may exist in multiple lists. We need to compare CA10 list customer_ids with other 11 lists to find matching id count by list_name. The query output should be something similar below: +--+---+ | list_name |count | +--+---+ | CA05 | 60 | | CA07 | 42 | | CA01 | 35 | | CA03 | 28 | | CA09 | 15 | | AND SO ON | +---+--+ Can this be done with a SELECT statement without using perl or php? Thanks in advance for any help. - Yahoo! Mail Bring photos to life! New PhotoMail makes sharing a breeze. __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] - Yahoo! Mail Use Photomail to share photos without annoying attachments.
Compare lists Query?
Good morning all, We have 12 customer lists: CA01, CA02, .,CA12. Table: customerList +---+--+ | list_name | id | +---+--+ |CA10 | 20BE | |CA07 | 20BE | |CA11 | 20BE | |CA03 | 20BE | |CA10 | NQCR | |CA04 | NQCR | |CA02 | MVYK | |CA10 | 0BEC | | AND SO ON. | +---+--+ Each list has 25 to 350 customers. Same customer_id may exist in multiple lists. We need to compare CA10 list customer_ids with other 11 lists to find matching id count by list_name. The query output should be something similar below: +--+---+ | list_name |count | +--+---+ |CA05 | 60 | |CA07 | 42 | |CA01 | 35 | |CA03 | 28 | |CA09 | 15 | | AND SO ON | +---+--+ Can this be done with a SELECT statement without using perl or php? Thanks in advance for any help. - Relax. Yahoo! Mail virus scanning helps detect nasty viruses!
How to find Balance?
Dear All, I am trying to calculate balance for all id's. Can it be done using only one MySQL query (without any scripting, eg: php,perl,asp..)? price table: id|price 1|150 2|100 3|300 payment table: id|transaction_type|amount 1|PAID|75 1|PAID|25 1|REFUND|60 1|REFUND|30 1|PAID|140 2|PAID|35 2|REFUND|15 3|PAID|300 Thanks in advance for any help. __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com