I am developing an application which records students’ school fees in a table named ‘dailyincome’. I have two categories of student, those in the junior secondary school ‘jss’ and senior secondary school ‘sss’. I have two other tables ‘jss’ and ‘sss’ housing the information about the two categories of students. I want a situation that whenever incomeList.php is loaded in the browser, it access the database and list out the payee (student’s) full name, amount paid, date of payment. The link between ‘dailyincome’ table and ‘jss & sss’ is that they both has a column ‘payersID’ which should reference and uniquely identify the full details of the payer and display it on the page. My whole page is now below as I’m trying to get information from dailyincome and jss tables presently though I want to get information from both jss and sss tables and identify them within the dailyincome table.
<?php
//connect to the database
include_once ('DatabaseManager.php');
$conn = DatabaseManager::getConnection();
//get the function
include_once ('function.php');
$page = (int) (!isset($_GET["page"]) ? 1 : $_GET["page"]);
$limit = 20;
$startpoint = ($page * $limit) - $limit;
//to make pagination
$statement = "SELECT dailyincome.id, paymentType, amountPaidInFigure, paymentDate, jss.firstName AS firstName, jss.lastName AS lastName, jss.id FROM dailyincome, jss WHERE jss.payersID = dailyincome.payersID";
require_once '../classes/Config.php';
$result = new ECConfig();
$result2 = new ECConfig();
$result3 = new ECConfig();
$result->getSiteName();
$result2->getSiteNickName();
$result3->getCurrency();
?>
<!DOCTYPE html>
<!--[if IE 8]> <html class="no-js lt-ie9" lang="en" > <![endif]-->
<!--[if gt IE 8]><!--> <html class="no-js" lang="en" > <!--<![endif]-->
<head>
<meta charset="utf-8" />
<meta name="viewport" content="width=device-width" />
<title><?php echo $result->getSiteName(); ?> | Finance</title>
<link href="../css/normalize.css" rel="stylesheet" />
<link href="../css/foundation.css" rel="stylesheet" />
<link href="../css/pagination.css" rel="stylesheet" type="text/css" />
<link href="../css/B_blue.css" rel="stylesheet" type="text/css" />
<link href="../main.css" rel="stylesheet" type="text/css" media="all">
<script src="../js/vendor/custom.modernizr.js"></script>
</head>
<body>
<?php $query = $conn->query("{$statement} ORDER BY jss.id DESC LIMIT {$startpoint} , {$limit}"); ?>
<?php $dailyIncomeRegister = $conn->query("{$statement}");
$dailyIncome = $dailyIncomeRegister->rowCount(); ?>
<div class="contain-to-grid">
<div class="top-bar text-center">
<h3 class="me1"><?php echo $result->getSiteName(); ?></h3>
<h5 class="subheader"><?php echo $result->getSiteNickName(); ?></h5>
</div>
</div>
<!-- The part above takes care of the banner region -->
<div class="row">
<div class="breadcrumbs">
<div class="large-7 columns">
<a href="../homepage.php">Main Home</a> || <a href="index.php">Finance</a>
</div>
<div class="large-5 columns">
You are logged in as <?php if(isset($_SESSION['userName'])) echo $_SESSION['userName']; ?> || Log Out <a href="index.php?action=logout"><img src="../img/quit.png" alt="Log Out" /></a>
</div>
</div>
</div>
<br/>
<div class="row">
<div class="large-9 columns">
<?php include 'incomeConsole.php'; ?>
</div>
<div class="large-3 columns">
<?php include 'mainmenu.php'; ?>
</div>
</div><!--closing tag for the first row -->
<div class="row">
<div class="large-12 columns">
<?php if(isset($query)): ?>
<p class="searchResult">
<?php
if(isset($dailyIncome)) {
if($dailyIncome == 0)
{
echo 'There is no record of any daily income in the database.';
}
if($dailyIncome == 1)
{
echo 'There is 1 record of daily income in the database.';
}
if($dailyIncome > 1)
{
echo "There are $dailyIncome records of daily income in the database.";
}
}
?>
</p>
<div class="large-3 columns panel"><h5>Payee's Name</h5></div>
<div class="large-3 columns panel"><h5>Amount Paid</h5></div>
<div class="large-2 columns panel"><h5>Payment Date</h5></div>
<div class="large-4 columns panel"><h5>Action</h5></div>
<?php foreach($query as $i=>$dailyIncome): ?>
<div class="large-3 columns"><?php echo ($dailyIncome['lastName']); ?> <?php echo ($dailyIncome['firstName']); ?> <?php echo ($dailyIncome['id']); ?></div>
<div class="large-3 columns"><?php echo $result->getCurrency(); ?><?php echo htmlentities(number_format($dailyIncome['amountPaidInFigure']), 2); ?></div>
<div class="large-2 columns"><?php echo (date($dailyIncome['paymentDate'])); ?></div>
<div class="large-4 columns">
<form action="index.php" method="post">
<div>
<input type="hidden" name="id" value="<?php echo htmlentities($dailyIncome['id']); ?>"/>
<input type="submit" name="action" value="Edit Income" class="button myconsole2 radius" />
<input type="submit" name="action" value="Delete Income" onclick="return confirm('Delete This Income?')" class="button myconsole2 radius" />
</div>
</form>
</div>
<hr/>
<?php endforeach; ?>
<?php endif; ?>
<div class="large-12 columns paginate"><?php echo pagination($statement, $limit, $page); ?></div>
</div>
</div>
<br/>
<br/>
<br/>
<!-- Footer -->
<div class="wrapper row2">
<div class="row">
<br/>
</div>
</div>
<div class="wrapper row4">
<div class="row">
<div class="large-12 columns text-center">
<p> © <?php echo date('Y'); ?>School Manager Licensed to <?php echo $result->getSiteName(); ?>, All Rights Reserved.</p>
</div>
</div>
</div>
</body>
</html>
I keep getting this error message:
Fatal error: Uncaught exception ‘PDOException’ with message ‘SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘SELECT dailyincome.id, paymentType, amountPaidInFigure, paymentDate, jss.fir’ at line 1’ in C:\xampp\htdocs\finance\function.php:9 Stack trace: #0 C:\xampp\htdocs\finance\function.php(9): PDO->query(‘SELECT COUNT(*)…’) #1 C:\xampp\htdocs\finance\incomeList.php(137): pagination(‘SELECT dailyinc…’, 20, 1) #2 {main} thrown in C:\xampp\htdocs\finance\function.php on line 9.
Now, function.php is to help in paginating my result and its working fine for all other pages where results need to be paginated. Function.php is shown below:
<?php
function pagination($query, $per_page = 10,$page = 1, $url = '?'){
include_once ('DatabaseManager.php');
$conn = DatabaseManager::getConnection();
$query = "SELECT COUNT(*) as `num` FROM {$query}";
foreach($conn->query($query) as $row);
$total = $row['num'];
$adjacents = "2";
$page = ($page == 0 ? 1 : $page);
$start = ($page - 1) * $per_page;
$prev = $page - 1;
$next = $page + 1;
$lastpage = ceil($total/$per_page);
$lpm1 = $lastpage - 1;
$pagination = "";
if($lastpage > 1)
{
$pagination .= "<ul class='pagination'>";
$pagination .= "<li class='details'>Page $page of $lastpage</li>";
if ($lastpage < 7 + ($adjacents * 2))
{
for ($counter = 1; $counter <= $lastpage; $counter++)
{
if ($counter == $page)
$pagination.= "<li><a class='current'>$counter</a></li>";
else
$pagination.= "<li><a href='{$url}page=$counter'>$counter</a></li>";
}
}
elseif($lastpage > 5 + ($adjacents * 2))
{
if($page < 1 + ($adjacents * 2))
{
for ($counter = 1; $counter < 4 + ($adjacents * 2); $counter++)
{
if ($counter == $page)
$pagination.= "<li><a class='current'>$counter</a></li>";
else
$pagination.= "<li><a href='{$url}page=$counter'>$counter</a></li>";
}
$pagination.= "<li class='dot'>...</li>";
$pagination.= "<li><a href='{$url}page=$lpm1'>$lpm1</a></li>";
$pagination.= "<li><a href='{$url}page=$lastpage'>$lastpage</a></li>";
}
elseif($lastpage - ($adjacents * 2) > $page && $page > ($adjacents * 2))
{
$pagination.= "<li><a href='{$url}page=1'>1</a></li>";
$pagination.= "<li><a href='{$url}page=2'>2</a></li>";
$pagination.= "<li class='dot'>...</li>";
for ($counter = $page - $adjacents; $counter <= $page + $adjacents; $counter++)
{
if ($counter == $page)
$pagination.= "<li><a class='current'>$counter</a></li>";
else
$pagination.= "<li><a href='{$url}page=$counter'>$counter</a></li>";
}
$pagination.= "<li class='dot'>..</li>";
$pagination.= "<li><a href='{$url}page=$lpm1'>$lpm1</a></li>";
$pagination.= "<li><a href='{$url}page=$lastpage'>$lastpage</a></li>";
}
else
{
$pagination.= "<li><a href='{$url}page=1'>1</a></li>";
$pagination.= "<li><a href='{$url}page=2'>2</a></li>";
$pagination.= "<li class='dot'>..</li>";
for ($counter = $lastpage - (2 + ($adjacents * 2)); $counter <= $lastpage; $counter++)
{
if ($counter == $page)
$pagination.= "<li><a class='current'>$counter</a></li>";
else
$pagination.= "<li><a href='{$url}page=$counter'>$counter</a></li>";
}
}
}
if ($page < $counter - 1){
$pagination.= "<li><a href='{$url}page=$next'>Next</a></li>";
$pagination.= "<li><a href='{$url}page=$lastpage'>Last</a></li>";
}else{
$pagination.= "<li><a class='current'>Next</a></li>";
$pagination.= "<li><a class='current'>Last</a></li>";
}
$pagination.= "</ul>\
";
}
return $pagination;
}
?>
I want this page to work and more importantly, I want to get list of students who have paid from both jss and sss tables and cross reference it with dailyincome table and have the result displayed and paginated if necessary, how do I get it to work? I understand the issue is with my sql syntax. please I need help. Thanks.