In our last session, we looked at the process of entering information on ad banners and ad clients, as well as generating ad activity reports. This week, we examine how to retrieve a random banner ad. We will also learn how to delete, activate and deactivate ads and clients.

The getRandomAd() function retrieves a random ad by using the PHP rand() function. The function returns the ad’s primary key ID, client ID, title, URL and redirect path.

The function also updates the activity table for this banner ad. If no entry in the activity table exists for this ad (i.e. this is the first time this ad has appeared), the function inserts a new record for this ad into the activity table.

function getRandomAd() {

        $sql = "SELECT
                    ad_id,
                    ad_client_id,
                    ad_title,
                    ad_url,
                    ad_path
                FROM
                    devdrive_ads
                WHERE
                    status=1
                    AND deleted=0
                ORDER BY
                    rand()
                LIMIT 0, 1";

        if (DB::isError($rsTmp = $this->_oConn->query($sql))) {

            catchExc($rsTmp->getMessage());
            return false;
        }

        // assign result to array
        $aRow = $rsTmp->fetchRow(DB_FETCHMODE_ASSOC);

        // assign return array values
        $return["Ad Id"] = $aRow["ad_id"];
        $return["Client Id"] = $aRow["ad_client_id"];
        $return["Title"] = $aRow["ad_title"];
        $return["URL"] = $aRow["ad_url"];
        $return["Path"] = $aRow["ad_path"];

        // update advertisement activity
        $sql = "UPDATE devdrive_ads_activity SET
                    ad_view_cnt=ad_view_cnt+1,
                    ad_activity_month=".date("m").",
                    ad_activity_year=".date("Y")."
                WHERE
                    ad_id=".$aRow["ad_id"]."
                    AND ad_activity_month=".date("m")."
                    AND ad_activity_year=".date("Y");

        if (DB::isError($rsTmp = $this->_oConn->query($sql))) {

            catchExc($rsTmp->getMessage());
            return false;
        }

        // check if update affected any rows
        if ($this->_oConn->affectedRows() < 1) {

            // add new record for auditing
            $sql = "INSERT INTO devdrive_ads_activity (
                        ad_id,
                        ad_view_cnt,
                        ad_activity_month,
                        ad_activity_year
                    ) values (
                        ".$aRow["ad_id"].",
                        1,
                        ".date("m").",
                        ".date("Y")."
                    )";

            if (DB::isError($rsTmp = $this->_oConn->query($sql))) {

                catchExc($rsTmp->getMessage());
                return false;
            }
        }

        return $return;
    }

The redirectAd() function updates the activity table if the user clicks the banner ad. If no entry in the activity table exists for this ad (i.e. this is the first time this ad has appeared), the function inserts a new record for this ad into the activity table.

function redirectAd() {

        // update activity logs
        $sql = "UPDATE devdrive_ads_activity SET
                    ad_click_cnt=ad_click_cnt+1,
                    ad_activity_month=".date("m").",
                    ad_activity_year=".date("Y")."
                WHERE
                    ad_id=".$this->_id."
                    AND ad_activity_month=".date("m")."
                    AND ad_activity_year=".date("Y");

        if (DB::isError($rsTmp = $this->_oConn->query($sql))) {

            catchExc($rsTmp->getMessage());
            return false;
        }

        // check if any rows were updated
        if ($this->_oConn->affectedRows() < 1) {

            // add new activity record
            $sql = "INSERT INTO devdrive_ads_activity (
                        ad_id,
                        ad_click_cnt,
                        ad_activity_month,
                        ad_activity_year
                    ) values (
                        ".$iAdId.",
                        1,
                        ".date("m").",
                        ".date("Y")."
                    )";

            if (DB::isError($rsTmp = $this->_oConn->query($sql))) {

                catchExc($rsTmp->getMessage());
                return false;
            }
        }

        return true;
    }

The deleteAd() function sets the status of a record in the ads table to “deleted” and calls the deactivateAd() function.

    function deleteAd() {

        $sql = "UPDATE devdrive_ads SET
                    deleted=1,
                    deleted_dt=(NOW())
                WHERE
                    ad_id=".$this->_id;

        if (DB::isError($rsTmp = $this->_oConn->query($sql))) {

            catchExc($rsTmp->getMessage());
            return false;
        }

        $this->deactivateAd();
        return true;
    }

The deleteClient() function sets the status of a record in the clients table to “deleted”. The function also  sets all of that client’s ads to “deleted”.

function deleteClient() {

        $sql = "UPDATE devdrive_ads_clients SET
                    status=0,
                    deleted=1,
                    deleted_dt=(NOW())
                WHERE
                    ad_client_id=".$this->_id;

        if (DB::isError($rsTmp = $this->_oConn->query($sql))) {

            catchExc($rsTmp->getMessage());
            return false;
        }

        $sql = "UPDATE devdrive_ads SET
                    status=0,
                    deleted=1,
                    deleted_dt=(NOW() )
                WHERE
                    ad_client_id=".$this->_id;

        if (DB::isError($rsTmp = $this->_oConn->query($sql))) {

            catchExc($rsTmp->getMessage());
            return false;
        }
    }

The activateAd() function sets the status of the banner ad record to “active” in the ads table.

    function activateAd() {

        $sql = "UPDATE devdrive_ads SET
                    status=1
                WHERE
                    ad_id=".$this->_id;

        if (DB::isError($rsTmp = $this->_oConn->query($sql))) {

            catchExc($rsTmp->getMessage());
            return false;
        }
    }

The activateClient() function sets the status of the client record to “active” in the clients table.

    function activateClient() {

        $sql = "UPDATE devdrive_ads_clients SET
                    status=1
                WHERE
                    ad_client_id=".$this->_id;

        if (DB::isError($rsTmp = $this->_oConn->query($sql))) {

            catchExc($rsTmp->getMessage());
            return false;
        }
    }

The deactivateAd() function sets the status of the banner ad record to “inactive” in the ads table, but does not delete the record.

    function deactivateAd() {

        $sql = "UPDATE devdrive_ads SET
                    status=0
                WHERE
                    ad_id=".$this->_id;

        if (DB::isError($rsTmp = $this->_oConn->query($sql))) {

            catchExc($rsTmp->getMessage());
            return false;
        }
    }

The deactivateClient() function sets the status of the client record to “inactive” in the client table, but does not delete the record.

    function deactivateClient() {

        $sql = "UPDATE devdrive_ads_clients SET
                    status=0
                WHERE
                    ad_client_id=".$this->_id;

        if (DB::isError($rsTmp = $this->_oConn->query($sql))) {

            catchExc($rsTmp->getMessage());
            return false;
        }
    }

} // close the class definition

?>

Now that we have finished defining the functions in the ads class, we can start building the interface. In the next lesson, we will examine the forms that allow the site administrator to manage banner ad campaigns.

By Gerald Hanks
Gerald Hanks has been involved in web development applications since 1996. He has designed applications with JavaScript, ASP.NET and PHP, as well as building databases in MS SQL Server and MySQL. He lives in Houston, Texas.
  • Please consider avoiding the use of ‘ORDER BY rand()’ in queries. It is far more effective to generate a random integer in PHP and use that as a ‘ORDER BY ‘. $randomnumber in your query.

    With the rand() option, you explicitly deny the query cache in your database backend, which can end up as a killer in high-traffic environments (usually where you end with ads).

Home XML WordPress Web Services Web Development Web Design Underscore Uncategorized Tutorial Tools SQL Server Social Apps Snippet SEO Security RoR Responsive Design Resources Python PHP News MySQL Mobile Miscellaneous jQuery JavaScript Java J2EE HTML5 HTML Design Patterns