Seattle - July DUG Meeting

Walk down there from work all the time anyways!

Looking forward to hearing how Dupal Camp went. Will there be some video for me to edit? I saw some streaming and it looked good.

I am offering a Beverage Bounty* to anyone who can provide an efficient SQL select/update statement to find duplicate records in a content type and update a CCK count field if dupe count > 1. (*Any beverage of your choice: beer, coffee, HeySong Sarsaparilla, etc.)

Before updating the count fields, any count field greater than 1 should be set to null.

Bonus points: The SQL should run in a PHP file and provide screen output in test mode. PHP file to run on demand or via cron. To make it easier, test mode can be turned on/off with a code patch and database updates with similar on/off code patch.

Bonus points: SQL select should cause minimal load, so outputting run time is helpful. (I have a web host who frowns on long sessions and will shut down the offending process with directory chmod.)

Example screen output in test mode, where dupes are zip codes:

Pike Place Brewery, Seattle, WA, 98101, 3
Fancy Pants, Seattle, WA, 98101, 3
Top Pot, Seattle, WA, 98101, 3
Mikes Chili, Seattle, WA, 98112, 2
Brain Drain Coffee, Seattle, WA, 98112, 2
etc.
...
Total Records: 9999
Runtime: 5.01 seconds

Drupal Application: After I batch update the dupe counts, I will View List duplicates by filtering on the count field.

========================================
I used this PHP/SQL code on 1000+ Drupal records and it got shutdown by the web host admin after running it 3 times:

<?php
if ($db = @mysqli_connect('localhost', 'userid', 'password')) {
mysqli_select_db($db, 'dbname');
    $result = mysqli_query($db, 'SELECT field_org_name_value,field_org_city_value,field_org_zip_value,field_org_state_value, count(field_org_name_value) FROM content_type_resource GROUP BY field_org_name_value ASC HAVING COUNT(field_org_name_value)>1;
    while ($row = mysqli_fetch_object($result)) {
      printf(
        '<tr><td>%s</td><td>%s</td><td>%s</td><td>%s</td></tr>',
        htmlspecialchars($row->field_org_name_value),
        htmlspecialchars($row->field_org_city_value),
        htmlspecialchars($row->field_org_zip_value),
        htmlspecialchars($row->field_org_state_value)
      );
    }
    mysqli_close($db);
  } else {
    echo '<tr><td colspan="5">Connection failed.</td></tr>';
  }
?>

Organization	City	ZIP	State

==============================
Here are two references I found which might be helpful:

http://dev.mysql.com/doc/refman/5.0/en/select.html
Posted by Paul Montgomery on August 13 2005 8:06am

CREATE TEMPORARY TABLE dupes SELECT * FROM tablename GROUP BY colname HAVING COUNT(*)>1 ORDER BY colname;
SELECT t.* FROM tablename t, dupes d WHERE t.colname = d.colname ORDER BY t.colname;

http://www.delphifaq.com/faq/delphi/database/f20.shtml

// Return all duplicate cities and how often they appear
// Works with ORACLE
select city_name, count(city_name) as cnt
from areas
group by city_name
where cnt>1

// not all SQL dbms will support the reference to the count column cnt in the where clause.
// The following will return ALL rows with counter, but sorted by number of appearances
// Your duplicates will be at the top.
// Works with MYSQL
select city_name, count(city_name) as cnt
from areas
group by city_name
order by cnt desc

// finally, no back reference to count column cnt at all-
// the following will work on all SQL dbms:
// Return all cities and how often they appear
select city_name, count(city_name) as cnt
from areas
group by city_name

// version for Micrsoft's MSSQL Server
// make use of the HAVING clause
select city_name
from areas
group by city_name
having count(*) > 1

=================================
John Walling
wallingconsulting @ yahoo .com

I decided updating duplicates is not the best choice for now. Instead, I am providing Drupal search links for discovered matches. I made 4 match searches to increase the chances of finding unintended duplicates in a large and growing database.

1 Resource ID - Duplicate Matches
2 Org Name + Org City - Duplicate Matches
3 Web URL - Duplicate Matches
4 Org Name - Duplicate Matches

To see it in action go here:
http://www.disasterassistancenetwork.com/drupal-dupes/duplicate_match.php

HTML/PHP/SQL for match #2, an example of hacking for the complete idiot:
Note: Replace the word 'asterisk' with the single character.

at the July DUG meeting there was discussion about meeting for a couple hours before the next meeting to have a little co-work time. I am wondering if there might be any interest in setting up a co-work afternoon or early evening before the date of the next meeting. if you are interested please reply here and we could set it up- maybe 3-5 some afternoon next week @ Vivace?

thanks