how to split results into page 1,2,3 etc...

andy_b_1502 · June 17, 2012

Hello,

I have a code to display results on my website from mySQL table, there is a rising number of results being added daily which is great but rather than displaying a full list i'd like them to be separated into pages but have no idea how to split them up into say 50 per page.

Each added listing has to be approved by admin and the code displays by approved status like this:

<?PHP
session_start();
include ('php only scripts/db.php');
?>


    <?php
$what_services = "R";

    $query = "SELECT * FROM companies WHERE what_services IN('R','B') ORDER BY approved DESC, company_name ASC";
$result = mysql_query($query);

while ( $row = mysql_fetch_array($result)) {
$approved = $row['approved'];
$what_services = $row['what_services'];

SWITCH ($approved) {

	case 0:
		break;
	case 1:
	?>
    
<?PHP echo $row['company_name']; ?>
      
      <?PHP 
			echo $row['street1'] . 
			"<br>" . $row['street2'] . 
			"<br>" . $row['city'] . "," .  $row['postcode'] .  
			"<br>phone: " . $row['phone']  ?>      
<?PHP echo $row['company_name']; ?>
    
      <?PHP
		break;
	case 2:
		?>
<?PHP echo $row['company_name']; ?>      
      <?PHP 
			echo $row['street1'] . 
			"<br>" . $row['street2'] . 
			"<br>" . $row['city'] . "," .  $row['postcode'] .  
			"<br>phone: " . $row['phone']  ?>      
<img src="images/thumbs/<?PHP echo $row['upload']; ?>" alt="logo"/>
      
        <?PHP
		break;
	case 3:
		?>
<a href="view00.php?id=<?PHP echo $row['id']; 
?>"><?PHP echo $row['company_name']; ?></a>
<a href="view00.php?id=<?PHP echo $row['id'];
  ?>">
        <?PHP 
			echo $row['street1'] . 
			"<br>" . $row['street2'] . 
			"<br>" . $row['city'] . "," .  $row['postcode'] .  
			"<br>phone: " . $row['phone'] .
			"<br>mobile/other: " . $row['phone'] ?>
      </a>

	click logo to read more...<a href="view00.php?id=<?PHP echo $row['id']; 
?>"><img src="images/thumbs/<?PHP echo $row['upload']; ?>" 
alt="logo"/></a>

      <?PHP
		break;
}
}

?>

The switch sorts the approved, i just dont want all the results displayed in one massive list as the site is expected to have at least 5000 listings on it :-\

andy_b_1502 · June 17, 2012

forgot to make a link for you to see what i mean:

http://www.removalspace.com/removals.php

Barand · June 17, 2012

http://www.google.co.uk/url?sa=t&rct=j&q=php+paging+database+results&source=web&cd=3&ved=0CFgQtwIwAg&url=http%3A%2F%2Fwww.youtube.com%2Fwatch%3Fv%3DK8xYGnEOXYc&ei=ZbTdT5nHL6mY0QWqsOHUCg&usg=AFQjCNG7cB3g2si3vtZZgVwCiWoyrTkV9g

andy_b_1502 · June 17, 2012

Thanks Barand, it's a really good tutorial,

I have been working through it and adding bits and bobs from the source file, do i have to change the 2nd query in my script in any way for it to work properly with my original script i had?

In the tutorial it has query2 in his while loop, in my original i have

while ( $row = mysql_fetch_array($result)) {
$approved = $row['approved'];
$what_services = $row['what_services'];

andy_b_1502 · June 17, 2012

thought i best put the full code here.

it doesn't seem to be there yet

18615_.php

andy_b_1502 · June 18, 2012

Can someone please help me, the limit value (set by 10 results per page) isn't working, it's showing everything in the table on one page?

<?PHP
session_start();
include ('php only scripts/db.php');
?>

<!DOCTYPE html>
  <head>
    <title>Removals | Removalspace.com</title>
<style type="text/css">
<!--
body {
background-image: url(styles/downloaded%20styles/todo/todo/images/bg.png);

}
-->
</style>
<link href="styles/downloaded styles/todo/todo/css/style.css" rel="stylesheet" type="text/css" />
<link rel="stylesheet" type="text/css" href="styles/downloaded styles/todo/todo/css/style9.css" />
    <link rel="stylesheet" type="text/css" href="styles/downloaded styles/todo/todo/css/demo.css" />    
        <link href='http://fonts.googleapis.com/css?family=Terminal+Dosis' rel='stylesheet' type='text/css' />
        <style type="text/css">
<!--
.Stile1 {color: #333333}
.aa {
font-size: 13px;
}
#container header nav #logo p .cursive {
font-size: 9px;
}
#apDiv1 {
position:absolute;
width:466px;
height:24px;
z-index:1;
left: 585px;
top: 191px;
}
-->
        </style>
<script type="text/javascript">

  var _gaq = _gaq || [];
  _gaq.push(['_setAccount', 'UA-31656176-1']);
  _gaq.push(['_trackPageview']);

  (function() {
    var ga = document.createElement('script'); ga.type = 'text/javascript'; ga.async = true;
    ga.src = ('https:' == document.location.protocol ? 'https://ssl' : 'http://www') + '.google-analytics.com/ga.js';
    var s = document.getElementsByTagName('script')[0]; s.parentNode.insertBefore(ga, s);
  })();

</script>
<style type="text/css">
<!--
.pagNumActive {
color: #000;
border:#060 1px solid; background-color: #D2FFD2; padding-left:3px; padding-right:3px;
}
.paginationNumbers a:link {
color: #000;
text-decoration: none;
border:#999 1px solid; background-color:#F0F0F0; padding-left:3px; padding-right:3px;
}
.paginationNumbers a:visited {
color: #000;
text-decoration: none;
border:#999 1px solid; background-color:#F0F0F0; padding-left:3px; padding-right:3px;
}
.paginationNumbers a:hover {
color: #000;
text-decoration: none;
border:#060 1px solid; background-color: #D2FFD2; padding-left:3px; padding-right:3px;
}
.paginationNumbers a:active {
color: #000;
text-decoration: none;
border:#999 1px solid; background-color:#F0F0F0; padding-left:3px; padding-right:3px;
}
#apDiv2 {
position:absolute;
width:587px;
height:40px;
z-index:1;
left: 261px;
top: 541px;
}
-->
</style>
</head>
  
<body>
<!--start container -->
<div id="container">
<header>
    <nav>
      <div id="apDiv1"><span class="cursive">Upagrade Now For Your Logo Here!</span></div>   
      <div id="logo">
        <p><a href="index.php"><img src="images/header2.png" alt="Logo here" width="219" height="161" /></a></p>
        <p><span class="cursive">Company Logo's/Upgraded UK Listings Shown Below (enter your postcode for your area)</span></p>
        <p> </p>
        <p>click <a href="register00.php">here</a> to add your company</p>
      </div>
      
      <div id="search-top"><img src="styles/downloaded styles/todo/todo/images/quote-right.png" alt="images" /><span class="cursive">Enter your postcode here</span><img src="styles/downloaded styles/todo/todo/images/quote-left.png" alt="images" />
        <form method="post" action="search.php">
  <input type="text" name="strSearch" onFocus="if(this.value=='Search Area')this.value='';" onBlur="if(this.value=='')this.value='Search Area';" value="Search Area"  id="search-field"/>
  <input type="submit" value="" id="search-btn"/>
  </form> 
      </div>  
      <div id="nav_social"><a href="http://www.facebook.com/pages/Removalspace/181434181939226"><img src="styles/downloaded styles/todo/todo/images/facebook_32.png" alt="Become a fan" width="32" height="32" /></a><a href="#"><img src="styles/downloaded styles/todo/todo/images/twitter_32.png" alt="Follows on Twitter" /></a><a href="id=183427956&trk=tab_pro"><img src="styles/downloaded styles/todo/todo/images/linkedin_32.png" alt="Linked in" /></a><a href="contact.php"><img src="styles/downloaded styles/todo/todo/images/email_32.png" alt="Contact" width="32" height="32" /></a><!-- Place this tag where you want the +1 button to render -->
<g:plusone size="small" annotation="inline"></g:plusone>

<!-- Place this render call where appropriate -->
<script type="text/javascript">
  (function() {
    var po = document.createElement('script'); po.type = 'text/javascript'; po.async = true;
    po.src = 'https://apis.google.com/js/plusone.js';
    var s = document.getElementsByTagName('script')[0]; s.parentNode.insertBefore(po, s);
  })();
</script></div>
  </nav>
  
  </header>
  
<p><figure><a href="removals.php">Search Removals</a></figure> |</p>
<p><figure><a href="storage.php">Search Storage</a></figure> |</p>
<p><figure><a href="register00.php">Add Listing</a></figure> |</p>
<p><figure><a href="about.php">About</a></figure> |</p>
<p><figure><a href="contact.php">Contact</a></figure> |</p>
<p><figure><a href="login00.php">Login</a></figure></p>

<div class="content">
  <!--star main --> 
  
  <main>
    <?php
$what_services = "R";

    $query = "SELECT * FROM companies WHERE what_services IN('R','B') ORDER BY approved DESC, company_name ASC";
$result = mysql_query($query);

if(isset($_GET['pn'])){
	$pn = preg_replace('#[^0-9]#i', '', $_GET['pn']);
}else{
	$pn = 1;
}

$itemsPerPage = 10;

$lastPage = ceil($nr / $itemsPerPage);

if($pn < 1){
	$pn = 1;
}else if($pn > $lastPage){
	$pn = $lastPage;
}
// This creates the numbers to click in between the next and back buttons
$centerPages = ""; // Initialize this variable
$sub1 = $pn - 1;
$sub2 = $pn - 2;
$add1 = $pn + 1;
$add2 = $pn + 2;
if ($pn == 1) {
$centerPages .= '  <span class="pagNumActive">' . $pn . '</span>  ';
$centerPages .= '  <a href="' . $_SERVER['PHP_SELF'] . '?pn=' . $add1 . '">' . $add1 . '</a>  ';
} else if ($pn == $lastPage) {
$centerPages .= '  <a href="' . $_SERVER['PHP_SELF'] . '?pn=' . $sub1 . '">' . $sub1 . '</a>  ';
$centerPages .= '  <span class="pagNumActive">' . $pn . '</span>  ';
} else if ($pn > 2 && $pn < ($lastPage - 1)) {
$centerPages .= '  <a href="' . $_SERVER['PHP_SELF'] . '?pn=' . $sub2 . '">' . $sub2 . '</a>  ';
$centerPages .= '  <a href="' . $_SERVER['PHP_SELF'] . '?pn=' . $sub1 . '">' . $sub1 . '</a>  ';
$centerPages .= '  <span class="pagNumActive">' . $pn . '</span>  ';
$centerPages .= '  <a href="' . $_SERVER['PHP_SELF'] . '?pn=' . $add1 . '">' . $add1 . '</a>  ';
$centerPages .= '  <a href="' . $_SERVER['PHP_SELF'] . '?pn=' . $add2 . '">' . $add2 . '</a>  ';
} else if ($pn > 1 && $pn < $lastPage) {
$centerPages .= '  <a href="' . $_SERVER['PHP_SELF'] . '?pn=' . $sub1 . '">' . $sub1 . '</a>  ';
$centerPages .= '  <span class="pagNumActive">' . $pn . '</span>  ';
$centerPages .= '  <a href="' . $_SERVER['PHP_SELF'] . '?pn=' . $add1 . '">' . $add1 . '</a>  ';
}
// This line sets the "LIMIT" range... the 2 values we place to choose a range of rows from database in our query
$limit = 'LIMIT ' .($pn - 1) * $itemsPerPage .',' .$itemsPerPage; 
// Now we are going to run the same query as above but this time add $limit onto the end of the SQL syntax
// $sql2 is what we will use to fuel our while loop statement below
$sql2 = mysql_query("SELECT * FROM companies WHERE what_services IN('R','B') ORDER BY approved DESC, company_name ASC $limit"); 
//////////////////////////////// END Adam's Pagination Logic ///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
///////////////////////////////////// Adam's Pagination Display Setup ///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
$paginationDisplay = ""; // Initialize the pagination output variable
// This code runs only if the last page variable is not equal to 1, if it is only 1 page we require no paginated links to display
if ($lastPage != "1"){
    // This shows the user what page they are on, and the total number of pages
    $paginationDisplay .= 'Page <strong>' . $pn . '</strong> of ' . $lastPage. '<img src="images/clearImage.gif" width="48" height="1" alt="Spacer" />';
// If we are not on page 1 we can place the Back button
    if ($pn != 1) {
    $previous = $pn - 1;
	$paginationDisplay .=  '   <a href="' . $_SERVER['PHP_SELF'] . '?pn=' . $previous . '"> Back</a> ';
    } 
    // Lay in the clickable numbers display here between the Back and Next links
    $paginationDisplay .= '<span class="paginationNumbers">' . $centerPages . '</span>';
    // If we are not on the very last page we can place the Next button
    if ($pn != $lastPage) {
        $nextPage = $pn + 1;
	$paginationDisplay .=  '   <a href="' . $_SERVER['PHP_SELF'] . '?pn=' . $nextPage . '"> Next</a> ';
    } 
}
///////////////////////////////////// END Adam's Pagination Display Setup ///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

while ( $row = mysql_fetch_array($result)) {
$approved = $row['approved'];
$what_services = $row['what_services'];



SWITCH ($approved) {

	case 0:
		break;
	case 1:
	?>
    
<div class="abox">
      <figure>
      <fcapion>
      <p> </p>
      <p><?PHP echo $row['company_name']; ?></p>
      <p> </p>
      </figure>
</div>
<div class="abox">
      <figure>
      <fcapion>
      </fcaption>
      
      <?PHP 
			echo $row['street1'] . 
			"<br>" . $row['street2'] . 
			"<br>" . $row['city'] . "," .  $row['postcode'] .  
			"<br>phone: " . $row['phone']  ?>      </figure>
</div>
<div class="abox">
      <figure>
      <fcapion>
      <p> </p>
      <h1><?PHP echo $row['company_name']; ?></h1>
      <p> </p>
      </figure>
</div>
    <p>
      <?PHP
		break;
	case 2:
		?>
    </p>
    <div class="abox">
      <figure>
      <fcapion>
      <p> </p>
      <p><?PHP echo $row['company_name']; ?>      </p>
      <p> </p>
      </figure>
</div>
<div class="abox">
      <figure>
      <fcapion>
      </fcaption>
      <?PHP 
			echo $row['street1'] . 
			"<br>" . $row['street2'] . 
			"<br>" . $row['city'] . "," .  $row['postcode'] .  
			"<br>phone: " . $row['phone']  ?>      </figure>
</div>
<div class="abox">
      <figure>
      <fcapion>
      <p> </p>
      <h1><img src="images/thumbs/<?PHP echo $row['upload']; ?>" alt="logo"/></h1>
      <p> </p>
      </figure>
</div>
      
        <?PHP
		break;
	case 3:
		?>
<div class="abox">
	<figure>
	<fcapion>
	<p> </p>
	<p><a href="view00.php?id=<?PHP echo $row['id']; 
?>"><?PHP echo $row['company_name']; ?></a></p>
	<p> </p>
	</figure>
</div>
<div class="abox">
      <figure>
      <fcapion>
      <p> </p>
      <p><a href="view00.php?id=<?PHP echo $row['id'];
  ?>">
        <?PHP 
			echo $row['street1'] . 
			"<br>" . $row['street2'] . 
			"<br>" . $row['city'] . "," .  $row['postcode'] .  
			"<br>phone: " . $row['phone'] .
			"<br>mobile/other: " . $row['phone'] ?>
      </a></p>
      <p> </p>
      </figure>
</div>
<div class="abox">
	<figure>
	<fcapion>
	<h1>click logo to read more...<a href="view00.php?id=<?PHP echo $row['id']; 
?>"><img src="images/thumbs/<?PHP echo $row['upload']; ?>" 
alt="logo"/></a></h1>
	</figure>
</div>
    <p> </p>
    <p>
      <?PHP
		break;
}
}

?>
    </p>
  </main>
    <!--end main -->
  <!--start middle --> </div>
<!--end middle -->
<!--start footer -->
<footer>
  <div style="margin-left 58px, margin-right 58px, padding 6px border #999, 1px solid"><?php echo $paginationDisplay;?></div>  
  <div id="footer">
  <div id="footerleft">
      <h2>removalspace</h2>
      <ul>
        <li><a href="contact.php">Contact</a></li>
        <li><a href="about.php">About Us</a></li>
        <li><a href="other-info.php">Other</a></li>
        <li></li>
      </ul>
    </div>
    <div id="footermiddle">
      <h2>User Comments</h2>
      <ul>
        <li><a href="#">Read Reviews</a></li>
        <li><a href="contact.php">Leave A Comment</a></li>
        <li><a href="#">Read Blogs</a></li>
        <li></li>
      </ul>
    </div>
    <div id="footerright">
      <h2>our mission</h2>
      <ul>
        <li><a href="about.php">Who Are We?</a></li>
        <li><a href="about.php">What We Do</a></li>
        <li><a href="about.php">Our Mission</a></li>
        <li></li>
      </ul>
    </div>
    <details>
<summary>
<p><a href="http://www.imediacreatives.it" target="_blank">Imediacreatives.it</a><a href="http://freehtml5templates.com">Designers</a>
  Copyright © removalspace.com -2012.</p></summary><p></p>
</details>
    </div>
</footer>
<!--end footer -->
</div>
<!--end container -->

  </body>
<script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.6.4/jquery.min.js"></script>
</html>

PFMaBiSmAd · June 18, 2012

The query result resource that you are looping over, isn't the one from the query with the LIMIT clause in it. The query result resource you are looping over is from the query that is getting the total count of matching rows, which you aren't actually using to get the total count of matching rows from.

andy_b_1502 · June 18, 2012

so how can i rectify it?

by swapping the two over?

i changed this line

$sql2 = mysql_query("SELECT * FROM companies WHERE what_services IN('R','B') ORDER BY approved DESC, company_name ASC $limit");

to

$query = mysql_query("SELECT * FROM companies WHERE what_services IN('R','B') ORDER BY approved DESC, company_name ASC $limit");

and

$result = mysql_query("SELECT * FROM companies WHERE what_services IN('R','B') ORDER BY approved DESC, company_name ASC $limit");

but only one worked (result) but still gave all the listings on one page, i don't get it?

andy_b_1502 · June 18, 2012

it does go in between the query and the while loop though, right??

PFMaBiSmAd · June 18, 2012

it does go in between the query and the while loop though, right??

What goes between the query and the while loop? The only problem is -

The query result resource that you are looping over, isn't the one from the query with the LIMIT clause in it.

All you would need to do to fix that is to look at your code and put the correct result resource from the query with the LIMIT clause in it, into the while(){} statement.

andy_b_1502 · June 18, 2012

its not working?

i get this error:

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /hermes/bosoraweb023/b109/ipg.removalspacecom/removals.php on line 208

after changing the result resource to this:

<?php while ( $row = mysql_fetch_array($result2)) {
$approved = $row['approved'];
$what_services = $row['what_services']; ?>

and 2nd query this:

$query2 = "SELECT * FROM companies WHERE what_services IN('R','B') ORDER BY approved DESC, company_name ASC $limit";
$result2 = mysql_query($query2);

andy_b_1502 · June 18, 2012

i have done as suggested, made the while statement have the correct result resource but i'm getting the invalid resource error?

Can somebody please help?

PFMaBiSmAd · June 18, 2012

That means that the query failed due to an error of some kind. You would need to debug why your query is failing. Echo $query2; to see exactly what the query statement is and echo mysql_error(); to get php/mysql to tell you where in the query it found a problem.

andy_b_1502 · June 18, 2012

I uploaded and tested this:

<?PHP

include ('php only scripts/db.php');


$query2 = "SELECT * FROM companies WHERE what_services IN('R','B') ORDER BY approved DESC, company_name ASC, $limit";
$result2 = mysql_query($query2); 

echo('$query2');
echo mysql_error();
?>

and get this error:

$query2You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1

Which doesn't tell me much? is there a code for more detail?

litebearer · June 18, 2012

if the above code is the complete code for that test script, where did you define $limit?

PFMaBiSmAd · June 18, 2012

It would be -

echo $query2;

Also, the query statement you just posted has an extra comma after the ASC keyword that is incorrect and is not present in the query statement in your actual code. The query you posted above also wont work because $limit hasn't been defined. You need to troubleshoot the actual query statement in your actual code.

andy_b_1502 · June 18, 2012

SELECT * FROM companies WHERE what_services IN('R','B') ORDER BY approved DESC, company_name ASC LIMIT 0,You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1

<?PHP
session_start();
include ('php only scripts/db.php');
$limit = 'LIMIT ' .($pn - 1) * $itemsPerPage .',' .$itemsPerPage; 

$query2 = "SELECT * FROM companies WHERE what_services IN('R','B') ORDER BY approved DESC, company_name ASC $limit";
$result2 = mysql_query($query2); 

echo $query2;
echo mysql_error();
?>

Porl123 · June 18, 2012

In this smaller page are $pn and $itemsPerPage still defined?

andy_b_1502 · June 18, 2012

testing this code it works fine, so why doesn't it with the full code? its something to do with the either limit or the second query?

<?PHP
session_start();
include ('php only scripts/db.php');


$query2 = "SELECT * FROM companies WHERE what_services IN('R','B') ORDER BY approved DESC, company_name ASC";
$result2 = mysql_query($query2); 

echo $query2;
echo mysql_error();
?>

Porl123 · June 18, 2012

Well if that's the case I'd assume $pn and/or $itemsPerPage aren't being set correctly.

andy_b_1502 · June 18, 2012

} else { // If the pn URL variable is not present force it to be value of page number 1

$pn = 1;

}

doesn't that do what it say's force it to be number 1?

//This is where we set how many database items to show on each page

$itemsPerPage = 10;

i don't understand why $itemsPerPage wouldn't be set?

PFMaBiSmAd · June 18, 2012

} else { // If the pn URL variable is not present force it to be value of page number 1
$pn = 1;

}

^^^ That's not all the code in your script that can affect the value in $pn.

Sign In

how to split results into page 1,2,3 etc...

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information