Find invalid email formats using MySQL query.

2006-05-20 Thread Yesmin Patwary 
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.

2006-05-19 Thread Yesmin Patwary 
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! Messenger’s low  PC-to-Phone call rates.

Re: Find ids that are NOT in other two tables.

2006-05-19 Thread Yesmin Patwary 
  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.

2006-03-22 Thread Yesmin Patwary 
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?

2006-03-13 Thread Yesmin Patwary 
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_id’s 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.

2006-03-13 Thread Yesmin Patwary 
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_id’s 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.

2006-03-13 Thread Yesmin Patwary 
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_id’s 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?

2006-03-10 Thread Yesmin Patwary 
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_id’s 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?

2005-08-04 Thread Yesmin Patwary 

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