I'm doing my own little project converting UserCake (latest) to OOP with PDO, i've finalised a lot of features already. But right now i've been stuck for awhile on this Page functionality. So according to the code it's suppose to read the root folder for PHP files and add them to DB table (uc_pages) if they don't already exist. And if there's pages in the DB that doesn't exist in the root folder to delete those from the db.
I get no errors at all which is kinda interesting... but i'll post the code if anyone would be kind to give me a hand on this one.
Pages.php
<?php
require_once("resources/database.php");
$website_pages = new dbPages($db);
// set number of records per page
$records_per_page = 6;
// calculate for the query LIMIT clause
$from_record_num = ($records_per_page * $page) - $records_per_page;
$website_pages->getPageFiles();
//Retrieve list of pages in root usercake folder
$website_pages->fetchAllPages();
//Retrieve list of pages in pages table
$creations = array();
$deletions = array();
//Check if any pages exist which are not in DB
foreach ($website_pages->getPageFiles() as $web_page){
if(!isset($website_pages->readOne()[$web_page])){
$creations[] = $web_page;
}
}
//Enter new pages in DB if found
if (count($creations) > 0) {
$website_pages->create($creations);
}
if (count($website_pages->fetchAllPages()) > 0){
//Check if DB contains pages that don't exist
foreach ($website_pages->readOne() as $web_page){
if(!isset($website_pages->fetchAllPages()[$web_page['page']])){
$deletions[] = $web_page['id'];
}
}
}
//Delete pages from DB if not found
if (count($deletions) > 0) {
$website_pages->delete($deletions);
}
//Update DB pages
$website_pages->readAll($from_record_num, $records_per_page);
// header settings
$page_url="pages.php?";
$page_title = "UNFINISHED: All pages";
include_once "./resources/header.php";
?>
<div class='container'>
<div class='page-header'>
<h1><?php echo"{$page_title}";?></h1>
</div>
</div>
<div class="jumbotron">
<div class="container">
<?php
// query products
$stmt = $website_pages->readAll($from_record_num, $records_per_page);
$num = $stmt->rowCount();
// display the products if there are any
if($num>0){
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)){
extract($row);
echo "<div class='col-xs-12 col-md-6'>
<div class='btn-group btn-group-justified' role='group'
aria-label='Justified button group'>
<a href='page.php?id={$id}' class='btn btn-warning' role='button'>
<span class='glyphicon glyphicon-edit'></span> Edit</a>
</div>";
echo "
<div class='panel panel-primary'>
<div class='panel-heading'>
<h3 class='panel-title'>{$page_name}</h3>
</div>
<div class='panel-footer'>";
//Show public/private setting of page
if($private == 0){
echo "This page is Public";
}
else {
echo "This page is Private";
}
echo "</div></div></div>";
}
echo "</div>";
// needed for paging
$total_rows=0;
if($page_url=="pages.php?"){
$total_rows=$website_pages->countAll();
}
// paging buttons
include_once './resources/paging.php';
}
// tell the user there are no products
else{
echo "<div class=\"alert alert-danger alert-dismissable\">";
echo "<button type=\"button\" class=\"close\" data-
dismiss=\"alert\" aria-hidden=\"true\">×</button>";
echo "No pages found.";
echo "</div>";
}
echo "</div>";
?>
</div>
</div>
<?php require("./resources/footer.php")?>
Database.php
<?php
class Database{
// specify your own database credentials
private $host = "###########";
private $db_name = "website";
private $username = "###########";
private $password = "###########";
public $conn;
// get the database connection
public function getConnection(){
$this->conn = null;
try{
$this->conn = new PDO(
"mysql:host=" . $this->host . ";dbname=" . $this->db_name,
$this->username, $this->password);
}catch(PDOException $exception){
echo "Connection error: " . $exception->getMessage();
}
return $this->conn;
}
}
// instantiate database and product object
$database = new Database();
$db = $database->getConnection();
require_once './resources/functions.php';
$website = new Configuration($db);
$website->readConfig();
?>
The part from functions.php that is struggling
class dbPages {
// database connection and table names
private $conn;
private $table_name = "uc_pages";
private $table_name2 = "uc_permission_page_matches";
// object properties
public $id;
public $page_id;
public $permission_id;
public $page_name;
public $private;
public $pages;
public $row;
public function __construct($db){
$this->conn = $db;
}
//Retrieve a list of all .php files in root files folder
function getPageFiles() {
$directory = "";
$pages = glob($directory . "*.php");
//print each file name
foreach ($pages as $web_page){
$row[$web_page] = $web_page;
}
return $row;
}
//Fetch information on all pages
function fetchAllPages() {
$query = "SELECT
id,
page_name,
private
FROM
" . $this->table_name . " ";
// prepare query statement
$stmt = $this->conn->prepare( $query );
$stmt->execute();
while ($stmt->fetch(PDO::FETCH_ASSOC)){
$row[$web_page] = array(
'id' => $id, 'page_name' => $page_name, 'private' => $private);
}
if (isset($row)){
return ($row);
}
}
// read products
function readAll($from_record_num, $records_per_page){
// select query
$query = "SELECT
id,
page_name,
private
FROM
" . $this->table_name . "
ORDER BY
page_name ASC
LIMIT
?, ?";
// prepare query statement
$stmt = $this->conn->prepare( $query );
// bind variable values
$stmt->bindParam(1, $from_record_num, PDO::PARAM_INT);
$stmt->bindParam(2, $records_per_page, PDO::PARAM_INT);
// execute query
$stmt->execute();
// return values from database
return $stmt;
}
// used for paging products
public function countAll(){
$query = "SELECT COUNT(*) as total_rows
FROM " . $this->table_name . "";
$stmt = $this->conn->prepare( $query );
$stmt->execute();
$row = $stmt->fetch(PDO::FETCH_ASSOC);
return $row['total_rows'];
}
// used when filling up the update product form
function readOne(){
$query = "SELECT
id,
page_name,
private
FROM
" . $this->table_name . "
WHERE
page_name = ?
LIMIT
0,1";
$stmt = $this->conn->prepare( $query );
$stmt->bindParam(1, $this->id);
$stmt->execute();
$row = $stmt->fetch(PDO::FETCH_ASSOC);
$this->id = $row['id'];
$this->page_name = $row['page_name'];
$this->private = $row['private'];
}
// create product
function create($pages){
//write query
$query = "INSERT INTO
" . $this->table_name . "
SET
id = ?,
page_name = ?,
private = ?";
$stmt = $this->conn->prepare($query);
$stmt->bindParam(1, $this->id);
$stmt->bindParam(2, $this->page_name);
$stmt->bindParam(3, $this->private);
foreach($pages as $page_name){
if($stmt->execute()){
return true;
}else{
return false;
}
}
}
// delete the product
function delete($pages){
$query = "DELETE FROM " . $this->table_name . " WHERE id = ?";
$stmt = $this->conn->prepare($query);
$stmt->bindParam(1, $this->id);
foreach($pages as $id){
if($result = $stmt->execute()){
return true;
}else{
return false;
}
}
$query2 = "DELETE FROM " . $this->table_name2 . "
WHERE page_id = ?";
$stmt2 = $this->conn->prepare($query);
$stmt2->bindParam(1, $this->page_id);
foreach($pages as $id){
if($result = $stmt2->execute()){
return true;
}else{
return false;
}
}
}
}
