10 Replies - 554 Views - Last Post: 25 November 2012 - 07:33 PM Rate Topic: -----

#1 ettedo2000  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 33
  • Joined: 26-September 12

Question on Multiple page display of a query

Posted 24 November 2012 - 05:33 PM

Hello,I need some pointers with an assignment I have.

This is what I am doing: Taking a self-referential Form (that intakes a state name and than uses the database query to returned the information),where only 10 rows are shown on a page, give the user a button or link to click,that will bring the user to the next page. They should be able to do this until they reach the end of the data.I also can use session variables to track what page you are on and the query terms/results.

I already have the self-referential Form and Output page fully working. It just outputs all the rows, or I display only 10 rows using LIMIT 10.

What I can't figure out is how I create a "Next button" that will go to the next page, displaying the next 10 rows of the full query.

I would appreciated any pointers where I can learn more about this stuff or what I need to use to be able to move from page to page displaying the rows from the database. Thank you

Is This A Good Question/Topic? 0
  • +

Replies To: Question on Multiple page display of a query

#2 CTphpnwb  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 3099
  • View blog
  • Posts: 10,887
  • Joined: 08-August 08

Re: Question on Multiple page display of a query

Posted 24 November 2012 - 08:51 PM

LiMIT can take two parameters, the first being where to start.
Was This Post Helpful? 0
  • +
  • -

#3 goku87  Icon User is offline

  • D.I.C Head
  • member icon

Reputation: 4
  • View blog
  • Posts: 81
  • Joined: 17-October 09

Re: Question on Multiple page display of a query

Posted 24 November 2012 - 11:48 PM

When doing things like this I often use $_GET variables to go between pages by using something like /?page=view&start=11 and the previous/next links just subtract/add to $_GET['start'] and are concatenated into new links for the other pages. A way to check if you need a previous page is simple: whenever $_GET['start'] is greater than 1 you need a previous page. The next page, however, takes a little more clever trick. Set LIMIT to 11 instead of 10, and just loop through the first 10 rows when displaying data. Then, if there is still data in the 11th row, you know you need to display more data. :)/>
Was This Post Helpful? 0
  • +
  • -

#4 ettedo2000  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 33
  • Joined: 26-September 12

Re: Question on Multiple page display of a query

Posted 25 November 2012 - 06:04 AM

Thank you for your information, I'll start building my code and see where it will lead me. If I come across any problem I'll be back with some more question. Thank you for your help
Was This Post Helpful? 0
  • +
  • -

#5 ettedo2000  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 33
  • Joined: 26-September 12

Re: Question on Multiple page display of a query

Posted 25 November 2012 - 06:13 PM

Hello again, I have been reading and working on this assignment all day and I am really lost. I have included my code to show you what I have done so far.

Here is what is working: Right now the program is queering the user input and displaying the amount of rows and how may pages it would have if 10 rows are displayed. It also shows a Next button.

Here is where I am stuck: While it displaying the query it is displaying all the rows not just 10 rows per page. Also I am not sure how I can get it to link to the next page. Right know if I click on the next button it tells me fallowing error messages and is not displaying any rows.

Notice: Undefined index: city in on line 14

Notice: Undefined index: state in on line 15

Notice: Undefined index: zip in on line 16



I am so confused with this and my mind is tired. I hope you can help me. Thank you.

<!DOCTYPE HTML >
<?php

$city= $_GET['city'];
$state= $_GET['state'];
$zip= $_GET ['zip'];
$pagenum = 0;
$page_rows = 10; 
?>

<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<style>
table 	{width: 900px; 
		margin: auto;}
h2 		{text-align:center;
		font-style:bold;
		color:#5c743d;
		font-family:serif;
		font-size: 20pt;}
p		{text-align:center;}
</style>
<title>LAB </title>
</head>
<body>

<table border = "1">
   <thead>
      <tr>
	  
<?php
$myconn = new mysqli('localhost','phpuser','phppass','zipcode');						//Connect to Databas
	
if ($myconn->connect_errno) 															//Error message if connection failed
		{
		 echo "<p>";
		 printf("Connect failed: %s\n", $myconn->connect_error);
		 exit();
		}
		

//this makes sure the page number isn't below one, or more than our maximum pages 
 if ($pagenum < 1) 
 { 
 $pagenum = 1; 
 } 
 elseif ($pagenum > $last) 
 { 
 $pagenum = $last; 
 } 
 
$result = $myconn->query("SELECT * FROM zipcode WHERE city = '". $city . "' OR state = " . "'$state' OR Zipcode  = " . "'$zip' ");
$rows = mysqli_num_rows($result); 	

//This tells us the page number of our last page 
 $last = ceil($rows/$page_rows);	
	
	
	echo "<br><h2> There are ". mysqli_num_rows($result) . " Returns</h2></ br>";
	echo "<p>";
	
$finfo = $result->fetch_fields();														//fetch info from database
 
?>
<br> 
<?php																					//Loop for header names
    foreach ($finfo as $val) 																
	{
       echo "<th>". $val->name . "</ th>";
    }
?>
       </tr>
	</thead>

<?php


while ($line = $result->fetch_row()) 													//Ouput Loop of fetched data
	{
    echo "<tr>";   
    foreach ($line as $val) {
      echo "<td> $val </ td>";
	}    
	echo "</tr>";
}
 

 //This does the same as above, only checking if we are on the last page, and then generating the Next and Last links
 if ($pagenum == $last) 
 {
 } 
 else 
 {
 $next = $pagenum+1;
 echo " <a href='{$_SERVER['PHP_SELF']}?pagenum=$next'>Next -></a> ";
 echo " ";
 } 
 echo " --Page $pagenum of $last-- <p>";
?>
</table>

<?php

	mysqli_close($myconn);																//Close connection

?>
</body>
</html>



Was This Post Helpful? 0
  • +
  • -

#6 CTphpnwb  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 3099
  • View blog
  • Posts: 10,887
  • Joined: 08-August 08

Re: Question on Multiple page display of a query

Posted 25 November 2012 - 06:42 PM

Some of the things you're doing wrong:
  • Mixing HTML, CSS, and PHP in the same file. No wonder you're confused!
  • You're using MySQLi as if it were MySQL: without prepared statements! That's a major security issue.
  • Most importantly, you're not paying attention to advice! Did you read post #2 and the link provided?

Was This Post Helpful? 1
  • +
  • -

#7 ettedo2000  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 33
  • Joined: 26-September 12

Re: Question on Multiple page display of a query

Posted 25 November 2012 - 07:02 PM

You know this is not the easiest stuff to learn and I am not a professional programmer. We all have to learn somehow, often more on mistakes than success.

I am trying to get the hang of it and been reading and learning about this stuff all day. I did read post #2 and followed the link to the parameter information. I understand that Limit 0,10 will show the first 10 rows, and if I understand this right using Limit 10,10 would show the next 10 rows till the end? How would I create a "NEXT" button to access the next 10 rows?

I am not to confused in dedifferentiating the PHP, html and CSS codes. I am confused with using MySQLi (prepared statements) and on how to tell the query to go to the next page and show the the next 10 pages.

Thank you for replying I know my code is a mess sorry.
Was This Post Helpful? 0
  • +
  • -

#8 goku87  Icon User is offline

  • D.I.C Head
  • member icon

Reputation: 4
  • View blog
  • Posts: 81
  • Joined: 17-October 09

Re: Question on Multiple page display of a query

Posted 25 November 2012 - 07:04 PM

View PostCTphpnwb, on 25 November 2012 - 07:42 PM, said:

Some of the things you're doing wrong:
  • Mixing HTML, CSS, and PHP in the same file. No wonder you're confused!
  • You're using MySQLi as if it were MySQL: without prepared statements! That's a major security issue.
  • Most importantly, you're not paying attention to advice! Did you read post #2 and the link provided?

^ +1

Also, are you passing your city/state/zip via a URL ($_GET) or from a submitted form ($_POST) because that may be why your variables aren't being set.

And definitely read up about LIMIT; you need to use that to limit the rows to 10 per page. It will save you a lot of heartache as you've got a lot of extra foreach going on in there. Additionally, I think you are missing your "previous" link.

This post has been edited by goku87: 25 November 2012 - 07:05 PM

Was This Post Helpful? 0
  • +
  • -

#9 ettedo2000  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 33
  • Joined: 26-September 12

Re: Question on Multiple page display of a query

Posted 25 November 2012 - 07:17 PM

I am using the ($_GET) city/state/zip from a form where a user enters the desired value and than submits the page.

I have entered in my query
$result = $myconn->query("SELECT * FROM zipcode WHERE city = '". $city # "' OR state = " # "'$state' OR Zipcode  = " . "'$zip' LIMIT 0,10 ");



This of course will output the 10 rows of the query. Until know I pretty much understood how to use php but this assignment is taking me for a loop.

I'll work on the Previous butten next.

Thank you for trying to help I really appreciated it.
Was This Post Helpful? 0
  • +
  • -

#10 goku87  Icon User is offline

  • D.I.C Head
  • member icon

Reputation: 4
  • View blog
  • Posts: 81
  • Joined: 17-October 09

Re: Question on Multiple page display of a query

Posted 25 November 2012 - 07:27 PM

View Postettedo2000, on 25 November 2012 - 08:02 PM, said:

You know this is not the easiest stuff to learn and I am not a professional programmer. We all have to learn somehow, often more on mistakes than success.

I am trying to get the hang of it and been reading and learning about this stuff all day. I did read post #2 and followed the link to the parameter information. I understand that Limit 0,10 will show the first 10 rows, and if I understand this right using Limit 10,10 would show the next 10 rows till the end? How would I create a "NEXT" button to access the next 10 rows?

I am not to confused in dedifferentiating the PHP, html and CSS codes. I am confused with using MySQLi (prepared statements) and on how to tell the query to go to the next page and show the the next 10 pages.

Thank you for replying I know my code is a mess sorry.


Helping while trying not to do your assignment for you...

Some shorthand that would be immensely helpful in this situation would be conditionally setting a variable. You do this by: $var = (condition) ? [if_true] : [if_false];
To give a specific example, I would start out would be setting a variable to be the start of the query results:
$start = (isset($_GET['start'])) ? $_GET['start'] : 0;


Now to get the most out of this variable, you use it in your query statement, like so:
LIMIT $start, 11
as referenced from the previously provided link.

The rest is pretty simple. Just loop through the rows in a loop while using a counter, then once it reaches 10, break the loop. Afterwards, count the number of rows that were in the query; as I said earlier, if the number of rows is greater than 10, you need to do a next page. Going with my previous example, I would add 10 to $start and include it in the url for the next page, e.g. whatever/blah.php?start=$start

On another note, it appears that you are trying to do a lot at once. What I suggest you do is figure out what you actually need to do. Okay, so you need to display 10 rows per page. How would you do that? Break it down into smaller chunks and try to figure out how to do each of those smaller chunks, and then put the pieces together as you progress. Think about the smaller pieces rather than the puzzle as a whole. It makes the project much more manageable.

Also, you can clean up your query statement with fewer quotes:
$query = "SELECT * FROM zipcode WHERE city='$city' OR state='$state' OR Zipcode='$zip'";


Just for your information, you can include $vars within "double quotes" and they will be interpreted as the variable they represent, but if they are between 'single quotes' then they will be parsed as just a string. This is why the above query statement should work.
Was This Post Helpful? 0
  • +
  • -

#11 ettedo2000  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 33
  • Joined: 26-September 12

Re: Question on Multiple page display of a query

Posted 25 November 2012 - 07:33 PM

Thank you for your help goku87. Never wanted anyone to do the homework for me, that's not how you learn, just needed an idea where to start. Thank you again
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1