The sales counter

Now the badge says Hot Deal · 14 sold this week.

This episode has the two things that separate a toy plugin from a real one: a database query, and a cache.

Step 1. Add the counter method

Open includes/class-orb-badge.php. Add this method inside the class:

	private function get_recent_sales_count( $product_id ) {

		$cache_key = 'orb_sales_' . $product_id;
		$count     = get_transient( $cache_key );

		if ( false === $count ) {
			global $wpdb;

			$count = (int) $wpdb->get_var(
				$wpdb->prepare(
					"SELECT COALESCE( SUM( pl.product_qty ), 0 )
					FROM {$wpdb->prefix}wc_order_product_lookup AS pl
					INNER JOIN {$wpdb->prefix}wc_order_stats AS os
						ON os.order_id = pl.order_id
					WHERE pl.product_id = %d
					AND os.status = 'wc-completed'
					AND pl.date_created >= %s",
					$product_id,
					gmdate( 'Y-m-d H:i:s', time() - WEEK_IN_SECONDS )
				)
			);

			set_transient( $cache_key, $count, 6 * HOUR_IN_SECONDS );
		}

		return (int) $count;
	}

Do not read it all at once. We take it in three parts.

Part A. The cache

	$count = get_transient( $cache_key );

	if ( false === $count ) {
		...
		set_transient( $cache_key, $count, 6 * HOUR_IN_SECONDS );
	}

A transient is a locker with a timer. You put a value in, you say how long it lives, and WordPress throws it away after that.

get_transient returns false when the locker is empty or the time is up. That is the signal to do the work again.

Why bother? Your shop page shows 12 products. Without a cache, that is 12 database queries on every single page load, for every visitor. With the cache, it is 12 queries one time, then none for six hours.

Note the key: 'orb_sales_' . $product_id. One locker per product. A shared key would show the same number on every product.

Part B. The query

$wpdb is the database. It is a global, so we pull it in with global $wpdb.

$wpdb->get_var() runs the query and gives back one single value. There are others. get_row for one row, get_results for many.

{$wpdb->prefix} is the table prefix of that site. Most sites use wp_. Many hosts use something random for safety. Write wp_ by hand and your plugin breaks on those sites.

The two tables belong to WooCommerce:

• wc_order_product_lookup holds one row per product per order, with the quantity.
• wc_order_stats holds one row per order, with the status.

We join them, because we only want products from orders that are completed. A cancelled order is not a sale.

Part C. prepare, the important part

	$wpdb->prepare( "... WHERE pl.product_id = %d ...", $product_id )

%d means “a number goes here”. %s means “a string goes here”. prepare puts your values in, and it cleans them on the way.

Never glue a value into a query with a dot. Never. Even a value that “can only be a number”.

Here is the reason, in one picture. Your query is an instruction to the database. A value glued into the text stops being data and becomes part of the instruction. A person who controls that value now writes your instruction with you.

prepare keeps the instruction and the data apart. Always use it, even when you are sure the value is safe. You will not be sure in six months.

Step 2. Print the number

In render_badge, replace the echo line with this:

		$sales = $this->get_recent_sales_count( $product->get_id() );

		echo '<span class="orb-badge">' . esc_html( $text );

		if ( $sales > 0 ) {
			echo ' &middot; ' . esc_html( $sales ) . ' sold this week';
		}

		echo '</span>';

$this->get_recent_sales_count(...) calls the method on this same object. You saw $this in Episode 2. Same word, now used to call instead of to hook.

private on the method means only this class can call it. It is not part of the outside world. Mark everything private unless another file truly needs it.

If nothing sold, we print nothing extra. Hot Deal · 0 sold this week would hurt sales, not help.

Step 3. Test

Reload the shop. Most likely you see only Hot Deal, because your test store has no completed orders this week.

Make one:

1. Go to WooCommerce > Orders and add an order.
2. Add the product you put on sale.
3. Set the status to Completed and save.

Reload the shop. Still only Hot Deal? That is correct, and it is a lesson.

Those two tables are not the order tables. They are report tables, and WooCommerce fills them in the background, a moment later. Until the background job runs, your number stays at zero.

Give it a minute and reload. Now the badge says Hot Deal · 1 sold this week.

Break it on purpose

Break 1. See the cache work.

Change the lifetime to 20 seconds:

			set_transient( $cache_key, $count, 20 );

Reload the shop. Change the order quantity in the admin. Reload again straight away. The number does not move. Wait 20 seconds, reload. Now it moves.

Nothing is broken. That is a cache, doing exactly its job.

Put 6 * HOUR_IN_SECONDS back.

Break 2. The ticket this creates.

A shop owner writes: “The counter says 3, but I sold 9 today.”

Your plugin is not broken. The number is up to six hours old, by design. Your job in support is to explain the trade, and to know the fix:

delete_transient( 'orb_sales_123' );

That empties one locker. The next page load does the query again.

A better plugin clears that locker when an order completes. That is one more hook, and it is the correct answer if a customer asks for live numbers.

One warning about false

	if ( false === $count ) {

Three equals signs, not two. This is not style.

A real count of 0 is not the same as an empty locker. With two equals signs, PHP treats 0 and false as the same thing. Then a product with zero sales would run the query on every single page load, forever, and your cache would do nothing.

Three equals signs check the value and the type. Use them in checks like this, always.

Next episode: we finish the plugin. Filters for other developers, translation, and the readme.