CODEWITHSUNDEEP
phpsql
demechanico
demechanico

Reputation: 361

PHP - Showing the next row from database on each pageload

I need to be able to show next quote from database on each page load. How can I accomplish this?

The only solution I can think of would be:

1. SELECT counter FROM certain_table
2. SELECT COUNT(*) as num_of_quotes FROM quotes
3. counter = (counter + 1) % num_of_quotes;
4. SELECT * FROM quotes LIMIT counter, 1
5. Show quote
6. UPDATE certain_table SET counter = {counter}

But this means that 4 SQL queries will be done each time to show a quote. Any way to do it more efficiently?

P.S. It will be coded in PHP and page will be available for use on external pages, through a JS file.

Upvotes: 0

Views: 138

Answers (2)

STT LCU
STT LCU

Reputation: 4330

Add a column in your quotes table, and call it view_counter. This will contain the number of times each quote has been shown to the user.

This example presumes the table structure as follows:

quotes(id, quote, view_counter)

Here's the query:

select id, quote
from quotes
order by view_counter asc
limit 1

Pretty simple, right? Now, before rendering the page, run the following query:

update quotes set view_counter = 1+ view_counter where id = :quote_id

where :quote_id is the value of the query id you downloaded.

Two queries! not bad. But what if we have a race condition, and by chance two different users request a query at the same time? You have two choices:

  1. Don't care (which I endorse) because nobody will lose a sweat if somebody else has read the same quote as him, the other side of the world.
  2. Lock tables. If you want to use this route, run this query before the 2 I said: LOCK TABLES quote WRITE and this one as last query UNLOCK TABLES

I vote for option 1.

Upvotes: 1

srakl
srakl

Reputation: 2619

if you are storing your quotes with a unique ID, you could do something like this

next quote:

SELECT * FROM `quotes` WHERE `id` > $currentID ORDER BY `id` ASC LIMIT 1 ;

for previous quote

SELECT * FROM `quotes` WHERE `id` < $currentID ORDER BY `id` DESC LIMIT 1 ;

basically you will be looking for the next and previous ID in your database. and comparing it to the one you are on right now. This will display the quotes you want

Upvotes: 0

Related Questions