[sarva842003]

$x=Mysql_query(‘Select blahblah…’)
While($m=Mysql_fetch_assoc($x))
{
displaying the results
}

Now I want to show 10 results per page and display links link 1 2 3 4 NEXT. How to do??

This post has been edited by sarva842003: 03 June 2011 - 12:09 PM

[RPGonzo]

Pagination .... http://www.dreaminco...-how-to-use-it/

[codeprada]

RPGonzo, on 03 June 2011 - 03:30 PM, said:

Pagination .... http://www.dreaminco...-how-to-use-it/

That's the long way...Pagination can be as simply as using the SQL keyword LIMIT.

$stmt = $mysqli->prepare("SELECT * FROM table LIMIT ?, ?");
$stmt->bind_params("ii", $begin, $amount);

mysql_query("SELECT * FROM table LIMIT 0, 10"); //10 rows starting from row 0

[RPGonzo]

Not really the "long way", the limit keyword only limits the amount of results that the query provides. So that's half the battle.

It in no way gives you the ability to offer a "paging" option to your users to have a truly "paged" experience, without doing the extra leg work and creating it. Which is from what i gathered from the OP what he wanted by wanting:

Quote

display links link 1 2 3 4 NEXT

to be honest that tutorial lacks anything i would prefer to see anyhow ... here is a test script I used a while back playing with pagination ...

<?php
error_reporting(E_ALL);
ini_set('display_errors', 1);

/**
 * This is our database settings
 */
$dsn = 'mysql:dbname=test;host=127.0.0.1';
$user = 'test';
$password = 'test';

/**
 * This is our paging options
 */
$_per_page = 10;
$_numeric_index = true;
$_url_param = 'page';
$_cur_page = isset($_GET[$_url_param]) ? $_GET[$_url_param] : 1; // default on first page


try {
    $db = new PDO($dsn, $user, $password);
} catch (PDOException $e) {
    die('Connection failed: ' . $e->getMessage());
}

$query = "SELECT * FROM data";
$prepped = $db->prepare($query);
$prepped->execute();

echo "We pulled (" . $prepped->rowCount() . ") rows from the database.<br/>";
echo "But we only want to show (" . $_per_page . ") per page.<br/>";

if ($prepped->rowCount() > $_per_page) {
	// do some match for our low and total pages
	$_low_limit = $_per_page * ($_cur_page - 1);
	$_total_pages = ceil($prepped->rowCount() / $_per_page);
	
	// modify the query to limit
	$query .= " LIMIT " . $_low_limit . "," . $_per_page;
	
	// re prepare and execute the query
	$prepped = $db->prepare($query);
	$prepped->execute();
	
	// now we compile our paging HTML
	$_paging_html = '<div class="paging">';
	$_url = $_SERVER['SCRIPT_NAME'] . '?';
	// if we have arguments in the URL already we need to preserve them
	if (preg_match('/[^\/\?](.*)$/', $_SERVER['QUERY_STRING'], $params)) {
		$_url .= $params[0];
		// if our paging param is there remove it
		$_url = preg_replace('/&' . $_url_param . '=[\d]/', '', $_url);
	}
	$_link = '<a href="' . $_url . '&' . $_url_param . '=%s" target="_self">%s</a>';
	
	// if we want a numeric index
	if ($_numeric_index) {
		// now the rest of the pages
		for ($i=1; $i<=$_total_pages; $i++) {
			// if it's the current page we don't show it
			if ($_cur_page == $i) continue;
			$_paging_html .= sprintf($_link, $i, $i) . " .. ";
		}
	} else {
		// we just want next and prev buttons
		if (($_cur_page - 1) > 0) {
			$_paging_html .= sprintf($_link, ($_cur_page - 1), '<- Prev') . "..";
		}
		if (($_cur_page + 1) <= $_total_pages) {
			$_paging_html .= sprintf($_link, ($_cur_page + 1), 'Next ->');
		}
	}	
	
	// both ways we show a 'legend'
	$_paging_html .= " Page (" . $_cur_page . ") of (" . $_total_pages . ")";
	// close our div
	$_paging_html .= "</div>";
}

while ($row = $prepped->fetch(PDO::FETCH_OBJ)) {
	echo $row->dbid . "<br/>";
}

echo isset($_paging_html) ? $_paging_html : '';

?>

test db data

CREATE TABLE `data` (
  `dbid` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`dbid`)
) ENGINE=MyISAM AUTO_INCREMENT=64 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

/*Data for the table `data` */

insert  into `data`(`dbid`) values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15),(16),(17),(18),(19),(20),(21),(22),(23),(24),(25),(26),(27),(28),(29),(30),(31),(32),(33),(34),(35),(36),(37),(38),(39),(40),(41),(42),(43),(44),(45),(46),(47),(48),(49),(50),(51),(52),(53),(54),(55),(56),(57),(58),(59),(60),(61),(62),(63);

If i were any good at commenting my own code i would submit this myself, but my commenting skills are lacking when it comes to others understanding them lol.

Hence my comment under my avatar.

This post has been edited by RPGonzo: 03 June 2011 - 02:32 PM

[satis]

My only contribution would be to use Select count(*) instead of select *. No need to pull back all that data if you're not really going to use it. Besides, I hate select *, as it causes no end of trouble if you end up changing the schema of your db.

So, basically, do a select count(*) to see how many rows there are, pull your GET/POST/SESSION var to figure out where in the result set you're currently located, then run a second query with a LIMIT based on that position.

In some cases, the SQL query may be extremely expensive, so doing just a single select and doing everything else in code may be the smartest route... a good example would be in a shared hosting environment where you're limited in the number of queries you can do in a timeframe. Otherwise, I'd just do two separate queries to reduce the memory usage and overhead of iterating through a large resultset. This is certainly true if you've got a really huge number of rows coming back.

[RPGonzo]

satis, on 03 June 2011 - 08:54 PM, said:

My only contribution would be to use Select count(*) instead of select *. No need to pull back all that data if you're not really going to use it. Besides, I hate select *, as it causes no end of trouble if you end up changing the schema of your db.

So, basically, do a select count(*) to see how many rows there are, pull your GET/POST/SESSION var to figure out where in the result set you're currently located, then run a second query with a LIMIT based on that position.

In some cases, the SQL query may be extremely expensive, so doing just a single select and doing everything else in code may be the smartest route... a good example would be in a shared hosting environment where you're limited in the number of queries you can do in a timeframe. Otherwise, I'd just do two separate queries to reduce the memory usage and overhead of iterating through a large resultset. This is certainly true if you've got a really huge number of rows coming back.

This would be true when your using a traditional mysql(i)_query, with the pdo prepared statements i don't believe the worry is there. The only time the query gets executed a second time is IF the result set is larger than the amount limit per page. in which case it uses the pulled result set without performing another query. But you truly don't pull a data set with the prepared statement until you execute the fetch function, to my knowledge and my understanding anyways.

I fully agree with the select * ... this was merely for an example.

How the prepared statements effect your connection limit on a shared hosting platform i'm not familiar with unfortunately so to that i will leave that to someone else to comment on.

This post has been edited by RPGonzo: 03 June 2011 - 09:13 PM

[noorahmad]

try this:

Record Navigation

Short & simple

This post has been edited by noorahmad: 03 June 2011 - 09:20 PM

[codeprada]

LIMIT cuts the coding down in half and why retrieve all the rows when you're only going to show\use just 10 or how much ever of them? Your SELECT statement should only retrieve what you are going to use.

[RPGonzo]

Technically if I'm understanding the manual correctly when you execute a statement using prepared statements it does not retrieve the data set until you call the fetch* function ...

Quote

<?php
$sth = $dbh->prepare("SELECT name, colour FROM fruit");
$sth->execute();

/* Fetch all of the remaining rows in the result set */
print("Fetch all of the remaining rows in the result set:\n");
$result = $sth->fetchAll();
print_r($result);
?>

So in my example i only pull the data set once which is where i'm displaying the data. Therefore only pulling the data i needed for that particular page.

Again LIMIT is half the battle with a dynamically generated paging system. At least from what I have seen if you have a better faster solution by all means.

This post has been edited by RPGonzo: 04 June 2011 - 05:57 PM