How to write PHP code to go through records one at a time?

So I'm new to php/mysql. I am testing with a very small database (single table with only 10 records) as I'm just trying to learn.

I'm displaying the following result on a php page: select * from mytable where city = "atlanta"

It works.. the result shows up on my page. Now, I'm wondering, how can I put a link under 开发者_JS百科this record that says "show next result" and on clicking it, it simply pulls up a new page with the next result in my database?

Does this require a whole pagination script or is there a simple way?

Thanks!

---

The easiest way would be to include an integer $_GET variable that is the row number + 1.

<a href="index.php?id=<?php echo ($id + 1) ?>" />

Then for your PHP script, you need to get the value, or use a default value (0) if one is not provided.

<?php
    if(!isset($_GET['id']))
        $id = 0;
    else
        $id = $_GET['id'];
?>

Don't forget to bound your integer, so you can't get invalid results. In your case, make sure that $id does not go below 0 or above 9. Something simple like

    if($id < 0) $id = 0;

    if($id > 9) $id = 9;

should work. This goes immediately after the previous code sample.

Then for your query, simply do

$sql = "select * from mytable where city = 'atlanta' LIMIT {$id}, 1";

Notice the LIMIT clause here that takes two parameters. The first, your current $id number tell the query where to start looking in the result set. The second parameter tells it how many results from the start it should take. In this case, we always want one, so we'll leave it as 1.

Of course, you should parametrize this query, but since it sounds like you're just learning SQL, that's not a huge deal. If this was a public site, you would need to though.

You could also use mysql_num_rows or the PDO equivalent to get the total number of applicable rows, then determine whether $id + 1 is greater than the total number of rows (meaning you were on the last record), and if so, not display the link to the next record.

Complete code

<?php
    if(!isset($_GET['id']))
        $id = 0;
    else
        $id = $_GET['id'];

    if($id < 0) $id = 0;

    if($id > 9) $id = 9;

    $sql = "select * from mytable where city = 'atlanta' LIMIT {$id}, 1";

    $result = mysql_fetch_assoc($sql);
?>

Display your result here

<?php if( ( $id + 1 ) < mysql_num_rows($result) ) :

<a href="index.php?id=<?php echo ($id + 1) ?>" />

<?php endif; ?>

---

What you are looking is paging in php.There are so many tutorials on the web.here is one you can see

---

A whole pagination script? :) Nah, just something like this

$items_per_page = 1;
$page = max( 1, (int) $_GET['page'] );
$offset = ($page-1) * $items_per_page;
$limit = $items_per_page;

$sql = "SELECT * FROM `table` LIMIT {$offset}, {$limit}"

Now just call your script using

http://localhost/index.php?page=1
http://localhost/index.php?page=2
http://localhost/index.php?page=3
etc

---

Now, I'm wondering, how can I put a link under this record that says "show next result" and on clicking it

You must provide your link with next data base condition key value.

For example Your Page is:

From db selected :atlanta

Link=>Next Entry(Here you must provide the next condition. Your real condition is where city = "atlanta" so next You must know)

The right way to do this is create sql question in this example

$sql = "SELECT * FROM mytable WHERE id='<?=$_POST['id']?>'";

and in this way you can print link

<a href='?id=<?=db["id"]+1?>'>Next Entry</a>

---

The simplest way I can think of offhand (if you don't want to use a fullblown pagination script) is to get the ids of your rows in an array (probably using array_keys()), track the currently active key (via $_GET) then use some simple logic to figure out what the previous and next values in the id array are, and use them in building the nav links.