Search / Navigate Function
- Add Search Button (NOT COMPLETE)
- Add Pagination Scheme
- Add Previous/Next and First/Last Navigation links/buttons (NOT COMPLETE)
Add Search Button
Currently, you have to press the ENTER key to invoke the search. While this work, it may not be obvious to some users that they need to press the ENTER key to obtain a search. In this section, we will add a button to make it more obvious that you need to click the Search button to invoke the search.
Need to complete
Add Pagination Scheme
If you know that you may have more records from the database that will fit within the viewing area of the page or device (e.g., smart phone), you may want to implement a paging navigational scheme. To limit the number of records returned from the database, you can use the LIMIT keyword in an SQL statement and then build a navigational scheme to "fetch" additional records from the database as needed. The navigational scheme will also be used to move back and forth to "flip through" the "pages" or records.
Selecting a Subset of Records
The LIMIT keyword is used with two numbers separated by a comma. The first is used to set which record to start from. The second is used to set the maximum number of records to retrieve from the database. For example, LIMIT 0, 11 will return the first 10 records from the database -- zero up to 10 but not including 11. Remember, like array, SQL count start with zero. To return the next ten records, the LIMIT statement would need to read LIMIT 11,11
"SELECT * FROM employees LIMIT 0,11"
- Open the master_page.php and add the following highlighted code in the opening PHP script:
<?php
session_start();
require ("db.php"); define("ROW_PER_PAGE",2);
CODE EXPLANATION:
- The define("ROW_PER_PAGE",2); statement is a CONSTANT that is used to set the number of rows per page.
- In our case, it is 2 because we only have a few records in the database. However, you can change this to whatever value you want depending on
the size of your data. - Add the following highlighted code to the second PHP script:
<!-- SQL SELECT QUERY ------------------------------------------- -->
$row_count = $pagination_statement->rowCount();
<?php
$search_keyword = '';
if(!empty($_POST['search']['keyword'])) {
$search_keyword = $_POST['search']['keyword'];
}
$sql = "SELECT ID, CONCAT(FirstName, ' ' , LastName) AS FullName, Department, Image, Department FROM employees
WHERE FirstName LIKE :keyword OR LastName LIKE :keyword OR Department LIKE :keyword ORDER BY FullName ASC";
/* Pagination Code starts here */
$per_page_html = '';
$page = 1;
$start=0;
if(!empty($_POST["page"])) {
$page = $_POST["page"];
$start=($page-1) * ROW_PER_PAGE;
}
$limit=" limit " . $start . "," . ROW_PER_PAGE;
$pagination_statement = $pdo_conn->prepare($sql);
$pagination_statement->bindValue(':keyword', '%' . $search_keyword . '%', PDO::PARAM_STR);
$pagination_statement->execute();
if(!empty($row_count)){
$per_page_html .= "<div style='text-align:center;margin:20px 0px;'>";
$page_count=ceil($row_count/ROW_PER_PAGE);
if($page_count>1) {
for($i=1;$i<=$page_count;$i++){
if($i==$page){
$per_page_html .= '<input type="submit" name="page" value="' . $i . '" class="btn-page current" data-inline = "true" data-mini="true"/>';
} else {
$per_page_html .= '<input type="submit" name="page" value="' . $i . '" class="btn-page" data-inline = "true" data-mini="true"/>';
}
}
}
$per_page_html .= "</div>";
}
$query = $sql.$limit;
/* Pagination Code end here */
$pdo_statement = $pdo_conn->prepare($query);
$pdo_statement->bindValue(':keyword', '%' . $search_keyword . '%', PDO::PARAM_STR);
$pdo_statement->execute();
$result = $pdo_statement->fetchAll();
?>
CODE EXPLANATION:
- This code is used to determine how many pagination buttons to create.
- Change the variable $sql to $query BELOW the pagination script:
$query = $sql.$limit;
/* Pagination Code end here */
$pdo_statement = $pdo_conn->prepare($query);
- Add the following highlighted code BELOW the search
<input>
field:
WHY: This script was added at the top of the form instead of at the bottom of the form. If you were to add it to the bottom of the form and there were many records returned on the page, a user may not be able to see the pagination buttons especially if in it on a mobile device. If it is at the top, it will always be visible no matter what the number of records returned from the database.
<input type='search' name='search[keyword]' value="<?php echo $search_keyword; ?>"
id='keyword' maxlength='25' placeholder="Enter a search word and press ENTER key">
<?php echo $per_page_html; ?>
- CHECK POINT: Save the file and then preview it in a browser.
You should see the pagination buttons at the top of the form. If you were to click any one of them other than the first one, you will be taken to another page that will show a LIMITED amount of records. In our case, it's two records.
- Add the following highlighted code to the FIRST $per_page_html statement INSIDE of the pagination code:
$per_page_html .= '<input type="submit" name="page" value="' . $i . '" class="btn-page current" data-inline = "true" data-mini ="true" style="background-color:red"/>';
- CHECK POINT: Save the file and preview it in a browser. You should see that the current pagination page is highlighted in a light red code. Click another pagination button to see that it changes on the one that you click.
- Add the following highlighted code:
WHY: To ascertain the total number of records from the database BEFORE the LIMIT is set.
add code here later
- CHECK POINT: Save the file and preview it in a browser. You should see the total number of employees returned from the database and not just the LIMIT amount for the EMPLOYEE TOTAL. In our case 2.
Add Previous/Next and First/Last Navigation links/buttons
To create a more advanced pagination, particularly for large content, you can also add previous/next and first/last links or buttons to the navigation scheme.
Need to complete