Pagination with PHP OOP system #3 Create search system, Dynamic SQL Query

Repository

https://github.com/php/php-src

What Will I Learn?

• Basic OOP Class
• SQL Where clause
• Create a search system

Requirements

• Basic HTML
• Basic OOP
• Basic Sql Query
• Localhost (xampp, wampp, or etc)
• PHP >= 5.6

Difficulty

• Intermediate

Tutorial Contents

In this tutorial, we will create a search system on the pagination we have created in the previous tutorial, we will create a parameter query and create a function for the search system. of course, we will make changes to the functions we have made before.

Create is_search() function

We will begin to create a function that will be used to capture the query parameters that exist in the URL with the GET method. in this function, we will create a ternary operator (statement ? true : false).

Example:

public function is_search(){
            return isset($_GET['search']) ? $_GET['search'] : ' '  ;
        }

• The idea is that we will check whether the query parameter ?search = 'value' exists. If any we will return the value $_GET['search'], but otherwise we will return the empty string ' '.

Use is_search()function

We will add the is_search() function to another function we have created in the previous tutorial.

• Add query WHERE clause in set_total_records() function
  In the function set_total_records(). I will check by using the is_search()function to find out whether the query parameter ?search = 'value' exists. if exists then we will use query where clause in our SQL query.

Example:

public function set_total_records(){
    $query  = "SELECT id FROM $this->table";
    if($this->is_search()){
        $val    = $this->is_search();
        $query  = "SELECT id FROM $this->table WHERE username LIKE '%$val%'";
    }
    $stmt   = $this->db->prepare($query);
    $stmt->execute();
    $this->total_records = $stmt->rowCount();
}

• We make the query more dynamic by storing it in the $query variable. Its default query is $query = "SELECT id FROM $this->table";
• We use the is_search () function as if statement. If the result is exist(true), then we will change the value of the $query variable to be like this $query = "SELECT id FROM $this->table WHERE username LIKE '%$val%'";. The value of the query parameter ?search='value' contained in the function is_search(). in SQL query you can search data in a table with LIKE% keyword%.
• We use the Where clause query to search for data in a specific table. in this case, we will search the data in the username table. you can change the table name to be more dynamic by making it a parameter.
• Then we set up the SQL query with prepare() method and run it with the execute() method.

Note: in PHP OOP we can access the function in another function by using $this->function_name()

• Add query WHERE clause in get_data() function
  In the function set_total_record() we only summing the total data search results, to display the data is a task of get_data() function. We will do the same thing with what we do in the function set_total_records(). I will check by using the is_search()function to find out whether the query parameter ?search = 'value' exists. if exists then we will use the query where clause in our SQL query.

Example:

public function get_data(){
    $start = 0;
    if($this->current_page() > 1){
        $start = ($this->current_page() * $this->limit) - $this->limit;
    }
    $query  = "SELECT * FROM $this->table LIMIT $start, $this->limit";
    if($this->is_search()){
        $val    = $this->is_search();
        $query  = "SELECT id FROM $this->table WHERE username LIKE '%$val%' $start, $this->limit";
    }
    $stmt = $this->db->prepare($query);
    $stmt->execute();
    return $stmt->fetchAll(PDO::FETCH_OBJ);
}

• We make the query more dynamic by storing it in the $query variable. Its default query is "SELECT * FROM $this->table LIMIT $start, $this->limit";.

• We use the is_search () function as if statement. If the result is exist(true), then we will change the value of the $query variable to be like this $query = "SELECT id FROM $this->table WHERE username LIKE '%$val%' $start, $this->limit";. The value of the query parameter ?search='value' contained in the function is_search().

• The $var variable comes from the function is_search().
  
  

• The Result

We can see the result like this, but as we have seen there are pictures. when we click the page number. we will return to the URL page=pageNumber. Should be when we click page number we will go to URL with query parameter search like this. ?page=pageNumber&search=keyword.
to handle it we need to create check_search() to add a new value in the <a> tag.

Create check_search() function

This function is used to return the parameter query value. before returning the value of this function will check whether the query parameter ?search exists in the URL Browser.

Example:

public function check_search(){
    if($this->is_search()){
        return '&search='.$this->is_search();
    }
    return '';
}

• We can check whether the query parameter ?search exists. If the result is true then we will return the query value of the paramaeter return '&search='.$this->is_search();.

• If the result is false, We return an empty string ' '.
  
  

• Using the check_search () function
  We can use it in the <a> tag by accessing the variable which is the initialization of the pagination class $pagination->check_search();.

Example:

<a href="?page=prev_page()?>"> << </a>
<? for($=i; $i<=$pages; $i++): ?>
   <a class="is_active_class($i) ?>" 
       href="?page=check_search(); ?>"><? echo $i;
</a>
<? endfor; ?>
<a href="?page=next_page()?>"> >> </a>

• The Result
  If there is no an error then we will see the results like this, when we do the element in the browser we can see the tag <a> has a query parameter ?search.

• Using the check_search () function in the next button
  We can also do the same in the next_page () function by adding the check_seacrh () function, to merge it we can use the separator .' '.
  Example:

<a href="?page=next_page().''.$pagination->check_search();?>"> >> </a>

• The Result
  
  
  
• Using the check_search () function in the previous button
  We can also do the same in the prev_page () function by adding the check_seacrh () function, to merge it we can use the separator .' '.

Example:

<a href="?page=next_page().''.$pagination->check_search();?>"> >> </a>

• The Result
  

Thank you for following this tutorial hopefully this tutorial can help you build a more complex pagination system.

Curriculum

Basic OOP, Fetch Data with PDO database
Previous page, Next page , and Active Class in PHP

Proof of Work Done

https://github.com/Ryanalfarisi/paginationOOP