Ad Management Console

[eluser]skunkbad[/eluser]
I've implemented a simple ad rotation script, which takes ads, whose HTML I have stored in arrays, and uses a session to display the ads in sequence. While making the simple script, I thought of many things to be desired. It would be really nice to have a console in my admin area, and be able to add ads there. I've got a few questions though.

1) Is it best to store the ad HTML in a database table? This is just one more query to the database on every pageload. My host limits my database activity to 10 simultaneous connections, and I already have at least one, and up to four per pageload. If more than 1 or 2 people were on my site at one time, I could see an extra query as something bad.

2) It's an obvious security risk to have users upload their own ad HTML to the database. I don't have users who can post ads, but I'm always thinking to satisfy any website I make in the future. If a user was to pay for a ad to be in rotation for 30 days, how would you handle the HTML? How does a big site handle this? Would they require that the user send the HTML via email, and then personally check it before database insert?

I'm looking for ideas, because I'd like this ad rotation script to be complete, and not have to do it twice, and security is very important. If you wanted to manage ads on your website, what kind of features would you think were critical?

Thanks,
Brian

[eluser]Johan André[/eluser]
Hey!

I use an adsystem for a site.
I'm storing all ads in a table:

id
name
clicks
url
image
start_date
end_date
position
weight

Most of it should be easy to grasp. The position is because I have different pagelocations of the ads. The weight is the sort order of the ads.

I then get the ads with sql:

SELECT * FROM ads WHERE start_date > NOW() AND end_date > NOW() AND position = 'top' ORDER BY weight ASC

Just cycle the array och print something like:

Code:

<a href="&lt;?=$row['url']?&gt;"><img src="&lt;?=$row['image']?&gt;" alt="&lt;?=$row['name']?&gt;" /></a>

By the way, if your host only lets you have 10 connections you should switch.

Good luck!

[eluser]skunkbad[/eluser]
[quote author="Johan André" date="1249959267"]

By the way, if your host only lets you have 10 connections you should switch.

Good luck![/quote]

Hi Johan,

Thanks for your response. I was wrong. I checked and it is 30 MySQL connections. I don't know if that is much better, or how many other hosts allow, but I'm sure it is sufficient for my immediate needs.

So... when people click on your ads, you are obviously redirecting them, or you wouldn't be able to track clicks and other data, correct? Most of the ads that I currently use are from Commission Junction, and also from Google Adsense. They do provide their own click counts, but I always wonder if they are correct or not. With CJ, some advertisers do or do not allow redirects, so this could limit if an ad could be tracked.

Having ads on a site is an interesting task. I always try to think about a customer's needs. If it is just me I can edit the code, but a customer needs a GUI/user console of some sort. How do you handle this?

So far I have this sql:

Code:

-- Ad Management

CREATE TABLE `ads` (
  `ad_id` int(10) unsigned NOT NULL,
  `user_id` varchar(12) NOT NULL,
  `clicks` int(10) NULL,
  `ad_url` varchar(255) NULL,
  `ad_image` varchar(255) NULL,
  `ad_html` varchar(255) NULL,
  `start_date` int(10) NOT NULL,
  `end_date` int(10) NOT NULL,
  `ad_class` char(1) NOT NULL,
  `ad_rating` tinyint(2) unsigned NOT NULL,
  PRIMARY KEY (`ad_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

CREATE TABLE `ad_classes` (
  `ad_class` char(1) NOT NULL,
  `width` smallint(4) unsigned NOT NULL,
  `height` smallint(4) unsigned NOT NULL,
  PRIMARY KEY (`ad_class`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

[eluser]Johan André[/eluser]
Hey!

My admins (I have 15-20 of them, atleast one for each district) handles the ads. My ads does not contain ANY html, just an image (in predefined sizes) and a link. I do not work with Google Ads or something...

When the users click on ads I redirect them to a controller, update (via an ad-model) the click-count and redirect them to the url in the ad.

Simple solution, but fits my needs right now...

[eluser]skunkbad[/eluser]
A simple solution would more than fit my needs right now, but I am always thinking to satisfy any client or friend.

An HTML ad could be a Google ad, but could also be some embed code for a Flash Ad. There are so many things to think about!

One of those things that I am thinking about is what level of validation/filtering to do on posted HTML, or even the ad's URL, before it is stored in the database. Does CI's xssclean do the job?

Another question. I know in a perfect scenario that the file uploads folder isn't on the same server, or at least isn't at or below root. Images then get served via a controller. Do you take any special security measures for file uploads that go beyond what CI offers?

If you only have admins managing your ads, and users can't buy ads and upload their files/links, then your simple solution might not need much in the way of security, but I'm thinking to create a module that I can drop in to any website, and that website would have what they need, regardless of their needs.