1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

Is there anyway I can make some ad display at random more often than others

Discussion in 'Databases' started by ketting00, Oct 29, 2013.

  1. #1
    Hi,
    I have a table of advertisement with expiry date and location. Currently, I display ads at random base on user’s location.

    In the near future, I want to add tier system in to my ad sale. It means the first tier ad should be displayed more often than the second tier.
    SEMrush
    Is there anyway I can do this?

    Thank you,
     
    ketting00, Oct 29, 2013 IP
    SEMrush
  2. bartolay13

    bartolay13 Active Member

    Messages:
    735
    Likes Received:
    14
    Best Answers:
    1
    Trophy Points:
    98
    #2
    yes definitely, post what you have started.

    adding some sort of percentage to each tier will work.
     
    bartolay13, Nov 3, 2013 IP
  3. ketting00

    ketting00 Well-Known Member

    Messages:
    751
    Likes Received:
    27
    Best Answers:
    3
    Trophy Points:
    128
    #3
    Thank you very much for your response. I thought there was hopeless. Here's the thing I've been doing so far:
    Creating a table:
    
    $mysqli->query("CREATE TABLE `ads` (
      `id` INT NOT NULL AUTO_INCREMENT,
      `uid` INT NOT NULL,
      `txt` TEXT NOT NULL,
      `date` DATE NOT NULL default '0000-00-00',
      `img` VARCHAR( 120 ) NOT NULL,
      `location` VARCHAR( 75 ) NOT NULL,
      `expiry` DATE NOT NULL default '0000-00-00',
    PRIMARY KEY ( `id` ),
    UNIQUE (
      `id`
      )
    ) ENGINE = INNODB CHARACTER SET UTF8 COLLATE utf8_general_ci
    ")
    or die("DB error!");
    
    Code (markup):
    Random with PHP:
    
    SELECT id, uid, img, location, expiry
    FROM ads
    WHERE location = '$location' AND CURDATE() < expiry AND RAND()<(SELECT ((1/COUNT(*))*10) FROM ads)
    ORDER BY RAND()
    LIMIT 1
    
    Code (markup):
    I've tried to google what you've suggested. No luck so far...
     
    ketting00, Nov 5, 2013 IP
  4. bartolay13

    bartolay13 Active Member

    Messages:
    735
    Likes Received:
    14
    Best Answers:
    1
    Trophy Points:
    98
    #4
    oh you used query, not in a single query though but in a procedure
    ie.
    given 3 types of ads
    1st - 70%
    2nd - 20%
    3rd - 10%

    pseudo code

    Initialize ad_number to 1
    Initialize random number in between 1-100 to n variable
    if n in between 1-70
    return 1st tier
    else n in between 71-90
    return 2nd tier
    else n in between 91-100
    return 3rd tier
    else
    return null
     
    bartolay13, Nov 6, 2013 IP
  5. ketting00

    ketting00 Well-Known Member

    Messages:
    751
    Likes Received:
    27
    Best Answers:
    3
    Trophy Points:
    128
    #5
    Thank you. I'd try it.
     
    ketting00, Nov 6, 2013 IP