1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

Please help me with this keyword tracking script!

Discussion in 'PHP' started by Lpdesigner, Dec 29, 2007.

  1. #1
    I'm a total PHP newb. Right now, I'm trying to get this keyword tracking script by Smaxor to work.
    SEMrush
    Here's the link to his code and an explanation of what it does:
    http://oooff.com/php-affiliate-seo-...ur-affiliate-marketing-advertising-using-php/

    The problem I'm having is that if you go to a keyword link like this:
    http://lpdesigner.com/kw/index.php?keyword=pokemongames

    Instead of using an existing ID already in the database, it duplicates itself again. You can look at the link on the page to see what I'm talking about.

    Most likely I'm doing something wrong, I'm a newb at this, so if you guys could help me out - that'd be great :)

    Here's the MySQL:
    CREATE TABLE `kwtracking` (
      `id` int(11) NOT NULL auto_increment,
      `keyword` varchar(200) NOT NULL default '',
      `count` double NOT NULL default '0',
      PRIMARY KEY  (`id`),
      KEY `keyword` (`keyword`)
    ) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=0 ;
    
    Code (markup):
    And here's the PHP code:
    <?
    include("dbconnect.php");
    
    //
    $keyword = trim($_GET['keyword']);
    $keyword = mysql_real_escape_string($keyword);
    
    // Finding the keyword in the database if it’s there
    $sql = "SELECT * FROM kwtracking WHERE keyword = $keyword";
    $sql_out = mysql_query($sql);
    
    if($sql_out){
    
    $row = mysql_fetch_assoc($sql_out);
    $id = $row['id']; //gets the id associated with the word
    $sql = "UPDATE kwtracking SET count = count + 1?"; // sql to increment the counter for that word
    mysql_query($sql);
    
    }else{
    
    // This chunk puts our new word in the database and grabs it’s ID
    $sql = "INSERT INTO kwtracking (keyword) VALUES ('$keyword')"; // sql to insert the new keyword we don’t have cataloged
    mysql_query($sql);
    $id = mysql_insert_id(); // gets the id of the record we just inserted.
    }
    ?>
    
    <h1> here is some text</h1>
    <p> here more text and a <a href="http://destination.com/subid=<? echo $id; ?>">link</a><p>
    Code (markup):
     
    Lpdesigner, Dec 29, 2007 IP
    SEMrush
  2. dataman

    dataman Peon

    Messages:
    94
    Likes Received:
    8
    Best Answers:
    0
    Trophy Points:
    0
    #2
    This...

    if($sql_out){
    Code (markup):
    Doesn't tell you if the query returned a result, it only tells you if the query was executed. It's a bad idea to assume anything! Never do B if A is not what your expecting!

    The update statement must have a "WHERE" clause, so the database knows what "ROW" you are updating the "count" column for!

    <?
    
    // default keyword = '';
    
    $keyword = '';
    
    // constructs, not a function()
    
    include 'dbconnect.php';
    
    // is there a GET variable called keyword
    
    if ( isset ( $_GET['keyword'] ) )
    {
    	$keyword = trim ( $_GET['keyword'] );
    
    	if ( ! empty ( $keyword ) )
    	{
    		$query = mysql_query ( "SELECT id FROM kwtracking WHERE keyword = '" . mysql_real_escape_string ( $keyword ) . "';" );
    
    		if ( $query )
    		{
    			if ( mysql_num_rows ( $query ) > 0 )
    			{
    				$id = mysql_result ( $query, 0 );
    
    				mysql_free_result ( $query );
    
    				mysql_query ( "UPDATE kwtracking SET count = count + 1 WHERE id = " . $id . ";" );
    			}
    			else
    			{
    				mysql_query ( "INSERT INTO kwtracking VALUES ( '', '" . mysql_real_escape_string ( $keyword ) . "', 0 );" );
    
    				$query = mysql_query ( "SELECT LAST_INSERT_ID();" );
    
    				$id = mysql_result ( $query, 0, 0 );
    
    				mysql_free_result ( $query );
    
    			}
    
    		echo "<h1>
    		here is some text
    	</h1>
    	<p>
    		here more text and a <a href='http://destination.com/subid=" . $id . "'>link</a>
    	<p>
    ";
    		exit ();
    
    		}
    	}
    }
    
    // no keyword or query failed, do something else here or do nothing
    
    ?>
    PHP:
     
    dataman, Dec 29, 2007 IP
  3. Lpdesigner

    Lpdesigner Peon

    Messages:
    86
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #3
    Thanks a lot! Updated with your code and it works like a charm now :)
     
    Lpdesigner, Dec 29, 2007 IP
  4. Lpdesigner

    Lpdesigner Peon

    Messages:
    86
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #4
    To add on to this script, let's say I wanted to cloak my affiliate links. How could this be done?

    For example, instead of using this link on my landing page:
    http://nbjmp.com/click/?s=4234&c=2346&subid=" . $id . "
    Code (markup):
    I use this and have it redirected to the link above:
    http://example.com/subid=" . $id . "
    Code (markup):
     
    Lpdesigner, Dec 29, 2007 IP