-1

I need to generate diferent ads for a webpage, without them repeating.

Lets say i have 6 zones where ads go, and 100 ads. I need to generate 6 rows with banner.id and zone.id, which will be unique every time.

the tables:

  • banner_ads (id, ad_location)

  • zones (id, zone_code)

  • banner_to_zones (banner_id, zone_id)

it can be a combination of PHP and SQL if needed

Dharman
  • 30,962
  • 25
  • 85
  • 135
Arnas Petruškevičius
  • 107
  • 1
  • 6
  • What dou you mean by "I need to generate 6 rows with banner.id and zone.id, which will be unique every time." ? Do you mean the combination of 6 rows must be unique? Do you mean these rows should not be shown before? Or do you mean that the 6 rows should not have repeating values. – Menelaos Jun 18 '13 at 13:43
  • I am not sure, to understand your question. Could you try to be more explicit? What do you want in each zone? 100/6 => 16 or 17 ads? 6 rows with 6 zones or 6 zones with 6 rows? ... – André Pletschette May 01 '20 at 15:22

1 Answers1

0

Here is an approach. For each banner_id randomly select a possible zone (from the data). Then from the random zones chosen, randomly select a banner_id. At this point, you have a collection of distinct zones and banners. You can then get six of them:

select thezone as zone_id,
       substring_index(group_concat(banner_id order by RAND()), ',', 1) as banner_id
from (select banner_id, substring_index(group_concat(zone_id order by RAND()), ',', 1) as thezone
      from banner_to_zones b2z
      group by banner_id
     ) b
order by rand()
limit 6

There are some cases where this won't work. But assuming that you have enough zones and banners, then it should return six that are distinct on both banner and zone.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786