Checking Database for Active Billing Services

Question

I am putting together a small script that will query our billing database and report any customer accounts that have free services active (provided complimentary) but no longer have paid services on their accounts.

The idea is basically if they have Product ID a, Product ID b, or both of them active (we will say those are the free ones) but nothing else, I want to find those accounts. Should I just keep bools such as bProductA = false and bProductB = false and set them true if found, or could I possibly do this in a cleaner way?

<?php

    // Establish the MySQL Database Connections
    include_once("db.php");

    $clients = mysql_query('SELECT id FROM tblclients WHERE status = "Active"');
    while ($client = mysql_fetch_array($clients))
    {
        $client_id = $client["id"];

        $products = mysql_query('SELECT packageid FROM tblhosting WHERE userid = "' . $client_id . '" AND domainstatus = "Active" OR domainstatus = "Suspended"');
        while ($product = mysql_fetch_array($products))
        {
            // Check for MYSQL/DNS Products
            $product_id = $product["id"];
            $package_id = $product["packageid"];

            // MySQL Database
            if ($package_id == "999")
            {

            }

            // DNS Record
            if ($package_id == "999")
            {

            }
        }
    }

?>

Answer 1

If you're going to do much work with databases, you'll be better off learning to think in terms of sets. SQL databases are based on sets. Any time you're tempted to

• run a query, then
• loop through the results, then
• execute another query to get the data you're interested in, then

you're almost certainly on the wrong track.

What you should be thinking about is identifying

• the set of active clients who have active free services, and
• the set of active clients who have active paid services.

(I'm not willing to bet that every inactive client has no free or paid services.)

You're interested in the clients who are members of the first set, but are not members of the second. You can get that with a single query.

Edit your question, and paste the CREATE TABLE statements if you want more help writing that query.