public function getTable($res, $rows)
{
// Get parameters
$hits = isset($_GET['hits']) ? $_GET['hits'] : 8;
$page = isset($_GET['page']) ? $_GET['page'] : 1;
$id = isset($_GET['id']) ? $_GET['id'] : null;
// Check that incoming parameters are valid
is_numeric($hits) or die('Check: Hits must be numeric.');
is_numeric($page) or die('Check: Page must be numeric.');
if ($id !== null) {
return $this->getDetails($res[0]);
exit;
}
// Prepare the table, firt the header row
$admin = CUser::isUser() ? '<th>Admin</th>' : NULL;
$tr = "<tr>\n <th>Id</th>\n <th>Akronym " . CFunc::orderby('acronym') . "</th>\n <th>Namn " . CFunc::orderby('name') . "</th>\n <th>Roll</th>\n {$admin}\n </tr>";
// Prepare the data rows
foreach ($res as $key => $val) {
$admin = CUser::isCurrentUser($val->id) ? "<a href='user_regedit.php?id={$val->id}'>Edit</a> <a href='user_delete.php?id={$val->id}'>Delete</a>" : NULL;
$admin = CUser::isUser($val->id) ? "<td>{$admin}</td>" : NULL;
$admin = CUser::isAuthenticated() ? "<td><a href='user_edit.php?id={$val->id}'>Edit</a> <a href='user_delete.php?id={$val->id}'>Delete</a></td>" : $admin;
// $synopsys = CTextFilter::nl2br($val->synopsys);
$href = CFunc::getQueryString(array('id' => $val->id));
$tr .= "<tr>\n </a></td>\n <td>{$val->id}</td>\n <td><a href='{$href}'>{$val->acronym}</a></td>\n <td>{$val->name}</td>\n <td>{$val->role}</td>\n {$admin}\n </tr>";
}
// Prepare navigation bars and admin information
$max = ceil($rows / $hits);
$hitsPerPage = CFunc::getHitsPerPage(array(4, 8, 16), $hits);
$navigatePage = CFunc::getPageNavigation($hits, $page, $max);
$adminAnchors = CUser::isAuthenticated() ? "<p><a href='user_create.php'>Skapa ny användare</a> <a href='user_reset.php'>Återställ användardatabasen</a></p>" : null;
// Put rows in HTML-table with navigation bars
$htmlTable = <<<EOD
<div class='dbtable'>
<div class='rows'>{$rows} träffar. {$hitsPerPage}</div>
<table>
{$tr}
</table>
<div class='pages'>{$navigatePage}</div>
{$adminAnchors}
</div>
EOD;
return $htmlTable;
}
/**
* Constructor
*
*/
public function __construct($dbOptions)
{
// Connect to a MySQL database using PHP PDO
$db = new CDatabase($dbOptions);
// Get parameters
$title = isset($_GET['title']) ? $_GET['title'] : null;
$genre = isset($_GET['genre']) ? $_GET['genre'] : null;
$hits = isset($_GET['hits']) ? $_GET['hits'] : 8;
$page = isset($_GET['page']) ? $_GET['page'] : 1;
$year1 = isset($_GET['year1']) && !empty($_GET['year1']) ? $_GET['year1'] : null;
$year2 = isset($_GET['year2']) && !empty($_GET['year2']) ? $_GET['year2'] : null;
$orderby = isset($_GET['orderby']) ? strtolower($_GET['orderby']) : 'id';
$order = isset($_GET['order']) ? strtolower($_GET['order']) : 'asc';
$id = isset($_POST['id']) ? strip_tags($_POST['id']) : (isset($_GET['id']) ? strip_tags($_GET['id']) : null);
// Check that incoming parameters are valid
is_numeric($hits) or die('Check: Hits must be numeric.');
is_numeric($page) or die('Check: Page must be numeric.');
is_numeric($year1) || !isset($year1) or die('Check: Year must be numeric or not set.');
is_numeric($year2) || !isset($year2) or die('Check: Year must be numeric or not set.');
// Get all genres that are active
$sql = '
SELECT DISTINCT G.name
FROM `rm_genre` AS G
INNER JOIN `rm_movie2genre` AS M2G
ON G.id = M2G.idGenre
';
$res = $db->ExecuteSelectQueryAndFetchAll($sql);
$_SESSION['genres'] = $res;
$genres = null;
foreach ($res as $val) {
if ($val->name == $genre) {
$genres .= "{$val->name} ";
} else {
$genres .= "<a href='" . CFunc::getQueryString(array('genre' => $val->name)) . "'>{$val->name}</a> ";
}
}
$genreOptions = "<option value=''>Select genre</option>\n";
foreach ($res as $val) {
$selected = $val->name == $genre ? 'selected' : null;
$genreOptions .= "<option value='{$val->name}' {$selected}>{$val->name}</option>\n";
}
// Prepare the query based on incoming arguments
$sqlOrig = '
SELECT
M.*,
GROUP_CONCAT(G.name) AS genre
FROM `rm_movie` AS M
LEFT OUTER JOIN `rm_movie2genre` AS M2G
ON M.id = M2G.idMovie
LEFT OUTER JOIN `rm_genre` AS G
ON M2G.idGenre = G.id
';
$where = null;
$groupby = ' GROUP BY M.id';
$limit = null;
$sort = " ORDER BY {$orderby} {$order}";
$params = array();
// Select by title
if ($title) {
$where .= ' AND title LIKE ?';
$params[] = $title;
}
// Select by year
if ($year1) {
$where .= ' AND year >= ?';
$params[] = $year1;
}
if ($year2) {
$where .= ' AND year <= ?';
$params[] = $year2;
}
// Select by genre
if ($genre) {
$where .= ' AND G.name = ?';
$params[] = $genre;
}
// Select by id
if ($id) {
$where .= ' AND M.id = ?';
$params[] = $id;
}
// Pagination
if ($hits && $page) {
$limit = " LIMIT {$hits} OFFSET " . ($page - 1) * $hits;
}
// Complete the sql statement
$where = $where ? " WHERE 1 {$where}" : null;
$sql = $sqlOrig . $where . $groupby . $sort . $limit;
$this->res = $db->ExecuteSelectQueryAndFetchAll($sql, $params);
// Get max pages for current query, for navigation
$sql = "\n SELECT\n COUNT(id) AS rows\n FROM \n (\n {$sqlOrig} {$where} {$groupby}\n ) AS Movie\n ";
$res = $db->ExecuteSelectQueryAndFetchAll($sql, $params);
$this->rows = $res[0]->rows;
}
