PHP

HOW TO RETRIEVE KEYWORDS FROM URL QUERY STRINGS WITH PHP

As a website owner, it is very important to know what keywords your visitors are using on search engines to get to your site. You can use this information for your Seo efforts as well as for Adwords. If you are using Adwords or other ppc, you can pick up long tail keywords, popular search phrases or negative keywords for your campaigns. This tactic alone can reduce your ppc spend quite drastically.

You can do this by monitoring your server logs but I always found that a bit cumbersome and tiring so I came up with this small script to make my life a lot easier. You will need PHP with Mysql.

If you don't have one yet, you will need a database connection script. Let's name ours open_db.php with the following contents:

open_db.php

<?
$username="your username";
$password="your password";
$database="your database name";
mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");
?>

Create a table called keywords (or whatever name you like) with this script make_table.php as follows:

make_table.php

<?
include("open_db.php"); // your db connection
mysql_query("CREATE TABLE keywords (
id INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY(id),
keywords TEXT,
the_date DATE)")
or die(mysql_error());
?>

Now run make_table.php to create your table.

Now put the following script on your homepage or whatever page your ppc is targetted to.

<?
include("open_db.php");// your db connection
$the_date=date("Y-m-d");
$ref=$_SERVER['HTTP_REFERER'];
$ref=urldecode($ref);
$piece1=explode("q=",$ref);
$piece2=$piece1[1];
$piece3=explode("&",$piece2);
$piece4=$piece3[0];
mysql_query("INSERT INTO keywords (keywords, the_date)
VALUES ('$piece4', '$the_date')");
?>

To view your records, create file called view.php with the following code and run it when required.

view.php

<?
include("open_db.php");// your db connection
$result = mysql_query("SELECT * FROM keywords");
while($row = mysql_fetch_array($result))
{
echo $row['the_date'] . " " . $row['keywords'];
echo "<br />";
}
?>