In the previous lesson, we examined the basic functions of our banner ad tracking system, including the retrieval of records for both banner ads and advertisers.
In this lesson, we will look at the functions responsible for generating reports and manipulating the data in the ads table and advertiser table.
The getClientsList() function retrieves active client records and sorts them alphabetically by client name:
function getClientsList() {
// get clients from db
$sql = "SELECT
ad_client_id,
ad_client_name
FROM
devdrive_ads_clients
WHERE
deleted=0
and status=1
ORDER BY
ad_client_name";
if (DB::isError($rsTmp = $this->_oConn->query($sql))) {
catchExc($rsTmp->getMessage());
return false;
}
// loop through results and build return array
$i = 0;
while ($aRow = $rsTmp->fetchRow(DB_FETCHMODE_ASSOC)) {
$return[$i]["Client Id"] = $aRow["ad_client_id"];
$return[$i]["Client"] = $aRow["ad_client_name"];
++$i;
}
return $return;
}The getAdsReport() function retrieves the data to display an activity report for a particular banner ad campaign, including the banner ad data, client data and activity data.
function getAdsReport() {
// get report data
$sql = "SELECT
a.ad_title,
a.ad_url,
a.created_dt,
c.ad_client_name,
c.ad_client_contact,
c.ad_client_email,
c.ad_client_phone,
r.ad_view_cnt,
r.ad_click_cnt,
r.ad_activity_month,
r.ad_activity_year
FROM
devdrive_ads a,
devdrive_ads_clients c,
devdrive_ads_activity r
WHERE
a.ad_client_id=c.ad_client_id
AND r.ad_id=a.ad_id
AND a.deleted=0
ORDER BY
r.ad_activity_year desc,
r.ad_activity_month desc";
if (DB::isError($rsTmp = $this->_oConn->query($sql))) {
catchExc($rsTmp->getMessage());
return false;
}
// loop through result and build return array
$i = 0;
while ($aRow = $rsTmp->fetchRow(DB_FETCHMODE_ASSOC)) {
$return[$i]["Title"] = $aRow["ad_title"];
$return[$i]["URL"] = $aRow["ad_url"];
$return[$i]["Client"]["Name"] = $aRow["ad_client_name"];
$return[$i]["Client"]["Contact"] = $aRow["ad_client_contact"];
$return[$i]["Client"]["Email"] = $aRow["ad_client_email"];
$return[$i]["Client"]["Phone"] = $aRow["ad_client_phone"];
$return[$i]["View Count"] = $aRow["ad_view_cnt"];
$return[$i]["Click Count"] = $aRow["ad_click_cnt"];
$return[$i]["Month"] = $aRow["ad_activity_month"];
$return[$i]["Year"] = $aRow["ad_activity_year"];
$return[$i]["Created Date"] = strtotime($aRow["created_dt"]);
++$i;
}
return $return;
}The addAd($aArgs) method inserts a new record into the devdrive_ads table. The arguments for this function include the Client ID, the URL for the banner ad, the Title of the banner ad campaign, and the path that the browser will follow when the viewer clicks on the ad.
function addAd($aArgs) {
// if no client id was passed
if (empty($aArgs["Client Id"])) {
$aArgs["Client Id"] = $this->addClient($aArgs);
}
// insert new advertisement record
$sql = "INSERT INTO devdrive_ads (
ad_client_id,
ad_url,
ad_title,
ad_path,
status,
created_dt,
modified_dt
) values (
".$aArgs["Client Id"].",
'".$aArgs["URL"]."',
'".$aArgs["Title"]."',
'".$aArgs["Path"]."',
1,
(NOW()),
(NOW())
)";
if (DB::isError($rsTmp = $this->_oConn->query($sql))) {
catchExc($rsTmp->getMessage());
return false;
}
return true;
}The addClient($aArgs) function adds a new record to the devdrive_ads_clients table. The arguments include the client’s business name, contact name, telephone number and e-mail address. If the function returns a true value, it generates the value of $iClientID from the primary key for the new record.
function addClient($aArgs) {
// lock tables to capture unique identifier
$sql = "LOCK TABLES devdrive_ads_clients WRITE";
if (DB::isError($rsTmp = $this->_oConn->query($sql))) {
catchExc($rsTmp->getMessage());
return false;
}
// insert new client record
$sql = "INSERT INTO devdrive_ads_clients (
ad_client_name,
ad_client_contact,
ad_client_email,
ad_client_phone
) values (
'".$aArgs["Client"]["Name"]."',
'".$aArgs["Client"]["Contact"]."',
'".$aArgs["Client"]["Email"]."',
'".$aArgs["Client"]["Phone"]."'
)";
if (DB::isError($rsTmp = $this->_oConn->query($sql))) {
catchExc($rsTmp->getMessage());
return false;
}
// get unique client id
$sql = "SELECT MAX(ad_client_id) FROM devdrive_ads_clients";
if (DB::isError($iClientId = $this->_oConn->getOne($sql))) {
catchExc($iClientId->getMessage());
return false;
}
// unlock tables
$sql = "UNLOCK TABLES";
if (DB::isError($rsTmp = $this->_oConn->query($sql))) {
catchExc($rsTmp->getMessage());
return false;
}
return $iClientId;
}The editAd($aArgs) function allows the administrator to update the record in the devdrive_ads table. The arguments for this function include the Client ID, the URL for the banner ad, the Title of the banner ad campaign, and the path that the browser will follow when the viewer clicks on the ad.
function editAd($aArgs) {
// initialize sql filter
$sFilter = "";
// if a file was uploaded, ad path value to sql filter
if (!empty($aArgs["Path"])) $sFilter = "ad_path='".$aArgs["Path"]."', ";
// update advertisement record
$sql = "UPDATE devdrive_ads SET
ad_client_id=".$aArgs["Client Id"].",
ad_url='".$aArgs["URL"]."',
ad_title='".$aArgs["Title"]."',
".$sFilter."
modified_dt=(NOW())
WHERE
ad_id=".$this->_id;
if (DB::isError($rsTmp = $this->_oConn->query($sql))) {
catchExc($rsTmp->getMessage());
return false;
}
return true;
}The editClient($aArgs) allows the user to update a record in the devdrive_ads_clients table. The arguments include the client’s business name, contact name, telephone number and e-mail address.
function editClient($aArgs) {
// update client record
$sql = "UPDATE devdrive_ads_clients SET
ad_client_name='".$aArgs["Client"]["Name"]."',
ad_client_contact='".$aArgs["Client"]["Contact"]."',
ad_client_email='".$aArgs["Client"]["Email"]."',
ad_client_phone='".$aArgs["Client"]["Phone"]."',
modified_dt=(NOW())
WHERE
ad_client_id=".$this->_id;
if (DB::isError($rsTmp = $this->_oConn->query($sql))) {
catchExc($rsTmp->getMessage());
return false;
}
return true;
}In the next lesson, we will look at retrieving ads and managing an advertising campaign.
