We help IT Professionals succeed at work.

Display MySql queries

350 Views
Last Modified: 2012-06-12
Hi Experts,

I'm a php-mysql newbie and I'm trying to set up a web page which basically displays the data that I collect from a form.  The query is working fine and it's pulling up the 5 most recent records, but I'm completely stuck with this:

 - At the bottom of the page I want to add the "Next and Previous" option, so that readers can navigate to another page if there are more than five records and come back to a previous page if needed.  

Here's is the current code:

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
<title>My web page</title>
 <?php
include 'inc/config.php';
include 'inc/opendb.php'; ?>
<link rel="stylesheet" type="style/principal.css" />

</head>

<body>

 <div>    
   
           <!-- QUERY STARTS HERE --->
      <?php
         mysql_query("SET NAMES 'charset=CP1251' ", $conn );
$query = "SELECT * FROM `ap_form_1` INNER JOIN `ap_element_options` ON (ap_form_1.element_4 = ap_element_options.option_id)  ORDER BY ID DESC LIMIT 5";


$result = mysql_query($query, $conn );       
       
         
if( $result === FALSE ) {
  echo '<div class="ERROR">'.mysql_error().'</div>';
} else {
while($row = mysql_fetch_array($result))
{

echo "<div id=\"bloque\">
<div id=\"fecha\">" . utf8_encode($row["element_1"]). "</div>
<div id=\"actititle\">" . utf8_encode($row["element_3"]). "</div>
<div id=\"actititle\">" . utf8_encode($row["element_5"]). "</div>
<div id=\"actititle\">" . utf8_encode($row["option"]). "(Tipo de archivo)</div>
<div id=\"actititle\">" . utf8_encode($row["element_6"]). "</div>
";

}
}

?>

                   <!--END QUERY--->


</div>


</body>
</html>

Thanks for the help!
Comment
Watch Question

Most Valuable Expert 2011
Author of the Year 2014

Commented:
Without (admittedly) spending a lot of time investigating this question, it sounds like a "pagination" issue.  The canonical explanation of pagination is from SitePoint, here:
http://www.sitepoint.com/perfect-php-pagination/

If that is not helpful, please post back here and we can look into it further.  Best, ~Ray
CERTIFIED EXPERT

Commented:
limit clause can be give 2 parameters instread of one

so you can select rows 10 to 15 with "LIMIT 10 , 5"

just build it as "LIMIT $page_number * $rows_per_page , $rows_per_page"

in your example, rows_per_page is 5

and page_number should be given as an argument to the page in the previous and next links
Most Valuable Expert 2011
Author of the Year 2014

Commented:
@skullnobrains: +1

:-)
~Ray

Author

Commented:
Thanks guys,

But, from what I currently have,  how would I add this to my code so that the pagination works?
CERTIFIED EXPERT

Commented:
// get the current page from the query string if it has been passed
$curpage=$_GET['curpage'];

//if not or if it is invalid, make it default to the first page
$curpage > 0 or $curpage=1;

// set the number of rows per page
$npp=5;

// build the LIMIT clause of the query
q="... LIMIT ".(($curpage-1)*$npp)." , ".$npp;

// print the previous link if the current page was not the first one
$curpage > 1 and print('<a href="?curpage='.($curpage-1).'">prev</a>');

i believe you can figure out how to build the next link by yourself and also where to stick these few lines of code in your page : first 3 should be somewhere on top, limit clause should probably be built right before you execute the query, prev and next links should probably be built where you want them to be printed.

Author

Commented:
Thanks skullnobrains,

I'm a total newbie but I did try setting this up on my page... I couldn't get it to work. (going crazy here!)

Can you please show me how  to include this on my  current code so that it works?

Thanks a lot!
CERTIFIED EXPERT

Commented:
this should look like it

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
<title>My web page</title>
 <?php
include 'inc/config.php';
include 'inc/opendb.php'; ?>
<link rel="stylesheet" type="style/principal.css" />

</head>

<body>

 <div>    
   
           <!-- QUERY STARTS HERE --->
      <?php
      
      // get the current page from the query string if it has been passed
      $curpage=$_GET['curpage'];

      //if not or if it is invalid, make it default to the first page
      $curpage > 0 or $curpage=1;

      // set the number of rows per page
      $npp=5;

      
      mysql_query("SET NAMES 'charset=CP1251' ", $conn );
      
      $query = "SELECT * FROM `ap_form_1` INNER JOIN `ap_element_options` ON (ap_form_1.element_4 = ap_element_options.option_id)  ORDER BY ID DESC";
      $query.=" LIMIT ".(($curpage-1)*$npp)." , ".$npp;

      $result = mysql_query($query, $conn );       
       
         
if( $result === FALSE ) {
  echo '<div class="ERROR">'.mysql_error().'</div>';
} else {
while($row = mysql_fetch_array($result))
{

	// print the previous link if the current page was not the first one
	$curpage > 1 and print('<a href="?curpage='.($curpage-1).'">prev</a>');
	
echo "<div id=\"bloque\">
<div id=\"fecha\">" . utf8_encode($row["element_1"]). "</div>
<div id=\"actititle\">" . utf8_encode($row["element_3"]). "</div>
<div id=\"actititle\">" . utf8_encode($row["element_5"]). "</div>
<div id=\"actititle\">" . utf8_encode($row["option"]). "(Tipo de archivo)</div>
<div id=\"actititle\">" . utf8_encode($row["element_6"]). "</div>
";

}
}

?>

                   <!--END QUERY--->


</div>


</body>
</html>

Open in new window

Author

Commented:
Thanks skullnobrains,

Please forgive my ignorance; I really need help to get this done.

I don't know how to build the "next page" link or where to put that code.  Thanks for the help!

Author

Commented:
Hello again,  

Can you please reply to my last post.  I really need help building the "next page"  links.

Thanks.
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
Thanks skullnobrains,

I have a small company, I'm a one man show, which is why Experts Exch. comes as a great help for me.  Asking for help is what this whole thing is about, isn't it?

Thank you very much for your tips and comments! I beleive I've got it now.

Regards.
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.