• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1185
  • Last Modified:

PHP Mysql Banner Ads rotation - random but with Banner weighting?

Hi,
I have a Banner Ads table which contains aprox 20 rows.

Table stucture:

CREATE TABLE `ba` (
  `banid` int(11) NOT NULL auto_increment,
  `userid` int(11) default NULL,
  `email` varchar(60) default NULL,
  `status` char(3) NOT NULL default 'act',
  `sstate` char(3) NOT NULL default 'ALL',
  `bantype` varchar(5) default NULL,
  `banner` varchar(60) default 'banners/sample1.gif',
  `bkgcol` varchar(12) default '#FFFFFF',
  `link` varchar(150) NOT NULL default 'http://www.mysite.com/advertise.htm',
  `alttxt` varchar(60) NOT NULL default 'Click here for more information',
  `lstdate` date NOT NULL default '0000-00-00',
  `expdate` date NOT NULL default '0000-00-00',
  `impr` mediumint(12) NOT NULL default '0',
  `imps` mediumint(12) NOT NULL default '0',
  `impp` mediumint(12) NOT NULL default '0',
  `banw` smallint(4) NOT NULL default '468',
  `banh` smallint(4) NOT NULL default '60',
  `priority` smallint(1) NOT NULL default '1',
  PRIMARY KEY  (`banid`),
  KEY `banid` (`banid`),
  KEY `image` (`banner`)
) TYPE=MyISAM AUTO_INCREMENT=21 ;


mysql_select_db($database_conn_data, $conn_data);
$query_ban_sum = "SELECT * FROM `ba` WHERE `status` =  'ACT' ORDER BY RAND() LIMIT 0,1";
$ban_sum = mysql_query($query_ban_sum, $conn_data) or die(mysql_error());
$row_ban_sum = mysql_fetch_assoc($ban_sum);

My current query above selects banners to appear on a page randomly and works ok, but how would I set it up so that certain banners with a higher priority are displayed more frequently than those with a low priority.


For example if I had banner 1 with priority 1  how could I ensure that it appeared say 5 time more often that banner 15 with priority 5.

Any help or feedback would be greatly appreciated.

Thanks.


0
sabecs
Asked:
sabecs
  • 4
  • 4
  • 2
1 Solution
 
ldbkuttyCommented:
Assuming how about this:

<?php

mysql_select_db($database_conn_data, $conn_data);

// Define the priorities in an array.
$priority_array = array(1, 1, 1, 1, 1, 2, 2, 2, 2, 3, 3, 3, 4, 4, 5);

// Select a random number out of the priority array.
$rand_num =  array_rand($priority_array, 1);

$query_ban_sum = "SELECT * FROM `ba` WHERE `status` =  'ACT' WHERE priority = $rand_num ORDER BY RAND() LIMIT 0,1";
$ban_sum = mysql_query($query_ban_sum, $conn_data) or die(mysql_error());
$row_ban_sum = mysql_fetch_assoc($ban_sum);

?>
0
 
ldbkuttyCommented:
>>  Assuming how about this:

should be:

 Assuming priority column as static (in other words, assuming you know the length of priority column), how about this:
0
 
RoonaanCommented:
Couldn't you just sort by rand*priority?

$query_ban_sum = "SELECT * FROM `ba` WHERE `status` =  'ACT' ORDER BY RAND() * `priority` DESC LIMIT 0,1";

-r-
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
sabecsAuthor Commented:
Thanks for your Feedback,
I tried ldbkutty suggestion but $rand_num was returning the position in the array and not the actual number, that is was returning number from 1 to 15. Also if it was to return a number between 1 and 5 what happens if I don't have any banners with say priority 4, it would not return a banner at all when the random number happens to be a 4.

I also tried Roonaan suggestion but seemed to be getting the same number of banner ads with priority 5 as I with priority 1, not sure if it had anything to do with the fact that most of my banners have a lower priority (5) than a higher priority (1).

Thanks for your help, do you have any other suggestions of how I can achive this?
0
 
RoonaanCommented:
Sabecs,

I have difficulties to understand your line:
>but seemed to be getting the same number of banner ads with priority 5 as I with priority 1,
> not sure if it had anything to do with the fact that most of my banners have a lower priority
> (5) than a higher priority (1).

Should I interpret this as that it seems that priority has no effect whatsoever, or that it prioritizes in the wrong order of some sort?

-r-

0
 
ldbkuttyCommented:
<?php

mysql_select_db($database_conn_data, $conn_data);

// Define the priorities in an array.
$priority_array = array(1, 1, 1, 1, 1, 2, 2, 2, 2, 3, 3, 3, 4, 4, 5);

$is_repeat = true;

while(!empty($is_repeat)) {

  // Select a random number out of the priority array.
  $rand_num =  $priority_array[rand(0, count($priority_array)-1)];

  $query_ban_sum = "SELECT * FROM `ba` WHERE `status` =  'ACT' WHERE priority = $rand_num ORDER BY RAND() LIMIT 0,1";
  $ban_sum = mysql_query($query_ban_sum, $conn_data) or die(mysql_error());
  $is_repeat = (mysql_num_rows($ban_sum) < 1) ? true : false;
}

  $row_ban_sum = mysql_fetch_assoc($ban_sum);

?>
0
 
RoonaanCommented:
Why would you want to define $priority_array when you have a `priority` field in your db?

-r-
0
 
ldbkuttyCommented:
Because the "priority" field in the DB does not specify the actual priority systematically.

How would one know that the value "1" in priority column has five times higher priority when compared to the value "5" ? Thats why I provided the "priority of values as number of repeated values" in the array!
0
 
RoonaanCommented:
Áh, but that would go fairly against the usefullnes of the database an sich. Better to change fieldtype, or add an additional field then.

-r-
0
 
sabecsAuthor Commented:
Thanks for your feedback and comments.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

  • 4
  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now