Some basic PHP scripting

As an app, the business logic will be retrieving data from the database, and displaying it in a list, with the relevant details. The database is needed for the following pages of the app:

  • getting the list of events available to the public
  • displaying a random inspirational quote each time the app is opened by the user
  • handling the user logins and registastations
  • saving feedback and sending it by email to the administators

The above features of the app, all need to be backed by qualitative sql statements to get the relevant data in the least time possible. These will be handled through PHP Server side scripting. PHP was chosen for such a project for the following reasons:

  • PHP is an ope source language which runs on various platforms (Windows, Linux, Unix, Mac OS X, etc.)
  • Since it is, open source, it is also compatible with almost all servers used nowadays (Apache, IIS, etc.)
  • PHP supports a wide range of databases engines, although it has its own database named mySQL.

Starting with the most basic php function, we will now explain how the php script getting the events will work. This will get the event name coupled with the date and time, and type. The type will show the type of event which the public will be attending to, so they cna be prepared beforehand of what to expect. This data is ordered in an ascending order to display the most forthcoming events first, and the later ones later.

<?php
require_once __DIR__ . '/config/db_connect.php';
$results_array = array();
$response = array();
if ($result = $con->query("SELECT eventID, eventName, eventType, DATE_FORMAT( eventDate, '%d/%m/%Y' ) AS DATE, TIME_FORMAT( eventDate, '%H:%i' ) AS TIME FROM Event WHERE eventDate >=CURRENT_TIMESTAMP( ) ORDER BY eventDate ASC LIMIT 0 , 5") 
or die('Table is currently not accessible')) {
if ($count = $result -> num_rows) {
$response["Event"] = array();
while ($rows = $result -> fetch_assoc()){
$results_array[] = $rows;
array_push($response["Event"], $rows);
}
 
$response["success"] = 1;
echo json_encode($response);
 
} else {
$response["success"] = 0;
$response["message"] = "No events found";
 
// echo no users JSON
echo json_encode($response);
}
}

While the first part of the code above connects to the database, whose details are saved in a seperate file, the script then select the relevant data from the Events table and displays it in an array. Finally, the script encodes the data in JSON format, to then be parsed later on, by the app. We will be discussing JSON in the next blog post.

Next is displaying a random daily inspirational quote each time the app is opened. In this case, the following php script is used:

<?php 
require_once __DIR__ . '/config/db_connect.php';
$results_array = array();
$response = array();
if ($result = $con->query("SELECT quoteID, quoteName, quoteAuthor FROM Quote WHERE RAND()<
(SELECT ((1/COUNT(*))*10) FROM Quote) ORDER BY RAND() LIMIT 1") 
or die('Table is currently not accessible')) {
if ($count = $result -> num_rows) {
$response["Quote"] = array();
 
while ($rows = $result -> fetch_assoc()){
$results_array[] = $rows;
array_push($response["Quote"], $rows);
}

$response["success"] = 1;
echo json_encode($response);
} else {
$response["success"] = 0;
$response["message"] = "No Quotes found";
 
// echo no users JSON
echo json_encode($response);
}
}

Here, the select statement uses the SQLI random function to select a quote at random and displays it to the user. The database table is populated with several inspirational quotes to choose from, and each quote is chosen at random by the script. Since some select statements consume more resources than others on the database, especially when it come to using the random function, therefore the best random select statement as used. Again here, the result of the statment needs to be encoded in JSON for use by the app, later on.

When it comes to user handling, the app will be needing 3 functions with which to relate to the database. These are:

  • Registering a new user.
  • Verifying if  a username and password entered by the user are correct.
  • Resetting the password for a user who has forgotten it.

These are all handled in one php file as shown in the following code snippet:

public function saveUser($name, $password) {
$usrID = uniqid('', true);
$hash = $this->hashSSHA($password);
$encrypted_password = $hash["encrypted"];
$salt = $hash["salt"];
$result = $con->query("INSERT INTO User (userID, fullName, userEmail, password, dateCreated,dateModified) VALUES('$usrID', '$usrName', '$usrEmail', '$encrypted_password', CURRENT_TIMESTAMP, NOW())");
 
if ($result) {
$uid = mysqli_insert_id();
$result = $con->query("SELECT * FROM User WHERE userID = '$usrID'");
 
return mysqli_fetch_array($result, MYSQL_ASSOC);
} else {
return false;
}
}
public function getUser($usrEmail, $password) {
 
$usrEmail = mysqli_real_escape_string($userEmail);
$password = mysqli_real_escape_string($password);
$query = "SELECT userEmail, password FROM User WHERE userEmail = '$usrEmail'"; 
$result = mysqli_query($con, $query)or die(mysqli_error());
 
$rowNum = mysqli_num_rows($result);
if ($rowNum == 1) {
$result = mysqli_fetch_array($result, MYSQL_ASSOC);
$salt = $result['salt'];
$encrypted_password = $result['encrypted_password'];
$hash = $this->checkhashSSHA($salt, $password);
 
if ($encrypted_password == $hash) {
return $result;
}
} else {
return false;
}
}
public function isExisting($email) {
$result = $con->query("SELECT userEmail from User WHERE userEmail = '$usrEmail'");
$rowNum = mysqli_num_rows($result);
 
if ($rowNum > 0) {
return true;
} else {
return false;
}
}

The code above is also responsible for hashing the password in such a way that it is not readable shown a security breach on the database occur.

The last script will handle the anonymous feedback messaging, whereby a user can anonymously post feedback about the experience in the app or about the group’s activities in general. Below is the php code:

$result = $con->query("INSERT INTO `Feedback` (`messageID`, `messageSubject`, `messageContent`, `dateCreated`, `messageSent`) VALUES (NULL, '$subject', '$message', CURRENT_TIMESTAMP, '0')");
 
 if ($result) {
 $uid = mysqli_insert_id();
 
 return mysqli_fetch_array($result, MYSQL_ASSOC);
 } else {
 return false;
 }
 
 $query = "SELECT `messageID`, `messageSubject`, `messageContent` FROM `Feedback` WHERE `mes sageSent` = '0' LIMIT 1";
 $result = mysqli_query($con, $query)or die(mysqli_error());
 
 $rowNum = mysqli_num_rows($result);
 
 if ($rowNum == 1) {
 $result = mysqli_fetch_array($result, MYSQL_ASSOC);
 } else {
 return false;
 }
$messageID = mysqli_real_escape_string('messageID');
$to = "michele.laferla@gmail.com"; 
 $subject = 'messageSubject'; 
 $email = $_POST['Email'] ; 
 $message = $_POST['messageContent'] ; 
 $headers = "From: noreply@tibiapp.com"; 
 $sent = mail($to, $subject, $message, $headers) ;
if($sent) {
 $result = $con->query("UPDATE `Feedback` SET `messageSent`= 1 WHERE `messageID` = 'messageID'");
 }

On of the most important considerations being taken when parsing data from the database onto the app is the fact that the queries can sometimes be resource comsuming, hence increasing the connectivity relay between the database and the app, which occurs over a normal internet connection. As we will see further on, all these php files discussed above will serve as a web service to get the data from the database and pass it over onto the app.

For this reason, the queries need to be as fast as possible, so as not to increase the the consumption of resources, which could result in a connection timeout with the server.