Archive for the ‘MySQL’ Category

Completely customisable PHP pagination class

Monday, January 12th, 2009

If you need to paginate your database results quickly and reliably then this could be the class for you. It allows you complete access to all attributes of the pagination, from the link templates to the results padding, and auto querying.

Simple pagination class download


/*******************************************************************************
*                                  Pagination class                            *
*                             Created: 12th January 2009                       *
*                             Updated: 16th Octember 2011                      *
*                         ©Copyright Jay Gilford 2009 - 2011                   *
*                              http://www.jaygilford.com                       *
*                            email: jay [at] jaygilford.com                    *
*******************************************************************************/
 
class pagination
{
    ################################
    # PRIVATE VARS - DO NOT ALTER  #
    ################################
    private $_query = '';
    private $_current_page = 1;
    private $_padding = 2;
    private $_results_resource;
    private $_output;
 
    ################################
    #       RESULTS VARS           #
    ################################
    public $results_per_page = 10;          #Number of results to display at a time
    public $total_results = 0;              #Total number of records
    public $total_pages = 0;                #Total number of pages
 
    public $link_prefix = '/?page=';        #String for link to go before the page number
    public $link_suffix = '';               #String for link to go after the page number
    public $page_nums_separator = ' | ';    #String to go between the page number links
 
    ################################
    #      ERROR HOLDING VAR       #
    ################################
    public $error = null;
 
    ################################
    # PAGINATION TEMPLATE DEFAULTS #
    ################################
    public $tpl_first = '« | ';
    public $tpl_last = ' | » ';
 
    public $tpl_prev = '‹ | ';
    public $tpl_next = ' | › ';
 
    public $tpl_page_nums = '{page}';
    public $tpl_cur_page_num = '{page}';
 
    /**
     * In the above templates {link} is where the link will be inserted and {page} is
     * where the page numbers will be inserted. Other than that, you can modify them
     * as you please
     *
     * NOTE: You should have a separator of some sort at the right of $tpl_first and
     * $tpl_prev as above in the defaults, and also have a separator of some sort
     * before the $tpl_next and $tpl_last templates
     **/
 
 
    ##################################################################################
 
 
    public function __construct($page, $query)
    {
        #Check page number is a positive integer greater than 0 and assign it to $this->_current_page
        if ((int)$page > 0)
            $this->_current_page = (int)$page;
 
        #Remove any LIMIT clauses in the query string and set if
        $query = trim(preg_replace('/[\s]+LIMIT[\s]+\d+([\s,]*,[^\d]*\d+)?/i', '', $query));
        if (empty($query)) {
            return false;
        } else {
            $this->_query = $query;
        }
    }
 
    /**
     * pagination::paginate()
     *
     * Processes all values and query strings and if successful
     * returns a string of html text for use with pagination bar
     *
     * @return string;
     */
    public function paginate()
    {
        $output = '';
 
        #########################################
        # GET TOTAL NUMBER OF RESULTS AND PAGES #
        #########################################
        $result = mysql_query($this->_query);
        if (!$result) {
            $this->error = __line__ . ' - ' . mysql_error();
            return false;
        }
        $this->total_results = mysql_num_rows($result);
        $this->total_pages = ceil($this->total_results / $this->results_per_page);
 
        ########################
        # FREE RESULT RESOURCE #
        ########################
 
        ################################
        # IF TOTAL PAGES <= 1 RETURN 1 #
        ################################
        if ($this->total_pages <= 1)
        {
        	$this->_results_resource = $result;
			$this->_output = '1';
			return $this->_output;
        }
 
        mysql_free_result($result);
 
        ###################################################
        # CHECK CURRENT PAGE ISN'T GREATER THAN MAX PAGES #
        ###################################################
        if ($this->_current_page > $this->total_pages)
            $this->_current_page = $this->total_pages;
 
        ######################################
        # SET FIRST AND LAST PAGE VALUES AND #
        # ERROR CHECK AGAINST INVALID VALUES #
        ######################################
        $start = ($this->_current_page - $this->_padding > 0) ? $this->_current_page - $this->
            _padding : '1';
        $finish = ($this->_current_page + $this->_padding <= $this->total_pages) ? $this->
            _current_page + $this->_padding : $this->total_pages;
 
        ###########################################
        # CREATE LIMIT CLAUSE AND ASSIGN TO QUERY #
        ###########################################
        $limit = ' LIMIT ' . ($this->results_per_page * ($this->_current_page - 1)) .
            ',' . $this->results_per_page;
        $query = $this->_query . $limit;
 
        #############################################
        # RUN QUERY AND ASSIGN TO $_result_resource #
        #############################################
        $result = mysql_query($query);
        if ($result === false) {
            $this->error = __line__ . ' - ' . mysql_error();
            return false;
        }
        $this->_results_resource = $result;
 
        ###########################################
        # ADD FIRST TO OUTPUT IF CURRENT PAGE > 1 #
        ###########################################
        if ($this->_current_page > 1) {
            $output .= preg_replace('/\{link\}/i', 'href="' . $this->link_prefix . '1' . $this->
                link_suffix . '"', $this->tpl_first);
        }
 
        ##########################################
        # ADD PREV TO OUTPUT IF CURRENT PAGE > 1 #
        ##########################################
        if ($this->_current_page > 1) {
            $output .= preg_replace('/\{link\}/i', 'href="' . $this->link_prefix . ($this->
                _current_page - 1) . $this->link_suffix . '"', $this->tpl_prev);
        }
 
        ################################################
        # GET LIST OF LINKED NUMBERS AND ADD TO OUTPUT #
        ################################################
        $nums = array();
        for ($i = $start; $i <= $finish; $i++) {
            if ($i == $this->_current_page) {
                $nums[] = preg_replace('/\{page\}/i', $i, $this->tpl_cur_page_num);
            } else {
                $patterns = array('/\{link\}/i', '/\{page\}/i');
                $replaces = array('href="' . $this->link_prefix . $i . $this->link_suffix . '"', $i);
                $nums[] = preg_replace($patterns, $replaces, $this->tpl_page_nums);
            }
        }
        $output .= implode($this->page_nums_separator, $nums);
 
        ##################################################
        # ADD NEXT TO OUTPUT IF CURRENT PAGE < MAX PAGES #
        ##################################################
        if ($this->_current_page < $this->total_pages) {
            $output .= preg_replace('/\{link\}/i', 'href="' . $this->link_prefix . ($this->
                _current_page + 1) . $this->link_suffix . '"', $this->tpl_next);
        }
 
        ############################################
        # ADD LAST TO OUTPUT IF FINISH < MAX PAGES #
        ############################################
        if ($this->_current_page < $finish) {
            $output .= preg_replace('/\{link\}/i', 'href="' . $this->link_prefix . $this->total_pages . $this->link_suffix . '"', $this->
                tpl_last);
        }
 
        $this->_output = $output;
        return $output;
    }
 
 
    /**
     * pagination::padding()
     *
     * Sets the padding for the pagination string
     *
     * @param int $val
     * @return bool
     */
    public function padding($val)
    {
        if ((int)$val < 1)
            return false;
 
        $this->_padding = (int)$val;
        return true;
    }
 
 
    /**
     * pagination::resource()
     *
     * Returns the resource of the results query
     *
     * @return resource
     */
    function resource()
    {
        return $this->_results_resource;
    }
 
 
    /**
     * pagination::__tostring()
     * returns the last pagination output
     *
     * @return string
     */
    function __tostring()
    {
        if (trim($this->_output)) {
            return trim($this->_output);
        }else{
        	return '';
        }
    }
}

Instructions on class usage:
To create an instance of the class, call it with your current page and the query you want to run
$paginator = new pagination($page_num_variable, 'SELECT * FROM `table_name`');
After that, you need to set up any of the parameters for the class such as
$paginator->results_per_page = 15;
$paginator->padding(5);
$paginator->link_prefix = '/results/page/';
$paginator->link_suffix = '/';
$paginator->page_nums_separator = ' -=- ';

Once you have done that, you can call the paginate method
NOTE: you must be connected to your database in order to run this method as it uses the mysql_query function.
The paginate method returns the string of html for you to insert and sets the $pagination->resource() to be the resource id of the query that is run for the pagination

You can use either the returned paginate() string or echo the variable name of the class and it will generate the pagination
So either$page_links = $paginator->paginate();
And then
echo $page_links; wherever you want the links to go.

Alternatively you can use
$paginator->paginate();
and then
echo $paginator;

When you are using mysql_fetch_array() or mysql_fetch_assoc to get your data from your database, you simply need to replace your old resource handle with $paginator->resource();
Example:

while($row = mysql_fetch_assoc($paginator->resource()))
{
    echo $row['field_name'];
}

If you have any questions, bugs or suggestions regarding this class, feel free to contact me either by comment or via one of the contact methods in the contact section

Jay

sprintf and mysql_real_escape_string all in one function

Wednesday, October 8th, 2008

Well as many php developers will know, there is the arduous task of having to sanitize all of your data before actually being able to add it to your queries for running in MySQL. So I decided to make a small function that would basically be a clone of the sprintf function, with the added bonus of running the mysql_real_escape_string on all of the arguments passed to it

function mressf()
{
    $args = func_get_args();
    if (count($args) < 2)
        return false;
    $query = array_shift($args);
    $args = array_map('mysql_real_escape_string', $args);
    array_unshift($args, $query);
    $query = call_user_func_array('sprintf', $args);
    return $query;
}

You would then call it as you would with your regular sprintf function such as

echo mressf("SELECT * FROM `table` WHERE `name` = '%s' AND `password` = '%s'", 'username', 'pass');

which would return

SELECT * FROM `table` WHERE `name` = 'username' AND `password` = 'pass'

creating random activation links for downloads

Monday, July 21st, 2008

This article is intended for advanced users. It explains the principles behind creating a download activation link that is completely random and will stay active for 48 hours after a payment through paypal for example is made

You are going to need two files for this to work. The first is going to be the file that creates the link, adds it to a database table and sends the link via email. The second is the file that will parse all incoming links for the download script, and if the link is verified as being correct it will proceed to allow a user to download the file

NOTE: I will not be describing the intricacies of email or the payment via paypal in this article, merely the methods by which you will need to follow in order to achieve a link creation and verification

Part 1 – Creating the activation key

This should go in your script after your payment has been accepted. I have also not included mysql connection details and function either ie the mysql_connect function or the close function. This is in case you are working with multiple databases whilst doing this

The rand_text function is explained here

function rand_text(   $min = 10,
                      $max = 20,
                      $randtext = 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890' )
{
    if($min < 1) $min=1;
    $varlen = rand($min,$max);
    $randtextlen = strlen($randtext);
    $text = '';

    for($i=0; $i < $varlen; $i++)
    {
        $text .= substr($randtext, rand(1, $randtextlen), 1);
    }
    return $text;
}

// Some setup values
$tblname = 'tbl_keys'; #table name in database
$keymin = 50; #mimumum key length
$keymax = 80; #maximum key length
$keyurl = 'http://www.domain.com/activate.php?key='; #domain to add key to
$datefield = 'added'; #date field to put insert date into
$keyfield = 'key'; #activation key field
$mailto = 'email@example.com'; #email to send link to
$mailsubject = 'Download activation link'; #email subject line

// create random string
$key = rand_text($keymin,$keymax);
// add it to database with current date
$query = "INSERT INTO
              `{$tblname}`
          SET
              `{$keyfield}` = '{$key}',
              `{$datefield}` = NOW()";
mysql_query($query);

//Add key to activation url template
$keyurl .= $key;

//Create mail message
$message = "Below is your activation link. You have 48 hours in which to use it, after which it will expire

{$keyurl}

Your website name
http://www.yourdomain.com/";

//Mail activation key to the user
mail($mailto, $mailsubject, $message);

So you now have an emailing key generator. Next you will need to make a table in your database (remember to change tbl_keys to the one assigned to $tblname above)

CREATE TABLE `tbl_keys` (
  `id` int(11) NOT NULL auto_increment,
  `added` datetime NOT NULL,
  `key` varchar(100) NOT NULL,
  PRIMARY KEY  (`id`)
)

That's a basic example just for this tutorial. For yours you can add other information such as the download id for the link (ie what the user will download upon clicking the link) plus any other info you wish to store with each link

Part 2 - Creating the key verification and download script

Now we need to create the activate.php script that was in the $keyurl above, to take the key and verify that the key hasn't expired

//Verify a key has been entered
if(!isset($_GET['key']) || strlen($_GET['key'] == 0))
{
	//Redirect to site homepage
	header('Location: /index.php');
}

// Some setup values (same as first script)
$tblname = 'tbl_keys'; #table name in database
$datefield = 'added'; #date field to put insert date into
$keyfield = 'key'; #activation key field

//Assign key to shorter variable for ease of use
$key = $_GET['key'];

//////////////////////////////////////////////////
//CONNECT HERE TO DATABASE USING mysql_connect()//
//////////////////////////////////////////////////

//Remove any nasty characters that might cause SQL Injection
//(removes any characters except a-z and 0-9)
$key = preg_replace('/[^A-Za-z0-9]/','',$key);

//Set up query to run (The 172800 is 48 hours in seconds)
$query = "SELECT
              *
          FROM
              `{$tblname}`
          WHERE
              (unix_timestamp(NOW()) - unix_timestamp(`{$datefield}`)) < 172800
          AND
              `{$keyfield}` = '{$key}'";

//Run the query
$res = mysql_query($query);

//Check that a result was found
if(mysql_num_rows($res) < 1)
{
	//Key not found
	die('

ERROR: KEY INVALID/EXPIRED

'); }else{ //////////////////////////////////////////////////////// //INSERT YOUR CODE HERE FOR WHAT HAPPENS IF THE KEY IS// //CORRECT AND HASNT EXPIRED // //////////////////////////////////////////////////////// }

All that's left is for you to add your mysql connection and also your code for what to do if the link is valid in the above code

If you have any suggestions on how to improve it or any questions, just drop me a line