In our last PHP Ad Tracker lesson, we constructed the database tables for our ad banner application.
Now we are ready to construct the data object that will hold the variables and functions that will display, add, edit and delete the data in those tables.
Once we name all of the variables and functions, we will start applying the code to them.
Variables:
The data object class will hold two variables: one to hold the banner ad ID number from the ads table, and one to hold the database connection information.
Variable: $_id
Value: Primary key ID number for ads table
Variable: $_oConn
Value: Object data for MySQL database connection.
Functions
Before we write the code for these methods, we need to decide exactly what task each method will accomplish.
Function: ads($id=’’)
Task: Open the connection to the MySQL database. If the $_id variable has an integer value, the ads function will call the setId function.
Function: setId($id)
Task: Set the class variable $_id to the value of $id (assuming $id is an integer).
Function: getAdsCount()
Task: Count the total number of banner ad records in ads table
Function: getClientsCount()
Task: Count the total number of banner ad clients in clients table
Function: getAds($sSort, $iPage=0)
Tasks: Return ad data from ads table. Sort the data according to the value in the $sSort variable and retrieve up to $iPage records.
Function: getClients($sSort, $iPage=0)
Tasks: Return client data from clients table. Sort the data according to the value in the $sSort variable and retrieve up to $iPage records.
Function: getAd()
Task: Get ad and client data from their respective tables where the primary key in the ads table equals $_id.
Function: getClient()
Task: Get client data from client table where the primary key is a specified value.
Function: getClientsList()
Task: Return names of active clients
Function: getAdsReport()
Task: Return data from ads, clients and activity tables.
Function: addAd($aArgs)
Task: Add a new record to the ads table. The $aArgs variable is an array that contains the data to be inserted into the ads table.
Function: addClient($aArgs)
Task: Add a new record to the clients table. The $aArgs variable is an array that contains the data to be inserted into the clients table.
Function: editAd($aArgs)
Task: Update a record in the ads table. The $aArgs variable is an array that contains the data to updated in the ads table record.
Function: editClient($aArgs)
Task: Edit a record to the clients table. The $aArgs variable is an array that contains the data to be updated in the clients table record.
Function: getRandomAd()
Tasks: Return data on a random record in the ads table. Update the activity table.
Function: redirectAd()
Tasks: Redirects browser to URL specified in ads table for this record. If this is the first time the ad has been clicked, insert a new record in the activity table. If not, update the activity table record for this ad.
Function: deleteAd()
Tasks: Set the value of the “status” column to “false”, set the “deleted” column to “true” and set the value of the date deleted to the current date for the ad record.
Function: deleteClient()
Tasks: Set the value of the “status” column to “false”, set the “deleted” column to “true” and set the value of the date deleted to the current date for the client record.
Function: activateAd()
Task: Set the value of the “status” column to “true” for the ad record.
Function: activateClient()
Task: Set the value of the “status” column to “true” for the client record.
Function: deactivateAd()
Task: Set the value of the “status” column to “false” for the ad record.
Function: deactivateClient()
Task: Set the value of the “status” column to “false” for the client record.
Now that we have defined the functions, we will examine the coding for these methods.
First, we include the database connection string information. This information includes the database server name, cursor location and other pertinent information. Since this information is used throughout the site, it is usually stored in a separate file. In this instance, we have included it in a “require_once” method.
<?php
require_once("dev_drive_dbconn.php"); //database connection string infoNow we will create the ads class and instantiate the variables:
class ads {
var $_id;
var $_oConn;Now we will create the ads function. This function calls the $_oConn variable and connects the functions to the database tables.
function ads($id = '') {
// implement pear db object
$this->_oConn =& DB::connect(DSN);
if (DB::isError($this->_oConn) ) {
catchExc($this->_oConn->getMessage());
}
// set ad id
if (is_int($id)) {
$this->setId($id);
}
}Once we have established the database connection, we can set the value of the $_id variable with the setId($id) function.
function setId($id) {
if (is_int($id)) {
$this->_id = $id;
}
}Next, we will build the getAdsCount function. This function will return the number of ads in the database table.
function getAdsCount() {
$sql = "SELECT
count(ad_id) AS cnt
FROM
dev_drive_ads
WHERE
deleted=0";
if (DB::isError($iCnt = $this->_oConn->getOne($sql))) {
catchExc($iCnt->getMessage());
return false;
}
return $iCnt;
}The getClientsCount function returns the number of clients.
function getClientsCount() {
$sql = "SELECT
count(ad_client_id) AS cnt
FROM
devdrive_ads_clients
WHERE
deleted=0";
if (DB::isError($iCnt = $this->_oConn->getOne($sql))) {
catchExc($iCnt->getMessage());
return false;
}
return $iCnt;
}The getAds function pulls the data from the ads table, sorts it according to the $sSort variable, and sets the database cursor with the $iPage variable.
function getAds($sSort, $iPage=0) {
$sql = "SELECT
ad_id,
ad_title,
status,
created_dt,
modified_dt
FROM
devdrive_ads
WHERE
deleted=0
ORDER BY
".$sSort."
LIMIT ".$iPage.", ".ROWCOUNT;
if (DB::isError($rsTmp = $this->_oConn->query($sql))) {
catchExc($rsTmp->getMessage());
return false;
}
// loop through result and return data collection
$i = 0;
while ($aRow = $rsTmp->fetchRow(DB_FETCHMODE_ASSOC)) {
$return[$i]["Ad Id"] = $aRow["ad_id"];
$return[$i]["Title"] = $aRow["ad_title"];
$return[$i]["Status"] = $aRow["status"];
$return[$i]["Created Date"] = strtotime($aRow["created_dt"]);
$return[$i]["Modified Date"] = strtotime($aRow["modified_dt"]);
++$i;
}
return $return;
}The getClients function pulls the data from the clients table, sorts it according to the $sSort variable, and sets the database cursor with the $iPage variable.
function getClients($sSort, $iPage=0) {
$sql = "SELECT
ad_client_id,
ad_client_name,
status,
created_dt,
modified_dt
FROM
devdrive_ads_clients
WHERE
deleted=0
ORDER BY
".$sSort."
LIMIT ".$iPage.", ".ROWCOUNT;
if (DB::isError($rsTmp = $this->_oConn->query($sql))) {
catchExc($rsTmp->getMessage());
return false;
}
// loop through result and return data collection
$i = 0;
while ($aRow = $rsTmp->fetchRow(DB_FETCHMODE_ASSOC)) {
$return[$i]["Client Id"] = $aRow["ad_client_id"];
$return[$i]["Client"] = $aRow["ad_client_name"];
$return[$i]["Status"] = $aRow["status"];
$return[$i]["Created Date"] = strtotime($aRow["created_dt"]);
$return[$i]["Modified Date"] = strtotime($aRow["modified_dt"]);
++$i;
}
return $return;
}The getAd function pulls the data on a single ad by its primary key ($_id variable).
function getAd() {
// get advertisement record
$sql = "SELECT
a.ad_id,
a.ad_client_id,
a.ad_url,
a.ad_title,
a.ad_path,
c.ad_client_name,
c.ad_client_contact,
c.ad_client_email,
c.ad_client_phone,
a.status,
a.deleted,
a.deleted_dt,
a.created_dt,
a.modified_dt
FROM
devdrive_ads a,
devdrive_ads_clients c
WHERE
c.ad_client_id=a.ad_client_id
AND a.ad_id=".$this->_id;
if (DB::isError($rsTmp = $this->_oConn->query($sql))) {
catchExc($rsTmp->getMessage());
return false;
}
// capture results row in an array
$aRow = $rsTmp->fetchRow(DB_FETCHMODE_ASSOC);
// build return array
$return["Ad Id"] = $aRow["ad_id"];
$return["Client Id"] = $aRow["ad_client_id"];
$return["URL"] = $aRow["ad_url"];
$return["Title"] = $aRow["ad_title"];
$return["Path"] = $aRow["ad_path"];
$return["Client"]["Name"] = $aRow["ad_client_name"];
$return["Client"]["Contact"] = $aRow["ad_client_contact"];
$return["Client"]["Email"] = $aRow["ad_client_email"];
$return["Client"]["Phone"] = $aRow["ad_client_phone"];
$return["Status"] = $aRow["status"];
$return["Deleted"] = $aRow["deleted"];
$return["Deleted Date"] = strtotime($aRow["deleted_dt"]);
$return["Created Date"] = strtotime($aRow["created_dt"]);
$return["Modified Date"] = strtotime($aRow["modified_dt"]);
return $return;
}The getClient function pulls the data from the clients table on a single client by its primary key.
function getClient() {
// get advertisement record
$sql = "SELECT
ad_client_name,
ad_client_contact,
ad_client_email,
ad_client_phone,
status,
deleted,
deleted_dt,
created_dt,
modified_dt
FROM
devdrive_ads_clients
WHERE
ad_client_id=".$this->_id;
if (DB::isError($rsTmp = $this->_oConn->query($sql))) {
catchExc($rsTmp->getMessage());
return false;
}
// capture results row in an array
$aRow = $rsTmp->fetchRow(DB_FETCHMODE_ASSOC);
// build return array
$return["Name"] = $aRow["ad_client_name"];
$return["Contact"] = $aRow["ad_client_contact"];
$return["Email"] = $aRow["ad_client_email"];
$return["Phone"] = $aRow["ad_client_phone"];
$return["Status"] = $aRow["status"];
$return["Deleted"] = $aRow["deleted"];
$return["Deleted Date"] = strtotime($aRow["deleted_dt"]);
$return["Created Date"] = strtotime($aRow["created_dt"]);
$return["Modified Date"] = strtotime($aRow["modified_dt"]);
return $return;
}In the next lesson, we will examine the functions that get the list of clients, generate ad reports, and add, edit and delete records.
