asked on 5/30/2012

Display MySql queries

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>


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

}
}

?>



</div>

</body>
</html>

Thanks for the help!

PHP MySQL Server SQL

Last Comment

Jacko_Pinto

6/12/2012

Ray Paseur

5/30/2012

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

skullnobrains

5/30/2012

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

Ray Paseur

5/30/2012

@skullnobrains: +1

:-)
~Ray

Jacko_Pinto

5/30/2012

ASKER

Thanks guys,

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

skullnobrains

5/31/2012

// 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.

Jacko_Pinto

5/31/2012

ASKER

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!

skullnobrains

6/3/2012

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

Jacko_Pinto

6/4/2012

ASKER

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!

Jacko_Pinto

6/8/2012

ASKER

Hello again,

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

Thanks.

ASKER CERTIFIED SOLUTION

skullnobrains

6/11/2012

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

Jacko_Pinto

6/12/2012

ASKER

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.