Introduction

In this tutorial we are going to be grabbing HTML from websites and scanning it, rather than just give you a script which just does this i've put it into a practical sense by creating a script which will scan a page looking for an affiliate link and update an mysql table accordingly.


The SQL

Firstly we are going to have to create the table in which the affilates are stored.

CREATE TABLE `affiliates` (

  `aff_id` int(5) NOT NULL AUTO_INCREMENT',

  `aff_name` varchar(255) NOT NULL default '',

  `aff_url` varchar(255) NOT NULL default '',

  `aff_in` int(5) NOT NULL default '0',

  `aff_out` int(5) NOT NULL default '0',

  `aff_active` enum('1','0') NOT NULL default '1',

  PRIMARY KEY  (`aff_id`)

) ENGINE=MyISAM DEFAULT CHARSET=latin1;



-- 

-- Dumping data for table `affiliates`

-- 



INSERT INTO `affiliates` VALUES (1, 'Testing', 'http://arutha.co.uk', 0, 0, '1');

There is currently one example for you in the table, this example is to scan my blog for a link however bear in mind i update my blog regularly so it may not be there forever. So you may want to enter your own test data.



Step 1 - Connecting and Retreiving

As in any MySQL and PHP script you need to connect to the database therefore we begin with the all toofamiliar connection script.

 <?

// Firstly we connect to the database.

$db_host = "";

$db_user = "";



$db_pass = "";

$db_name = "affiliatecheck";

$dba = mysql_connect($db_host,$db_user,$db_pass);

mysql_select_db ($db_name) or die ("Cannot connect to database");

After we've connected we want too get all the rows off the database so we can check each affiliate, i've also included the link structure in this line off code. Read the tips and tricks box below for details.

 // Now we check all the sites.

$yourlinkstructure = "arutha.co.uk?id=";

$sql = mysql_query("SELECT * FROM affiliates");

Step 2 - The loop and assignment of custom variables

 

while ($result = mysql_fetch_array($sql))

{

    // Take Variables from the database

    $url = $result['aff_url'];

    $idretrieved = $result['aff_id'];

When retrieving multiple rows from a mysql database i like to use the while loop. The first statement is basically saying while there are rows we haven't looked at carry on.

The function inside the while loop will return the values from that row in an array. In this case we are retrieving this data and putting them into other variables.



Step 3 - Loading and Searching the HTML

We now want to grab the HTML from our target site, we will use the following functions to do this. We are using the file_get_contents() function to retrieve the HTML from our target site and then using the htmlspecialchars() to make it easier to find our affiliate link.

We are looking for an instance anywhere in the code for that affiliate link. My previous affiliates policies have been that my link must be on every page off the site however random affiliate boxes are become more common therefore it will search the page 3 times or until the link is found.

 

// Check to see if the site contains the link

    $i = 0;

    $linkfound = false;

    while($i != 3 or $linkfound = true)

    {

        $file = file_get_contents($url);

        $file = htmlspecialchars($file);

        if (strstr($file,$yourlinkstructure.$idretrieved))

        {

            $linkfound = true;

        }

        else

        {

            $linkfound = false;

        }

        $i = $i + 1;

    }

Step 4 - Recording the results

     

// Updating the database

    if ($linkfound == true)

    {

        // Update your database accordingly - Link has been found.

        $update = mysql_query("UPDATE affiliates set aff_active = '1' where aff_id = '$idretrieved'");

    }

    else

    {

        // Update your database accordingly - Link hasn't been found.

        $update = mysql_query("UPDATE affiliates set aff_active = '0' where aff_id = '$idretrieved'");

    }

}

?>

In this code we are going to record the result into our database. With regards to the active field 1 means yes and 0 means No.

I hope you enjoyed this tutorial and found it useful. Full code is on the last page.

Arutha

<?

// Firstly we connect to the database.

$db_host = "localhost";

$db_user = "root";

$db_pass = "";

$db_name = "affiliatecheck";

$dba = mysql_connect($db_host,$db_user,$db_pass);

mysql_select_db ($db_name) or die ("Cannot connect to database");



// Now we check all the sites.

$yourlinkstructure = "arutha.co.uk?id=";

$sql = mysql_query("SELECT * FROM affiliates");

while ($result = mysql_fetch_array($sql))

{

    // Take Variables from the database

    $url = $result['aff_url'];

    $idretrieved = $result['aff_id'];

    // Check to see if the site contains the link

    $i = 0;

    $linkfound = false;

    while($i != 3 or $linkfound = true)

    {

        $file = file_get_contents($url);

        $file = htmlspecialchars($file);

        if (strstr($file,$yourlinkstructure.$idretrieved))

        {

            $linkfound = true;

        }

        else

        {

            $linkfound = false;

        }

        $i = $i + 1;

    }

    // Updating the database

    if ($linkfound == true)

    {

        // Update your database accordingly - Link has been found.

        $update = mysql_query("UPDATE affiliates set aff_active = '1' where aff_id = '$idretrieved'");

    }

    else

    {

        // Update your database accordingly - Link hasn't been found.

        $update = mysql_query("UPDATE affiliates set aff_active = '0' where aff_id = '$idretrieved'");

    }

}

?>