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.
