Affiliate Link Keyword Tracking/Rewrite (subid) Script - Having Some Trouble

This seems to be a good script for tracking converting keywords using the subid= option (CJ is just sub= I think) for many affiliate sites. Smaxor posted the script on his blog. Take a look.

You can follow the code, but in short:

It gets the keyword from the url
Checks to see if that keyword is already in the database
If so, it gets the ID for that field
Else, it puts the new keyword in the db and gets the ID
You can then use that ID in the subid= field of links​

The reason to use an id instead of the keyword for the subid is to keep nosy affiliate managers/sites from 'borrowing' your secret, awesomely converting keywords.

Here's the code with some echos for debug. Any suggestions? You can also see the posts I've had with Smaxor on his original post.

// ORIGINAL FROM: http://oooff.com/php-affiliate-seo-blog/php-automation-coding/how-to-track-converting-keywords-for-your-affiliate-marketing-advertising-using-php/

$keyword = trim($_GET['kw']);
$keyword = mysql_real_escape_string($keyword);
echo "keyword grab: $keyword <br>"; 

// Finding the keyword in the database if it's there
$sql = "SELECT * FROM keywords WHERE keyword = '$keyword'" or die(mysql_error());
echo "$sql <br>";
$sql_out = mysql_query($sql) or die(mysql_error());

if($sql_out){

$row = mysql_fetch_assoc($sql_out) or die(mysql_error());
$id = $row['id']; //gets the id associated with the word
echo "id1: $id <br>";
$sql = "UPDATE keywords SET count = count + 1" or die(mysql_error()); // sql to increment the counter for that word
echo "sql2: $sql <br>";
mysql_query($sql) or die(mysql_error());
}

else{

// This chunk puts our new word in the database and grabs it's ID
$sql = "INSERT INTO keywords (keyword) VALUES ('$keyword')" or die(mysql_error());  // sql to insert the new keyword we don't have cataloged
echo "sql3: $sql <br>";
mysql_query($sql) or die(mysql_error());
$id = mysql_insert_id(); // gets the id of the record we just inserted.
echo "id2: $id <br>";
}

PHP:

DB export:

– Table structure for table `keywords`
–

CREATE TABLE `keywords` (
`id` int(11) NOT NULL auto_increment,
`keyword` varchar(200) NOT NULL default ”,
`count` double NOT NULL default ‘1′,
PRIMARY KEY (`id`),
KEY `keyword` (`keyword`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

PHP:

Currently: Fails at adding a new keyword. If I add one manually and test that specific keyword it will assign the ID variable and auto-increment fine.

However, new keywords are not added. It seems to fail just at or after the else statement AND auto increments the wrong/only keyword in the db that I added manually for testing. Odd. Ideas?

If you like the script, thank Smaxer.

 

Hello,

Replace your following code line ....

if($sql_out){

PHP:

with..

$sql_count = mysql_num_rows($sql_out);
if($sql_count > 0){

PHP:

Hope this will solve your issue.
Regards,

 

Thanks!! That part is now working.

It still has a problem though - seems the count of all keywords in the db is incremented with each pageload. So if I have three keywords in the db and they have respective counts of 2, 5, 9 -- on a new page load for an existing keyword in the db they become 3, 6, 10... all incremented. Ideas?

Thanks again!

 

Hello,

That is because you re firing Update query for all the records in the table. Modify your query

$sql = "UPDATE keywords SET count = count + 1" or die(mysql_error()); // sql to increment the counter for that word

PHP:

to..

$sql = "UPDATE keywords SET count = count + 1 WHERE id = '".$id."'" or die(mysql_error()); // sql to increment the counter for that word

PHP:

hope this will solve your issue.

Regards

 

Oops. All working now. Thank you once again hemlata!