Jump to content
xisto Community
Sign in to follow this  
Blessed

Simple Mysql Query Limiting

Recommended Posts

Simple MySQL Query Limiting

 

<?php// If mypage?limit=blah is not defined returs 5if ( $_GET['limit'] == NULL ){	$limit = 5;}// Elseelse{	$limit = $_GET['limit'];}$query = mysql_query("SELECT * FROM members LIMIT '$limit'") or die(mysql_error());while ($sql = mysql_fetch_array ($query) ){	echo $sql['mem_name'] . '<br>';}?>

Have fun..

Share this post


Link to post
Share on other sites

I understand the script but i want to ask does it have a purpose as of yet or is it meant as an example of using LIMIT in a query?I think you should write a tutorial about the LIMIT function, tell people how its used correctly and then give an example of how it can be used, perhaps in a guestbook type situation, I think it would be interesting as ive never used LIMIT before so it would help me and you'd get some credits :lol:

Share this post


Link to post
Share on other sites

Simple Mysql Query Limitting

 

Part 1

Make a file called gbook.php.

 

at the first line start with.

 

<?php$conn = mysql_connect("localhost","username","password");mysql_select_db("database") or die(mysql_error());
so we can make a connection to the database

 

Part 2

 

after add.

if the ?limit == empty it automaticly returs to 5

// If mypage?limit=blah is not defined returs 5if ( $_GET['limit'] == NULL ){	$limit = 5;}// Elseelse{	$limit = $_GET['limit'];}

Part 3

 

Now we center the table and built up the links

<div align="center"><!-- You can add more if you want --><a href="index.php?limit=10">Show 10</a> <a href="index.php?limit=20">Show 20</a>

Part 4

Select everything from the g_book table

thats what "SELECT * FROM g_book" means

<?php$query = mysql_query("SELECT * FROM g_book LIMIT '$limit'") or die(mysql_error());

Part 5

 

with the while funtion we start the loop.

and we define some things

while ($sql = mysql_fetch_array ($query) )// Loop begin{// define some things	$g_title	 = $sql['g_title'];	$g_date	 = $sql['g_date'];	$g_text	 = $sql['g_text'];	$g_poster	 = $sql['g_poster'];?>

Part 6

 

Now we build the results table.

inside the while loop.

now we use echo to output the things we just defined. :lol:

<table style="border:solid #999999 1px;"width="200">  <tr>	<td><?php echo $g_title;?> | <?php echo $g_poster;?></td>  </tr>  <tr>	<td><?php echo $g_text;?></td>  </tr>  <tr>	<td><?php echo $g_date;?></td>  </tr></table>

Part 7

 

Now we close the while loop

and close the div.

<?php// Close the loop}?></div>

Edited by Blessed (see edit history)

Share this post


Link to post
Share on other sites

Add a simple pagination to it
like [Previous] | [Next]

<html><head><title>Simple G Book with pagination</title></head><body><table>  <?phpdefine('ROWS_PER_PAGE', 5);/*if ?page is emty returns to page 1*/if (!isset($page) || $page < 1) {	$page = 1;}/*Count the lines*/$result = mysql_query("select count(id) from g_book", $dbconn);$row = mysql_fetch_row($result);$numrows = $row[0];mysql_free_result($result);if ($numrows < 1) {	// There is no data in the g_book!	die ("There is no data in the g_book!");}/*Check how many pages in total are needed*/$numpages = ceil($numrows / ROWS_PER_PAGE);if ($numpages < $page) {	$page = $numpages;}/*many rows to display per page, we can work out the offset*/$offset = ROWS_PER_PAGE * ($page - 1);/*construct the query, and pass in the limit clause*/$query = "select id, g_title, g_poster, g_date, g_text from g_book limit $offset, ".ROWS_PER_PAGE;$result = mysql_query($query, $dbconn);if (mysql_num_rows($result) < 1) {	// We know we _should_ have a result, but we haven't	die("No data retrieved!");}/*Do the loop*/while ($row = mysql_fetch_assoc($result)) {// define some things	$g_title 	= $row['g_title'];	$g_date 	= $row['g_date'];	$g_text 	= $row['g_text'];	$g_poster 	= $row['g_poster'];	?><table style="border:solid #999999 1px;"width="200">  <tr>	<td><?php echo $g_title;?> | <?php echo $g_poster;?></td>  </tr>  <tr>	<td><?php echo $g_text;?></td>  </tr>  <tr>	<td><?php echo $g_date;?></td>  </tr></table><?php}/*Free the memory used for this result set*/mysql_free_result($result);?></table><!-- If necessary, display a prev button --><?php if ($page > 1) { ?><a href="<?php echo $PHP_SELF; ?>?page=<?php echo $page - 1; ?>">[ Previous Page ]</a><?php } ?><!-- If necessary, display a next button --><?php if ($page < $numpages) { ?><a href="<?php echo $PHP_SELF; ?>?page=<?php echo $page + 1; ?>">[ Next Page ]</a><?php } ?></body></html>

have fun ... :( :(

credits ?? :lol::lol:
Edited by Blessed (see edit history)

Share this post


Link to post
Share on other sites

There looks like some good code there :lol: but what i would do it split it up for those who are new to PHP or at a medium level and explain each part of code, obviously not every command, you'd be here all day! But explain how and why you are getting the data from a database, and how you're formatting it etcetera and then write it up as a tutorial, take a look in the tutorial section so you can see the format you should use and that way you get lots of credits because stuff inside the CODE and QUOTE tags doesn't get credits. I wrote a basic guestbook style tutorial in there and someone else wrote one two and we compared, theres was better though :lol: It would be nice to have another comparison. Check it out.

Share this post


Link to post
Share on other sites

Thats the idea! This way less experienced scriptwriters can understand the code, and when i see a big block of code i usually only scan it and dont take it in so its useful for people like me too! If you get the chance do the same for the second code you posted :lol: Good work!

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×
×
  • Create New...

Important Information

Terms of Use | Privacy Policy | Guidelines | We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.