Using button to iterate through mysql resultset using php

Question

Hi people as a new web programming im having a scenario and i need some help.

Im trying to have a 'next' button on the page which will pull the next question from the database in a iterative manner. im passing php values straight to the button as text.

Currently I can grab the mysql resultset and print them all or i can grab just the one row and return its fields but what is the strategy to grab one with the next button, then another, and so on.

$result = mysql_query("SELECT * FROM $DB_QUEST_NAME ORDER BY pk_Id DESC;") or die(mysql_error());

    while ($row = mysql_fetch_array($result)){

    echo $pk_id = $row[0] . "<br>";
    echo $question = $row[1].  "<br>";
    echo $answera = $row[2]  .  "<br>";
    echo $answerb = $row[3] . "<br>";
    echo $answerc = $row[4] . "<br>";
    echo $answerd = $row[5] . "<br><br>";

    break;  
    }

In the mark up include the getNextPoll.php (above) which initial grabs the first result to displays them as button. but with every subsequent click I want to get the next, then the next and so on. I know how to set the button texts and Im also aware of server side client side responsibilites but as Im quite new to this type of development the methodology behind it a lil tricky.

Any feedback would be appreciated. Thanks.

Dave · Accepted Answer · 2011-05-10 13:34:54Z

2

I believe you're looking for MySQL's "offset". This allows you to run a query like ... limit 1 offset 0 to return the first result, then ... limit 1 offset 1 to return the 2nd result...and so on.

(You don't really need "offset 0", since that's the default anyway, but - just trying to illustrate the point.)

EDIT:

If you store the page variable in the URL like this: www.myPage.com/questions.php?pg=2

Then you can do this:

$numPerPage = 1;
$pg = $_GET['pg'];
$nxtPage = $pg++;
$offset = ($numPerPage * $pg) - 1;
$question = mysql_query("SELECT * FROM questions LIMIT " . $numPerPage . 
" OFFSET " . $offset);

Then, you're "next" link could be:

<a href="questions.php?pg=<?=$nxtPage?>">NEXT</a>

edited May 10, 2011 at 13:34

answered May 10, 2011 at 13:17

Dave

29.2k26 gold badges117 silver badges184 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Dave Over a year ago

The query you mentioned will return 10 results start at the 10th result - so - it would show 10, 11, 12...19 I suggest keeping the "question" variable in the URL: www.myPage.com?q=2 Then, you're query can just offset by the value of $_GET['q'] Does that make sense? Or did I misunderstand your comment-question?

L-Samuels Over a year ago

Thanks guys. Offset is new to me. I was using limit though in some other scripts but I in this one i removed it to get the whole resultset to iterate through. okay so if i'm using subsequent calls to the php script containing the 'SELECT column FROM table LIMIT 1 OFFSET 1' statement how will i ensure that the next call will pull the next row and not start again from the beginning (giving me always the first question). Its is better to wrap the statement inside a function or something? then call it somehow from the triggering of a html 'next' button.

Dave Over a year ago

Also - Andre has a better line for the $pg variable - his checks if it exists, and if it doesn't, sets $pg to 1 by default: $page_number = isset($_GET['page']) ? $_GET['page'] : 1;

Ortiga · Accepted Answer · 2011-05-10 13:27:25Z

2

You can use LIMIT for your query

Example getting first 10 results:

"SELECT * FROM $DB_QUEST_NAME ORDER BY pk_Id DESC LIMIT 1,10";

To get next just multiply by the page number:

$page_number = isset($_GET['page']) ? $_GET['page'] : 1;
$results_per_page = 10;
$end = $results_per_page * $page_number;
start = $end - $results_per_page;

"SELECT * FROM $DB_QUEST_NAME ORDER BY pk_Id DESC LIMIT {$start},{$end}";

This is just a basic example. For a real implementation, use offset, as others have explained

answered May 10, 2011 at 13:27

Ortiga

8,8466 gold badges45 silver badges74 bronze badges

Comments

neurino · Accepted Answer · 2011-05-10 13:21:09Z

1

Your $DB_QUEST_NAME table should also have an ord column where you set your questions order (I won't base it on primary key: what if you want to later add a new question as the first one?)

Then next button should query for $ord + 1 (if any).

answered May 10, 2011 at 13:21

neurino

12.5k3 gold badges48 silver badges69 bronze badges

1 Comment

L-Samuels Over a year ago

Okay thanks @neurone I'll look it. That is a valid point. I was just coding to get functionality up and running quick as part of a learning project.

Matic · Accepted Answer · 2011-05-10 13:19:08Z

0

Use "Offset" and "Limit" in the query. Check: http://www.petefreitag.com/item/451.cfm for a simple example.

answered May 10, 2011 at 13:19

Matic

4842 silver badges7 bronze badges

Collectives™ on Stack Overflow

Using button to iterate through mysql resultset using php

4 Answers 4

3 Comments

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related