“Seo tips and tricks” was not due til November, but this one just popped up today. I was looking for a tool to build a rapid keyword set for a blog, without doing extensive keyword research. The blackhat ‘scraper’ scripts I found come up with ‘michigan seo’ far too many times :)
How to grab keyword sets from 7Search
I want a set of keywords as blog categories to write a blog that contains material with the most popular keywords covering the whole active search pattern set. A nice tool for that is 7Search‘s keyword tool.
It has a captcha protection, you have to answer it once and then you can query as much as you like, it shows last months top 100 search patterns with that keyword and the search volumes :
| seo |
1,991,112 | $0.34 | $0.33 | $0.21 | $0.09 | $0.08 |
| seo web design | 8,085 | $0.07 | $0.02 | $0.01 | ||
| seo tool | 2,647 | $0.05 | $0.02 | $0.02 | $0.01 |
As I am extremely lazy and hate typing data, I’ll make a quick script to cut and paste that list and have it magically transformed in a wordpress blog category list.
It turned out to be a simple one page program : I do a query on a keyword, select the result table area of the 7Search page (with the mouse : ) and paste it as text into my own form’s textarea, add the main key, and post it.
From the $_POST array, I take the textarea input and explode it on linebreaks. To get the keywords, I check for the first occurrence of 0-9, take the part that comes before it, and have the keywords.
In this function I test for the first 0-9. Had I stopped at the first number and started at 0, I would get thrown out of the loop if there is any 0 in the line (or 1, 2, 3…), regardless of there being any number before the first 0 :
-
$pos = strpos($linesarr[$x], $i);
-
if($pos >0) {
-
if($pos< $minpos) {
-
$mykeys = substr($linesarr[$x],0,$minpos);
-
echo $mykeys;
-
break;
-
}}}
So I test for the first 0 and store the position in minpos, then test for the first 1, 2, 3…, if it comes before the first 0, minpos is set to the lowest position.
-
$lines=$_POST['textarea'];
-
$linesarr = explode("\r\n", $lines);
-
-
for ($x=0;$x<count ($linesarr);$x++) {
-
//set minpos to the length of the line
-
$minpos=strlen($linesarr[$x]);
-
-
//check numbers 0-9
-
for($i=0;$i<10;$i++) {
-
//get position of first number $i in the line
-
$pos = strpos($linesarr[$x], $i);
-
if($pos >0) {
-
if($pos< $minpos) $minpos=$pos;
-
}
-
}
-
-
//is minpos smaller than the length of the line ? then its valid data
-
if($minpos<strlen($linesarr[$x])) {
-
$mykeys = substr($linesarr[$x],0,$minpos);
-
echo $mykeys;
-
}
-
}
That way I always get the first number in the line and the part before it is the whole keyword text.
I also want the search volumes, which is the first full string after the keywords up till the first $-dollar sign. The minpos counter is already at the start digit of the volume. I can get the position of the first dollar sign, and trim off the blanks.
-
//volume is the is at the start of the string after minpos
-
$volstr = trim(substr($linesarr[$x], $minpos));
-
//and before the first dollar sign
-
$volcut = strpos($volstr, "$");
-
//it contains "," : 9,111,222 so filter out the nonsense for mysql :
-
$vol = preg_replace('/,/', '', trim(substr($volstr, 0, $volcut)));
-
-
if($minpos<strlen ($linesarr[$x])) {
-
$mykeys = substr($linesarr[$x],0,$minpos);
-
echo $mykeys."_".$vol;
-
}
[After this I stuff the data in a mysql table `sevencats`].
How to add a keyword list to wordpress as categories
Let's add the keywords to a wordpress blog as categories. WordPress has a very simple function for it wp_insert_term in the taxonomy.php file.
In wpmu you do first have to pick the target blog, as you work on a blogs tableset, wp1_, wp2_ etcetera and if you start it up you get the admin users main blog as active tableset. If you want to add data like categories in another blogs taxonomy table you have to switch to that table set first.
-
function connect_data() {
-
$DB_USER = "";
-
$DB_PASSWORD = "";
-
$DB_HOST = "";
-
$DB_DATA = "";
-
$link = mysql_connect($DB_HOST, $DB_USER, $DB_PASSWORD) or $error = mysql_error();
-
if (!$link) {
-
return $error;
-
}
-
mysql_select_db($DB_DATA, $link) or $error = mysql_error();
-
return $link;
-
}
-
-
//link
-
$cats=connect_data();
-
-
//get array with categories
-
$categories=array();
-
-
$qry="SELECT cat FROM `sevencats`";
-
$lst=mysql_query($qry, $cats) or die('list error '.mysql_error());
-
while($row=mysql_fetch_assoc($lst)) {
-
$categories[]=$row['cat'];
-
}
-
//close db connection
-
mysql_close($cats);
-
-
//open wordpress connection
-
include_once('wp-config.php');
-
include_once('wp-includes/wp-db.php');
-
include_once('wp-includes/taxonomy.php');
-
-
//select target blog by id
-
switch_to_blog(3);
-
-
//insert categories
-
for ($i=0;$i<count ($categories);$i++) {
-
wp_insert_term($categories[$i], 'category');
-
}
-
-
//switch back to users main blog
-
restore_current_blog();
For a normal wordpress install you'd not have to switch blogs :
-
//open wordpress connection
-
include_once('wp-config.php');
-
include_once('wp-includes/wp-db.php');
-
include_once('wp-includes/taxonomy.php');
-
-
//insert categories
-
for ($i=0;$i<count ($categories);$i++) {
-
wp_insert_term($categories[$i], 'category');
-
}
That gets me the top 100 searches of last month as categories for my new blog all. You can fiddle with it a bit and only pick searches with a volume above 2000 monthly searches (just in case you want to go scraping and only want material that gets you in the serp pages for the high volume search terms).
Next edition : Red Hat Seo (with jingle bells) the Christmas Special :)