Simple Mysql Query Limiting

Blessed

Apr 4 2007, 04:42 PM

Simple MySQL Query Limiting

CODE

<?php
// If mypage?limit=blah is not defined returs 5
if ( $_GET['limit'] == NULL )
{
    $limit = 5;
}
// Else
else
{
    $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..

shadowx

Apr 4 2007, 06:56 PM

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

Blessed

Apr 5 2007, 08:54 AM

give me some time.
will post it soon

Blessed

Apr 5 2007, 09:15 AM

Simple Mysql Query Limitting

[b]Part 1
Make a file called gbook.php.

at the first line start with.

CODE

<?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

CODE

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

Part 3

Now we center the table and built up the links

CODE

Part 4
Select everything from the g_book table
thats what "SELECT * FROM g_book" means

CODE

<?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

CODE

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.

CODE

Part 7

Now we close the while loop
and close the div.

CODE

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

Blessed

Apr 5 2007, 09:48 AM

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

CODE

<html>

<head>
<title>Simple G Book with pagination</title>
</head>

<body>

<table>

<?php

define('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>


<?php if ($page > 1) { ?>
<a href="<?php echo $PHP_SELF; ?>?page=<?php echo $page - 1; ?>">[ Previous Page ]</a>
<?php } ?>


<?php if ($page < $numpages) { ?>
<a href="<?php echo $PHP_SELF; ?>?page=<?php echo $page + 1; ?>">[ Next Page ]</a>
<?php } ?>

</body>

</html>

have fun ...

credits ??

shadowx

Apr 5 2007, 05:07 PM

There looks like some good code there

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

It would be nice to have another comparison. Check it out.

Blessed

Apr 5 2007, 09:29 PM

i just Updated one of my topics

look above

shadowx

Apr 5 2007, 09:44 PM

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

Good work!

joacorock

Apr 10 2007, 09:24 AM

Are a very simples solutions, they are a simples SQL limits, like a pagination

Blessed

Apr 10 2007, 09:41 AM

i will UPDATE the [Previous] | [Next]
when i get home later

ADD REPLY / Got an Opinion!	a humble request :-)	RAPID SEARCH!	Free Hosting	[X]
Express your Opinions, Thoughts or Contribute your information that might help someone here. Ask your Doubts & Queries to get answers.. "Together, We enlight each other!"	Register FREE for AD-FREE forum, Create your own topics, Ask Questions, track topics, setup subscriptions & notifications and Get a Free Website w/ Email and FTP.		500MB Space No Ads, CPanel, FTP, PHP, MySQL, EMails - 100% FREE