In last week’s lesson, we looked at the variables and methods we would need to interact with the advertising database tables.
This week, we’ll examine the code for the data object class file and how the other parts of the site will use 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 next week’s lesson, we will examine the functions that get the list of clients, generate ad reports, and add, edit and delete records.
