Script to update subscription vat rates based on last invoice

invoices.php

<?php
// Get everyone's "last subscription invoice" for people who've paid in the last year
$sql = "SELECT i1.vatrate, i1.username
FROM invoices i1
LEFT JOIN invoices i2 ON (i1.username = i2.username AND i1.id < i2.id)
WHERE i2.id IS NULL
AND i1.datestamp > '2014-04-01'
AND i1.type IN ('sub','subpayment','upgrade','resubscription')
ORDER BY i1.vatrate DESC";

$r = xdb::query($sql);

// Loop through each user
while ( $invoice = xdb::fetch_array($r)) {

        // Update the user's subscription vatrate 
	$update = "UPDATE subscriptions
	JOIN sites on subscriptions.siteid = sites.id
	JOIN users ON users.ID = sites.userid
	SET vatrate = '" . $invoice['vatrate'] . "'
	WHERE users.username = '" . xdb::escape($invoice['username']) . "'";

	$doit = xdb::query($update);

	// Debug output
	echo $invoice['username'] . ".. ";
}

Why do you need the join in the select?

@AidanThreadgold Because It's getting the 'last' invoice for each user, and I used this method over a sub query.