Count clicks in your MySQL database

With this simple script it’s possible to count and track (country code and IP address) clicks from a visitors which followed a link to one of your link partners or advertisers. This script can be used together with your existing link database or, if you have made some modifications, with an array of links. I used the IP2nation database to get the visitors country code to store this code with the other information. We use the server variable HTTP_REFERER to store the URL, where the link was clicked, with the other data. You can use this script to cloak affiliate links too.

Use these SQL statements within your MySQL client:

CREATE TABLE `links` (
  `id` int(11) NOT NULL auto_increment,
  `titel` varchar(75) NOT NULL default '',
  `naam` varchar(35) NOT NULL default '',
  `url` varchar(150) NOT NULL default '',
  `description` text NOT NULL,
  PRIMARY KEY  (`id`)
) TYPE=MyISAM;

CREATE TABLE `clicks` (
  `id` int(10) NOT NULL auto_increment,
  `link_id` int(10) NOT NULL default '0',
  `visitor_ip` varchar(15) NOT NULL default '',
  `click_at` datetime NOT NULL default '0000-00-00 00:00:00',
  `country` char(2) NOT NULL default '',
  `on_page` varchar(50) NOT NULL default '',
  PRIMARY KEY  (`id`)
) TYPE=MyISAM;

Install in the same way the data from ip2nation tables

The PHP code snippet

Create a script past this code between the PHP tags.

// place here your database connection code

if (isset($_GET['id']) && intval($_GET['id']) > 0) {
    $delay = 12*3600; // change here the number of hours how often a unique click must be counted
    $sql_check = sprintf("SELECT COUNT(*) AS test FROM clicks WHERE link_id = %d AND visitor_ip = '%s' AND UNIX_TIMESTAMP(click_at) + %d > UNIX_TIMESTAMP(NOW())", $_GET['id'], $_SERVER['REMOTE_ADDR'], $delay);
    if (mysql_result(mysql_query($sql_check), 0, "test") == 0) {
        $country_sql = "SELECT country FROM ip2nation WHERE ip < INET_ATON('".$_SERVER['REMOTE_ADDR']."') ORDER BY ip DESC LIMIT 0,1";
        $country = mysql_result(mysql_query($country_sql), 0, "country");
        $sql_insert = sprintf("INSERT INTO clicks (link_id, visitor_ip, click_at, country, on_page) VALUES (%d, '%s', NOW(), '%s', '%s')", $_GET['id'], $_SERVER['REMOTE_ADDR'], $country, $_SERVER['HTTP_REFERER']);
        mysql_query($sql_insert);
    }
    $sql_url = sprintf("SELECT url FROM link_table WHERE id = %d", $_GET['id']);
    $url = mysql_result(mysql_query($sql_url), 0, "url");
    header("Location: ".$url);
    exit;
} else {
    header("Location: http://www.yourwebsite.com/");
    exit;
}

How-to use the script in your web page?

The script above is your link target, you need to create links like:

<a href="http:domain.com/myclickscript.php?id=34">Click this link please</a>

Parent pages filter function your WordPress admin section

If your WordPress Blog has a lot of pages (not posts) it becomes very hard to select them in the  pages list (WordPress back-end). A select menu with all the parent (top) pages would be a great and useful filter function (like the categories filter in the posts section).

Check this comment for a modified function which is able to filter grouped products in WooCommerce.

The function below will add the select menu with the parent pages as a filter:

function fws_admin_posts_filter( $query ) {
    global $pagenow;
    if ( is_admin() && $pagenow == 'edit.php' && !empty($_GET['my_parent_pages'])) {
        $query->query_vars['post_parent'] = $_GET['my_parent_pages'];
    }
}
add_filter( 'parse_query', 'fws_admin_posts_filter' );

function admin_page_filter_parentpages() {
    global $wpdb;
    if (isset($_GET['post_type']) && $_GET['post_type'] == 'page') {
		$sql = "SELECT ID, post_title FROM ".$wpdb->posts." WHERE post_type = 'page' AND post_parent = 0 AND post_status = 'publish' ORDER BY post_title";
		$parent_pages = $wpdb->get_results($sql, OBJECT_K);
		$select = '
			<select name="my_parent_pages">
				<option value="">Parent Pages</option>';
		$current = isset($_GET['my_parent_pages']) ? $_GET['my_parent_pages'] : '';
		foreach ($parent_pages as $page) {
			$select .= sprintf('
				<option value="%s"%s>%s</option>', $page->ID, $page->ID == $current ? ' selected="selected"' : '', $page->post_title);
		}
		$select .= '
			</select>';
		echo $select;
	} else {
		return;
	}
}
add_action( 'restrict_manage_posts', 'admin_page_filter_parentpages' );

How-to use this PHP code

Open the file named “functions.php” which is inside your themes directory and copy the code into this file and re-visit the pages section.

This function as based on some code I found at WP-Snippets.

Tip! If you need more or better filter options, try Admin Columns. You need to buy the PRO version to use all features, but it’s worth any penny!

Optimize Apache and MySQL for a 256MB VPS

For small websites or not so popular WordPress blogs a small VPS with only 256MB of RAM should be enough. If you’ve followed this guide to install a Ubuntu web server you need to optimize your server a little bit.

Start installing MySQLtuner

Download the Perl script to your (admin) home directory:

wget http://mysqltuner.pl/mysqltuner.pl

Create also a file nano .my.cnf and add this code:

[client]
user=someusername
pass=thatuserspassword

After running MySQLtuner script perl mysqltuner.pl you should get this warning:

Reduce your overall MySQL memory footprint for system stability

To resolve this an other memory related issues we need to optimize the MySQL database settings. Continue reading Optimize Apache and MySQL for a 256MB VPS

SSH Authentication without entering a password

Follow the instructions below to create and install a SSH public key on your remote server. After you followed these steps your able to login via SSH without entering a password. This is extremely useful for backups with rsync or for other automatic tasks you like to execute.

  1. Change to your user’s home directory (local system)
    cd ~
  2. create a pair of private keys
    ssh-keygen -f .ssh_key -t rsa -N ''
  3. *** upload the public key to the remote system using rsync via SSH
    rsync -e ssh ~/.ssh_key.pub user@YourServer:key1.pub
  4. login to your server via SSH using the command line or Putty
    ssh user@YourServer

    (you need to enter your password too)

  5. check if the .ssh directory exists, if not create it
    mkdir .ssh
  6. add the public key to the user authorized key files
    cat key1.pub >> .ssh/authorized_keys
  7. remove the uploaded key from your home directory (remote system)
    rm key1.pub
  8. test your new “private” connection
    rsync -avz -e "ssh -i .ssh_key" "someFolder" user@YourServer

Now you’re able to access the remote system (YourServer) from your local system without using a password. This tutorial is for Linux only.

*** UPDATE: 12th december 2011

If you can’t create a persistent SSH connection to you backup server this way you need a small workaround:

After the creation of your “local” keys you need to download the “authorized_keys” file from the backup server

rsync -e ssh user@YourServer:.ssh/authorized_keys ~/authorized_keys

(check the exact locations first)

Now add your public key to the downloaded file

cat .ssh_key.pub >> authorized_keys

Upload the modified “authorized_keys” file to the backup server

rsync -e ssh ~/authorized_keys user@YourServer:.ssh/authorized_keys

Remove the downloaded file and test you connection like described in step 8.

How-to create a virtual host in Apache (Ubuntu)

You will find on most Linux based web hosts a directory which is called “sites-available” (inside /etc/httpd/ or /etc/apache2/). Create a configuration file with the name “domain.com” for your host and add this code:

ServerName domain.com
ServerAlias www.domain.com
ServerAdmin webmaster@localhost
DocumentRoot /home/user/domain.com/public_html/
ErrorLog /var/log/apache2/domain.com-error.log
# Possible values include: debug, info, notice, warn, error, crit, alert, emerg
LogLevel warn
CustomLog /var/log/apache2/domain.com-access.log combined

Create the directory for your web host and activate the host with a2ensite domain.com and reload your apache engine.