Solved

Select rows one after another?

Posted on 2012-03-13
9
360 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
 
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
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
This article discusses four methods for overlaying images in a container on a web page
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

762 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

20 Experts available now in Live!

Get 1:1 Help Now