Show ads in order

[m3bik]

I'm working on a simple ad server and would like to display the ads in order (not random).

 

I'm fairly certain I can do something like this:

 

// Get the last ad id, or becomes 0 if not available
$lastid = ($_GET['lastid'] == '') ? '0' : $_GET['lastid'];

// Get the next ad in order, or get the first one available
mysql_query("SELECT adcode FROM ads WHERE zone='$zoneid' AND id > $lastid ORDER BY id ASC LIMIT 1");

 

The above code would get the first ad or the next ad in order.. How do I go back to the beginning when I run out of ads to show?

[Psycho]

I would probably use a column in the table for "last_viewed". Then when you need to get a new ad you would run a query to get the ad with the oldest "last_viewed" date, then run another to update the "last_viewed" of that record to the current timestamp. The ads will continually roll.

 

Example:

//Get the id of the oldest viewed ad
$query = "SELECT adcode FROM ads WHERE zone='{$zoneid}' ORDER BY last_viewed ASC LIMIT 1";
$result = mysql_query($query);
$ad_id = mysql_result($result, 0);

//Update the ad with current timestamp
$query = "UPDATE ads SET last_viewed = NOW() WHERE adcode='{$ad_id}'";
$result = mysql_query($query);

[m3bik]

Well I do like your logic... but I need the ads to view in order for each user separately. Sorry that I didn't specify that in the beginning.

 

So when I view the ads, it starts from the beginning and goes in order for me.. and if you view the ads around the same time, it starts from the beginning and goes in order for you (not starting where I left off)

[shankarganesh]

Hi,

 

Use the below query to display the ads inorder for respective zone

 

$query = "SELECT adcode FROM ads WHERE zone='{$zoneid}' ORDER BY auto_incremented_id ASC";

$result = mysql_query($query);

 

Thanks.

[Psycho]

Well I do like your logic... but I need the ads to view in order for each user separately. Sorry that I didn't specify that in the beginning.

 

So when I view the ads, it starts from the beginning and goes in order for me.. and if you view the ads around the same time, it starts from the beginning and goes in order for you (not starting where I left off)

 

Really? That would meant that the ads at the end may never get viewed by users. The advertisers who's ads are at the end probably wouldn't be OK with that.

 

But, anyway. What are you expecting to use to track the ads of a user? Using a GET var is not a good idea IMHO. For one, you would have to make sure to append that id to every page request and second you wouldn't be able to track a user's last viewed ad from session to session. If you want the ads to start over for a user on each session, then using a session var makes more sense. If you want the ads to start back where they left off on subsequent sessions, then you should use a cookie.

 

In either case, the logic is quite simple. Just use the query you have above. Then when, you retrieve the record, if the result is false it means you went past the last record and you need to reset the counter. It would probably be easiest to make this a function so you could recall the function (and the counter).

 

function getNextID($zoneID, $lastID = 0)
{
    $query = "SELECT adcode FROM ads WHERE zone='$zoneID' AND id > $lastID ORDER BY id ASC LIMIT 1";
    $result = mysql_query($query);
    $next_id = mysql_result($result, 0);
    if(!$next_id)
    {
        //If false it means there were no results. Call function
        //again with no $lastID value to reset from 0
        $next_id = getNextID();
    }
    return $next_id;
}

## - IF USING SESSION
//Get current ad id for user
$ad_id = getNextID($zoneID, $_SESSION['ad_id']);
//Set current ad id for user
$_SESSION['ad_id'] = $ad_id;

## - IF USING COOKIE
//Get current ad id for user
$ad_id = getNextID($zoneID, $_COOKIE['ad_id']);
//Set current ad id for user
setcookie('ad_id', $ad_id, time()+2500000); //Expire in ~1 month

[Psycho]

OK< wait. I see that you are returning "adcode" from the query and not the ID. I'm suspecting those are two different values? If that's the case I would put the code to set the current ad_id into the function and only return the ad code. So, assuming that what I just stated is correct I would go with

 

function getAdCode($zoneID, $lastID = 0)
{
    $query   = "SELECT `id`, `adcode` FROM `ads` WHERE zone='$zoneID' AND `id` > '$lastid' ORDER BY `id` ASC LIMIT 1";
    $result  = mysql_query($query);
    $next_ad = mysql_fetch_assoc($result);
    if(!$next_ad)
    {
        //If false it means there were no results.
        //If already at 0, exit function to prevent infinite loops
        if($lastID == 0) { return false; }
        //Call function again with no $lastID value to reset from 0
        $adCode = getAdCode($zoneID);
    }
    else
    {
        $adCode = $next_ad['adcode'];
        //Set current ID in session or cookie var
        $_SESSION['ad_id'] = $next_ad['id'];
        setcookie('ad_id', $next_ad['id'], time()+2500000); //Expire in ~1 month
    }

    //Return current ad code
    return $adCode;
}


//Get current ad id for user
## - IF USING SESSION
$ad_id = getAdCode($zoneID, $_SESSION['ad_id']);
## - IF USING COOKIE
$ad_id = getAdCode($zoneID, $_COOKIE['ad_id']);