How do I get the row number from from an sql table query

[jaisol99]

Hi all

 

I have a table that I query and it returns a number of rows.

mysql_num_rows($query)  = $total

 

I want to have next and previous buttons, is there a way of retrieving the current row number, and can you move it backwards and forwards by 1, or do I have to keep a track of the button presses

 

many thanks

 

[guidance51x]

you could do something like this:

 

$query = mysql_query($sql)
$total = mysql_num_rows($query)

while($row = mysql_fetch_assoc($query)){
      //do whatever
      $current = $row['id']; // best practice to have an 'id' field for entries
      while($current > 0){
             echo "<a href='previouspage.php?id=" . $current - 1 . "'>Previous</a>";
       }
      while($current < $total){
            echo "<a href='nextpage.php?id=" . $current + 1 . "'>Next</a>";
      }
}

 

Hope this helps!

[jaisol99]

That looks like what I need

 

Is the $row['id'] generated automaticaly by the query

[Stephen]

That looks like what I need

 

Is the $row['id'] generated automaticaly by the query

 

You would have to create your own id row, but you could make it auto-increment.

[jaisol99]

Darn..

The results from my query are only a small group. A unique 'íd' would not tell me where I was in the rows of results.

Surely there is something like a pointer that tells mysql where it is when it returns its results.

If not I'll have to track button presses.

[Stephen]

Well you could use LIMIT. For example, if you started on page one, a previous button would be disabled and the next button would go to page two (assuming there is more than one "page"). On page two, the previous button would be enabled. Obviously, next would add one to the page number and previous would subtract one. Lets say you want to show 3 results per page:

 

<?php
//mysql connect, select db, queries done, basically the script you had before excluding outputting the information

$page = intval($_GET["page"]);
$total = mysql_num_rows($query);
$increment = 3;
$current = ($page-1)*$increment;
$previous = false;
$next = false;

if ($current > 0)
{
	$previous = true;
}

if ($current < $total)
{
	$next = true;
}

if ($previous)
{
	//display previous, $page -1
}

if ($next)
{
	//display next, $page +1
}

$sql = "SELECT * FROM table LIMIT {$current},{$increment}";
$query = mysql_query($sql);
$numrows = mysql_num_rows($query);
if ($numrows > 0)
{
	while ($rows = mysql_fetch_array($query))
	{
		//output the information or something
	}
}
else
{
	die("Page information not located");
}
?>

 

I did that pretty fast, so replace the comments with whatever is needed. The main thing you need to look at is SQL and the variables at the top. If, after the comments are replaced, the script doesn't work (through an error of my own), please note that I did not test it, but I'm pretty sure there shouldn't be any errors.