lucnap · April 19, 2019 13:09
diff --git a/paginations.php b/paginations.php
 <?php

 /*
 	Generic functions to paginate data from a database select and from json data
 	thanks to
 	https://stackoverflow.com/a/23910460
 	https://stackoverflow.com/a/3707457
 	http://www.sqlitetutorial.net/sqlite-sample-database/
 	
 */

        $pdo = new PDO("sqlite:./chinook.db");

        $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        $pdo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);

 		// test table data
 		$recs = paginateTable($pdo, 
 					'tracks', 
 					'select Count(*) as TotalRecs, t1.Name, t1.Composer, t2.Title as AlbumTitle 
 					from tracks t1 Left Join albums t2 ON t1.AlbumId = t2.AlbumId 
 					Where t1.Name like \'B%\'
 					Group by t1.Composer', 
 					10,
 					filter_input(INPUT_GET, 'page', FILTER_VALIDATE_INT));		
 		print_r('<br /><h2>paginated select table</h2><br /><pre>'. json_encode($recs, JSON_PRETTY_PRINT) . '</pre>');
 		
 		
 		
 		
 		// test json data
 		$sample_data = '{ "records": [ {"Name": "Branch Closing", "Composer": null,    "AlbumTitle": "The Office, Season 3"}, {"Name": "Bad Boy Boogie", "Composer": "AC\/DC", "AlbumTitle": "Let There Be Rock"}, {"Name": "Beautiful Day", "Composer": "Adam Clayton, Bono, Larry Mullen, The Edge", "AlbumTitle": "All That You Can\'t Leave Behind"}, {"Name": "Breaking The Rules", "Composer": "Angus Young, Malcolm Young, Brian Johnson", "AlbumTitle": "For Those About To Rock We Salute You"}, {"Name": "By The Way", "Composer": "Anthony Kiedis, Flea, John Frusciante, and Chad Smith", "AlbumTitle": "By The Way"}, {"Name": "Blood Sugar Sex Magik", "Composer": "Anthony Kiedis\/Chad Smith\/Flea\/John Frusciante", "AlbumTitle": "Blood Sugar Sex Magik"}, {"Name": "Balada Do Louco", "Composer": "Arnaldo Baptista - Rita Lee", "AlbumTitle": "Minha Hist\u00f3ria"}, {"Name": "Bring\'em Back Alive", "Composer": "Audioslave\/Chris Cornell", "AlbumTitle": "Audioslave"}, {"Name": "Banho De Lua", "Composer": "B. de Filippi - F. Migliaci - Vers\u00e3o: Fred Jorge", "AlbumTitle": "Minha Hist\u00f3ria"}, {"Name": "Binky The Doormat", "Composer": "Bill Berry-Peter Buck-Mike Mills-Michael Stipe", "AlbumTitle": "New Adventures In Hi-Fi"}, {"Name": "Belong", "Composer": "Bill Berry\/Michael Stipe\/Mike Mills\/Peter Buck", "AlbumTitle": "Out Of Time"}, {"Name": "Brain Stew", "Composer": "Billie Joe Armstrong -Words Green Day -Music", "AlbumTitle": "International Superhits"}, {"Name": "Bullet With Butterfly Wings", "Composer": "Billy Corgan", "AlbumTitle": "Rotten Apples: Greatest Hits"}, {"Name": "Breathe", "Composer": "Billy Duffy\/Ian Astbury\/Marti Frederiksen\/Mick Jones", "AlbumTitle": "Beyond Good And Evil"}, {"Name": "Bloodsucker", "Composer": "Blackmore, Gillan, Glover, Lord, Paice", "AlbumTitle": "Deep Purple In Rock"}, {"Name": "Breakfast In Bed", "Composer": "Blackmore, Glover, Turner", "AlbumTitle": "Slaves And Masters"}, {"Name": "Bullet The Blue Sky", "Composer": "Bono\/Clayton, Adam\/Mullen Jr., Larry\/The Edge", "AlbumTitle": "Rattle And Hum"}, {"Name": "Breadfan", "Composer": "Bourge\/Phillips\/Shelley", "AlbumTitle": "Garage Inc. (Disc 2)"}, {"Name": "Beach Sequence", "Composer": "Brian Eno, Bono, Adam Clayton, The Edge & Larry Mullen Jnr.", "AlbumTitle": "Original Soundtracks 1"}, {"Name": "Bring Your Daughter... ...To The Slaughter", "Composer": "Bruce Dickinson", "AlbumTitle": "No Prayer For The Dying"}, {"Name": "Brave New World", "Composer": "Bruce Dickinson\/David Murray\/Steve Harris", "AlbumTitle": "Rock In Rio [CD1]"}, {"Name": "Be Quick Or Be Dead", "Composer": "Bruce Dickinson\/Janick Gers", "AlbumTitle": "Fear Of The Dark"}, {"Name": "Baby", "Composer": "Caetano Veloso", "AlbumTitle": "Minha Hist\u00f3ria"}, {"Name": "Bem Leve", "Composer": "Caetano Veloso e Gilberto Gil", "AlbumTitle": "Barulhinho Bom"}, {"Name": "Beija-Flor", "Composer": "Caruso\/Cleber\/Deo\/Osmar", "AlbumTitle": "Sambas De Enredo 2001"}, {"Name": "Bai\u00e3o Ambiental [Instrumental]", "Composer": "Chico Science", "AlbumTitle": "Afrociberdelia"}, {"Name": "Bleed Together", "Composer": "Chris Cornell", "AlbumTitle": "A-Sides"}, {"Name": "Black Moon Creeping", "Composer": "Chris Robinson\/Rich Robinson", "AlbumTitle": "Live [Disc 2]"}, {"Name": "Bell Bottom Blues", "Composer": "Clapton", "AlbumTitle": "The Cream Of Clapton"}, {"Name": "Badge", "Composer": "Clapton\/Harrison", "AlbumTitle": "The Cream Of Clapton"}, {"Name": "Bite The Bullet", "Composer": "Clarke\/Kilmister\/Taylor", "AlbumTitle": "Ace Of Spades"}, {"Name": "Be Yourself", "Composer": "Cornell, Commerford, Morello, Wilk", "AlbumTitle": "Out Of Exile"}, {"Name": "Burn", "Composer": "Coverdale\/Lord\/Paice", "AlbumTitle": "MK III The Final Concerts [Disc 1]"}, {"Name": "Berlim", "Composer": "Da Gama\/Toni Garrido", "AlbumTitle": "Ac\u00fastico MTV [Live]"}, {"Name": "Blood", "Composer": "Dave Abbruzzese\/Eddie Vedder\/Jeff Ament\/Mike McCready\/Stone Gossard", "AlbumTitle": "Vs."}, {"Name": "Best Of You", "Composer": "Dave Grohl, Taylor Hawkins, Nate Mendel, Chris Shiflett", "AlbumTitle": "In Your Honor [Disc 1]"}, {"Name": "Black", "Composer": "Dave Krusen\/Stone Gossard", "AlbumTitle": "Ten"}, {"Name": "Big Machine", "Composer": "Dave Kushner, Duff, Matt Sorum, Scott Weiland & Slash", "AlbumTitle": "Contraband"}, {"Name": "Back in the Village", "Composer": "Dickinson\/Smith", "AlbumTitle": "Powerslave"}, {"Name": "Book Of Thel", "Composer": "Eddie Casillas\/Roy Z", "AlbumTitle": "Chemical Wedding"}, {"Name": "Better Man", "Composer": "Eddie Vedder", "AlbumTitle": "Live On Two Legs [Live]"}, {"Name": "Balada Da Paula", "Composer": "Emerson Villani", "AlbumTitle": "Roda De Funk"}, {"Name": "Before You Accuse Me", "Composer": "Eugene McDaniel", "AlbumTitle": "Unplugged"}, {"Name": "Balada Do Amor Inabal\u00e1vel", "Composer": "Fausto Fawcett\/Samuel Rosa", "AlbumTitle": "Maquinarama"}, {"Name": "Burn Away", "Composer": "Foo Fighters", "AlbumTitle": "One By One"}, {"Name": "Bate Lata", "Composer": "F\u00e1bio Nolasco\/Gal Sales\/Ivan Brasil", "AlbumTitle": "Ax\u00e9 Bahia 2001"}, {"Name": "By-Tor And The Snow Dog", "Composer": "Geddy Lee And Alex Lifeson\/Geddy Lee And Neil Peart\/Rush", "AlbumTitle": "Retrospective I (1974-1980)"}, {"Name": "Blues For Pablo (Alternate Take)", "Composer": "Gil Evans", "AlbumTitle": "Miles Ahead"}, {"Name": "Beira Mar", "Composer": "Gilberto Gil", "AlbumTitle": "Unplugged"}, {"Name": "Bat Macumba", "Composer": "Gilberto Gil - Caetano Veloso", "AlbumTitle": "Minha Hist\u00f3ria"}, {"Name": "Believe", "Composer": "Henry Hirsch\/Lenny Kravitz", "AlbumTitle": "Greatest Hits"}, {"Name": "Busca Vida", "Composer": "Herbert Vianna", "AlbumTitle": "Arquivo II"}, {"Name": "Better Than You", "Composer": "Hetfield, Ulrich", "AlbumTitle": "ReLoad"}, {"Name": "Bad Seed", "Composer": "Hetfield, Ulrich, Hammett", "AlbumTitle": "ReLoad"}, {"Name": "Boogie With Stu", "Composer": "Ian Stewart\/John Bonham\/John Paul Jones\/Mrs. Valens\/Robert Plant", "AlbumTitle": "Physical Graffiti [Disc 2]"}, {"Name": "Bad Moon Rising", "Composer": "J. C. Fogerty", "AlbumTitle": "Chronicle, Vol. 1"}, {"Name": "Born To Move", "Composer": "J.C. Fogerty", "AlbumTitle": "Chronicle, Vol. 2"}, {"Name": "Battery", "Composer": "J.Hetfield\/L.Ulrich", "AlbumTitle": "Master Of Puppets"}, {"Name": "Big Bad Bill (Is Sweet William Now)", "Composer": "Jack Yellen\/Milton Ager", "AlbumTitle": "Diver Down"}, {"Name": "Baby Break It Down", "Composer": "Jagger\/Richards", "AlbumTitle": "Voodoo Lounge"}, {"Name": "Blackened", "Composer": "James Hetfield, Lars Ulrich & Jason Newsted", "AlbumTitle": "...And Justice For All"}, {"Name": "Bleeding Me", "Composer": "James Hetfield, Lars Ulrich, Kirk Hammett", "AlbumTitle": "Load"}, {"Name": "Believe", "Composer": "James Iha", "AlbumTitle": "Judas 0: B-Sides and Rarities"}, {"Name": "Black Capricorn Day", "Composer": "Jay Kay", "AlbumTitle": "Synkronized"}, {"Name": "Butterfly", "Composer": "Jay Kay\/Toby Smith", "AlbumTitle": "Synkronized"}, {"Name": "Big Wave", "Composer": "Jeff Ament", "AlbumTitle": "Pearl Jam"}, {"Name": "Bleed The Freak", "Composer": "Jerry Cantrell", "AlbumTitle": "Facelift"}, {"Name": "Bron-Yr-Aur", "Composer": "Jimmy Page", "AlbumTitle": "Physical Graffiti [Disc 2]"}, {"Name": "Bring It On Home", "Composer": "Jimmy Page, Robert Plant", "AlbumTitle": "Led Zeppelin II"}, {"Name": "Burning Up", "Composer": "Jimmy Page, Robert Plant, Charlie Jones, Michael Lee", "AlbumTitle": "Walking Into Clarksdale"}, {"Name": "Bron-Y-Aur Stomp", "Composer": "Jimmy Page, Robert Plant, John Paul Jones", "AlbumTitle": "Led Zeppelin III"}, {"Name": "Babe I\'m Gonna Leave You", "Composer": "Jimmy Page\/Robert Plant", "AlbumTitle": "Led Zeppelin I"}, {"Name": "Bankrobber", "Composer": "Joe Strummer\/Mick Jones", "AlbumTitle": "The Singles"}, {"Name": "Bonzo\'s Montreux", "Composer": "John Bonham", "AlbumTitle": "Coda"}, {"Name": "Boris The Spider", "Composer": "John Entwistle", "AlbumTitle": "My Generation - The Very Best Of The Who"}, {"Name": "Baba O\'Riley", "Composer": "John Entwistle\/Pete Townshend", "AlbumTitle": "My Generation - The Very Best Of The Who"}, {"Name": "Black Dog", "Composer": "John Paul Jones\/Robert Plant", "AlbumTitle": "BBC Sessions [Disc 2] [Live]"}, {"Name": "Baltimore, DC", "Composer": "John Scofield", "AlbumTitle": "Outbreak"}, {"Name": "Blitzkrieg", "Composer": "Jones\/Sirotto\/Smith", "AlbumTitle": "Garage Inc. (Disc 2)"}, {"Name": "Borogodo", "Composer": "Jo\u00e3o Suplicy", "AlbumTitle": "Cafezinho"}, {"Name": "Blind Curve: Vocal Under A Bloodlight \/ Passing Strangers \/ Mylo \/ Perimeter Walk \/ Threshold", "Composer": "Kelly, Mosley, Rothery, Trewaves", "AlbumTitle": "Misplaced Childhood"}, {"Name": "Breed", "Composer": "Kurt Cobain", "AlbumTitle": "Nevermind"}, {"Name": "Bad Boy", "Composer": "Larry Williams", "AlbumTitle": "BackBeat Soundtrack"}, {"Name": "Black Velveteen", "Composer": "Lenny Kravitz", "AlbumTitle": "Greatest Hits"}, {"Name": "Blanco", "Composer": "Marisa Monte\/poema de Octavio Paz\/vers\u00e3o: Haroldo de Campos", "AlbumTitle": "Barulhinho Bom"}, {"Name": "Back to Black", "Composer": "Mark Ronson", "AlbumTitle": "Back to Black"}, {"Name": "Bicycle Race", "Composer": "Mercury, Freddie", "AlbumTitle": "Greatest Hits I"}, {"Name": "Billie Jean", "Composer": "Michael Jackson", "AlbumTitle": "Carry On"}, {"Name": "Bitter Pill", "Composer": "Mick Mars\/Nikki Sixx\/Tommy Lee\/Vince Neil", "AlbumTitle": "Motley Crue Greatest Hits"}, {"Name": "Boulevard Of Broken Dreams", "Composer": "Mike Dint, Billie Joe, Tr\u00e9 Cool", "AlbumTitle": "American Idiot"}, {"Name": "Black Satin", "Composer": "Miles Davis", "AlbumTitle": "The Essential Miles Davis [Disc 2]"}, {"Name": "Brasil", "Composer": "Milton Nascimento, Fernando Brant", "AlbumTitle": "Milton Nascimento Ao Vivo"}, {"Name": "Beijo do Olhar", "Composer": "M\u00f4nica Marianno", "AlbumTitle": "Demorou..."}, {"Name": "Bark at the Moon", "Composer": "O. Osbourne", "AlbumTitle": "Bark at the Moon (Remastered)"}, {"Name": "Believer", "Composer": "O. Osbourne, R. Daisley, R. Rhoads", "AlbumTitle": "Tribute"}, {"Name": "Black Diamond", "Composer": "Paul Stanley", "AlbumTitle": "Greatest Kiss"}, {"Name": "Beth", "Composer": "Peter Criss, Stan Penridge, Bob Ezrin", "AlbumTitle": "Unplugged [Live]"}, {"Name": "Breakthru", "Composer": "Queen", "AlbumTitle": "Greatest Hits II"}, {"Name": "Bad Attitude", "Composer": "Richie Blackmore, Ian Gillian, Roger Glover, Jon Lord", "AlbumTitle": "Knocking at Your Back Door: The Best Of Deep Purple in the 80\'s"}, {"Name": "Black Night", "Composer": "Richie Blackmore, Ian Gillian, Roger Glover, Jon Lord, Ian Paice", "AlbumTitle": "Knocking at Your Back Door: The Best Of Deep Purple in the 80\'s"}, {"Name": "Break on Through", "Composer": "Robby Krieger, Ray Manzarek, John Densmore, Jim Morrison", "AlbumTitle": "The Doors"}, {"Name": "Black Country Woman", "Composer": "Robert Plant", "AlbumTitle": "Physical Graffiti [Disc 2]"}, {"Name": "B\u00ea-a-B\u00e1", "Composer": "Rodolfo", "AlbumTitle": "Cesta B\u00e1sica"}, {"Name": "Beth", "Composer": "S. Penridge, Bob Ezrin, Peter Criss", "AlbumTitle": "Greatest Kiss"}, {"Name": "Blow Your Mind", "Composer": "Smith, Toby", "AlbumTitle": "Emergency On Planet Earth"}, {"Name": "Black Hole Sun", "Composer": "Soundgarden", "AlbumTitle": "A-Sides"}, {"Name": "Blood On The World\'s Hands", "Composer": "Steve Harris", "AlbumTitle": "The X Factor"}, {"Name": "Blind Man", "Composer": "Steven Tyler, Joe Perry, Taylor Rhodes", "AlbumTitle": "Big Ones"}, {"Name": "Bushleager", "Composer": "Stone Gossard", "AlbumTitle": "Riot Act"}, {"Name": "Black", "Composer": "Stone Gossard & Eddie Vedder", "AlbumTitle": "Live On Two Legs [Live]"}, {"Name": "B.Y.O.B.", "Composer": "Tankian, Serj", "AlbumTitle": "Mezmerize"}, {"Name": "Book of Hours", "Composer": "Terry Bozzio, Steve Stevens, Tony Levin", "AlbumTitle": "[1997] Black Light Syndrome"}, {"Name": "Babylon", "Composer": "The Tea Party", "AlbumTitle": "Transmission"}, {"Name": "Bichos Escrotos (Vinheta)", "Composer": "Tit\u00e3s", "AlbumTitle": "Ac\u00fastico"}, {"Name": "Beijo Partido", "Composer": "Toninho Horta", "AlbumTitle": "Minas"}, {"Name": "Bad", "Composer": "U2", "AlbumTitle": "The Best Of 1980-1990"}, {"Name": "Babyface", "Composer": "U2; Bono", "AlbumTitle": "Zooropa"}, {"Name": "Ballot or the Bullet", "Composer": "Van Halen", "AlbumTitle": "Van Halen III"}, {"Name": "Bumbo Da Mangueira", "Composer": "V\u00e1rios", "AlbumTitle": "Chill: Brazil (Disc 2)"}, {"Name": "Breakdown", "Composer": "W. Axl Rose", "AlbumTitle": "Use Your Illusion II"}, {"Name": "Brain Damage", "Composer": "Waters", "AlbumTitle": "Dark Side Of The Moon"}, {"Name": "Back Door Man", "Composer": "Willie Dixon, C. Burnett", "AlbumTitle": "The Doors"}, {"Name": "Bad, Bad Leroy Brown", "Composer": "jim croce", "AlbumTitle": "My Way: The Best Of Frank Sinatra [Disc 1]"}] }';
 		$recs = paginatejson($sample_data, filter_input(INPUT_GET, 'page', FILTER_VALIDATE_INT), 10);
 		print_r('<br /><h2>paginated json</h2><br /><pre>'. json_encode($recs, JSON_PRETTY_PRINT) . '</pre>');
 		
 		
 		exit;




 function paginatejson($jsondata, $requestedPage, $itemsPerPage) {

 	// just normal getting data
 	$raw_data = json_decode($jsondata, true);
 	$raw_data = $raw_data["records"];

 	// use get variable to paging number
 	$page = !isset($requestedPage) ? 1 : intval($requestedPage);
 	if ($page < 1) $page = 1;
 	
 	$limit  = intval($itemsPerPage);	
 	$total_items = count($raw_data); 	// total items
 	$total_pages = ceil($total_items / $limit);
 	if ($page > $total_pages) $page = $total_pages;
 	
 	$offset = ($page - 1) * $limit; 	// offset
 	
 	$finalSlice = array_splice($raw_data, $offset, $limit); // splice them according to offset and limit


 	$navlinks = array();
 	$navlinks["totalRecords"] = $total_items;
 	$navlinks["currentPage"] = $page;
 	$navlinks["firstPage"] = 1;
 	$navlinks["prevPage"] = ($page - 1);
 	$navlinks["nextPage"] = ($page + 1);
 	$navlinks["lastPage"] = $total_pages;

 	return array("navlinks" => $navlinks, "data" => $finalSlice);
 }







 // paginate table and outputs json
 function paginateTable($pdo, $tablename, $query, $itemsPerPage, $requestedPage) {


 	try {

 		// Find out how many items are in the table
 		//$total = $pdo->query($query)->fetchColumn();
 		$total = intval($pdo->query($query)->fetch(PDO::FETCH_ASSOC)["TotalRecs"]);


 		// How many items to list per page
 		$limit = $itemsPerPage;

 		// How many pages will there be
 		$pages = ceil($total / $limit);

 		// What page are we currently on?
 		$page = min($pages, $requestedPage, array(
 			'options' => array(
 				'default'   => 1,
 				'min_range' => 1,
 			),
 		));


 		// Calculate the offset for the query
 		$offset = ($page - 1)  * $limit;

 		// Some information to display to the user
 		$start = $offset + 1;
 		$end = min(($offset + $limit), $total);

 		//echo "<br>pages: " . $pages . "  current page: " . $page . "   offset: " . $offset . "    start: " . $start . "    end: " . $end;
 		

 		$navlinks = array();
 		$navlinks["totalRecords"] = $total;
 		$navlinks["currentPage"] = $page;
 		$navlinks["firstPage"] = 1;
 		$navlinks["prevPage"] = ($page - 1);
 		$navlinks["nextPage"] = ($page + 1);
 		$navlinks["lastPage"] = $pages;

 		// print_r('<pre>' . json_encode($navlinks, JSON_PRETTY_PRINT) . '</pre><br /><br />');


 		// Prepare the paged query
 		$stmt = $pdo->prepare($query . ' LIMIT :limit OFFSET :offset');

 		// Bind the query params
 		$stmt->bindParam(':limit', $limit, PDO::PARAM_INT);
 		$stmt->bindParam(':offset', $offset, PDO::PARAM_INT);
 		$stmt->execute();

 		
 		$records = array();
 		
 		// Do we have any results?
 		//if ($stmt->rowCount() > 0) {
 		if ($stmt) {
 			// Define how we want to fetch the results
 			$stmt->setFetchMode(PDO::FETCH_ASSOC);
 			$iterator = new IteratorIterator($stmt);
 			
 			foreach ($iterator as $row) {
 				$newrow = array();
 				for ($i = 0; $i < $stmt->columnCount(); $i++) {
 					$colname = $stmt->getColumnMeta($i)['name'];
 					if ($colname != 'TotalRecs') {
 						$newrow[$colname] = $row[$colname];
 					}
 				}				
 				$records[] = $newrow;
 			}
 			
 		}

 		return array("navlinks" => $navlinks, "data" => $records);



 	} catch (Exception $e) {
 		return array("error" => $e->getMessage());
 	}

 	
 }








 /*
 		$sql = "Select * From tracks Where TrackId > :startdId";

 		$stmt = $pdo->prepare($sql);
 		$stmt->bindValue('startdId', 0);
 		$stmt->execute();

 		$dataOut = array();
 		$dataOut["records"] = array();
 		$counterRecs=0;
 		
 		if ($stmt) {
 			try {
 				while($rs = $stmt->fetch()) {
 				  $rec = array(
 					"TrackId"      => $rs["TrackId"],
 					"Name"         => $rs["Name"],
 					"Composer"     => $rs["Composer"]
 				  );
 				  $dataOut["records"][] = $rec;
 				  $counterRecs++;

 				}
 			} catch(\PDOException $e) {
 				$dataOut['error'] = $e->getMessage();
 			}

 		}


 		header('Content-Type: application/json');
 		print_r(json_encode($dataOut, JSON_PRETTY_PRINT));
 */
	<?php

	/*
	Generic functions to paginate data from a database select and from json data
	thanks to
	https://stackoverflow.com/a/23910460
	https://stackoverflow.com/a/3707457
	http://www.sqlitetutorial.net/sqlite-sample-database/

	*/

	$pdo = new PDO("sqlite:./chinook.db");

	$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
	$pdo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);

	// test table data
	$recs = paginateTable($pdo,
	'tracks',
	'select Count(*) as TotalRecs, t1.Name, t1.Composer, t2.Title as AlbumTitle
	from tracks t1 Left Join albums t2 ON t1.AlbumId = t2.AlbumId
	Where t1.Name like \'B%\'
	Group by t1.Composer',
	10,
	filter_input(INPUT_GET, 'page', FILTER_VALIDATE_INT));
	print_r('<br /><h2>paginated select table</h2><br /><pre>'. json_encode($recs, JSON_PRETTY_PRINT) . '</pre>');




	// test json data
	$sample_data = '{ "records": [ {"Name": "Branch Closing", "Composer": null, "AlbumTitle": "The Office, Season 3"}, {"Name": "Bad Boy Boogie", "Composer": "AC\/DC", "AlbumTitle": "Let There Be Rock"}, {"Name": "Beautiful Day", "Composer": "Adam Clayton, Bono, Larry Mullen, The Edge", "AlbumTitle": "All That You Can\'t Leave Behind"}, {"Name": "Breaking The Rules", "Composer": "Angus Young, Malcolm Young, Brian Johnson", "AlbumTitle": "For Those About To Rock We Salute You"}, {"Name": "By The Way", "Composer": "Anthony Kiedis, Flea, John Frusciante, and Chad Smith", "AlbumTitle": "By The Way"}, {"Name": "Blood Sugar Sex Magik", "Composer": "Anthony Kiedis\/Chad Smith\/Flea\/John Frusciante", "AlbumTitle": "Blood Sugar Sex Magik"}, {"Name": "Balada Do Louco", "Composer": "Arnaldo Baptista - Rita Lee", "AlbumTitle": "Minha Hist\u00f3ria"}, {"Name": "Bring\'em Back Alive", "Composer": "Audioslave\/Chris Cornell", "AlbumTitle": "Audioslave"}, {"Name": "Banho De Lua", "Composer": "B. de Filippi - F. Migliaci - Vers\u00e3o: Fred Jorge", "AlbumTitle": "Minha Hist\u00f3ria"}, {"Name": "Binky The Doormat", "Composer": "Bill Berry-Peter Buck-Mike Mills-Michael Stipe", "AlbumTitle": "New Adventures In Hi-Fi"}, {"Name": "Belong", "Composer": "Bill Berry\/Michael Stipe\/Mike Mills\/Peter Buck", "AlbumTitle": "Out Of Time"}, {"Name": "Brain Stew", "Composer": "Billie Joe Armstrong -Words Green Day -Music", "AlbumTitle": "International Superhits"}, {"Name": "Bullet With Butterfly Wings", "Composer": "Billy Corgan", "AlbumTitle": "Rotten Apples: Greatest Hits"}, {"Name": "Breathe", "Composer": "Billy Duffy\/Ian Astbury\/Marti Frederiksen\/Mick Jones", "AlbumTitle": "Beyond Good And Evil"}, {"Name": "Bloodsucker", "Composer": "Blackmore, Gillan, Glover, Lord, Paice", "AlbumTitle": "Deep Purple In Rock"}, {"Name": "Breakfast In Bed", "Composer": "Blackmore, Glover, Turner", "AlbumTitle": "Slaves And Masters"}, {"Name": "Bullet The Blue Sky", "Composer": "Bono\/Clayton, Adam\/Mullen Jr., Larry\/The Edge", "AlbumTitle": "Rattle And Hum"}, {"Name": "Breadfan", "Composer": "Bourge\/Phillips\/Shelley", "AlbumTitle": "Garage Inc. (Disc 2)"}, {"Name": "Beach Sequence", "Composer": "Brian Eno, Bono, Adam Clayton, The Edge & Larry Mullen Jnr.", "AlbumTitle": "Original Soundtracks 1"}, {"Name": "Bring Your Daughter... ...To The Slaughter", "Composer": "Bruce Dickinson", "AlbumTitle": "No Prayer For The Dying"}, {"Name": "Brave New World", "Composer": "Bruce Dickinson\/David Murray\/Steve Harris", "AlbumTitle": "Rock In Rio [CD1]"}, {"Name": "Be Quick Or Be Dead", "Composer": "Bruce Dickinson\/Janick Gers", "AlbumTitle": "Fear Of The Dark"}, {"Name": "Baby", "Composer": "Caetano Veloso", "AlbumTitle": "Minha Hist\u00f3ria"}, {"Name": "Bem Leve", "Composer": "Caetano Veloso e Gilberto Gil", "AlbumTitle": "Barulhinho Bom"}, {"Name": "Beija-Flor", "Composer": "Caruso\/Cleber\/Deo\/Osmar", "AlbumTitle": "Sambas De Enredo 2001"}, {"Name": "Bai\u00e3o Ambiental [Instrumental]", "Composer": "Chico Science", "AlbumTitle": "Afrociberdelia"}, {"Name": "Bleed Together", "Composer": "Chris Cornell", "AlbumTitle": "A-Sides"}, {"Name": "Black Moon Creeping", "Composer": "Chris Robinson\/Rich Robinson", "AlbumTitle": "Live [Disc 2]"}, {"Name": "Bell Bottom Blues", "Composer": "Clapton", "AlbumTitle": "The Cream Of Clapton"}, {"Name": "Badge", "Composer": "Clapton\/Harrison", "AlbumTitle": "The Cream Of Clapton"}, {"Name": "Bite The Bullet", "Composer": "Clarke\/Kilmister\/Taylor", "AlbumTitle": "Ace Of Spades"}, {"Name": "Be Yourself", "Composer": "Cornell, Commerford, Morello, Wilk", "AlbumTitle": "Out Of Exile"}, {"Name": "Burn", "Composer": "Coverdale\/Lord\/Paice", "AlbumTitle": "MK III The Final Concerts [Disc 1]"}, {"Name": "Berlim", "Composer": "Da Gama\/Toni Garrido", "AlbumTitle": "Ac\u00fastico MTV [Live]"}, {"Name": "Blood", "Composer": "Dave Abbruzzese\/Eddie Vedder\/Jeff Ament\/Mike McCready\/Stone Gossard", "AlbumTitle": "Vs."}, {"Name": "Best Of You", "Composer": "Dave Grohl, Taylor Hawkins, Nate Mendel, Chris Shiflett", "AlbumTitle": "In Your Honor [Disc 1]"}, {"Name": "Black", "Composer": "Dave Krusen\/Stone Gossard", "AlbumTitle": "Ten"}, {"Name": "Big Machine", "Composer": "Dave Kushner, Duff, Matt Sorum, Scott Weiland & Slash", "AlbumTitle": "Contraband"}, {"Name": "Back in the Village", "Composer": "Dickinson\/Smith", "AlbumTitle": "Powerslave"}, {"Name": "Book Of Thel", "Composer": "Eddie Casillas\/Roy Z", "AlbumTitle": "Chemical Wedding"}, {"Name": "Better Man", "Composer": "Eddie Vedder", "AlbumTitle": "Live On Two Legs [Live]"}, {"Name": "Balada Da Paula", "Composer": "Emerson Villani", "AlbumTitle": "Roda De Funk"}, {"Name": "Before You Accuse Me", "Composer": "Eugene McDaniel", "AlbumTitle": "Unplugged"}, {"Name": "Balada Do Amor Inabal\u00e1vel", "Composer": "Fausto Fawcett\/Samuel Rosa", "AlbumTitle": "Maquinarama"}, {"Name": "Burn Away", "Composer": "Foo Fighters", "AlbumTitle": "One By One"}, {"Name": "Bate Lata", "Composer": "F\u00e1bio Nolasco\/Gal Sales\/Ivan Brasil", "AlbumTitle": "Ax\u00e9 Bahia 2001"}, {"Name": "By-Tor And The Snow Dog", "Composer": "Geddy Lee And Alex Lifeson\/Geddy Lee And Neil Peart\/Rush", "AlbumTitle": "Retrospective I (1974-1980)"}, {"Name": "Blues For Pablo (Alternate Take)", "Composer": "Gil Evans", "AlbumTitle": "Miles Ahead"}, {"Name": "Beira Mar", "Composer": "Gilberto Gil", "AlbumTitle": "Unplugged"}, {"Name": "Bat Macumba", "Composer": "Gilberto Gil - Caetano Veloso", "AlbumTitle": "Minha Hist\u00f3ria"}, {"Name": "Believe", "Composer": "Henry Hirsch\/Lenny Kravitz", "AlbumTitle": "Greatest Hits"}, {"Name": "Busca Vida", "Composer": "Herbert Vianna", "AlbumTitle": "Arquivo II"}, {"Name": "Better Than You", "Composer": "Hetfield, Ulrich", "AlbumTitle": "ReLoad"}, {"Name": "Bad Seed", "Composer": "Hetfield, Ulrich, Hammett", "AlbumTitle": "ReLoad"}, {"Name": "Boogie With Stu", "Composer": "Ian Stewart\/John Bonham\/John Paul Jones\/Mrs. Valens\/Robert Plant", "AlbumTitle": "Physical Graffiti [Disc 2]"}, {"Name": "Bad Moon Rising", "Composer": "J. C. Fogerty", "AlbumTitle": "Chronicle, Vol. 1"}, {"Name": "Born To Move", "Composer": "J.C. Fogerty", "AlbumTitle": "Chronicle, Vol. 2"}, {"Name": "Battery", "Composer": "J.Hetfield\/L.Ulrich", "AlbumTitle": "Master Of Puppets"}, {"Name": "Big Bad Bill (Is Sweet William Now)", "Composer": "Jack Yellen\/Milton Ager", "AlbumTitle": "Diver Down"}, {"Name": "Baby Break It Down", "Composer": "Jagger\/Richards", "AlbumTitle": "Voodoo Lounge"}, {"Name": "Blackened", "Composer": "James Hetfield, Lars Ulrich & Jason Newsted", "AlbumTitle": "...And Justice For All"}, {"Name": "Bleeding Me", "Composer": "James Hetfield, Lars Ulrich, Kirk Hammett", "AlbumTitle": "Load"}, {"Name": "Believe", "Composer": "James Iha", "AlbumTitle": "Judas 0: B-Sides and Rarities"}, {"Name": "Black Capricorn Day", "Composer": "Jay Kay", "AlbumTitle": "Synkronized"}, {"Name": "Butterfly", "Composer": "Jay Kay\/Toby Smith", "AlbumTitle": "Synkronized"}, {"Name": "Big Wave", "Composer": "Jeff Ament", "AlbumTitle": "Pearl Jam"}, {"Name": "Bleed The Freak", "Composer": "Jerry Cantrell", "AlbumTitle": "Facelift"}, {"Name": "Bron-Yr-Aur", "Composer": "Jimmy Page", "AlbumTitle": "Physical Graffiti [Disc 2]"}, {"Name": "Bring It On Home", "Composer": "Jimmy Page, Robert Plant", "AlbumTitle": "Led Zeppelin II"}, {"Name": "Burning Up", "Composer": "Jimmy Page, Robert Plant, Charlie Jones, Michael Lee", "AlbumTitle": "Walking Into Clarksdale"}, {"Name": "Bron-Y-Aur Stomp", "Composer": "Jimmy Page, Robert Plant, John Paul Jones", "AlbumTitle": "Led Zeppelin III"}, {"Name": "Babe I\'m Gonna Leave You", "Composer": "Jimmy Page\/Robert Plant", "AlbumTitle": "Led Zeppelin I"}, {"Name": "Bankrobber", "Composer": "Joe Strummer\/Mick Jones", "AlbumTitle": "The Singles"}, {"Name": "Bonzo\'s Montreux", "Composer": "John Bonham", "AlbumTitle": "Coda"}, {"Name": "Boris The Spider", "Composer": "John Entwistle", "AlbumTitle": "My Generation - The Very Best Of The Who"}, {"Name": "Baba O\'Riley", "Composer": "John Entwistle\/Pete Townshend", "AlbumTitle": "My Generation - The Very Best Of The Who"}, {"Name": "Black Dog", "Composer": "John Paul Jones\/Robert Plant", "AlbumTitle": "BBC Sessions [Disc 2] [Live]"}, {"Name": "Baltimore, DC", "Composer": "John Scofield", "AlbumTitle": "Outbreak"}, {"Name": "Blitzkrieg", "Composer": "Jones\/Sirotto\/Smith", "AlbumTitle": "Garage Inc. (Disc 2)"}, {"Name": "Borogodo", "Composer": "Jo\u00e3o Suplicy", "AlbumTitle": "Cafezinho"}, {"Name": "Blind Curve: Vocal Under A Bloodlight \/ Passing Strangers \/ Mylo \/ Perimeter Walk \/ Threshold", "Composer": "Kelly, Mosley, Rothery, Trewaves", "AlbumTitle": "Misplaced Childhood"}, {"Name": "Breed", "Composer": "Kurt Cobain", "AlbumTitle": "Nevermind"}, {"Name": "Bad Boy", "Composer": "Larry Williams", "AlbumTitle": "BackBeat Soundtrack"}, {"Name": "Black Velveteen", "Composer": "Lenny Kravitz", "AlbumTitle": "Greatest Hits"}, {"Name": "Blanco", "Composer": "Marisa Monte\/poema de Octavio Paz\/vers\u00e3o: Haroldo de Campos", "AlbumTitle": "Barulhinho Bom"}, {"Name": "Back to Black", "Composer": "Mark Ronson", "AlbumTitle": "Back to Black"}, {"Name": "Bicycle Race", "Composer": "Mercury, Freddie", "AlbumTitle": "Greatest Hits I"}, {"Name": "Billie Jean", "Composer": "Michael Jackson", "AlbumTitle": "Carry On"}, {"Name": "Bitter Pill", "Composer": "Mick Mars\/Nikki Sixx\/Tommy Lee\/Vince Neil", "AlbumTitle": "Motley Crue Greatest Hits"}, {"Name": "Boulevard Of Broken Dreams", "Composer": "Mike Dint, Billie Joe, Tr\u00e9 Cool", "AlbumTitle": "American Idiot"}, {"Name": "Black Satin", "Composer": "Miles Davis", "AlbumTitle": "The Essential Miles Davis [Disc 2]"}, {"Name": "Brasil", "Composer": "Milton Nascimento, Fernando Brant", "AlbumTitle": "Milton Nascimento Ao Vivo"}, {"Name": "Beijo do Olhar", "Composer": "M\u00f4nica Marianno", "AlbumTitle": "Demorou..."}, {"Name": "Bark at the Moon", "Composer": "O. Osbourne", "AlbumTitle": "Bark at the Moon (Remastered)"}, {"Name": "Believer", "Composer": "O. Osbourne, R. Daisley, R. Rhoads", "AlbumTitle": "Tribute"}, {"Name": "Black Diamond", "Composer": "Paul Stanley", "AlbumTitle": "Greatest Kiss"}, {"Name": "Beth", "Composer": "Peter Criss, Stan Penridge, Bob Ezrin", "AlbumTitle": "Unplugged [Live]"}, {"Name": "Breakthru", "Composer": "Queen", "AlbumTitle": "Greatest Hits II"}, {"Name": "Bad Attitude", "Composer": "Richie Blackmore, Ian Gillian, Roger Glover, Jon Lord", "AlbumTitle": "Knocking at Your Back Door: The Best Of Deep Purple in the 80\'s"}, {"Name": "Black Night", "Composer": "Richie Blackmore, Ian Gillian, Roger Glover, Jon Lord, Ian Paice", "AlbumTitle": "Knocking at Your Back Door: The Best Of Deep Purple in the 80\'s"}, {"Name": "Break on Through", "Composer": "Robby Krieger, Ray Manzarek, John Densmore, Jim Morrison", "AlbumTitle": "The Doors"}, {"Name": "Black Country Woman", "Composer": "Robert Plant", "AlbumTitle": "Physical Graffiti [Disc 2]"}, {"Name": "B\u00ea-a-B\u00e1", "Composer": "Rodolfo", "AlbumTitle": "Cesta B\u00e1sica"}, {"Name": "Beth", "Composer": "S. Penridge, Bob Ezrin, Peter Criss", "AlbumTitle": "Greatest Kiss"}, {"Name": "Blow Your Mind", "Composer": "Smith, Toby", "AlbumTitle": "Emergency On Planet Earth"}, {"Name": "Black Hole Sun", "Composer": "Soundgarden", "AlbumTitle": "A-Sides"}, {"Name": "Blood On The World\'s Hands", "Composer": "Steve Harris", "AlbumTitle": "The X Factor"}, {"Name": "Blind Man", "Composer": "Steven Tyler, Joe Perry, Taylor Rhodes", "AlbumTitle": "Big Ones"}, {"Name": "Bushleager", "Composer": "Stone Gossard", "AlbumTitle": "Riot Act"}, {"Name": "Black", "Composer": "Stone Gossard & Eddie Vedder", "AlbumTitle": "Live On Two Legs [Live]"}, {"Name": "B.Y.O.B.", "Composer": "Tankian, Serj", "AlbumTitle": "Mezmerize"}, {"Name": "Book of Hours", "Composer": "Terry Bozzio, Steve Stevens, Tony Levin", "AlbumTitle": "[1997] Black Light Syndrome"}, {"Name": "Babylon", "Composer": "The Tea Party", "AlbumTitle": "Transmission"}, {"Name": "Bichos Escrotos (Vinheta)", "Composer": "Tit\u00e3s", "AlbumTitle": "Ac\u00fastico"}, {"Name": "Beijo Partido", "Composer": "Toninho Horta", "AlbumTitle": "Minas"}, {"Name": "Bad", "Composer": "U2", "AlbumTitle": "The Best Of 1980-1990"}, {"Name": "Babyface", "Composer": "U2; Bono", "AlbumTitle": "Zooropa"}, {"Name": "Ballot or the Bullet", "Composer": "Van Halen", "AlbumTitle": "Van Halen III"}, {"Name": "Bumbo Da Mangueira", "Composer": "V\u00e1rios", "AlbumTitle": "Chill: Brazil (Disc 2)"}, {"Name": "Breakdown", "Composer": "W. Axl Rose", "AlbumTitle": "Use Your Illusion II"}, {"Name": "Brain Damage", "Composer": "Waters", "AlbumTitle": "Dark Side Of The Moon"}, {"Name": "Back Door Man", "Composer": "Willie Dixon, C. Burnett", "AlbumTitle": "The Doors"}, {"Name": "Bad, Bad Leroy Brown", "Composer": "jim croce", "AlbumTitle": "My Way: The Best Of Frank Sinatra [Disc 1]"}] }';
	$recs = paginatejson($sample_data, filter_input(INPUT_GET, 'page', FILTER_VALIDATE_INT), 10);
	print_r('<br /><h2>paginated json</h2><br /><pre>'. json_encode($recs, JSON_PRETTY_PRINT) . '</pre>');


	exit;




	function paginatejson($jsondata, $requestedPage, $itemsPerPage) {

	// just normal getting data
	$raw_data = json_decode($jsondata, true);
	$raw_data = $raw_data["records"];

	// use get variable to paging number
	$page = !isset($requestedPage) ? 1 : intval($requestedPage);
	if ($page < 1) $page = 1;

	$limit = intval($itemsPerPage);
	$total_items = count($raw_data); // total items
	$total_pages = ceil($total_items / $limit);
	if ($page > $total_pages) $page = $total_pages;

	$offset = ($page - 1) * $limit; // offset

	$finalSlice = array_splice($raw_data, $offset, $limit); // splice them according to offset and limit


	$navlinks = array();
	$navlinks["totalRecords"] = $total_items;
	$navlinks["currentPage"] = $page;
	$navlinks["firstPage"] = 1;
	$navlinks["prevPage"] = ($page - 1);
	$navlinks["nextPage"] = ($page + 1);
	$navlinks["lastPage"] = $total_pages;

	return array("navlinks" => $navlinks, "data" => $finalSlice);
	}







	// paginate table and outputs json
	function paginateTable($pdo, $tablename, $query, $itemsPerPage, $requestedPage) {


	try {

	// Find out how many items are in the table
	//$total = $pdo->query($query)->fetchColumn();
	$total = intval($pdo->query($query)->fetch(PDO::FETCH_ASSOC)["TotalRecs"]);


	// How many items to list per page
	$limit = $itemsPerPage;

	// How many pages will there be
	$pages = ceil($total / $limit);

	// What page are we currently on?
	$page = min($pages, $requestedPage, array(
	'options' => array(
	'default' => 1,
	'min_range' => 1,
	),
	));


	// Calculate the offset for the query
	$offset = ($page - 1) * $limit;

	// Some information to display to the user
	$start = $offset + 1;
	$end = min(($offset + $limit), $total);

	//echo "<br>pages: " . $pages . " current page: " . $page . " offset: " . $offset . " start: " . $start . " end: " . $end;


	$navlinks = array();
	$navlinks["totalRecords"] = $total;
	$navlinks["currentPage"] = $page;
	$navlinks["firstPage"] = 1;
	$navlinks["prevPage"] = ($page - 1);
	$navlinks["nextPage"] = ($page + 1);
	$navlinks["lastPage"] = $pages;

	// print_r('<pre>' . json_encode($navlinks, JSON_PRETTY_PRINT) . '</pre><br /><br />');


	// Prepare the paged query
	$stmt = $pdo->prepare($query . ' LIMIT :limit OFFSET :offset');

	// Bind the query params
	$stmt->bindParam(':limit', $limit, PDO::PARAM_INT);
	$stmt->bindParam(':offset', $offset, PDO::PARAM_INT);
	$stmt->execute();


	$records = array();

	// Do we have any results?
	//if ($stmt->rowCount() > 0) {
	if ($stmt) {
	// Define how we want to fetch the results
	$stmt->setFetchMode(PDO::FETCH_ASSOC);
	$iterator = new IteratorIterator($stmt);

	foreach ($iterator as $row) {
	$newrow = array();
	for ($i = 0; $i < $stmt->columnCount(); $i++) {
	$colname = $stmt->getColumnMeta($i)['name'];
	if ($colname != 'TotalRecs') {
	$newrow[$colname] = $row[$colname];
	}
	}
	$records[] = $newrow;
	}

	}

	return array("navlinks" => $navlinks, "data" => $records);



	} catch (Exception $e) {
	return array("error" => $e->getMessage());
	}


	}








	/*
	$sql = "Select * From tracks Where TrackId > :startdId";

	$stmt = $pdo->prepare($sql);
	$stmt->bindValue('startdId', 0);
	$stmt->execute();

	$dataOut = array();
	$dataOut["records"] = array();
	$counterRecs=0;

	if ($stmt) {
	try {
	while($rs = $stmt->fetch()) {
	$rec = array(
	"TrackId" => $rs["TrackId"],
	"Name" => $rs["Name"],
	"Composer" => $rs["Composer"]
	);
	$dataOut["records"][] = $rec;
	$counterRecs++;

	}
	} catch(\PDOException $e) {
	$dataOut['error'] = $e->getMessage();
	}

	}


	header('Content-Type: application/json');
	print_r(json_encode($dataOut, JSON_PRETTY_PRINT));
	*/