Tag Archives: MySQL

My favourite DB, MySQL

Replication

If you are here reading Replication for MySQL, if you know what type of replication is available in other databases, then it may be a bit troubling for you to understand. However, One point will make everything clear.

In MySQL replication is that of the Binary Log, which has user’s commands logged in it.

Generally replication is used when there are many servers to balance the load and have the same data. In such cases, Usually A single master and multiple Slaves are used.

So let us consider we have 3 servers,

  • Server A: The Master Server (192.168.5.100)
  • Server B: The Slave Server no. 1 (192.168.5.101)
  • Server C: The Slave Server no. 2 (192.168.5.102)

The Servers B and C are peers to each other.

We’ll set up replication of the database test. When I say database “test”, it means its all tables are replicated as well. I’ll come up to the specific configurations, etc … but later. I assume we are starting with an empty database.

To setup replication, we need to configure the following

  • Master
    • Binary log
    • …and some configuration
  • Slave: Configuration of the master for the slave

Building a Tag Cloud

You may have come across Del.icio.us Tags, which is commonly referered as a “Tag cloud”
tag cloud

It is extremely easy to build such a cloud, all you need to have is to have tags associated with your posts, comments, blogs, etc.

Consider you have a table of posts called POSTS, and another one of TAGS, something like below.

posts
* post_id
* tag_id
* post_content

tags
* tag_id
* tag_name

We need to get the count of tags, and the no. of times that tag has been associated. The query would be.

select a.tag_name, count(b.post_id) tag_count
from tags a, posts b
where a.tag_id = b.tag_id
group by b.tag_id
order by a.tag_name

This will give us a result like this

AJAX, 15
HTTP, 10
Web, 30
XML, 3

Let us decide a minimum font size for the tag, let us take the value of 8pt as the smallest font possible, All that remains is to calculate the font sizes.

We’ll grab the result set first and get the sum of tag count.

$tags = array();
$tag_sum = 0;
while ($row = mysql_fetch_object($res)) {
  $tags[$row->tag_name] = $row->tag_count;
  $tag_sum = $tag_sum + $row->tag_count;
}

// just make a copy of the tags array
$tags_copy = $tags;

Updating statistics table

We have our array of keywords, with its hit count.
Now is the time to update our Statistics table, keyword_search_stats and search_engine_stats

<?php
echo '<pre>';
//print_r($query_term);
//print_r($query_term_cnt);
//print_r($final);

Run a loop, searching through keyword_search_stats, if the keyword is present.
If it is present, update the hit counter by 1.
If no record is found, then insert the new keyword

for ($i=0; $i<count($final); $i++)
{
    $sql_chk = "select * from keyword_search_stats where keyword = '{$final[$i][0]}'";
    $res_chk = mysql_query($sql_chk);
    if (mysql_num_rows($res_chk) > 0) {
        // row found .... update stats...
        $row_chk = mysql_fetch_object($res_chk);
        $new_count = $row_chk->keyword_count + 1;

        $sql_update = "update keyword_search_stats set keyword_count = $new_count, `update` = '".date("Y-m-d H:i:s")."' where keyword = '{$row_chk->keyword}'";
        mysql_query($sql_update);
    } else {
        // no row found... insert...
        echo ($final[$i][0] .' - ' . $final[$i][1]);
        $sql_ins = "insert into keyword_search_stats(keyword, keyword_count, `update`) values ('{$final[$i][0]}', {$final[$i][1]}, '".date("Y-m-d H:i:s")."')";
        mysql_query($sql_ins);
        echo ('<br/>');
    }
}

print_r($se_cnt);

Update the Search Engine’s statistics

foreach ($se_cnt as $key => $value) {
    $sql_chk = "select * from search_engine_stats where search_engine = '$key'";
    $res_chk = mysql_query($sql_chk);
    if (mysql_num_rows($res_chk) > 0) {
        // row found .... update stats...
        $row_chk = mysql_fetch_object($res_chk);
        $new_count = $row_chk->search_engine_count + 1;

        $sql_update = "update search_engine_stats set search_engine_count = $new_count, `update` = '".date("Y-m-d H:i:s")."' where search_engine = '$key'";
        mysql_query($sql_update);
    } else {
        // no row found... insert...
        echo ($key .' - ' . $value);
        $sql_ins = "insert into search_engine_stats(search_engine, search_engine_count, `update`) values ('{$key}', {$value}, '".date("Y-m-d H:i:s")."')";
        mysql_query($sql_ins);
    }
}

Since’ we’ve updated all the tables, so lets truncate the table.

echo '</pre>';
echo ('<br/> Truncating table view_log <br/>');
mysql_query("truncate table view_log");
?>

Script to make entries

We have our tables in place.
Its time to get with the code.

Lets get all the search engines and their attributes first.

$res_se = mysql_query('select * from search_engines', $conn);
$se = array();
while ($row_se = mysql_fetch_object($res_se)) {
    $se['id'][] = $row_se->se_id;
    $se['name'][] = $row_se->se_name;
    $se['regex'][] = $row_se->se_regex;
}


Lets collect our referers

$sql = 'select view_ref from view_log';
$res = mysql_query($sql, $conn);
$refs = array();
while ($row = mysql_fetch_object($res)) {
    $refs[] = $row->view_ref;
}
mysql_free_result($res);

Before we find out the keywords from the Referers, Let me explain how do you form a regular expression to grab out the keyword.
Let us consider someone searches a keyword “ruturaj” on google.com.
So the url where google shows the listing of my page will be
http://www.google.co.in/search?hl=en&q=ruturaj&btnG=Google+Search&meta=
This will also be the referer.

The most important part of this URL is the string “q=ruturaj”, and then the keyword “ruturaj” from that string.
let us start…

/ Pattern starts..
.*google.* Allow any charecters around string “google”
?q= These will be charecters prefixing the keyword
([^&]*) Start a class, which will allow all characters. But it should not contain any &, which means any other GET query.
.* Allow any trailing characters
/i End the pattern, specifying that is case-insensitive

So the final pattern would be…
/.*google.*?q=([^&]*).*/i

We have the Search Engine Attributes, and the referers, now is the time to apply the regular expression of the search engines to referers and grab out the keyword term.

$keywords = array();
$keyword = '';
$keyword_count = 0;
$se_cnt = array();

for ($i=0; $i<count($refs); $i++) {
   for ($j=0; $j<count($se['id']); $j++) {
       if(preg_match($se['regex'][$j], $refs[$i], $matches))
       {
           $k = strtolower($matches[1]);
           if ( !isset($keywords[$k]) ) { //exists....
               $keywords[$k] = 1;
           } else {
               $keywords[$k] += 1;
           }
           if ( !isset( $se_cnt[$se['name'][$j]] ) ) { //exists....
               $se_cnt[$se['name'][$j]] = 1;
           } else {
               $se_cnt[$se['name'][$j]] += 1;
           }
           //echo "<p>$refs[$i]<br/><b>{$se['name'][$j]}</b> - " . urldecode($matches[1]) . '</p>';
           //echo "<b>{$se['name'][$j]}</b> - " . urldecode($matches[1]) . '<br/>';
           break;
       }
   }
}

Get the array sorted in descending order maintaining their indexes

arsort($keywords);
arsort($se_cnt);

Grab the indexes and the values in arrays…

$query_term = array_keys($keywords);
$query_term_cnt = array_values($keywords);

Finally strip out the url encoding out of the keyword.

$final = array();
for ($i=0; $i<count($query_term); $i++) {
    $final[] = array(urldecode($query_term[$i]), $query_term_cnt[$i]);
}

Creating tables

The first and foremost need is to have access logs, Since in this tutorial, we’ll be logging in MySQL, we need to have a table in which we can maintain a good access log.

For an access log here are the following necessary data fields

  • Accessed URL
  • Referer to the Acessed URL
  • User Agent accessing the document
  • IP of the Client
  • Datetime stamp


Access Log Table
Here is the table view_log

CREATE TABLE `view_log` (
 `view_id` int(11) NOT NULL auto_increment,
 `view_url` varchar(255) NOT NULL default '',
 `view_ref` varchar(255) NOT NULL default '',
 `view_ua` varchar(255) NOT NULL default '',
 `view_ip` varchar(20) NOT NULL default '',
 `view_datetime` datetime NOT NULL default '0000-00-00 00:00:00',
 PRIMARY KEY  (`view_id`)
) TYPE=MyISAM COMMENT='Saves the log of viewing log'

Search Engines Table
Now we need to have a list of the Search Engines that link us to the site. I’ve taken the most popular of them, and the ones that I know..
The most important field that we have in this table is the se_regex, this field stores the regular expression, that will parse out the keyword from the referered URL.

CREATE TABLE `search_engines` (
`se_id` int(11) NOT NULL auto_increment,
`se_name` varchar(255) NOT NULL default '',
`se_regex` varchar(255) NOT NULL default '',
PRIMARY KEY (`se_id`)
) TYPE=MyISAM COMMENT='Search Engines'

Now that we have a table structure in place lets populate some data into it.
I’ll explain the regular expressions for the engines in the next page.

INSERT INTO search_engines VALUES (1,'Google','/.*google.*?q=([^&]*).*/i');
INSERT INTO search_engines VALUES (2,'Yahoo','/.*yahoo.*?p=([^&]*).*/i');
INSERT INTO search_engines VALUES (3,'MSN','/.*msn.*?q=([^&]*).*/i');
INSERT INTO search_engines VALUES (4,'Netscape','/.*netscape.*?search=([^&]*).*/i');
INSERT INTO search_engines VALUES (5,'AOL','/.*aol.*?query=([^&]*).*/i');
INSERT INTO search_engines VALUES (6,'Alexa','/.*alexa.*?q=([^&]*).*/i');
INSERT INTO search_engines VALUES (7,'AltaVista','/.*altavista.*?q=([^&]*).*/i');
INSERT INTO search_engines VALUES (8,'AllTheWeb','/.*alltheweb.*?q=([^&]*).*/i');
INSERT INTO search_engines VALUES (9,'A9','/.*a9.*?search=([^&]*).*/i');
INSERT INTO search_engines VALUES (10,'DMoz','/.*dmoz.*?search=([^&]*).*/i');
INSERT INTO search_engines VALUES (11,'Lycos','/.*lycos.*?query=([^&]*).*/i');
INSERT INTO search_engines VALUES (12,'Terra Lycos','/.*terra.*?query=([^&]*).*/i');
INSERT INTO search_engines VALUES (13,'Alexa','/.*alexa.*?q=([^&]*).*/i');
INSERT INTO search_engines VALUES (14,'Rediff','/.*rediff.*?MT=([^&]*).*/i');

Keyword Statistics Table
This table will hold keyword and its hit counter.

CREATE TABLE `keyword_search_stats` (
`keyword_search_stats` int(11) NOT NULL auto_increment,
`keyword` varchar(255) default NULL,
`keyword_count` int(11) default NULL,
`update` datetime NOT NULL default '0000-00-00 00:00:00',
PRIMARY KEY (`keyword_search_stats`),
KEY `keyword_index` (`keyword`)
) TYPE=MyISAM

I’m creating an index on the keyword column, so that it is easier to search the keywords

Search Engine Statistics Table
This table will hold Search Engine and no. of referals from it.

CREATE TABLE `search_engine_stats` (
`search_engine_stats_id` int(11) NOT NULL auto_increment,
`search_engine` varchar(255) default NULL,
`search_engine_count` int(11) NOT NULL default '0',
`update` datetime default NULL,
PRIMARY KEY (`search_engine_stats_id`)
) TYPE=MyISAM

Search Engine Referer Keyword Tracking

You really want to analyze your source of traffic. Most of the times you install, use some of the free softwares available on the net. But If you are a programmer… You will want to know how to track these visitors, Search engine keywords, etc…

Here I’ll be showing the programmer’s point of view to develop a solution.

To track most of the important aspects of search engine referals, are the HTTP_REFERER and the HTTP_USER_AGENT variables.

I’m assuming you have Apache as the web server and PHP as the scripting language with my favourite MySQL as the database server.

There are two ways that you can track the above content

  • Apache access logs
  • Database logging

Keyword Hits
So the final result would be like

Keyword Hit Count
keyowrd 1 100
keyowrd 2 70
keyowrd 3 60

Search Engine Referers
Search Engine hit counts

Search Engine Ref. Count
Google 100
Yahoo 70
MSN 60

In this tutorial, I’ll be focussing on the MySQL logging. So lets begin with it.

Code for writing the comment to the DB

Now create a new page called ‘addcomment.php’

<?php
$conn = mysql_connect('localhost', 'username', 'password');
mysql_select_db('yourdb');

$comdate = $_POST['comdate'];
$articleid = $_POST['articleid'];
$comsubject = htmlspecialchars(addslashes($_POST['comsubject']));
$comuser = htmlspecialchars(addslashes($_POST['comuser']));
$comtext = htmlspecialchars(addslashes($_POST['comtext']));

//write the query
$sql = "insert into comments(comdate, articleid, comsubject, comuser, comtext)";
$sql .= " values ('" . $comdate . "', " . $articleid . ", '" . $comsubject . "', '" . $comuser . "', '" . $comtext . "');";

if (!@mysql_query($sql)) {
  echo ('We are unable to register your comment, sorry');
} else {
  echo ("Your comment was added successfully<br>");
  echo ("<A HREF="article.php">Back to Articles</A>");
}

mysql_close($conn);
?> 

User Interface for adding comments

Now that we have had some article in our dB, it is now time to show the article to the world. Plus we need to show the comment to them

Let us make a page called as ‘article.php’ and the article is fetched by getting the a url as ‘article.php?articleid=1’ This will fetch the article # 1 and is stored in the DB as articleid=1

If the articleid query is not given then the page has to display all the aritcle listing with the article subject as an link to ‘article.php?article=[the articleid]’

<?php
//if articleid query is not set, show all the article listing
if (!isset($_GET['articleid'])) {

$conn = mysql_connect('localhost', 'username', 'password');
mysql_select_db('yourdb');

$sql = "select * from articles;";
$result = mysql_query($sql);

echo('<table boder=0 cellpadding=3 cellspacing=1>');
while ($row = mysql_fetch_assoc($result)) {
  echo('<tr>);
  echo('<td>);
  echo('<a href="aricle.php?articleid='.$row['articleid'].'"><b>'.$row['articlesubject'].'</b></a>');
  echo('<tr>);
  echo('<td>);
  echo($row['articlesubject']);
}
echo('</table>')
mysql_close($conn);

} else {

$conn = mysql_connect('localhost', 'username', 'password');
mysql_select_db('yourdb');

$sql = "select * from articles where articleid=" . $_GET['articleid'] . ";";

if (!($result = @mysql_query($sql))) {
  echo('Sorry We are not able to process your request');
} else {
  //now display the data
  $row = mysql_fetch_assoc($result);
  echo ("<p><b>" . nl2br(htmlspecialchars($row['articlesubject'])) .
"</b>");
  echo ("<p>".nl2br(htmlspecialchars($row['articletext']))."<p>");
?>

  Add Comment
  <table>
  <form name='comment' action'addcomment.php' method=post >
  <input type=hidden name='comdate' value="<?php echo(date("Y-m-d H:i:s"))?>">
  <input type=hidden name='articleid' value="<?php echo($_GET['articleid'])?>">
  <tr><td>
  Subject<input type=text name='comsubject' />
  <tr><td>
  User<input type=text name='comuser' />
  <tr><td>
  <textarea name='comtext' rows=15 cols=50></textarea>
  <input type=submit value='Post Comment' />
  </form>
  </table>

<?php
  //now show all the comments for this article
  
  $sql = "select * from comments where articleid=" . $_GET['articleid'] . ";";   $result = mysql_query($sql);
  if (mysql_num_rows($result)<1) {
    echo('No comments posted for this article');
} else {
    while ($row = mysql_fetch_assoc($result)) {
      echo('<p>');
      echo( htmlspechialchars(stripslashes($row['comsubject'])) . ' by ' . htmlspechialchars(stripslashes($row['comuser'])) . ' on ' date("d M Y h:i a", strtotime($row['comdate'])) . '<br>');
      echo( nl2br(htmlspechialchars(stripslashes($row['comtext']))) );
    }
  }
  mysql_close($conn);
  }

}
?>

This now completes the code for viewing the article, the page also displays a form which as a subject textbox, a username textbox and a textarea for comment text.
When user clicks the ‘Post Comment’ button, the data is ‘post’ fed to the page ‘addcomment.php’