Re: [PHP] Comparing multiple Mysql tables to find duplicates
HI John,
I have this working with the exception of when there is a ' in an email
address that I want to remove.
I have modified your line { $email_list .= '{$row['email']}',; },
with { $email_list .= str_replace(', \', '{$r[email]}',); }, but
now it escapes all the single quotes. Is there a php function that
escapes single quotes in Mysql queries? If not, do you know the best
way to escape *only* single quotes in the email address string and not
the single quotes surrounding the string?
Example (output of $email_list)
'[EMAIL PROTECTED]','[EMAIL PROTECTED]'that.com','etc...
Need to escape the [EMAIL PROTECTED]'that.com
Thanks.
--
Chris Bruce
[EMAIL PROTECTED]
Idextrus
E-Business Architects
http://www.idextrus.com
3282 Wilmar Cres.
Mississauga, ON
L5L4B2
CA
905.828.9189
This e-mail and its contents are privileged, confidential and
subject to copyright. If you are not the intended recipient,
please delete this e-mail immediately. Any unauthorized use
or disclosure of the information herein is prohibited.
On Jan 30, 2004, at 12:54 PM, John W. Holmes wrote:
From: Chris Bruce [EMAIL PROTECTED]
Mysql 3.23.54.
My first thought was to load the output from the tables into an array
and they use a foreach and in_array to create a list of dups, but I
wanted to see if there was an easier way.
Ah, in that case, my other query won't work. :)
This should:
?php
$query = SELECT list1.email FROM master_list, list1 WHERE
master_list.email
= list1.email;
$result = mysql_query($query) or die(mysql_error());
while($r = mysql_fetch_assoc($result))
{ $email_list .= '{$row['email']}',; }
$email_list = substr($email_list,0,-1); //remove last comma
$query = DELETE FROM list1 WHERE email IN ($email_list);
$result = mysql_query($query) or die(mysql_error());
?
Repeat for other tables.
---John Holmes...
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Comparing multiple Mysql tables to find duplicates
From: Chris Bruce Is there a php function that escapes single quotes in Mysql queries? addslashes() mysql_escape_string() mysql_real_escape_string() ---John Holmes... -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Comparing multiple Mysql tables to find duplicates
On Wednesday 04 February 2004 00:09, Chris Bruce wrote:
I have modified your line { $email_list .= '{$row['email']}',; },
with { $email_list .= str_replace(', \', '{$r[email]}',); }, but
now it escapes all the single quotes. Is there a php function that
escapes single quotes in Mysql queries?
Yes, surprisingly enough it's called mysql_escape_string().
If not, do you know the best
way to escape *only* single quotes in the email address string and not
the single quotes surrounding the string?
Example (output of $email_list)
'[EMAIL PROTECTED]','[EMAIL PROTECTED]'that.com','etc...
Need to escape the [EMAIL PROTECTED]'that.com
I don't understand why you want to escape the single-quote. You shouldn't be
allowing such entries anyway because quotes are not valid characters for
domain names thus it's an invalid email address.
--
Jason Wong - Gremlins Associates - www.gremlins.biz
Open Source Software Systems Integrators
* Web Design Hosting * Internet Intranet Applications Development *
--
Search the list archives before you post
http://marc.theaimsgroup.com/?l=php-general
--
/*
Trust everybody, but cut the cards.
-- Finlay Peter Dunne, Mr. Dooley's Philosophy
*/
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Comparing multiple Mysql tables to find duplicates
Hi, If you are using mysql 4 you can use subselects to delete from where in (select) the select itself can be a multi table join. if you are on mysql 3.xx you can use PHP to mimic a subselect. Chris Bruce wrote: Hello everyone, I am trying to write a function that would compare one table to a number of other tables in Mysql and remove any duplicates found. This is for tables of email addresses where I want to remove any dups found. Example: Master list - compare to list1, list2, list3, and so on and remove matches found from list1, list2, list3 etc. *not* from Master list. Does anyone know of any such beast before I set out to reinvent it? Thanks. Chris -- Raditha Dissanayake. http://www.radinks.com/sftp/ | http://www.raditha.com/megaupload Lean and mean Secure FTP applet with | Mega Upload - PHP file uploader Graphical User Inteface. Just 150 KB | with progress bar. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Comparing multiple Mysql tables to find duplicates
Mysql 3.23.54. My first thought was to load the output from the tables into an array and they use a foreach and in_array to create a list of dups, but I wanted to see if there was an easier way. -- Chris Bruce [EMAIL PROTECTED] Idextrus E-Business Architects http://www.idextrus.com 3282 Wilmar Cres. Mississauga, ON L5L4B2 CA 905.828.9189 On Jan 30, 2004, at 12:54 PM, Raditha Dissanayake wrote: Hi, If you are using mysql 4 you can use subselects to delete from where in (select) the select itself can be a multi table join. if you are on mysql 3.xx you can use PHP to mimic a subselect. Chris Bruce wrote: Hello everyone, I am trying to write a function that would compare one table to a number of other tables in Mysql and remove any duplicates found. This is for tables of email addresses where I want to remove any dups found. Example: Master list - compare to list1, list2, list3, and so on and remove matches found from list1, list2, list3 etc. *not* from Master list. Does anyone know of any such beast before I set out to reinvent it? Thanks. Chris -- Raditha Dissanayake. --- - http://www.radinks.com/sftp/ | http://www.raditha.com/megaupload Lean and mean Secure FTP applet with | Mega Upload - PHP file uploader Graphical User Inteface. Just 150 KB | with progress bar.
Re: [PHP] Comparing multiple Mysql tables to find duplicates
From: Chris Bruce [EMAIL PROTECTED] I am trying to write a function that would compare one table to a number of other tables in Mysql and remove any duplicates found. This is for tables of email addresses where I want to remove any dups found. Example: Master list - compare to list1, list2, list3, and so on and remove matches found from list1, list2, list3 etc. *not* from Master list. Does anyone know of any such beast before I set out to reinvent it? DELETE FROM list1 USING master_list, list1 WHERE list1.email = master_list.email Repeat for other tables. ---John Holmes... -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] Comparing multiple Mysql tables to find duplicates
My first thought was to load the output from the tables into an array and they use a foreach and in_array to create a list of dups, but I wanted to see if there was an easier way. -- If you're not using MySQL 4, then yeah, that's probably the best way. Just realize that this isn't going to be the fastest script in the world. If it's a one-off or very infrequently run script, then that's not a big deal. -M -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Comparing multiple Mysql tables to find duplicates
From: Chris Bruce [EMAIL PROTECTED]
Mysql 3.23.54.
My first thought was to load the output from the tables into an array
and they use a foreach and in_array to create a list of dups, but I
wanted to see if there was an easier way.
Ah, in that case, my other query won't work. :)
This should:
?php
$query = SELECT list1.email FROM master_list, list1 WHERE master_list.email
= list1.email;
$result = mysql_query($query) or die(mysql_error());
while($r = mysql_fetch_assoc($result))
{ $email_list .= '{$row['email']}',; }
$email_list = substr($email_list,0,-1); //remove last comma
$query = DELETE FROM list1 WHERE email IN ($email_list);
$result = mysql_query($query) or die(mysql_error());
?
Repeat for other tables.
---John Holmes...
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Comparing multiple Mysql tables to find duplicates
This will work for Mysql 3.23.54? -- Chris Bruce [EMAIL PROTECTED] Idextrus E-Business Architects http://www.idextrus.com 3282 Wilmar Cres. Mississauga, ON L5L4B2 CA 905.828.9189 On Jan 30, 2004, at 12:48 PM, John W. Holmes wrote: From: Chris Bruce [EMAIL PROTECTED] I am trying to write a function that would compare one table to a number of other tables in Mysql and remove any duplicates found. This is for tables of email addresses where I want to remove any dups found. Example: Master list - compare to list1, list2, list3, and so on and remove matches found from list1, list2, list3 etc. *not* from Master list. Does anyone know of any such beast before I set out to reinvent it? DELETE FROM list1 USING master_list, list1 WHERE list1.email = master_list.email Repeat for other tables. ---John Holmes...
Re: [PHP] Comparing multiple Mysql tables to find duplicates
Cool, thanks John, I'll give it a shot. Sorry for the last email, I
sent it before you sent this one :)
--
Chris Bruce
[EMAIL PROTECTED]
Idextrus
E-Business Architects
http://www.idextrus.com
3282 Wilmar Cres.
Mississauga, ON
L5L4B2
CA
905.828.9189
On Jan 30, 2004, at 12:54 PM, John W. Holmes wrote:
From: Chris Bruce [EMAIL PROTECTED]
Mysql 3.23.54.
My first thought was to load the output from the tables into an array
and they use a foreach and in_array to create a list of dups, but I
wanted to see if there was an easier way.
Ah, in that case, my other query won't work. :)
This should:
?php
$query = SELECT list1.email FROM master_list, list1 WHERE
master_list.email
= list1.email;
$result = mysql_query($query) or die(mysql_error());
while($r = mysql_fetch_assoc($result))
{ $email_list .= '{$row['email']}',; }
$email_list = substr($email_list,0,-1); //remove last comma
$query = DELETE FROM list1 WHERE email IN ($email_list);
$result = mysql_query($query) or die(mysql_error());
?
Repeat for other tables.
---John Holmes...
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Comparing multiple Mysql tables to find duplicates
Did you mention cofee John? now why did i suggest subselects when good old joins seem to do the tricks. Yikes! John W. Holmes wrote: From: Chris Bruce [EMAIL PROTECTED] I am trying to write a function that would compare one table to a number of other tables in Mysql and remove any duplicates found. This is for tables of email addresses where I want to remove any dups found. Example: Master list - compare to list1, list2, list3, and so on and remove matches found from list1, list2, list3 etc. *not* from Master list. Does anyone know of any such beast before I set out to reinvent it? DELETE FROM list1 USING master_list, list1 WHERE list1.email = master_list.email Repeat for other tables. ---John Holmes... -- Raditha Dissanayake. http://www.radinks.com/sftp/ | http://www.raditha.com/megaupload Lean and mean Secure FTP applet with | Mega Upload - PHP file uploader Graphical User Inteface. Just 150 KB | with progress bar. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
