Anthony Pace – Page 4 – Ant Pace Blog

March 14, 2022January 11, 2025

Remove Duplicates From a MySQL Database Table

I discovered a bug in a web app that I built a few years ago. It was difficult to debug because it only happened intermittently. As any programmer knows, issues that can’t be reproduced consistently (and locally) present the most pain. Ultimately, it was causing database records to be created in double – but only when certain conditions evaluated true in the app state.

I’ll get into the code fix in another post. Here, I’ll show you how I cleaned up the database. This application has over ten-thousand data records in production. The first thing I did before messing around was to export a back-up of the prod DB. That was only me being extra careful – I already have a nightly job that dumps the entire production database as a .sql file to an S3 bucket. Taking an export on the fly is easy through phpMyAdmin.

Step one is to identify duplicates and store them in a temporary table, using a GROUP BY clause. In MySQL (and most other SQL-based database systems),GROUP BY is used to group rows from a table based on one or more columns.

The duplicate rows that I am interested in have all identical values, except for their primary keys. I can group those rows (and put them into a new, temporary, table) by including all of the table columns names (except the primary key) in my SQL statement. You can list those names in phpMyAdmin with this command:

SHOW COLUMNS FROM `records`;

My tables have quite a few columns. Instead of copy/pasting each field name, I used SQL code to list them out together. This was possible by leveraging the INFORMATION_SCHEMA database, a special system database that provides metadata about the database server itself in MySQL. I could retrieve the column names and then concatenate them into a single string using the GROUP_CONCAT function:

SELECT GROUP_CONCAT(column_name SEPARATOR ', ') AS column_list
FROM information_schema.columns
WHERE table_schema = 'bjjtracker'
  AND table_name = 'records';

The result displayed as abbreviated until I selected “Full texts” from the options menu (highlighted below)

I could now copy/paste that column_list into my SQL statement. Remove the primary key field (usually the first one), or else no duplicates will be found (unless your use-case involves records having repeated primary key values, which is a less likely scenario).

CREATE TABLE TempTable AS
SELECT userid, type, date, beltrank, medal, weight, notes, created_date, style -- List all columns except the primary key
FROM `records`
GROUP BY userid, type, date, beltrank, medal, weight, notes, created_date, style -- Group by all columns except the primary key
HAVING COUNT(*) > 1; -- Indicates their is more than one record with exactly matching values

Now we have a new table that contains records that are duplicative in our original table. Step 2 is to delete the duplicates from the original table.

DELETE FROM `records`
WHERE (userid, type, date, beltrank, medal, weight, notes, created_date, style) IN (
    SELECT userid, type, date, beltrank, medal, weight, notes, created_date, style
    FROM TempTable
);

Don’t forget to delete that temporary table before you leave:

DROP TEMPORARY TABLE IF EXISTS TempTable;

Dealing with NULL values

On the first table I used this on, everything worked as expected. On a subsequent run against another table, zero rows were deleted even though my temp table contained duplicate records. I deduced that it was because of NULL values causing the comparison to not work as expected. I figured that I had to handle NULL values explicitly using the IS NULL condition on each field.

DELETE FROM recordsdetails
WHERE 
    (userid IS NULL OR userid, 
     recordid IS NULL OR recordid, 
     detailtype IS NULL OR detailtype, 
     technique IS NULL OR technique, 
     reps IS NULL OR reps, 
     partnername IS NULL OR partnername, 
     partnerrank IS NULL OR partnerrank, 
     pointsscored IS NULL OR pointsscored, 
     pointsgiven IS NULL OR pointsgiven, 
     taps IS NULL OR taps, 
     tappedout IS NULL OR tappedout, 
     result IS NULL OR result, 
     finish IS NULL OR finish, 
     created_date IS NULL OR created_date) 
IN (
    SELECT userid, recordid, detailtype, technique, reps, partnername, partnerrank, pointsscored, pointsgiven, taps, tappedout, result, finish, created_date
    FROM TempTable
);

But yet, I still got zero rows being deleted. This time though, I was seeing a warning. It complained: “Warning: #1292 Truncated incorrect DOUBLE value”

This suggests that there is a data type mismatch or issue in the comparison involving numeric and string values. My guess is that the IS NULL handling was causing type conversion issues. To remedy this, I wrote a more explicit query by combining the AND and OR conditions.

DELETE FROM recordsdetails
WHERE 
    (userid IS NULL OR userid IN (SELECT userid FROM TempTable)) AND
    (recordid IS NULL OR recordid IN (SELECT recordid FROM TempTable)) AND
    (detailtype IS NULL OR detailtype IN (SELECT detailtype FROM TempTable)) AND
    (technique IS NULL OR technique IN (SELECT technique FROM TempTable)) AND
    (reps IS NULL OR reps IN (SELECT reps FROM TempTable)) AND
    (partnername IS NULL OR partnername IN (SELECT partnername FROM TempTable)) AND
    (partnerrank IS NULL OR partnerrank IN (SELECT partnerrank FROM TempTable)) AND
    (pointsscored IS NULL OR pointsscored IN (SELECT pointsscored FROM TempTable)) AND
    (pointsgiven IS NULL OR pointsgiven IN (SELECT pointsgiven FROM TempTable)) AND
    (taps IS NULL OR taps IN (SELECT taps FROM TempTable)) AND
    (tappedout IS NULL OR tappedout IN (SELECT tappedout FROM TempTable)) AND
    (result IS NULL OR result IN (SELECT result FROM TempTable)) AND
    (finish IS NULL OR finish IN (SELECT finish FROM TempTable)) AND
    (created_date IS NULL OR created_date IN (SELECT created_date FROM TempTable));

That worked! With a cleaned database, it was time to figure out what was causing the bug in the first place, and to fix the problem.

Clones in quiet dance; Copies of our code converge; Echoes of our souls;

February 19, 2022January 11, 2025

Online Ordering for a Restaurant Website

A Digital Transformation Case Study: Boosting Restaurant Sales with Custom Web Development and Online Ordering Integration

Client background & challenge

When I was younger I worked as a pizza delivery driver. Years later, the pizzeria where I once worked commissioned me to build their website. They were busier than ever thanks to online ordering (GrubHub, Seamless, UberEats), but were getting hit with high service fees.

They wanted their own website to be able to take orders for food online and send a notification to their iPad. That way they could avoid using apps like GrubHub that charged additional fees.

Project overview & execution

I used a service called GloriaFood that provides ready-made website templates, a secure payment process, and a messaging system. It integrates with Stripe for processing payments. There is an iPad app that receives push notifications when new orders are placed. The website builder required no code, and had a ton of options. I was able to register the pizzeria’s domain name directly though the admin portal, and generate a sales optimized website with hosting all setup. It was “seamless” – pun intended!

There are also options for integrating their ordering UI with an existing website, a Facebook page, or a dine-in QR code. The an option to publish a custom app required an additional cost per month.

I even traveled to this restaurant’s physical location, selected and purchased a tablet computer for them, installed the GloriaFood app to receive orders, and connected it to their mobile printer.

It’s amazing how much I was able to accomplish without writing a single line of code. The most technical part of this project was setting up a Stripe account and putting the API keys into the GloriaFood admin panel. GloriaFood is a product by Oracle, a company that specializes in providing a wide range of software and hardware products and services.

Print Design

As an extra part of this project, I designed a business card with a QR code linking to the new website. The business owner planned to give this to customers who ordered through other food ordering apps such as GrubHub, Seamless, UberEats, and Slice.

Results

Sales Increase

Since the launch of the new website, the restaurant has witnessed a notable surge in online orders, marking a 25% increase. This substantial rise not only signifies a successful digital transformation but also illustrates the growing customer preference for a seamless, direct ordering experience. The intuitive interface and easy navigation on the restaurant’s website have played a strong role in attracting and retaining customers, driving a higher volume of online orders and significantly contributing to the restaurant’s revenue growth.

Cost Savings

Transitioning from third-party ordering platforms like GrubHub, Seamless, and UberEats to a self-hosted online ordering system through has led to big cost savings. Third-party platforms usually charge hefty commissions, which eat into the restaurant’s profits and inflate prices for customers. With the new website, the restaurant has eliminated these intermediary costs, ensuring better profitability while also offering customers more competitive pricing.

Customer Feedback

The feedback received from both the restaurant management and its customers has been overwhelmingly positive. The restaurant staff has praised the streamlined process, which has simplified order management and allowed for a smoother operation during busy hours.

January 18, 2022January 11, 2025

How I Helped a Business Optimize Their Google Ads

After a client approached me about improving their Google Ads campaign, I could tell they were doing a lot of things right—but they weren’t getting the results they wanted. Their ads were active, their click-through rate was decent, and they were spending about $650 per month. On the surface, everything seemed fine.

But as I dug deeper, I saw an opportunity to transform their campaign from “good enough” into something that could truly drive new clients to their business.

Google Ad Account Audit

The first thing I noticed was that their ads setup was missing some key pieces. For starters, there was no way to measure what happened after someone clicked on an ad. Conversion tracking hadn’t been set up, so there was no way to tell if those clicks led to sign-ups, contact form submissions, or any other meaningful action. It’s like running a sales campaign and never finding out how many people bought something.

Another issue was the structure of the ads themselves. Everything was running through a single ad and ad group, all of which pointed to their homepage. While this kind of setup can work as a starting point, it’s not ideal for targeting specific audiences. Imagine searching for “self-defense classes” and landing on a generic homepage with no mention of self-defense—it’s a missed opportunity to connect with a potential client.

Finally, their campaign wasn’t linked to Google Analytics, which meant there was no insight into what visitors did once they arrived on the site. Did they spend time exploring? Did they leave immediately? Did they look at multiple pages? These questions were left unanswered.

Google Ad Campaign Improvements Plan

After reviewing their campaign, I proposed a complete overhaul. First, I explained the importance of defining what success looks like—what actions do we want people to take when they click on an ad? For this client, it made sense to track things like contact form submissions and clicks to their scheduling platform. By setting up conversion tracking, we could finally measure the real impact of their ad spend.

Next, I suggested restructuring their campaign into three distinct ad groups, each with its own audience and purpose. The first ad group would target people new to their services, highlighting beginner-friendly options. The second would focus on fitness enthusiasts looking for a fun and effective workout. The third would spotlight programs for women, emphasizing empowerment and inclusion.

Each ad group would feature two different ads to test different messages and see what resonated most with each audience. And instead of pointing all traffic to the homepage, I recommended creating dedicated landing pages tailored to the ad group. If someone clicked on an ad for fitness classes, they’d land on a page showcasing the physical and mental benefits of their programs. If they clicked on an ad for women’s classes, they’d be greeted with content specifically designed for that audience.

Finally, I emphasized the need to connect Google Analytics to their campaign. This would give us a clearer picture of how people interacted with the site after clicking on an ad, providing valuable insights for ongoing optimization.

Marketing & Development Implementation

The client loved the plan, and here’s what I did next. I spent a couple of hours implementing these changes, setting up the ad groups, writing the ads, building the landing pages, and integrating analytics and conversion tracking. Once that was done, we were ready to relaunch the campaign with a much stronger foundation. I used the Google Site Kit plugin for WordPress to manage analytics.

I built the landing pages as part of their WordPress website. I drafted the copy for each page, added relevant photos, and applied proven design principles that would guide potential customers through the sales funnel. I used the same copy outline and content layout for each.

Landing Page Content Outline

Hero Section
1. Headline
2. Subheadline
3. CTA Button
Benefits Section
Headline: Why this offer is valuable
1. Highlight key benefit 1
2. Highlight key benefit 2
3. Highlight key benefit 3
What to Expect / Program Details
1. Headline: Set expectations
2. Short paragraph explaining details or structure
3. CTA button
Testimonials
Headline (Social Proof): “What are customers are saying”
1. Testimonial 1
2. Testimonial 2
FAQ Section
Headline: Answer common questions
1. Q1 & Answer
2. Q2 & Answer
3. Q3 & Answer
Final CTA Section
1. Headline (Closing statement)
2. CTA button

Getting Results with Google Ads

Of course, ads don’t run themselves. The client will need to decide when they want to turn the campaign back on and how much they’re comfortable spending each month. But with these optimizations in place, they’ll be able to make data-driven decisions and see exactly how their investment is paying off.

Every business wants to get the most out of their marketing budget. For this client, that means attracting new customers who are truly interested in their services. By taking the time to set up conversion tracking, target specific audiences, and create tailored landing pages, we’re turning their Google Ads campaign into a powerful tool for growth.

If you’re running Google Ads and feel like you’re not getting the results you should, you’re not alone. With the right strategy and a little bit of work, you can transform your campaign into something that delivers real, measurable value.

Want to learn more about how I can help your business grow? Let’s talk.

December 21, 2021January 11, 2025

Array Rotation in PHP

An operation to rotate an array will shift each element to the left. We can perform this function X number of times depending on the input parameters. Since the lowest index element moves to the highest index upon rotation, this is called a circular array.

All we need to do is remove the first element of the array, save it in a variable, and then attach it to the end of the array. This can be done inside of a loop that iterates the number of times that we want to rotate the data. The function can take in the original array and the number of rotations. It returns the newly rotated array:

function rotateLeft($array, $rotations) {     
    for($i=0;$i<$rotations;$i++){
        $firstElement = array_shift($array);
        array_push($array, $firstElement);
    }
    return $array;
}

Although this code will work, and gets the job done, it is slow. When I tried this as a solution on HackRank (a code challenge website) it passed 9/10 test cases. It failed once, citing “time limit exceeded”.

The problem is with array_shift. It’s a PHP function that removes “the first value of an array off and returns it.” This shortens the array by a single element, moving everything else down one index. The algorithmic efficiency (expressed in Big-O notation) of array_shift() is O(n). That means, the larger the input array, the more time it will take to complete.

Next, I tried using array_reverse and array_pop. I figured that since array_pop() has a constant algorithmic efficiency, noted as O(1), it would help. Regardless of the size of the input, it would always take the same amount of time. But, due to the use of array_reverse (twice!) it was even slower:

function rotateLeft($array, $rotations) {
    for($i=0;$i<$rotations;$i++){
        $array = array_reverse($array);
        $firstElement = array_pop($array);
        $array = array_reverse($array);
        array_push($array, $firstElement);
    }
    return $array;
}

Finally, I found a solution that was performant:

function rotLeft($array, $rotations) {
        $remaining = array_slice($array, $rotations);
        array_splice($array, $rotations);
        return array_merge($remaining, $array);
}

This code does not need to use any kind of loop, which helps to speed things up. First, array_slice() grabs the elements up to the point that the data should be rotated, and saves them to the variable $remaining. Next, array_splice() removes those same elements. Lastly, array_merge() glues the elements back together in the expected, rotated order.

This sort of computer science programming challenge can commonly be found during software engineering job interviews. When coming up with an answer, it is important to consider speed and performance. Understanding computing cost-effectiveness and Big-O can be a deciding factor in how your coding skills are judged.

November 13, 2021January 19, 2025

Look-and-Say in PHP

The look-and-say sequence is a series of integers. It can grow indefinitely. It is generated by reciting a number phonetically, and writing what you spoke numerically. Its popularity is attributed to famed cryptographer Robert Morris. It was introduced by mathematician John Conway. It looks like this:

The first line would be pronounced as “one 1”, and then written as “11” on the second line. That record would be spoken as “two 1’s”, giving us the third line “21”. The greatest individual symbol you’ll ever find in this consecution is a 3.

This topic has lots of trivia, variations, and history that could be dug up and expounded upon. Here, I’ll explain a solution written in PHP to produce this chain of numerals. The input will be the count of how many lines, or iterations, in the series to generate. Below is the code:

<?php

echo "Count And Say: \n";

function countAndSay($count=0){
	$value = 1; // initial seed
	for($i=1;$i<=$count;$i++){
		echo $value . "\n";
		$value = calcOutput($value);
	}
}
function calcOutput($value){
	$value = "$value";  // change it into a string, so we can iterate over each character
	$current = $value[0]; // first character
	$count = 1;
        $return = '';
	for ($i = 1; $i <= strlen($value); $i++) { // keep going until we get through the whole string
		if ($current != $value[$i] || $i == strlen($value)) { // found a different character, or end of the input string
			$return .= "$count$current";
			$count = 1; // reset count
			$current = $value[$i]; // set new current character
		} else {
			$count++;
		}
	}
	return $return;
}

countAndSay(7);

echo "\n\n";

?>

I separated my code into two functions. I think this is the best approach. As an exercise, see if you can figure out how to refactor it into one. This could help you to internalize the logic as you write it out for yourself.

The initial seed value is “1”, and that is hard-coded at the top. The for-loop iterates based on the count input parameter. That means the code circles back and re-runs, with updated values, until its internal count (represented by the variable $i ) matches the $count variable passed into countAndSay($count).

The code that we loop over outputs the current sequence value (starting with 1) as its own line (“\n” will output a new line in most programming languages) , and then calculates the next. The function that determines the next line of output, calcOutput($value), takes the current value as an argument.

The first thing we do is cast the integer value passed along into a string. This lets us refer to each character by index – starting at zero – and save it to a variable $current. We start a new $count, to keep track of how many times we see the same digit.

The next for-loop executes for the length of the $value string. On each loop, we check if the $current character we saved matches the subsequent one in that $value string. It is again referenced by index, this time based on the for-loop’s iteration count represented by the variable $i.

If it does match, one is added to the $count variable that is keeping track of how many times we see the same character is a row. If it doesn’t match (or we’ve reached the end of the input), the $count and $current number are concatenated to the $return element. At that point, the $count is reset to 1, and the $current value is updated.

Writing an algorithm to generate the look-and-say (also known as, count-and-say) sequence is a common coding puzzle. You might run into it during a job interview as a software engineer. As practice, see if you can simplify my example code, or even write it in a different programming language than PHP.

October 8, 2021January 11, 2025

CSS for Weighted Hyperlink Decoration

How to add an underline to website text should be covered in any intro to web development course. The old-fashioned HTML way uses a now-deprecated tag:

<u>This will appear underlined!</u>

Modern approaches use CSS to define such a style:

<p style="text-decoration: underline;">This will appear underlined!</p>

Even better, properly written code will separate the inline styles, like so:

<style>
.underlined-text{
  text-decoration: underline;
}
</style>
<p class="underlined-text">This will appear underlined!</p>

For hyperlink text, I might want to hide the underline when a user mouses over it. That’s easy with the “hover” pseudo-class:

<style>
a.hyperlink-text{
  text-decoration: underline;
}
a.hyperlink-text:hover{
  text-decoration: none;
}
</style>
<a class="hyperlink-text">This will appear underlined!</a>

But, suppose I want to have that underline to become thicker instead of disappearing. That will require an advanced, super-secret CSS technique. To make it work, we will utilize box-shadow.

In the world of cascading style sheets, the box-shadow property adds a shadow effect around any HTML element. The shadow is described by its offsets, relative to the element. Leveraging this, we can create a shadow that looks like an underline. On hover, we can adjust the settings to change its appearance:

<style>
a.hyperlink-text{
  text-decoration: none;
  box-shadow: inset 0 -1px 0 rgb(15 15 15);
}
a.hyperlink-text:hover{
  -webkit-box-shadow: inset 0 0 0 rgb(0 0 0 / 0%), 0 3px 0 rgb(0 0 0);
  box-shadow: inset 0 0 0 rgb(0 0 0 / 0%), 0 3px 0 rgb(0 0 0);
}
</style>
<a class="hyperlink-text">This will appear underlined!</a>

Point your cursor over any of the hyperlinks in this post to see what it looks like. Experiment with the above code to make it your own.

September 14, 2021January 11, 2025

7 tips for writing as a practice

Writing is the best way to develop new ideas. It exercises our ability to explain what we mean, what we want, and why. Clear writing is clear thinking.

Putting words down is good for our mind, and is calisthenics for the brain. Forcing ourselves to build coherent, connecting streams of thought flexes mental muscles that we usually ignore. Expression and mutation are two sides of the same coin.

It’s easy to state your opinions at a high level. But once you dig into it, you might realize you haven’t thought it through. At least, not recently. Writing adds flesh to otherwise gaunt stances. It turns shape-shifting clouds into crystals with obvious foundations.

Make it a habit. Turn it into a daily practice. Here are ten tips to develop a writing routine:

Spend time writing fiction. Short stories, character profiles, fantasy, and fan-fiction each count. Glue yourself to the keyboard, until you can say “That’s my story, and I’m sticking to it.”
Journal daily. Type out the things for which you have gratitude. Document your feelings. Spell out your hopes. Cast a silent incantation. Craft an ancient curse. The universe is listening. I wrote a blog post about journaling, you can read it here.
Don’t be a perfectionist. Put sentences down, even if you don’t like them. Keep them around, even if you hate them. But please remember, that doesn’t apply to people in your life.
Everything is a work-in-progress. Revisit your posts, essays and articles. Add that literary polish and be a wordsmith. This will help keep you from trying to be perfect.
Start a blog. Build in public. Let the world see how you think. Most people aren’t paying attention anyway. And if you can’t come up with something new, update existing pieces.
Editing counts. Delete unnecessary sentences. Remove unneeded words. Clarify things, find synonyms, and avoid using the word “really”.
1. When I edit, I never actually throw anything away. I borrow the “soft delete” methodology from software engineering. I flag writing as unusable, without actually getting rid of it. That way I can revisit it, use it for subsequent ideas, and remember where my mind was.
Hoard notes. Keep a physical notebook. Use a note-taking app. Bookmark stuff. Markup books while you’re reading them. Keep a stack of index cards near by for when inspiration strikes
Write more than you promised yourself you would, but only if you feel like it.

August 30, 2021January 11, 2025

The Great Resignation

The Great Resignation is an early 2020s buzz phrase. It describes a large portion of the workforce quitting. This Big Quit has been precipitated by numerous factors, but most obviously the CoVid-19 pandemic. Truthfully, the snowball started rolling prior to the worldwide shutdown. Online freelancing and digital nomadism have been picking up steam for nearly a decade. Ubiquitous Wi-Fi, inexpensive devices, and on-demand cloud computing paved the road of the Extraordinary Exodus. The ability to work remotely, I believe, is one of the strongest drivers of this cultural shift.

Employment as we know it is a raw deal. It exploits workers and business owners. Only since the advent of the industrial revolution has the modern work schedule and environment taken this shape.

Employees are tasked to toil in serfdom. The employer is expected to be a parent. Those costs, risks, and responsibilities weigh heavily on executive shoulders. And that gravity ultimately crushes workers as exploitation.

Employer sponsored medicine took hold after World War 2. To fight inflation, the 1942 Stabilization Act was passed. That law prevented employers’ from raising wages. As a work-around to compete for high-demand workers, companies began offering health benefits as a competitive incentive. Nearly a century later, healthcare and employment are intertwined in the bleakest of scenarios.

Now can be the epoch of freedom. It’s the original promise of all the technological advances we now enjoy. Greater prosperity, with fewer shackles to hold us down. That also means businesses can enjoy larger output with less overhead.

When writing this, I was careful not to refer to employers as bosses. Ultimately, they are the clients. Workers should be expected to “manage up”, as they control labor, and in turn, production output and capital income.

What about health insurance?

It’s an absurd burden that businesses are expected to provide employees with healthcare. With that type of expectation, employers feel justified squeezing out every last drop of labor juice – be it sweat or tears.

Even worse, as workers, it’s crazy that access to medicine is tied to employment. Public health should be a public service, paid for by taxes. And, I’m not suggesting that taxes should be raised, but instead reallocated from bureaucratic waste, corruption, and cruelty.

Healthcare is among numerous benefits subsidized and off-loaded to the private sector. It has a taste similar to the Western tipping scheme found in the restaurant service industry.

Freedom

Nine hours in an office each day doesn’t leave time for much else. It was a routine I faced for years, along with many other New York straphangers. A long daily commute was the little time I could find to read, meditate, or relax. My only exercise came from pushing deadlines, jumping through hoops, and squeezing in meetings.

An age of abundance is possible within our lifetime. Laborers are being replaced by technology. Remember, most jobs are bullshit anyway. Humanity can feed itself, and anything more is just pollution.

Freelance

A freelance side hustle is the first step in buying your freedom. Owning, and running, your own remote business comes pretty close to what I imagine as the Digital Dream. When working as a freelance web developer I follow a process to ensure success. I am able to pitch potential clients on improvements, audits, and maintenance agreements.