?
Solved

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

Posted on 2005-04-20
10
Medium Priority
?
1,149 Views
Last Modified: 2013-12-12
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
Comment
Question by:sabecs
  • 4
  • 4
  • 2
10 Comments
 
LVL 32

Expert Comment

by:ldbkutty
ID: 13830700
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
 
LVL 32

Expert Comment

by:ldbkutty
ID: 13830708
>>  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
 
LVL 49

Expert Comment

by:Roonaan
ID: 13831114
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:sabecs
ID: 13831845
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
 
LVL 49

Expert Comment

by:Roonaan
ID: 13831916
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
 
LVL 32

Accepted Solution

by:
ldbkutty earned 2000 total points
ID: 13832230
<?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
 
LVL 49

Expert Comment

by:Roonaan
ID: 13832330
Why would you want to define $priority_array when you have a `priority` field in your db?

-r-
0
 
LVL 32

Expert Comment

by:ldbkutty
ID: 13832413
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
 
LVL 49

Expert Comment

by:Roonaan
ID: 13832677
Á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
 

Author Comment

by:sabecs
ID: 13834075
Thanks for your feedback and comments.
0

Featured Post

[Video] Create a Disruption-Free Workspace

Open offices have their challenges. And Sometimes, it's even hard to work at work. It's time to reclaim your office and create a disruption-free workspace. With the MB 660, you can:

-Increase Concentration
-Improve well-being
-Boost Productivity

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The burden of debt that average person carries today has not only increased, but it keeps on growing. It is quite common nowadays that people find it extremely difficult to make ends meet in the face of millions of competing priorities that they hav…
This holiday season, we’re giving away the gift of knowledge—tech knowledge, that is. Keep reading to see what hacks, tips, and trends we have wrapped and waiting for you under the tree.
The viewer will learn how to count occurrences of each item in an array.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …
Suggested Courses
Course of the Month13 days, 22 hours left to enroll

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question