Solved

Select rows one after another?

Posted on 2012-03-13
9
370 Views
Last Modified: 2012-03-14
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
Comment
Question by:potworny
  • 4
  • 3
9 Comments
 

Author Comment

by:potworny
ID: 37713630
The question seems difficult. Increased point to 500.
0
 
LVL 34

Expert Comment

by:Beverley Portlock
ID: 37713768
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
 

Author Comment

by:potworny
ID: 37713778
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 34

Assisted Solution

by:Beverley Portlock
Beverley Portlock earned 100 total points
ID: 37713789
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
 
LVL 24

Accepted Solution

by:
johanntagle earned 400 total points
ID: 37713809
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
 

Author Comment

by:potworny
ID: 37713831
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
 

Author Comment

by:potworny
ID: 37713835
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
 
LVL 34

Expert Comment

by:Beverley Portlock
ID: 37713898
Thanks for the points
0

Featured Post

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

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

Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
The viewer will learn how to count occurrences of each item in an array.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

813 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now