Category: MySQL

I have two MySQL databases on my server for my customers. One has customers who work for universities and one has customers who purchased from our website or made inquiries by phone or email. There is some overlap between the two and for the latest product announcement, I wanted to avoid sending duplicate emails. I usually use a php script to do my queries. If I am sending to just one group, the query is fairly straightforward.

require_once('marketing_database_my.inc');

$fname = "./MarketingExports/email.txt";

    $sql  = "(SELECT name, email FROM Universities ";
    $sql .= "WHERE email IS NOT NULL AND email != '' ";
    $sql .= "AND duplicate_email = 0 ";
    $sql .= "AND remove_email != 1) ";
    
    $result = $dbMKT->query($sql);
    $numRecords = mysqli_num_rows($result);

    echo "There are $numRecords records.";
    // Open the file to save the words
        
    $fp = fopen($fname, 'w');
    if (!$fp) {
        echo "Couldn’t open file.";

    } else {

        echo "Save to file $fname.<br />";
        for ($i = 0; $i < $numRecords; $i++) {
            $row = $result->fetch_array();
            $id = $row["id"];
            $company = trim($row["company"]);
            $name = trim($row["name"]);
            $email = trim($row["email"]);
         
            // Add to the email mail list
            $info = "\"$name\" <$email>";
            fwrite ($fp, "$info" . "\r");
        }
    }
    fclose($fp);

However, if I want to combine the University list and the other customers list, I need to adjust the query a bit by using a UNION

  $sql  = "(SELECT name, email, company FROM Customers ";
  $sql .= "WHERE email IS NOT NULL AND email != '' ";
  $sql .= "AND duplicate_email = 0 ";
  $sql .= "AND remove_email != 1) ";
  $sql .= "UNION ";
  $sql .= "(SELECT Universities.name, Universities.email, Universities.title FROM Universities, Customers ";
  $sql .= "WHERE Universities.email IS NOT NULL AND Universities.email != '' ";
  $sql .= "AND Universities.duplicate_email = 0 ";
  $sql .= "AND Universities.remove_email != 1 ";
  $sql .= "AND Universities.email != Customers.email) ";
  $sql .= "ORDER BY email ";

Two things to note. Name and email are the same in both tables. However, some of my customer records have the customer name as the company and a blank name field. The UNION won’t work if there are a different number of fields in the SELECT statement, so I include the title field for Universities just to make MySQL happy.

I can order by email because it is in both tables. Notice also that I had to tell the second part of the query which table to work on for the WHERE statement.

When processing addresses for postcard mailings we get a lot of addresses that don’t pass the VistaPrint address validation process. Fortunately, VistaPrint has a button that lets you download the addresses. What makes it easy for me to mark the bad addresses in my database is that I don’t use a salutation and instead put the customer number in the salutation field. I strip out all of the address information and keep just the customer ID. I then massage the IDs so that they look like this.

INSERT INTO `customers` (id, BadVistaPrint) VALUES 
(15771, 1), 
...
(16248, 1), 
(16249, 1)
ON DUPLICATE KEY UPDATE BadVistaPrint=VALUES(BadVistaPrint)

What this code does is look for customer ID and then update the BadVistaPrint value. Not sure why it uses the ON DUPLICATE KEY syntax. Note that the last row before the ON DUPLICATE KEY does not have a comma.

I paste this into the SQL section of phpMyAdmin and I my database is now updated with bad addresses. I can manually look for easy to fix addresses e.g. missing numbers, zip codes, etc. and look them up. There are lots of addresses that are mailable but not in the VistaPrint database e.g. University departments and rural route addresses and I mail them separately.

I also use the USPS zip code finder to validate addresses.

I have a list of email addresses of people who no longer wish to get my product newsletter. I have another list of customers. I want to make sure that even if they bought something and got a confirmation email, that they don’t get a newsletter email. This code finds the ones who do not want to get the newsletter.

SELECT * FROM customers JOIN remove ON remove.email = customers.email

We used to do a cron job every night that made a copy of the MySQL database. Since we moved to Linode for hosting, we don’t need the cron job, but every once in a while I make a backup of the database and download it to my computer. This is the command I use.

mysqldump -u backup -A --default-character-set=utf8 -e --create-options >/srv/mysql-backup.sql

I usually use phpMyAdmin to manipulate tables in my databases but sometimes the file I want to import is too big. In that case I use the command line to import the file.

In this line, I replace a database with formatted sql statements that I’ve edited by hand. the -p flag without an argument will prompt for the root password of the MySQL installation.

mysql -u root -p original_database < /Users/username/Desktop/edited_data.sql

Here I want to replace the entire MySQL database with the backup. I do this on my backup machine from time to time using the nightly My backup. This example shows the password after the flag.

mysql -u root -pYourPassword < mysql-backup.sql