Solved

Select rows one after another?

Posted on 2012-03-13
9
372 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
How our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

 
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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

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.
When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

830 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