PHP Tutorial – Banner Rotator

Our script will need to save 7 things for each banner: the image url, link url (page to go to upon click on the banner), the location to display the banner on, views and max amount of views the banner may receive. We’ll create a database table to store these info into: we’ll call the fields: img_url, link_url, location, views, max_views and we’ll have one more field called id.

SQL
SET SQL_MODE=”NO_AUTO_VALUE_ON_ZERO”;

CREATE TABLE IF NOT EXISTS `banners` (
`id` int(250) NOT NULL AUTO_INCREMENT,
`img_url` varchar(250) NOT NULL,
`link_url` varchar(250) NOT NULL,
`location` varchar(250) NOT NULL,
`views` int(11) NOT NULL,
`max_views` int(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

INSERT INTO `banners` (`id`, `img_url`, `link_url`, `location`, `views`, `max_views`) VALUES
(1, ‘http://img856.imageshack.us/img856/4108/bannerct.png’, ‘http://www.squidoo.com/lensmaster/webcodez’, ‘*’, 4, 1000),
(2, ‘http://www.istad.org/lenses/buttons-banners/squidoo-banner-teal.gif’, ‘http://www.squidoo.com’, ‘*’, 4, 1000);

config.php
<?php

/*
DB CONFIG
*/

$db['server'] = “localhost”;
$db['user'] = “root”;
$db['pass'] = “”;
$db['name'] = “webcodez”;

/*
DB CONNECTION
*/

mysql_connect($db['server'], $db['user'], $db['pass']);
mysql_select_db($db['name']);

?>

We created an array $db containing the database connection data and then used mysql_connect and mysql_select_db to establish a connection with the MySQL database. We could’ve done that as well inside of the rotator file ( establishing the actual connection ) but that’s up to you if you prefer to do it instantly within the config file or do it for each file that requires it.

Next we’ll start creating the actual rotator script. We’ll simply create one function that gets the banner to display. We’ll call it “displayBanner”:

rotator.php
function displayBanner($location) {
}

It requires 1 parameter to be given: the location to display the banner, which determines which banners may be displayed (/are rotated). We’ll indicate a location equal to “*” as any location. Thus we’ll display the banners with location = $location OR location = “*”. Because these are the banners that may be displayed at the current page ( supplied in $location to the function upon calling it ). The query that gets the banner will thus atleast have this WHERE clause:

… WHERE location = ‘$location’ OR location = ‘*’

thus we get the following query:

SELECT * FROM banners WHERE location = ‘$location’ OR location = ‘*’

But this would select ALL the banners that may be displayed on the current page. We only want 1 though, a random one. To do this, we can use the following ‘trick’: adding a random order to the query using ORDER BY RAND():

<?php

function displayBanner($location) {

$get_banner = mysql_query(“SELECT * FROM banners WHERE location = ‘$location’ OR location = ‘*’ ORDER BY RAND()”);

}

?>

We stored the query inside of a variable $get_banner. Now we’ll use mysql_fetch_assoc to get 1 banner entry from the query ( which will be a random one as we ordered them randomly ).

<?php

function displayBanner($location) {

$get_banner = mysql_query(“SELECT * FROM banners WHERE location = ‘$location’ OR location = ‘*’ ORDER BY RAND()”);
$banner = mysql_fetch_assoc($get_banner);

}

?>

We now have all the info from the randomly selected banner inside of an array $banner ( containing all the table fields: id, img_url, link_url, etc. as sub-variable to the array ). We can now thus use these to display the banner using the HTML img tag and a href tag to link it:

<?php

function displayBanner($location) {

$get_banner = mysql_query(“SELECT * FROM banners WHERE location = ‘$location’ OR location = ‘*’ ORDER BY RAND()”);
$banner = mysql_fetch_assoc($get_banner);

echo “<a href=’”.$banner['link_url'].”‘><img src=’”.$banner['img_url'].”‘></a>”;

}

?>

All that’s left to do now is to update the banner that has been displayed by adding 1 to the amount of views of the banner. Here comes the ‘id’ field useful as we got the id of the banner we selected in $banner['id'] and as each banner has its own unique id, we can now easily UDPATE the banner with the id equal to $banner['id']:

rotator.php
<?php

function displayBanner($location) {

$get_banner = mysql_query(“SELECT * FROM banners WHERE location = ‘$location’ OR location = ‘*’ ORDER BY RAND()”);
$banner = mysql_fetch_assoc($get_banner);

echo “<a href=’”.$banner['link_url'].”‘><img src=’”.$banner['img_url'].”‘></a>”;

$update_banner = mysql_query(“UPDATE banners SET views = views+1 WHERE id = ‘”.$banner['id'].”‘ “);

}

?>

And that’s it! We now got our own banner rotation script. The usage will be shown below.