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;

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
So the url where google shows the listing of my page will be
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…

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/>';

Get the array sorted in descending order maintaining their indexes


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]);

Leave a Reply

Your email address will not be published. Required fields are marked *