Welcome Guest ( Log In | Register)



 
 Reply to this topicStart new topic
> Simple Mysql Query Limiting
Blessed
post Apr 4 2007, 04:42 PM
Post #1


Advanced Member
*******

Group: Members
Posts: 144
Joined: 22-March 07
Member No.: 40,472
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..

(list)

Go to the top of the page
 
+Quote Post
shadowx
post Apr 4 2007, 06:56 PM
Post #2


A clever man learns from his own mistakes, a WISE man learns from those of OTHERS
*********

Group: [HOSTED]
Posts: 884
Joined: 12-April 06
From: Essex, UK
Member No.: 21,719
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 smile.gif

(list)

Go to the top of the page
 
+Quote Post
Blessed
post Apr 5 2007, 08:54 AM
Post #3


Advanced Member
*******

Group: Members
Posts: 144
Joined: 22-March 07
Member No.: 40,472
give me some time.
will post it soon laugh.gif biggrin.gif

(list)

Go to the top of the page
 
+Quote Post
Blessed
post Apr 5 2007, 09:15 AM
Post #4


Advanced Member
*******

Group: Members
Posts: 144
Joined: 22-March 07
Member No.: 40,472
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
<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
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. biggrin.gif
CODE
<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.
CODE
<?php
// Close the loop
}
?>
</div>


This post has been edited by Blessed: Apr 5 2007, 09:28 PM

(list)

Go to the top of the page
 
+Quote Post
Blessed
post Apr 5 2007, 09:48 AM
Post #5


Advanced Member
*******

Group: Members
Posts: 144
Joined: 22-March 07
Member No.: 40,472
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>

<!-- 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 ... biggrin.gif laugh.gif

credits ?? rolleyes.gif rolleyes.gif

This post has been edited by Blessed: Apr 5 2007, 10:35 AM

(list)

Go to the top of the page
 
+Quote Post
shadowx
post Apr 5 2007, 05:07 PM
Post #6


A clever man learns from his own mistakes, a WISE man learns from those of OTHERS
*********

Group: [HOSTED]
Posts: 884
Joined: 12-April 06
From: Essex, UK
Member No.: 21,719
There looks like some good code there smile.gif 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 laugh.gif It would be nice to have another comparison. Check it out.

(list)

Go to the top of the page
 
+Quote Post
Blessed
post Apr 5 2007, 09:29 PM
Post #7


Advanced Member
*******

Group: Members
Posts: 144
Joined: 22-March 07
Member No.: 40,472
i just Updated one of my topics

look above biggrin.gif

(list)

Go to the top of the page
 
+Quote Post
shadowx
post Apr 5 2007, 09:44 PM
Post #8


A clever man learns from his own mistakes, a WISE man learns from those of OTHERS
*********

Group: [HOSTED]
Posts: 884
Joined: 12-April 06
From: Essex, UK
Member No.: 21,719
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 smile.gif

Good work!

(list)

Go to the top of the page
 
+Quote Post
joacorock