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

Select rows one after another?

Hi Experts,

I need to develop a simple ad server. I want to show ads one after another and if there are no more rows start from the beginning. Is there a simple way besides creating a counter?

The database:

AD1
AD2
AD3

The result from the ad server:
AD1, AD2, AD3, AD1...

Best regards,
The Terrible
0
potworny
Asked:
potworny
  • 4
  • 3
2 Solutions
 
potwornyAuthor Commented:
The question seems difficult. Increased point to 500.
0
 
Beverley PortlockCommented:
The question seems difficult.

No the question is simple enough, but it lacks sufficient detail to give a clear answer.

It all depends how you intend to present the ads. Are you only going to show one ad at a time? Are you going to use a javascript library like MooTools to dynamically switch the ads? If you do present one ad at a time do you depend on a client browser re-rfetching a page to show the next ad in the sequence? And so on..

If you can detail the scenario more it would be very helpful.
0
 
potwornyAuthor Commented:
bportlock, you look too deep :)

I just need a simple answer. How do I select next available record. If it is the last record, go to the first. Like a stack.

Adserver is just an example. It's a very simple one. No bells and whistles. Just a link to click. No MooTools or anything dynamic. :)
0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
Beverley PortlockCommented:
OK, if your adverts have ID codes like an auto-increment primary key, then if you know the ID of the current one then the next one is easily selected like this

$rs = mysql_query("SELECT id FROM advertTable WHERE id > $currentAdvertId ORDER BY id LIMIT 1 ");

where $currentAdvertId is the ID of the current advert. This could be stored in a cookie or (better still) a session variable when you load the page.

The only tricky bit is where you have processed all the adverts, so after executing the above do this check

if ( mysql_num_rows( $rs ) == 0 )
     $rs = mysql_query("SELECT id FROM advertTable ORDER BY id LIMIT 1 ");

and it will load the first advert. You can then continue. So the final code looks like

$rs = mysql_query("SELECT id FROM advertTable WHERE id > $currentAdvertId ORDER BY id LIMIT 1 ");

if ( mysql_num_rows( $rs ) == 0 )
     $rs = mysql_query("SELECT id FROM advertTable ORDER BY id LIMIT 1 ");

if ( mysql_num_rows( $rs ) == 0 )
     die("No adverts to serve);

$rw = mysql_fetch_assoc( $rs );
.... carry on coding....

Open in new window

0
 
johanntagleCommented:
See if something like this suffices:

mysql> create table ads_test (id int not null auto_increment primary key, ad varchar(20));
Query OK, 0 rows affected (0.08 sec)

mysql> insert into ads_test (ad) values ('first'),('second'),('third'),('fourth'),('fifth'),('sixth'),('seventh'),('eight'),('ninth'),('tenth');
Query OK, 10 rows affected (0.00 sec)
Records: 10  Duplicates: 0  Warnings: 0

Open in new window


In your application code, you have a variable that contains the id of last ad shown.  Default it to 0 upon startup.  This allows you to use the following SQL:

mysql> select a.id, a.ad from (
    -> (select id, ad, 1 as priority from ads_test where id >0 order by id limit 1) 
    -> union 
    -> (select id, ad, 2 as priority from ads_test order by id limit 1)) 
    -> a order by priority limit 1;
+----+-------+
| id | ad    |
+----+-------+
|  1 | first |
+----+-------+
1 row in set (0.00 sec)

Open in new window


note the "id > 0" above.  You replace zero with the next id given the next time you call it:

mysql> select a.id, a.ad from (
    -> (select id, ad, 1 as priority from ads_test where id >1 order by id  limit 1) 
    -> union 
    -> (select id, ad, 2 as priority from ads_test order by id limit 1)) 
    -> a order by priority limit 1;
+----+--------+
| id | ad     |
+----+--------+
|  2 | second |
+----+--------+
1 row in set (0.00 sec)

Open in new window


when it reaches the end:

mysql> select a.id, a.ad from (
    -> (select id, ad, 1 as priority from ads_test where id >9 order by id  limit 1) 
    -> union 
    -> (select id, ad, 2 as priority from ads_test order by id limit 1)) 
    -> a order by priority limit 1;
+----+-------+
| id | ad    |
+----+-------+
| 10 | tenth |
+----+-------+
1 row in set (0.00 sec)

mysql> select a.id, a.ad from (
    -> (select id, ad, 1 as priority from ads_test where id >10 order by id  limit 1) 
    -> union 
    -> (select id, ad, 2 as priority from ads_test order by id limit 1)) 
    -> a order by priority limit 1;
+----+-------+
| id | ad    |
+----+-------+
|  1 | first |
+----+-------+
1 row in set (0.00 sec)

Open in new window


You can change the limit to retrieve multiple ads at a time:

mysql> select a.id, a.ad from (
    -> (select id, ad, 1 as priority from ads_test where id >5 order by id  limit 3) 
    -> union 
    -> (select id, ad, 2 as priority from ads_test order by id limit 3)) 
    -> a order by priority limit 3;
+----+---------+
| id | ad      |
+----+---------+
|  6 | sixth   |
|  7 | seventh |
|  8 | eight   |
+----+---------+
3 rows in set (0.00 sec)

Open in new window


EDITED: added missing "order by id" on the first subselect
0
 
potwornyAuthor Commented:
Thank you very much for this answer. It's far more superior than my idea with a separate counter in a separate table. THANK you for opening my mind. :D
0
 
potwornyAuthor Commented:
johanntagle, thank you very much as well! GREAT ANSWER!

I will use both your code and the idea of storing the ads in sessions.
0
 
Beverley PortlockCommented:
Thanks for the points
0

Featured Post

Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

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