/**
* @brief 设置数据库读写分离并且执行SQL语句
* @param string $sql 要执行的SQL语句
* @return int or bool SQL语句执行的结果
*/
public function query($sql)
{
//取得SQL类型
self::$sqlType = $this->getSqlType($sql);
//读方式
if (self::$sqlType == 'select' || self::$sqlType == 'show') {
if (self::$rTarget == NULL || !is_resource(self::$rTarget)) {
//多数据库支持并且读写分离
if (isset(IWeb::$app->config['DB']['read'])) {
//获取ip地址
$ip = IClient::getIP();
$this->connect(IHash::hash(IWeb::$app->config['DB']['read'], $ip));
} else {
$this->connect(IWeb::$app->config['DB']);
}
self::$rTarget = $this->linkRes;
}
} else {
if (self::$wTarget == NULL || !is_resource(self::$wTarget)) {
//多数据库支持并且读写分离
if (isset(IWeb::$app->config['DB']['write'])) {
$this->connect(IWeb::$app->config['DB']['write']);
} else {
$this->connect(IWeb::$app->config['DB']);
}
self::$wTarget = $this->linkRes;
}
}
if (is_resource($this->linkRes)) {
return $this->doSql($sql);
} else {
return false;
}
}
/**
* @brief 设置数据库读写分离并且执行SQL语句
* @param string $sql 要执行的SQL语句
* @return int or bool SQL语句执行的结果
*/
public function query($sql)
{
//取得SQL类型
self::$sqlType = $this->getSqlType($sql);
//读方式
if (self::$sqlType == 'select' || self::$sqlType == 'show') {
if (self::$rTarget == NULL) {
//多数据库支持并且读写分离
if (isset(IWeb::$app->config['DB']['read'])) {
//获取ip地址
$ip = IClient::getIP();
self::$rTarget = $this->connect(IHash::hash(IWeb::$app->config['DB']['read'], $ip));
} else {
self::$rTarget = $this->connect(IWeb::$app->config['DB']);
}
}
$this->switchLink("r");
$result = $this->doSql($sql);
if ($result === false) {
throw new IException("{$sql}\n -- " . $this->linkRes->error, 1000);
return false;
}
return $result;
} else {
if (self::$wTarget == NULL) {
//多数据库支持并且读写分离
if (isset(IWeb::$app->config['DB']['write'])) {
self::$wTarget = $this->connect(IWeb::$app->config['DB']['write']);
} else {
self::$wTarget = $this->connect(IWeb::$app->config['DB']);
}
//写链接启用事务
$this->switchLink("w");
$this->autoCommit();
}
$this->switchLink("w");
$result = $this->doSql($sql);
if ($result === false) {
$errorMsg = $this->linkRes->error;
$this->rollback();
throw new IException("{$sql}\n -- " . $errorMsg, 1000);
return false;
}
return $result;
}
}
/**
* Constructor for survey class
*
* Loads data from survey table
*
* @param in $id number of current survey
* @return void
* @todo none
*/
public function __construct($id)
{
$id = (int) $id;
#cast to integer
$sql = "select Title, Description from sp15_surveys where SurveyID={$id}";
$result = mysqli_query(IDB::conn(), $sql) or die(trigger_error(mysqli_error(IDB::conn()), E_USER_ERROR));
if (mysqli_num_rows($result) > 0) {
#there are records - present data
while ($row = mysqli_fetch_assoc($result)) {
# pull data from associative array
$this->Title = dbOut($row['Title']);
$this->Description = dbOut($row['Description']);
$this->SurveyID = $id;
$this->isValid = true;
}
# Endwhile
}
# Endif
@mysqli_free_result($result);
}
/**
* Constructor for Response class.
*
* @param integer $id ID number of Response
* @return void
* @todo none
*/
function __construct($id)
{
$this->ResponseID = (int) $id;
if ($this->ResponseID == 0) {
return FALSE;
}
# invalid response id - abort
$iConn = \IDB::conn();
# uses a singleton DB class to create a mysqli improved connection
$sql = sprintf("select SurveyID, DateAdded from " . PREFIX . "responses where ResponseID =%d", $this->ResponseID);
$result = mysqli_query($iConn, $sql) or die(trigger_error(mysqli_error($iConn), E_USER_ERROR));
if (mysqli_num_rows($result) > 0) {
# returned a response!
while ($row = mysqli_fetch_array($result)) {
# load singular response object properties
$this->SurveyID = (int) $row['SurveyID'];
$this->DateTaken = dbOut($row['DateAdded']);
}
} else {
return FALSE;
#no responses - abort
}
mysqli_free_result($result);
parent::__construct($this->SurveyID);
# access parent class to build Question & Answers
# attempt to load choice array of Answer objects
if ($this->TotalQuestions > 0) {
# Questions must exist for this survey, if we are to proceed
$sql = sprintf("select AnswerID, QuestionID, RQID from " . PREFIX . "responses_answers where ResponseID=%d order by QuestionID asc", $this->ResponseID);
$result = mysqli_query($iConn, $sql) or die(trigger_error(mysqli_error($iConn), E_USER_ERROR));
if (mysqli_num_rows($result) > 0) {
# must be choices
while ($row = mysqli_fetch_array($result)) {
# load data into array of choices
$this->aChoice[] = new Choice((int) $row['AnswerID'], (int) $row['QuestionID'], (int) $row['RQID']);
}
@mysqli_free_result($result);
}
}
}
function showCustomers()
{
//Select Customer
global $config;
get_header();
echo '<h3 align="center">' . smartTitle() . '</h3>';
$sql = "select CustomerID,FirstName,LastName,Email from test_Customers";
$result = mysqli_query(IDB::conn(), $sql) or die(trigger_error(mysqli_error(IDB::conn()), E_USER_ERROR));
if (mysqli_num_rows($result) > 0) {
//show results
echo '<table align="center" border="1" style="border-collapse:collapse" cellpadding="3" cellspacing="3">';
echo '<tr>
<th>CustomerID</th>
<th>First Name</th>
<th>Last Name</th>
<th>Email</th>
</tr>
';
while ($row = mysqli_fetch_assoc($result)) {
//dbOut() function is a 'wrapper' designed to strip slashes, etc. of data leaving db
echo '<tr>
<td>' . (int) $row['CustomerID'] . '</td>
<td>' . dbOut($row['FirstName']) . '</td>
<td>' . dbOut($row['LastName']) . '</td>
<td>' . dbOut($row['Email']) . '</td>
</tr>
';
}
echo '</table>';
} else {
//no records
echo '<div align="center"><h3>Currently No Customers in Database.</h3></div>';
}
echo '<div align="center"><a href="' . THIS_PAGE . '?act=add">ADD CUSTOMER</a></div>';
@mysqli_free_result($result);
//free resources
get_footer();
}
function responseList($id)
{
$myReturn = '';
$sql = "select DateAdded, ResponseID from sm15_responses where SurveyID = {$id}";
#reference images for pager
$prev = '<img src="' . VIRTUAL_PATH . 'images/arrow_prev.gif" border="0" />';
$next = '<img src="' . VIRTUAL_PATH . 'images/arrow_next.gif" border="0" />';
# Create instance of new 'pager' class
$myPager = new Pager(10, '', $prev, $next, '');
$sql = $myPager->loadSQL($sql);
#load SQL, add offset
# connection comes first in mysqli (improved) function
$result = mysqli_query(IDB::conn(), $sql) or die(trigger_error(mysqli_error(IDB::conn()), E_USER_ERROR));
if (mysqli_num_rows($result) > 0) {
#records exist - process
if ($myPager->showTotal() == 1) {
$itemz = "response";
} else {
$itemz = "responses";
}
//deal with plural
$myReturn .= '<div align="center">We have ' . $myPager->showTotal() . ' ' . $itemz . '!</div>';
while ($row = mysqli_fetch_assoc($result)) {
# process each row
$myReturn .= '<div align="center"><a href="' . VIRTUAL_PATH . 'surveys/response_view.php?id=' . (int) $row['ResponseID'] . '">' . dbOut($row['DateAdded']) . '</a>';
$myReturn .= '</div>';
}
$myReturn .= $myPager->showNAV();
# show paging nav, only if enough records
} else {
#no records
$myReturn .= "<div align=center>There are currently no surveys</div>";
}
@mysqli_free_result($result);
//$myReturn .= $id;
return $myReturn;
}
/**
* Constructor for Survey class.
*
* @param integer $id The unique ID number of the Survey
* @return void
* @todo none
*/
function __construct($id)
{
#constructor sets stage by adding data to an instance of the object
$this->SurveyID = (int) $id;
if ($this->SurveyID == 0) {
return FALSE;
}
#get Survey data from DB
$sql = sprintf("select Title, Description from " . PREFIX . "surveys Where SurveyID =%d", $this->SurveyID);
#in mysqli, connection and query are reversed! connection comes first
$result = mysqli_query(IDB::conn(), $sql) or die(trigger_error(mysqli_error(IDB::conn()), E_USER_ERROR));
if (mysqli_num_rows($result) > 0) {
#Must be a valid survey!
$this->isValid = TRUE;
while ($row = mysqli_fetch_assoc($result)) {
#dbOut() function is a 'wrapper' designed to strip slashes, etc. of data leaving db
$this->Title = dbOut($row['Title']);
$this->Description = dbOut($row['Description']);
}
}
@mysqli_free_result($result);
#free resources
if (!$this->isValid) {
return;
}
#exit, as Survey is not valid
#attempt to create question objects
$sql = sprintf("select QuestionID, Question, Description from " . PREFIX . "questions where SurveyID =%d", $this->SurveyID);
$result = mysqli_query(IDB::conn(), $sql) or die(trigger_error(mysqli_error(IDB::conn()), E_USER_ERROR));
if (mysqli_num_rows($result) > 0) {
#show results
while ($row = mysqli_fetch_assoc($result)) {
#create question, and push onto stack!
$this->aQuestion[] = new Question(dbOut($row['QuestionID']), dbOut($row['Question']), dbOut($row['Description']));
}
}
$this->TotalQuestions = count($this->aQuestion);
//the count of the aQuestion array is the total number of questions
@mysqli_free_result($result);
#free resources
#attempt to load all Answer objects into cooresponding Question objects
$sql = "select a.AnswerID, a.Answer, a.Description, a.QuestionID from \n\t\t" . PREFIX . "surveys s inner join " . PREFIX . "questions q on q.SurveyID=s.SurveyID \n\t\tinner join " . PREFIX . "answers a on a.QuestionID=q.QuestionID \n\t\twhere s.SurveyID = %d \n\t\torder by a.AnswerID asc";
$sql = sprintf($sql, $this->SurveyID);
#process SQL
$result = mysqli_query(IDB::conn(), $sql) or die(trigger_error(mysqli_error(IDB::conn()), E_USER_ERROR));
if (mysqli_num_rows($result) > 0) {
#at least one answer!
while ($row = mysqli_fetch_assoc($result)) {
#match answers to questions
$QuestionID = (int) $row['QuestionID'];
#process db var
foreach ($this->aQuestion as $question) {
#Check db questionID against Question Object ID
if ($question->QuestionID == $QuestionID) {
$question->TotalAnswers += 1;
#increment total number of answers
#create answer, and push onto stack!
$question->aAnswer[] = new Answer((int) $row['AnswerID'], dbOut($row['Answer']), dbOut($row['Description']));
break;
}
}
}
}
}
$config->titleTag = smartTitle();
#Fills <title> tag. If left empty will fallback to $config->titleTag in config_inc.php
$config->metaDescription = smartTitle() . ' - ' . $config->metaDescription;
# SQL statement - PREFIX is optional way to distinguish your app
$sql = "select Title, Description from sp15_surveys";
//END CONFIG AREA ----------------------------------------------------------
get_header();
#defaults to header_inc.php
?>
<h3 align="center"><?php
echo $config->titleTag;
?>
</h3>
<?php
#IDB::conn() creates a shareable database connection via a singleton class
$result = mysqli_query(IDB::conn(), $sql) or die(trigger_error(mysqli_error(IDB::conn()), E_USER_ERROR));
echo '<div align="center"><h4>SQL STATEMENT: <font color="red">' . $sql . '</font></h4></div>';
if (mysqli_num_rows($result) > 0) {
#there are records - present data
while ($row = mysqli_fetch_assoc($result)) {
# pull data from associative array
echo '<p>';
echo 'Title: <b>' . $row['Title'] . '</b><br />';
echo 'Description: <b>' . $row['Description'] . '</b><br />';
echo '</p>';
}
} else {
#no records
echo '<div align="center">Sorry, there are no records that match this query</div>';
}
@mysqli_free_result($result);
/**
* For the pager to work, the class must adapt the SQL statement to be used.
*
* Since MySQL limits the number of records returned, this function will
* disassemble the SQL statement and re-assemble it to retrieve the total
* number of records per page.
*
* The adapted SQL statement is returned to be used by the page.
*
* This step is required for the Pager to operate.
*
* <code>
* $myPager = new Pager(10); //create new pager object
* $sql = $myPager->loadSQL($sql); //adapt existing SQL statement
* </code>
*
* IMPORTANT: The pager needs to adapt the SQL BEFORE the SQL statement is used by the page.
*
* @param string $sql The SQL statement to provide the number of records involved
* @return string The adapted SQL statement to be used by the page.
*/
public function loadSQL($sql)
{
//SQL statement must be loaded to extract the numrows
$this->sqlLoaded = TRUE;
#was getting errors if SQL was not loaded
$sql = str_replace(";", "", $sql);
//remove semi-colons
# create mysqli (improved) connection to MySQL
$iConn = IDB::conn();
$testsql = strtolower($sql);
//make lowercase to test for ' from '. Use original SQL to keep case
$findFrom = strrpos($testsql, " from ");
//find ' from ' in select statement
#Receiving an error on the following line means your SQL statement must include a single space around 'FROM' to rebuild your SQL;
if (!$findFrom) {
die(trigger_error("SQL statement must include a single space on each side of the SQL keyword ' from '", E_USER_ERROR));
}
$myFrom = substr($sql, $findFrom + 1);
//eliminate select fields so we can re-create count sql
$rowsql = "SELECT COUNT(*) AS numrows " . $myFrom;
//rows in db
$result = mysqli_query($iConn, $rowsql) or die(trigger_error(mysqli_error($iConn), E_USER_ERROR));
list($this->numrows) = mysqli_fetch_row($result) or die(trigger_error(mysqli_error($iConn), E_USER_ERROR));
$myOffset = ($this->pageNum - 1) * $this->rowsPerPage;
//get page offset
return $sql . " LIMIT " . $myOffset . ", " . $this->rowsPerPage;
//add on limiting
}
function updateExecute()
{
if (!is_numeric($_POST['CustomerID'])) {
//data must be alphanumeric only
feedback("id passed was not a number. (error code #" . createErrorCode(THIS_PAGE, __LINE__) . ")", "error");
myRedirect(THIS_PAGE);
}
$iConn = IDB::conn();
//must have DB as variable to pass to mysqli_real_escape() via iformReq()
$redirect = THIS_PAGE;
//global var used for following formReq redirection on failure
$CustomerID = iformReq('CustomerID', $iConn);
//calls mysqli_real_escape() internally, to check form data
$FirstName = strip_tags(iformReq('FirstName', $iConn));
$LastName = strip_tags(iformReq('LastName', $iConn));
$Email = strip_tags(iformReq('Email', $iConn));
//next check for specific issues with data
if (!ctype_graph($_POST['FirstName']) || !ctype_graph($_POST['LastName'])) {
//data must be alphanumeric or punctuation only
feedback("First and Last Name must contain letters, numbers or punctuation", "warning");
myRedirect(THIS_PAGE);
}
if (!onlyEmail($_POST['Email'])) {
//data must be alphanumeric or punctuation only
feedback("Data entered for email is not valid", "warning");
myRedirect(THIS_PAGE);
}
//build string for SQL insert with replacement vars, %s for string, %d for digits
$sql = "UPDATE test_Customers set \n FirstName='%s',\n LastName='%s',\n Email='%s'\n WHERE CustomerID=%d";
# sprintf() allows us to filter (parameterize) form data
$sql = sprintf($sql, $FirstName, $LastName, $Email, (int) $CustomerID);
@mysqli_query($iConn, $sql) or die(trigger_error(mysqli_error($iConn), E_USER_ERROR));
#feedback success or failure of update
if (mysqli_affected_rows($iConn) > 0) {
//success! provide feedback, chance to change another!
feedback("Data Updated Successfully!", "success");
} else {
//Problem! Provide feedback!
feedback("Data NOT changed!", "warning");
}
myRedirect(THIS_PAGE);
}
/**
* Creates a single instance of the database connection
*
* @return object singleton instance of the database connection
* @access public
*/
public static function conn()
{
if (self::$instance == null) {
self::$instance = new self();
}
#only create instance if does not exist
return self::$instance->dbHandle;
}
/**
* Constructor for survey class
*
* Loads data from survey table
*
* @param in $id number of current survey
* @return void
* @todo none
*/
public function __construct($id)
{
$id = (int) $id;
#cast to integer
if ($id < 1) {
return false;
}
// Don't hit the db if zero
$sql = "select Title, Description from sp15_surveys where SurveyID={$id}";
$result = mysqli_query(IDB::conn(), $sql) or die(trigger_error(mysqli_error(IDB::conn()), E_USER_ERROR));
if (mysqli_num_rows($result) > 0) {
#there are records - present data
while ($row = mysqli_fetch_assoc($result)) {
# pull data from associative array
$this->Title = dbOut($row['Title']);
$this->Description = dbOut($row['Description']);
$this->SurveyID = $id;
$this->isValid = true;
}
# Endwhile
}
# Endif
@mysqli_free_result($result);
$sql = "select q.QuestionID, q.Question, q.Description from sp15_questions q inner join sp15_surveys s on s.SurveyID = q.SurveyID where s.SurveyID = {$id}";
$result = mysqli_query(IDB::conn(), $sql) or die(trigger_error(mysqli_error(IDB::conn()), E_USER_ERROR));
if (mysqli_num_rows($result) > 0) {
#there are records - present data
while ($row = mysqli_fetch_assoc($result)) {
# pull data from associative array
$this->aQuestions[] = new Question(dbOut($row['QuestionID']), dbOut($row['Question']), dbOut($row['Description']));
/* $this->Title = dbOut($row['Title']);
$this->Description = dbOut($row['Description']);
$this->SurveyID = $id;
$this->isValid = true; */
}
# Endwhile
}
# Endif
@mysqli_free_result($result);
}
function idb()
{
return IDB::db();
}
function insertExecute()
{
$iConn = IDB::conn();
//must have DB as variable to pass to mysqli_real_escape() via iformReq()
$redirect = THIS_PAGE;
//global var used for following formReq redirection on failure
$FirstName = strip_tags(iformReq('FirstName', $iConn));
$LastName = strip_tags(iformReq('LastName', $iConn));
$Email = strip_tags(iformReq('Email', $iConn));
//next check for specific issues with data
if (!ctype_graph($_POST['FirstName']) || !ctype_graph($_POST['LastName'])) {
//data must be alphanumeric or punctuation only
feedback("First and Last Name must contain letters, numbers or punctuation");
myRedirect(THIS_PAGE);
}
if (!onlyEmail($_POST['Email'])) {
//data must be alphanumeric or punctuation only
feedback("Data entered for email is not valid");
myRedirect(THIS_PAGE);
}
//build string for SQL insert with replacement vars, %s for string, %d for digits
$sql = "INSERT INTO test_Customers (FirstName, LastName, Email) VALUES ('%s','%s','%s')";
# sprintf() allows us to filter (parameterize) form data
$sql = sprintf($sql, $FirstName, $LastName, $Email);
@mysqli_query($iConn, $sql) or die(trigger_error(mysqli_error($iConn), E_USER_ERROR));
#feedback success or failure of update
if (mysqli_affected_rows($iConn) > 0) {
//success! provide feedback, chance to change another!
feedback("Customer Added Successfully!", "notice");
} else {
//Problem! Provide feedback!
feedback("Customer NOT added!");
}
myRedirect(THIS_PAGE);
}
/**
* Constructor for Response class.
*
* @param integer $id ID number of Survey
* @return void
* @todo none
*/
function __construct($id)
{
$id = (int) $id;
# explicit integer conversion forces zero if not numeric
if ($id == 0) {
return FALSE;
}
$iConn = \IDB::conn();
# v5, now we only want one response! (limit 0,1)
$sql = sprintf("select ResponseID from " . PREFIX . "responses where SurveyID =%d", $id);
$result = mysqli_query($iConn, $sql) or die(trigger_error(mysqli_error($iConn), E_USER_ERROR));
if (mysqli_num_rows($result) > 0) {
# returned a response!
$this->isValid = TRUE;
while ($row = mysqli_fetch_assoc($result)) {
# load singular response object properties
$this->aResponse = new Response((int) $row['ResponseID']);
# v5: create one response object, not an array!
}
} else {
return FALSE;
#no results - abort
}
mysqli_free_result($result);
# v5: Will use group by to load Tallies property of answer object - no longer needing a Tally object
$sql = sprintf("select AnswerID, Count(*) as Tallies from " . PREFIX . "responses_answers where QuestionID in (select QuestionID from " . PREFIX . "questions where SurveyID = %d) group by AnswerID", $id);
$result = mysqli_query($iConn, $sql) or die(trigger_error(mysqli_error($iConn), E_USER_ERROR));
if (mysqli_num_rows($result) > 0) {
//returned a response!
while ($row = mysqli_fetch_array($result)) {
//load singular response object properties
$intAnswer = (int) $row['AnswerID'];
foreach ($this->aResponse->aQuestion as $question) {
//loop through questions to reveal answers
foreach ($question->aAnswer as $answer) {
//loop through to attach TotalChoices
if ($intAnswer == $answer->AnswerID) {
//calculate tally
$answer->Tallies = (int) $row['Tallies'];
break;
}
}
}
}
}
@mysqli_free_result($result);
$this->Description = $this->aResponse->Description;
#v5 - this info now from the single response, not array element
$this->Title = $this->aResponse->Title;
$this->TotalResponses = $this->aResponse->TotalResponses;
}
/**
* session_clean() housekeeping deletes old session DB data
*
* @global object database connection open to delete session data
* @param integer $expire ID number of seconds to keep session data
* @return integer Number of rows deleted - should be one
* @todo none
*/
function session_clean($expire)
{
global $iConn;
# Global connection to DB
if (!is_resource($iConn)) {
$iConn = IDB::conn();
}
# SQL to delete old sessions
$sql = sprintf('DELETE FROM ' . PREFIX . 'sessions WHERE DATE_ADD(LastAccessed, INTERVAL %d SECOND) < NOW()', (int) $expire);
mysqli_query($iConn, $sql) or die(trigger_error(mysqli_error($iConn) . " sql: " . $sql, E_USER_ERROR));
return mysqli_affected_rows($iConn);
}
