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");
?>

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.