Avatar of Jacko_Pinto
Jacko_Pinto
 asked on

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>    
   
           <!-- 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!
PHPMySQL ServerSQL

Avatar of undefined
Last Comment
Jacko_Pinto

8/22/2022 - Mon
Ray Paseur

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

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

@skullnobrains: +1

:-)
~Ray
Your help has saved me hundreds of hours of internet surfing.
fblack61
Jacko_Pinto

ASKER
Thanks guys,

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

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

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!
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
skullnobrains

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

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

ASKER
Hello again,  

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

Thanks.
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
ASKER CERTIFIED SOLUTION
skullnobrains

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Jacko_Pinto

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.