Skip to content

MySQL

Responsive Pagination with PHP, MySQL and Bootstrap

Share Button

 

Responsive Pagination with PHP, MySQL and Bootstrap

Most of the websites deals with enormous amount of content that of course somehow must be displayed to the users. If a website would need to fetch from database and display thousands of records(images, video links etc) on one page it would not be easy to digest, especially if there would be thousands of queries at the same time. Besides, no one want to see all to content on one , endless to scroll page.

sharemyweb_php_mysql_pagination

Paging allows to display only the records required / requested by a user. So instead of putting all results on one page, the content spread over several pages. It makes it much easier to read and of course, the content is fetched from database only when user request it by clicking on appropriate page.

Download

Pagination is possible thanks to MySQL ‘LIMIT’ and ‘OFFSET’.

‘LIMIT’ clause, basically specifies the number of records to be returned. So we can for example return only records from 1-26 and no more. Or records from 1-15. Simple as that.

‘OFFSET’ clause on the other hand allows to specify from which reord we would like to start. For example, we could start from record 7 or 9 or any other record we wish.

I came across many pagination classes that sometimes were to long winded and messy.That’s why I’ve decided to write a simple php pagination class that can be understand and implemented by anyone.

Throughout the code I have implemented appropriate explanations so you’ll be able to adjust the code to your needs if needed. Also, I’ve made the pagination demo responsive, so you can test in on desktop, laptops, tables and mobile devices.

sharemyweb_php_mysql_pagination_small_screen

All you need to do is:

– Download .zip file (Download button provided below)
– Create database ‘pagination’ in MySQL
– Import pagination.sql(perhaps via phpMyAdmin)
– Change your Database Credentials in dbConfig.php
– Open pagination demo files in your browser and test it ūüėČ

If you have already downloaded .zip, you’re ready to go.

Create MySQL database with:

CREATE DATABASE IF NOT EXISTS pagination;

When you already have your database in place, download pagination.sql via phpMyAdmin

  1. Click the name of newly crated database – > pagination
  2. Click on import to start the import process
  3. Browse for the pagination.sql and import it….. Done!!!!

sharemyweb_php_mysql_pagination_php_my_admin

 

sharemyweb_php_mysql_pagination_image_table

Change MySQL database credentials in dbConf.php file. You’ll only need to specify your database user and password. Then open the browser and open first(index.php) page.

sharemyweb_php_mysql_pagination_content

Pagination demo consists of following files:

sharemyweb_php_mysql_pagination_files_folders

 

Download

dbConf.php

This file includes all MySQL credentials necessary for database connection like. You should open the file and change DB user and password so the successful connection to MySQL database can be established. It’s purely, up to you, but I prefer to keep database credentials in separate file.

database.php

It’s a simple database class that defined method to establish MySQL connection, and fetch the data.

paination.php

This file defines the pagination class. It basically says how the pagination functionality is set up and how it should work. I’ve applied comments and explanations under each single method, so you won’t have any bigger problem with understanding.

First thing that you’ll notice are declared variables like links(first, next, previous, last), current page and so on. Same regards to the methods: There are methods that defines, previous or next page link, finding records using ‘LIMIT’ & ‘OFFSET’ clauses… simple as that.

Pagination class also consist of constructor that takes 3 arguments: current page, items/images displayed per page, total number of items. Current pages keeps track of current page(via $_GET variable), items per page allow to display only specific number of items per page and total number of items count all items in db.

Pagination class also specifies how many pages are to be displayed before and after current/active page.

<?php

/* 
    Pagination Class 1.1
    Developed by 'Kwacho', 05/2016
    info@sharemyweb.com     
*/
class Pagination extends MySQLDatabase{
 
//############################# 
//###   DECLARE VARS  ######### 
//#############################
    
    // Current page number
    public $current_page;
    
    // items displayed per page
    public $per_page;
    
    //total number of all items
    public $total_count;
    
    //number of links displayed after current page link
    public $links_after;
    
    //number of links displayed before current page link
    public $links_before;
    
    // Number of pages after a $current_page
    public $min_pages;
    
    // Number of pages before a $current_page
    public $max_pages;
    
    // First page link name
    public $first_link;
    
    // Last page link name
    public $last_link;
    
    // Next page link name
    public $next_link;
    
    // Previous page link name
    public $previous_link;
    
    
//########################################## 
//###   CONSTRUCTOR WITH DEFAULT VALUES  ###  
//##########################################
    
    public function __construct($page=1, $per_page=1, $total_count=0){
        $this->open_connection();
        
        $this->current_page = (int)$page; // current _page
        $this->per_page = (int)$per_page; // 
        $this->total_count = (int)$total_count;
        $this->links_after = 3;
        $this->links_before = 3;
        
        // Button names and custom design
        $this->first_link = '>&laquo&laquo; First';     // <<<< First
        $this->previous_link = '">&laquo; Previous';    // << Previous
        $this->next_link = '">Next &raquo;';            // Next >>
        $this->last_link = '"\">Last &raquo&raquo;';        // Last >>>>
        $this->max_pages=''; 
        $this->min_pages=''; 
    }
    
    // Return current page
    public function getPage($page){
        $this->page = $page;
        return $this->page;
    }
    
    // Return items displayed per page
    public function getPerPage(){
        $this->per_page = 4;
        return $this->per_page;
    }
    
    public function offset(){
        // Assuming 10 items per page:
        // page 1 has an offset of 0    (1-1)*10
        // page 2 has an offset of 20   (2-1)*10
        // in other words, page 2 starts with item 11        
        return ($this->current_page - 1) * $this->per_page;
    }
    
    // Check total pages of items. Total number of items divided 
    // by number of items per page
    public function total_pages(){
        return ceil($this->total_count / $this->per_page);
    }
    
    // Counts all item in databse.
    public function last_item_number(){
        return ceil($this->total_count);
    }
    
    // Previous page = $current_page - 1;
    public function previous_page(){
        return $this->current_page - 1;
    }
    
    // Next page = $current_page + 1;
    public function next_page(){
        return $this->current_page + 1;
    }

    // if previous page is > 1(not = 0) than there is a previous page
    public function has_previous_page(){
        return $this->previous_page() >= 1 ? true : false;
    }
    
    // if next page is not smaller than a total number of pages than there is a next page 
    public function has_next_page(){
        return $this->next_page() <= $this->total_pages() ? true : false;
    }
    
    // return first page link link
    public function getFirstLink(){
        return $this->first_link;
    }
    
    // return previous page link link
    public function getPreviousLink(){
        return $this->previous_link;
    }
    
    // return next page link link
    public function getNextLink(){
        return $this->next_link;
    }
    
    // return last page link link
    public function getLastLink(){
        return $this->last_link;
    }
    
    // Finds requested records of images : DESCENDING order, so the last image added will be displayed as first one
    public function findRecords($per_page, $offset){

        global $database;        
        // Finds records for the page only.
        $sql = "SELECT * FROM images ";
        $sql .= "ORDER BY id DESC ";
        $sql .= "LIMIT {$per_page} ";
        $sql .= "OFFSET {$offset}";
        return ($database->find_by_query($sql)) ? $sql : false;
    }
}

// Instantiate class
$pagination = new Pagination();

?>

painationView.php

Display the pagination links utilizing of already created methods in pagination class(pagination.php). I have applied plenty of comments in this file so you’ll have a good idea of what’s going on and how to use it. Some people put all the paging links into one function and display it, but I preffer to give each link an independent freedom so they can be called separately if needed.

<?php 

/*
    PAGINATION LINKS / BUTTONS DISPLAY
*/

/** Defines number of pages before & after current page /active 
    For example if $pagination->links_after is set to 3, only 3 pages after current page will be available....
    You can change it if Pagination class if you wish
*/
$min = max($page - $pagination->links_before, 1); // there are no pages < 1
$max = min($page + $pagination->links_after, $pagination->total_pages());

echo "<div class='pagination' id='paginationHolder'>
        <div class='bs-example' id='paginationNav'>
            <ul class='pagination paginationList'>";

    if($pagination->total_pages() > 1) {
        
        /*##############################################
        ###          FIRST PAGE LINK / BUTTON        ###
        ##############################################*/
        if($pagination->has_previous_page()){
            echo "<li><a class='pagination_links controLinks' id='first_link' href=index.php?page=1";
        } else {
            echo "<li><a class='pagination_links controLinks disabledLink' id='first_link' disabled ";
        }
            echo $pagination->getFirstLink() ."</a></li> ";
        
        
        /*##############################################
        ###       PREVIOUS PAGE LINK / BUTTON        ###
        ##############################################*/  
        
       if($pagination->has_previous_page()){
        echo "<li><a class='pagination_links controLinks' id='previous_link' href=\"index.php?page=";
        } else {
           echo "<li><a class='pagination_links controLinks disabledLink' id='previous_link' disabled";
        }
        echo $pagination->previous_page();
        echo $pagination->getPreviousLink() ."</a></li> ";
        
        
        
        /*##############################################
        ###            ALL PAGES LINKS               ###
        ##############################################*/
        for($i = $min; $i <= $max; ++$i) {

                echo "<li><a";
if( $i==$page ? $idStyleName='selected' : $idStyleName='regularLink');
                    echo " class='pagination_links' id='{$idStyleName}' href=\"index.php?page={$i}\">{$i}</a></li> ";
        }
        
        /*##############################################
        ###          NEXT PAGE LINK / BUTTON         ###
        ##############################################*/
        if($pagination->has_next_page()){
            
            echo "<li><a class='pagination_links controLinks' id='next_link' href=\"index.php?page="; 
            
        } else {  
            echo "<li><a class='pagination_links controLinks disabledLink' id='next_link' disabled "; 
        }   
            echo $pagination->next_page();
            echo $pagination->getNextLink() ."</a></li> ";
        
        
        /*##############################################
        ###          LAST PAGE LINK / BUTTON         ###
        ##############################################*/
        if($pagination->has_next_page()){
            echo "<li><a class='pagination_links controLinks' id='last_link' href=\"index.php?page=";
        }else{
            echo "<li><a class='pagination_links controLinks disabledLink' id='last_link' disabled";
            }
            echo $pagination->total_pages();
            echo $pagination->getLastLink() ."</a></li> ";      
    }

echo "
        </ul>
    </div>
</div>";

?>

sharemyweb_php_mysql_pagination_content_2

index.php

File that is being read once you access pagination demo files and folders. This files request all other files and is the only file that a user should interact with. index.php. Requests pagination class, sets current page with $_GET[], create an instance of pagination class and display content(images)

<?php
    require_once("header.php");
?>
<body>
<?php

    // Require database
    require_once("database.php");
    // Require pagination class
    require_once("pagination.php");
    
    // Sets the current page for Pagination - Get hte page from $_GET global variable
    $page = !empty($_GET['page']) ? (int)$_GET['page'] : 1;

    // Create new instance of Pagination class with arguments: current page, items per pages and count all items
    $pagination = new Pagination($pagination->getPage($page), $pagination->getPerPage(), $database->num_rows());

    // Find all images according to pagination criterias- items displayed per page and offset
    $images = $database->find_by_query($pagination->findRecords($pagination->getPerPage($page), $pagination->offset()));
 ?>
    
    <div class='row' style='margin: auto; text-align:center;'>
        <div class="span9">
             <a href='http://www.sharemyweb.com' target="_blank"><img class="img-responsive" style='width:400px; margin:0 auto;' alt="ShareMyweb_Logo" src="http://sharemyweb.com/wp-content/uploads/2016/01/Demo_site_logo.png"></a>
        </div>
        <h2 style='text-align:center;'>PHP, MySQL Responsive Pagination</h2>
    <hr>
    </div>
    
    <!-- Display images-->
    <div class='container' style='width:auto; text-align:center;'>
    <?php 
        // foreach image.... display image
        foreach($images as $image){
            echo "<img clas='img display_images' src='images/{$image['image_name']}' alt='{$image['image_desc']}'><br />";          
        }
    ?>
    </div>
    
<?php 
    //Display Pagination
    require_once("paginationView.php");
    // Display footer
    require_once("footer.php");
?>

 

Download

header and footer

I believe that these two sections don’tt need a bigger explanation so I’ll just take few seconds to describe header briefly. This file defines bootstrap links and scripts, website title, css file and small browser icon that appears on your browser tab(it just looks nice).

Images folder

I have included few custom images so you see how the pagination works in the demo. You can change images as you wish, same as you can use this demo as you like.

That’s all. Now, download the pagination demo, play with it and change it as much as you wish. If you have any questions, ideas for improvements, don’t hesitate let me know.

Share Button

PHP, MySQL, OOP Login Form Complete Example – Live Demo

Share Button

This is a complete example of a PHP MySQL Login Form.

Let’s start.

PHP, MySQL login form consists of:

Login, Password inputs & submit button
Custom Logo on top of the form
Error checking
Sessions with redirection to different page
Form Logout

php_mysql_login_form_s_shot

 

Live Demo

 

In order to start we need to create a Table that will store all login details.

CREATE DATABASE sharemyweb;

 

After a database is created, let’s create a table with some example data:

CREATE TABLE userLogin (
id INT(10) AUTO_INCREMENT PRIMARY KEY,
login VARCHAR(30) NOT NULL,
password VARCHAR(30) NOT NULL) 

 

 

Each time we add some new login detail, id will be auto incremented so we don’t need to do it manually.
Next we’re going to apply a MySQL class which will make MySQL connection, run login query, escape values and authenticate login & password input, close database connection.

<?php
/* Require MySQL Credentials : */
require_once("conf.php");

class MySQL{

    public $connection;

    /* Class constructor */
    function __construct() {
        $this->open_connection();
        $this->real_escape_string_exists = function_exists("mysqli_real_escape_string");    
    }

    /* Open MySQL Connection */
    public function open_connection() {
    /* Try connection */
        try {
            $this->connection = new mysqli(DB_SERVER, DB_USER, DB_PASSWORD, DB_NAME);
        }

        /* Catch Exception*/
        catch(Exception $except) {
            die("Unable to connect to Database");
        }
    }
        
    /* Run sql Query */
    public function query($sql){
        $result = mysqli_query($this->connection, $sql);         
        return $result;
    }
    
    /* Find entries by sql query */
    public function find_by_query($query=""){        
        $result=mysqli_query($this->connection,$query);        
        /* Associative array */
        $row=$this->fetch_array($result);
        return $row;
    }

    /* Fetch array and return result */
    public function fetch_array($result){
        return mysqli_fetch_array($result,MYSQLI_ASSOC);
    }
    
    /* Escape unwanted characters like slashes etc. */ 
    public function escape_value($value){
        $value = trim($value);
        /* PHP v4.3.0 or higher */
        if($this->real_escape_string_exists) {
            /* If magic quotes are active */ 
            if(get_magic_quotes_gpc()){ 
                /* Strip slashes */
                $value = stripslashes($value); 
            }
            /* Escape special characters in a string */
            $value = mysqli_real_escape_string($this->connection, $value);
        }
        else{
            // Before PHP 4.3.0
            if(!$this->magic_quotes_active){
                $value = addslashes($value);
            }
        }
        return $value;
    }
    
    /* Login authentications */
    public function authenticate($login="", $password=""){
        /* Sanitize input : Escape unwanted charactets */
        $login = $this->escape_value($login);
        $password = $this->escape_value($password);
        
        /*  Run query - Remember to put LIMIT 1 at the end of query in order to return
            only 1 row 
        */
        $sql = "SELECT login,password FROM userLogin " ;
        $sql .= "WHERE login = '{$login}' ";
        $sql .= "AND password = '{$password}' ";
        $sql .= "LIMIT 1";
        
        $result_array = $this->find_by_query($sql);
        return $result_array;
        
        /* Close connection everytime we don't need to run any more queries */
        $this->close_connection();
    }
    
    /* Close MySQL Connection */
    public function close_connection()
    {
        if(isset($this->connection))
        {
            $this->connection->close();
            unset($this->connection);
        }
    }
    
}// class End
?>

 

Remember! We’ll have to open a conf.php file and change MySQL Database credentials in order to successfully connect to database. you’ll find there a defined constant that’s awaiting your new Database detail. It looks like this:

/**
* The base configurations of MySQL settings.
*
*  Database Constrants
*  If consttants are defined : CONNECT if NOT defined :  DEFINE CONSTANTS
*
*  FILL values below! Otherwise, there'll be no Database Connection.
*/


/** MySQL Server / hostname .... optionally localhost*/
defined("DB_SERVER") ? null : define("DB_SERVER", "127.0.0.1");

/** MySQL Database to be Connect to*/
defined("DB_NAME") ? null: define("DB_NAME", "sharemyweb");

/** MySQL database username */
defined("DB_USER") ? null : define ("DB_USER", "your_DB_user");

/** MySQL database password */
defined("DB_PASSWORD") ? null : define ("DB_PASSWORD", "your_DB_password");

/** Database Charset to use in creating database tables. */
defined('DB_CHARSET') ? null : define('DB_CHARSET', 'utf8');

 

Markup¬† of the form is easy and straightforward so I won’t explain it that much.
You’ll see it when you download full example.

Below code shows an ValidateErrors class that will take care of input errors in the form.
You can add your own errors and call them later on during input validation.
This class in more than helpful and applies easy ways of checking and displaying errors when working with bigger form when you need to validate.
You can find another example usage of this class with some extra REGEX validation here:
LINKG TO THE POST CLASS.

<?php 
/** Simple Error Class *
* - Define Error Messages, Regular Expressions 
* - Log Errors to Error Array * - Sanitize Input 
* - Print/Display Errors 
*  Please Note that this class does it's job, but you may want to do things 
* different way or change something here & there. 
* This Class gives a Patern/shows the way, of how errors could be handled in OOP way. 
* Feel free to modify,use and reffer it anyway you want. 
*/ 

class ValidateErrors{ 

/** Error Array for all Errors Occured during Validation/Check process */ 
public $errors=array(); 

/** 
* validate_errors array stores information about Errors. 
* i.e. 'ERROR NAME' = > 'ERROR MESSAGE'
*/    
    /** Errors types - You can add your ow errors if you wish */
    protected function validationErr(){
        /* Array with error types */
        $this->validate_errors = array(
            "ERR_EMPTY_LOGIN"       =>  "Login field is empty",
            "ERR_EMPTY_PASS"        =>  "Password field is empty",
            "ERR_WRONG_DETAILS"     =>  "Wrong login or password"
        );        
        /** Return Error Array */
        return $this->validate_errors;
    }
        
    /** Logs an Error to Regular Error Array */
    public function logErrors($error){
        $this->errors["$error"] = $this->validationErr()["$error"];
    }
    
    /** Check if there are any errors in Error Array */
    public function noErrors(){
        if(empty($this->errors)){
            return true;
        } else {
            return false;
        }
    }
    
    /** If login field is empty log error to the error array */
    protected function emptyLogin($login){
        if(empty($login) || is_null($login) || !$login){
            /** Log Error to the Errors Array */
            $this->logErrors("ERR_EMPTY_LOGIN");
        }
    }
    
    /** If password field is empty log error to the error array */
    protected function emptyPass($password){
        if(empty($password) || is_null($password) || !$password){
            /** Log Error to the Errors Array */
            $this->logErrors("ERR_EMPTY_PASS");
        }
    }
        
    /** Below function check & display any number of arguments passed 
    *   Each argument should represent the key(Error) of FormErrors
    *   example use: printErrors("ERR_NO_NAME","ERR_REG_NAME")
    */
    public function printErrors(){  
        /**  Returns the number of arguments passed to the function */
        $numOfArgs = func_num_args();  
        /** Returns an array with all arguments passed to the function */   
        $listOfArgs = func_get_args();
        /** Loop through array & display results */
        for ($i = 0; $i < $numOfArgs; $i++) { if(!empty($this->errors)){
                if(array_key_exists($listOfArgs[$i],$this->errors)){
                    echo $this->errors[$listOfArgs[$i]];
                }
            }
        }
    }
    
    /** Pass functions you would like to check for a $_POST['name]/$name */
    public function checkErrors($login,$pass){
        
        /** Check for Conditions/ */
        $this->emptyLogin($login);
        $this->emptyPass($pass);
        
        /** Check for Errors in Error Array */
        if(!$this->noErrors()){
            return false;
        }else{
            return true;
        }   
    }    
} // Class End

 

After we’ve specified all errors types we would like to be displayed when occur, we can call printErrors() which specifies errors to be checked for like this:

<?php
    /* Chosen error types to be printed */
    if(isset($errors))
        $errors->printErrors("ERR_EMPTY_LOGIN","ERR_EMPTY_PASS","ERR_WRONG_DETAILS");
?>

 

Example of form with wrong input:

php_mysql_login_form_s_shot_wrong_input

 

Login & Password used withing Live Demo are:

Login:           sharemyweb

Password:   password

 

As It’s a complete login form example I’m going to show you how to make save a logged user to session.
It’s a very simple way to do so. I’ve created an authorized.php page to which we’ll be redirected after successful log in.

 

php_mysql_login_form_s_shot_authorized
After we are redirected to authorized.php page we won’t be able to go back to login page for as
long as we’re logged in.

 

Checkout Live Demo and  download full code for FREE!

Live Demo

 

Share Button

PHP MySQL Connection With PDO

Share Button

<?php

/**
*
*  MySQL Connection with PDO
*  Fill Values below with your own MySQL Database Credentials
*
*   if constants are defined:       CONNECT
*   if constants are NOT defined:   DEFINE CONSTANTS
*¬†¬† NOTE: It’s a good practice to keep Constants in separate file with appropriate permissions
*      
**/

/** server - 127.0.0.1 or localhost */
defined("DB_SERVERR") ? null : define("DB_SERVER", "127.0.0.1");
/** MySQL database */
defined("DB_NAME") ? null: define("DB_NAME", "your_db_name");
/** MySQL username */
defined("DB_USER") ? null : define ("DB_USER", "your_username");
/** MySQL password */
defined("DB_PASSWORD") ? null : define ("DB_PASSWORD", "your_password");
/* MySQL host & database name parameters */
defined("MYSQL_HOST_AND_DBNAME") ? null : define ("MYSQL_HOST_AND_DBNAME", "mysql:host=".DB_SERVER.";"."dbname=".DB_NAME);
class MySQL_PDO_CONN{

private $connection;

// Constructor
function __construct()
{
$this->open_connection();
}

/* Open MySQL Connection */
public function open_connection()
{

/* Try connection */
try {
$connection = new PDO(MYSQL_HOST_AND_DBNAME, DB_USER, DB_PASSWORD);

/* echo connection message */
echo 'Connected to Database<br/>';

/* Close connection */
$db = null;
}

/* Catch Exception*/
catch(PDOException $e)
{
echo $e->getMessage();
}

}
}// class End
/** Instance of a MySQL_PDO_CONN class 
*   Notice that there's no need to call open_connection() method as it
*   was already called in constructor
*/
$connect = new MySQL_PDO_CONN();

?>

Share Button

Set MySQL root password on OS X

Share Button

Login To MySQL with:

$ mysql -u root

 

Set new password for MySQL User: (‘Kwacho’ is our new password)

mysql> Use mysql;
mysql> UPDATE user SET password=PASSWORD("Kwacho") WHERE user='root';
mysql> FLUSH PRIVILEGES;
mysql> quit

 

Login to MySQL with new Password: (Password is Capitalized!)

$ mysql -u root -p

 

If You’ll get an error of a type:

bash: mysql: command not found

It means that You’ll need to update you $PATH environment variable

 

Try with Following Command: (full path to mysql)

$ /usr/local/mysql/bin/mysql -u root -p Kwacho

 

Share Button