Simple banner rotator

[Jaan]

Okay today I'm going to show you how to make a simple banner rotator... You can use it for your ads on your site

First of all we must create tables to database:

CREATE TABLE `banner` (
  `id` int(5) NOT NULL AUTO_INCREMENT,
  `bannerimg` tinytext NOT NULL,
  `bannerurl` varchar(100) NOT NULL,
  `bannertxt` varchar(100) NOT NULL,
  `views` int(5) NOT NULL DEFAULT '0',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ; 

Now we must start with our script..

PHP Code:

<?php
$SQLhost = "localhost";
$SQLuser = "";
$SQLpass = "";
$DB = "";

$SQLhost = "localhost"; - Here will be your SQL host, usually it's localhost but it can be something else also.. if you want to know what's your host.. contact with your hosting provider
$SQLuser = ""; - your database username
$SQLpass = ""; - your database password
$DB = ""; - Database

PHP Code:

mysql_connect($SQLhost, $SQLuser, $SQLpass) or die("Can not connect to database: ".mysql_error());
mysql_select_db($DB) or die("Can not select database: ".mysql_error()); 


mysql_connect($SQLhost, $SQLuser, $SQLpass) or die("Can not connect to database: ".mysql_error()); - This will connect to the database and if it can not connect, then it will generate a error message with details why you can't connect to your database
mysql_select_db($DB) or die("Can not select database: ".mysql_error()); - It will select a database for you.. but if it can't select it will generate a detailed error

Now let's find out how many banners we have in our database and let's generate a random number

PHP Code:

$query = mysql_query("SELECT * FROM banner");
$numTab = mysql_num_rows($query);
$RandNum = (rand()%$numTab);
$query = mysql_query("SELECT * FROM banner LIMIT $RandNum, 1"); 


$query = mysql_query("SELECT * FROM banner"); - It will select all tables from banner table
$numTab = mysql_num_rows($query); - This will show us how many records are in our table

Another 2 rows will generate a random number and they will select random banner.

Now let's show our banner.

PHP Code:

while ($row = mysql_fetch_array($query)){
$banURL = $row['bannerurl'];
$banTXT = $row['bannertxt'];
$Banner = $row['bannerimg'];
$BanViews = $row['views'];
$BanID = $row['id'];

echo"<a href='".$banURL."' target='_blank' title='".$banTXT."'><img src='".$banner."' alt='".$banTXT."' border='0'></a>";
$newViews = $BanViews+1;
mysql_query("UPDATE banner SET views = $newViews WHERE id = $BanID");
} 


while ($row = mysql_fetch_array($query)){ - Starts a while and get's info from the table
$banURL = $row['bannerurl']; - Banner's URL
$banTXT = $row['bannertxt']; - Banner's text
$Banner = $row['bannerimg']; - Banner itself
$BanViews = $row['views']; - Banner's views
$BanID = $row['id']; - Banner's ID

echo"<a href='".$banURL."' target='_blank' title='".$banTXT."'><img src='".$banner."' alt='".$banTXT."' border='0'></a>"; - Shows the banner that was randomly chosen from the table
$newViews = $BanViews+1; - Increases banner views by 1
mysql_query("UPDATE banner SET views = $newViews WHERE id = $BanID"); - Updates banner views

And we are done Simple 'eh
Okay here's the full script:

PHP Code:

<?php

$SQLhost = "localhost";
$SQLuser = "";
$SQLpass = "";
$DB = "";

mysql_connect($SQLhost, $SQLuser, $SQLpass) or die("Can not connect to database: ".mysql_error());
mysql_select_db($DB) or die("Can not select database: ".mysql_error());

$query = mysql_query("SELECT * FROM banner");
$numTab = mysql_num_rows($query);

$RandNum = (rand()%$numTab);
$query = mysql_query("SELECT * FROM banner LIMIT $RandNum, 1");

while ($row = mysql_fetch_array($query)){
$banURL = $row['bannerurl'];
$banTXT = $row['bannertxt'];
$Banner = $row['bannerimg'];
$BanViews = $row['views'];
$BanID = $row['id'];

echo"<a href='".$banURL."' target='_blank' title='".$banTXT."'><img src='".$banner."' alt='".$banTXT."' border='0'></a>";
$newViews = $BanViews+1;
mysql_query("UPDATE banner SET views = $newViews WHERE id = $BanID");
}

?>

Enjoy

[Xav]

I have just started learning PHP, and I am wondering - where do you insert the SQL code? Is it in a separate file, and if so, what sort of file?

[John]

Originally Posted by Xav

I have just started learning PHP, and I am wondering - where do you insert the SQL code? Is it in a separate file, and if so, what sort of file?

You insert it into your MySQL database.

[DevilsCharm]

Makes sense to insert SQL code into the MySQL database, indeed.

[Xav]

I didn't know what it was at the time. I'm still a little confused, actually. Is the MySQL database a separate file, or not? And what sort of file is it?

[Jaan]

mysql database isnt a file
it's a database.. it's like server..

[Xav]

OOHHHH... I still don't get a couple of things. Presumably they're already made, as web hosting providers offer a set amount of databases. So why do you need to create them using sql_create_database(), if they're already there?

[Jaan]

you can use this function also if you have permission..
BUT
usually you are not permitted to do it..
so..
you must create databases in your Cpanel or phpMyAdmin

[Xav]

Ok. I see now.

[Jaan]

hmm.. you gave me an idea.. i'll create some tutorials about that