Browse Function With Php & Mysql instead of 50 results on one page, browse through it.

[Samleeuwenburg 2]

Hey all, I have only recently joined but since this is such a good forum and it has helped me alot already, I decided i should post a tutorial about something I know. The reason I'm making this tutorial is because while I was looking how to make a browse function, all I found was pieces of code with explanation but that didn't really show step by step how the code was build up (starting from the core, and then adding functions). I believe that if you learn the core of a technique you not only understand it alot better but you also are able to adjust it easily.Now before starting off: the first bits are about making a table in a MySQL database and entering data into it, I am assuming you know how to do this.but first make a new page called " results.php " and start off by creating connecting to the database:

<?php$host = "localhost";$user = "your username";$pass = "your password";$db   = "your database";$ms = mysql_pconnect($host, $user, $pass);if ( !$ms ){echo "Error connecting to database.\n";}mysql_select_db($db);?>

then lets make a new table that will contain our data, I used a blog as an example (without the post itself; we won't need it)

<?phpmysql_query(" CREATE TABLE blog ( title VARCHAR(30), detail VARCHAr(30), writer VARCHAR(30))"); ?>

*you can just copy and paste this into your " results.php " but if you type it yourself you learn more )I assume you know how to enter data into your table, use PHPmyAdmin, PHP or SQLyog whatever you prefer. Just make sure you enter enough so you can test your browse page later on ( 13 or 16 should be enough )Let's go back to our results.php, at the very top of the page you should still have your code that connects to the database.now lets make our page echo out all the data from our table: lets make a query and we will order our results by the name of the post

$blogquery = mysql_query (" SELECT * FROM blog ORDER BY title DESC " );

then we can use the "while" function in php to echo out the results while there are more left

// mysql_fetch_array is fetching the results from our $blogquery and putting it into a new associative array variable called $rowwhile ($row = mysql_fetch_array($blogquery)) {// then we echo out the rows contentsecho "<p>".$row['title']."<br />".$row['detail']."<br />".$row['writer']."<br /><br /></p>"; }

Now save and run the page. (make sure you put the above code in between <?php and ?> tags) This should echo out all the results of our table and around this we will be building our script.We will need buttons so the user can tell our page where to go but, where should they go to?they will go to the same page but they will carry a variable with them. This is actually a very handy method and you see it all over the weblinks like: .com/php?q=searchthisbaby use it. and we will do the same but it will need a variable to work withput these above our $blogquery var

$pageback = 1;$pagenext = 1;

put the links on the bottom of the page

echo " <a href=\"$PHP_SELF?page=$pageback\">Back</a> - ";echo " <a href=\"$PHP_SELF?page=$pagenext\">Next</a> ";

Save and run to see whats happening, click the next and back links they are still doing nothing yet. but the link in the browser is changing.it is sending a variable to the link of the page (results?page=1 and we can do something with that variable, we can get it. above our other variables type the following line (it gets the variable from the link and puts it in a new one)

$page = $_GET['page'];

now we will need a way to limit our results, we don't want 16 posts when we load the page we can do this by changing the line from our $blogquery into:

$blogquery = mysql_query (" SELECT * FROM blog ORDER BY title DESC LIMIT $key, $amount " );

It now has the a new part "LIMIT $key, $amount" the $key means where to start, and the $amount is how many from that point.This won't work if we don't set these variables so below our $page and above our &pagenext put the following:

$amount = 5;$key = 0; 

now save and test the page, this is starting to look more like it. Think about it we have 2 buttons each can control a variable we also have a variable to tell us what posts we want to see and how much. A little math should do the trick right?if we set a variable let's say $i = 1; and we make our pagenext into pagenext = i + 1 we should then be able to get to results.php?page=3 right?well it isnt that simple if you load the page for the first time the $page variable is empty. we need a way to keep on incrementing our $page in the link when we press next and the opposite for when we press back.here is how I did it (maybe not the best of ways but I am still a beginner myself)

 $amount = 5;// I check if this is the first time i load this page, if it is... if ($page <= 0) {// if it is I set the page number to 1, and my counter $i to 2 the key is 0  because databases count the first entry as 0	$page = 1;	$i = $page + 1;	$key = 0;	} else {// if it isnt the first time I load the page the page number is already set and I only set the counter and the key	$i = $page + 1;	$key = $page * $amount - $amount;}// then I change our  next link variable to match counter our variable$pageback = 1;$pagenext = $i;

Go check it out! our next button can now easily reach ?page=20 ,( the reason why our $key is " page * 5 - 5 " is because if we press next to view results 15 to 20 for example we need to set our key to 15. page 4 * 5 - 5 = right, 15 )this doesnt mean we are finished yet we still need to make our back button work.This is easy though, in our " else " statement we can put another counter that does the opposite of $i lets call it $ii

else {	$i = $page + 1;        $ii = $page - 1;	$key = $page * $amount - $amount; }// and change our back button variable too$pageback = $ii;}

Check to see if it works. there is still a little problem with pressing back on the first page: its useless.We can now brush up things by making sure the back button doesnt show if your on the first page, put an if statement before it like this:

 // if we are on page 2 or higher, echo our back buttonif ($page >= 2) {	echo " <a href=\"$PHP_SELF?page=$pageback\">Back</a> - ";} 

You can now add all sorts of things, you can echo out the page you're on at the bottom of the screen, just echo out $page, or you can even put up things like: 10 - 15 results. but the basic mechanics are build now. one more thing might be usefull. The next button now goes on and on, lets stop that.Make a new variable named counter above our $blogquery and name it $counter, set the value to 0. Then in our While function add one to it, this adds one to it everytime a result is echoed.

 while ($row = mysql_fetch_array($blogquery)) {$counter ++;echo "<p>".$row['title']."<br />".$row['detail']."<br />".$row['writer']."<br /><br /></p>"; }

Then change the next link to this:

if ($counter < 1) { echo "  No more results available ";} else { echo " <a href=\"$PHP_SELF?page=$pagenext\">Next</a> "; }

That should do the trick. I hope you enjoyed this tutorial it's first I ever made, and I mainly hope you learned something new..If i get good replies I will probably make more of them. but till then Thanks for reading :angel:

[Mazarin 0]

....That should do the trick. I hope you enjoyed this tutorial it's first I ever made, and I mainly hope you learned something new.. If i get good replies I will probably make more of them. but till then Thanks for reading :angel:

Hello!

I was really impressed by your post. This is by far the best tutorial I ever seen on this subject. One thing is missing: I would suggest to publish the code in its final, cleared form, because there are many corrections and inserts and one will hardly guess the right positions where to make them.

Thanks a lot!

[Quatrux 4]

I looked at the code, but it seems that you could add some method to control the paging, for example, in your code, if you will have 120 pages, they will all show, did I understand correctly?So doing something like last page, first page and printing some intervals is also a good idea.

[Liquidized 1]

I looked at the code, but it seems that you could add some method to control the paging, for example, in your code, if you will have 120 pages, they will all show, did I understand correctly?
So doing something like last page, first page and printing some intervals is also a good idea.

Same question i have. And would this browse function detect things such as users? I've never really done any SQL inquiries myself. Just imported the required ones and let the CMS automatically install them.

[vhortex 1]

@QuatruxJust doing a quick scan with his code.His code if unaltered only display back and next links, this means that only 2 links will appear. @LiquidizedThis is just a basic pagination script, this have nothing to do with users tracking. You can also alter the query so you can list users if you want it.