Solved

Select rows one after another?

Posted on 2012-03-13
9
374 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

Suggested Solutions

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
3 proven steps to speed up Magento powered sites. The article focus is on optimizing time to first byte (TTFB), full page caching and configuring server for optimal performance.
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…
The viewer will learn how to count occurrences of each item in an array.

738 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