/**
* This function connects to the MySQL server
* and uses the global array $config. The
* function also selects the right database-schema
*
*/
function databaseConnect()
{
global $config;
# report exceptions, but don't throw them, and we are not interested in
# wether we use indexes.
mysqli_report(MYSQLI_REPORT_ALL ^ MYSQLI_REPORT_STRICT ^ MYSQLI_REPORT_INDEX);
$db_conn = mysqli_connect($config['mysql']['hostname'], $config['mysql']['username'], $config['mysql']['password'], $config['mysql']['database']);
// check connection
if (mysqli_connect_errno()) {
$errormessage = "Connect failed " . mysqli_connect_error();
add_to_message_stack($errormessage, true);
exit;
}
return $db_conn;
}
/**
* check for given variable if value is empty
* and appends message to the array if this is the case
*
*/
function ifEmptyAddMessage(&$variable, &$errorList, $message, $addToStack)
{
if (empty($variable)) {
$errorList[] = $message;
if ($addToStack) {
add_to_message_stack($message, FALSE);
}
}
}
/**
* This is a template rendering function. As such it
* serves illustrative purposes and in general needs to
* be configured for specific needs.
*
* This function makes a report of all the records in the
* Jobs table. It outputs HTML and can sort records in the
* table based on a predefined field and sortation order.
*/
function displayAllJobs()
{
global $db_conn;
#references connection to the database
$field = 'JobID';
#default sort column
$sort = 'ASC';
#default sorting order
// Set the sortation order requested via the URL
if (isset($_GET['sorting'])) {
if ($_GET['sorting'] == 'ASC') {
$sort = 'ASC';
} else {
$sort = 'DESC';
}
}
// Set the sortation field requested by URL
// on which soration should be applied
if (isset($_GET['field'])) {
switch ($_GET['field']) {
case "JobTitle":
$field = 'JobTitle';
break;
case "MinSalary":
$field = 'MinSalary';
break;
case "MaxSalary":
$field = 'MaxSalary';
break;
default:
$field = 'JobID';
}
}
// initializes a statement and return an object for use
// with mysqli_stmt_prepare
$stmt = mysqli_stmt_init($db_conn);
// the query that should be parsed
$sql = "SELECT * FROM `jobs` ORDER BY {$field} {$sort}";
if (!mysqli_stmt_prepare($stmt, $sql)) {
// if things go wrong we sent a message to the screen
add_to_message_stack("Query could not be parsed. This message is logged", true);
// and a message to the system log. Note we do not give information
// about SQL to the user.
log_message("Query could not be parsed: " . $sql);
} else {
mysqli_stmt_execute($stmt);
$result = mysqli_stmt_get_result($stmt);
mysqli_stmt_close($stmt);
?>
<h1>Jobs</h1>
<br/>
<input type="button" onclick="document.location.href='?action=addJob';" value="Add Job" />
<br/>
<br/>
<table>
<tr>
<th>Job ID <br/> <?php
echo parseSorting("jobs", "JobID", $field, $sort);
?>
</th>
<th>Title <br/> <?php
echo parseSorting("jobs", "JobTitle", $field, $sort);
?>
</th>
<th>Minimum Salary <br/> <?php
echo parseSorting("jobs", "MinSalary", $field, $sort);
?>
</th>
<th>Maximum Salary <br/> <?php
echo parseSorting("jobs", "MaxSalary", $field, $sort);
?>
</th>
<th>Action</th>
</tr>
<?php
$count = 1;
while ($row = mysqli_fetch_assoc($result)) {
$count++;
?>
<tr <?php
if ($count % 2 == 0) {
echo "class=\"highlight\" ";
}
?>
>
<td><?php
echo $row['JobID'];
?>
</td>
<td><?php
echo $row['JobTitle'];
?>
</td>
<td><?php
echo $row['MinSalary'];
?>
</td>
<td><?php
echo $row['MaxSalary'];
?>
</td>
<td>
<a href="index.php?action=editJob&id=<?php
echo $row['JobID'];
?>
">Edit</a>|<a class="delete" href="javascript:confirmAction('Are you sure?', 'index.php?action=deleteJob&id=<?php
echo $row['JobID'];
?>
');">Remove</a>
</td>
</tr>
<?php
}
?>
</table>
<?php
}
}
/**
* This is a template rendering function. As such it
* serves illustrative purposes and in general needs to
* be configured for specific needs.
*
* This function makes a report of all the records in the
* Jobs table. It outputs HTML and can sort records in the
* table based on a predefined field and sortation order.
*/
function displayAllCustomers()
{
log_message("Backtrace: displayAllCustomers() called. ");
global $db_conn;
#references connection to the database
$field = 'Wcode';
#default sort column
$sort = 'ASC';
#default sorting order
// Set the sortation order requested via the URL
if (isset($_GET['sorting'])) {
if ($_GET['sorting'] == 'ASC') {
$sort = 'ASC';
} else {
$sort = 'DESC';
}
}
// Set the sortation field requested by URL
// on which soration should be applied
if (isset($_GET['field'])) {
switch ($_GET['field']) {
case "Wcode":
$field = 'Wcode';
break;
case "Naam":
$field = 'Naam';
break;
case "Adres":
$field = 'Adres';
break;
case "Plaats":
$field = 'Plaats';
break;
case "Telefoonnr":
$field = 'Plaats';
break;
default:
$field = 'JobID';
}
}
// initializes a statement and return an object for use
// with mysqli_stmt_prepare
$stmt = mysqli_stmt_init($db_conn);
// the query that should be parsed
$sql = "SELECT * FROM `winkel` ORDER BY {$field} {$sort}";
if (!mysqli_stmt_prepare($stmt, $sql)) {
// if things go wrong we sent a message to the screen
add_to_message_stack("Query could not be parsed. This message is logged", true);
// and a message to the system log. Note we do not give information
// about SQL to the user.
log_message("Query could not be parsed: " . $sql);
} else {
mysqli_stmt_execute($stmt);
$result = mysqli_stmt_get_result($stmt);
mysqli_stmt_close($stmt);
?>
<h1>Customers</h1>
<br/>
<input type="button" onclick="document.location.href='?action=displayAddCustomer';" value="Add Customer" />
<br/>
<br/>
<table>
<tr>
<th>Wcode <br/> <?php
echo parseSorting("showCustomers", "Wcode", $field, $sort);
?>
</th>
<th>Name <br/> <?php
echo parseSorting("showCustomers", "Naam", $field, $sort);
?>
</th>
<th>Address <br/> <?php
echo parseSorting("showCustomers", "Adres", $field, $sort);
?>
</th>
<th>Place <br/> <?php
echo parseSorting("showCustomers", "Plaats", $field, $sort);
?>
</th>
<th>Phone number <br/> <?php
echo parseSorting("showCustomers", "Telefoonnr", $field, $sort);
?>
</th>
<th>Action</th>
</tr>
<?php
$count = 1;
while ($row = mysqli_fetch_assoc($result)) {
$count++;
?>
<tr <?php
if ($count % 2 == 0) {
echo "class=\"highlight\" ";
}
?>
>
<td><?php
echo $row['Wcode'];
?>
</td>
<td><?php
echo $row['Naam'];
?>
</td>
<td><?php
echo $row['Adres'];
?>
</td>
<td><?php
echo $row['Plaats'];
?>
</td>
<td><?php
echo $row['Telefoonnr'];
?>
</td>
<td>
<a href="index.php?action=displayEditCustomer&id=<?php
echo $row['Wcode'];
?>
">Edit</a>|<a class="delete" href="javascript:confirmAction('Are you sure?', 'index.php?action=deleteCustomer&id=<?php
echo $row['Wcode'];
?>
');">Remove</a>
</td>
</tr>
<?php
}
?>
</table>
<?php
}
}
