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; }