pagination problem

scitech · Aug 1, 2005

This code displays the first record in the limit query but when I click "next", the page is updated but no record is displayed.All the bits of code work independently, but now that I have got them together!!! classic problem. No error message is displayed.

Code:

<?PHP
include 'config.php';
include 'opendb.php';
$page_name = 'update_form01.php'; //  If you use this code with a different page ( or file ) name then change this 


//create search form, 
?>
<form method="POST" action="<?php echo $_SERVER['PHP_SELF'];?>">
Enter First Name:-<INPUT NAME="FirstName" TYPE="TEXT" id="FirstName" size="50" maxlength="50"><br>
Enter Surname:-<INPUT NAME="SurName" TYPE="TEXT" id="SurName" size="50" maxlength="50"><br>
<INPUT TYPE="SUBMIT" NAME="Search" id="SUBMIT" VALUE="Search"> 
</FORM>

<?PHP
$queryItem = array();
///create the start variable that will chage when the link is clicked
	
	$start =(isset($_GET['start']))?$_GET['start']:0;


$eu = ($start); 
$limit = 1;                                 // No of records to be shown per page.
$this_one = $eu + $limit; 
$back = $eu - $limit; 
$next = $eu + $limit;

if(isset($_POST['Search']))
{

	
	if ($_POST['FirstName'])
	{
   $queryItem[] = "FirstName='".mysql_escape_string($_POST['FirstName'])."'";
	}
	if ($_POST['SurName'])
	{
   $queryItem[] = "SurName='".mysql_escape_string($_POST['SurName'])."'";
	}
	if ($_POST['Postcode'])
	{
   $queryItem[] = "Postcode='".mysql_escape_string($_POST['Postcode'])."'";
	}	

 
//////////////////////////now create query statement 
$query1 = "SELECT * FROM Addressbook 
		WHERE ".implode(" AND ", $queryItem)  
		or die(mysql_error());

$result1 = mysql_query($query1) or die(mysql_error());
$nume = mysql_num_rows($result1) or die(mysql_error());

/////// The variable nume above will store the total number of records in the query////
////////////// Now let us start executing the query with variables $eu and $limit  set at the top of the page///////////
$query = "SELECT * FROM Addressbook
	WHERE  ".implode(" AND ", $queryItem)." 
	LIMIT $eu, $limit " 
	or die(mysql_error());

$result = mysql_query($query) or die(mysql_error());
// get the entry from the result

	// Print out the contents 
		while($row = mysql_fetch_array($result)) 
                {
		echo $row['FirstName']." ".$row['SurName'];
		echo"<br>";
		echo $row['Address1']." ".$row['Address2']." ".$row['Address3'];
		echo"<br>";
		echo $row['Town']." ".$row['Postcode'];
		echo"<br>";
		echo "<em><strong>E-Mail:-</em></strong>"." ".$row['email'];
		echo"<br>";
		echo "<em><strong>Phone:-</em></strong>"." ".$row['Phone'];
		echo"<p></P>";
		}
echo "<br>";
}		
////////////////////////////// End of displaying the table with records ////////////////////////
/////////////// Start the buttom links with Prev and next link with page numbers /////////////////
echo "<table align = 'center' width='50%'><tr><td  align='left' width='30%'>";
//// if our variable $back is equal to 0 or more then only we will display the link to move back ////////
if($back >0)
	{ 
	print "<a href='$page_name?start=$back'>PREV</a>"; 
	} 

echo "</td><td  align='right' width='30%'>";
///////////// If we are not in the last page then Next link will be displayed. Here we check that /////
if($this_one < $nume)
	{ 
	print "<a href='$page_name?start=$next'>NEXT</a>";
	} 
	echo "</td></tr></table>";

?>

WoodyRoundUp · Aug 1, 2005

Check this one first:

$query = "SELECT * FROM Addressbook
WHERE ".implode(" AND ", $queryItem)."
LIMIT $eu, $limit ";
echo $query;

What does it give you?

scitech · Aug 5, 2005

Hi Woody
The echo $query, gives me the query with the values for the Limit section.
My problem is that the code works fine when it is like this

Code:

"SELECT * FROM  addressbook LIMIT $eu, $limit";

It is when I have added the WHERE statement that it only displays the First(as chosen by the variable $start) record. So the above code works fine. This does not!

Code:

"SELECT * FROM  addressbook WHERE ".implode(" AND",$queryItem)." LIMIT $eu, $limit";

WoodyRoundUp · Aug 5, 2005

Try this.

Code:

"SELECT * FROM  addressbook WHERE ".implode(" AND ",$queryItem)." LIMIT $eu, $limit";

scitech · Aug 5, 2005

Hi woody
Sorry that's my typo here,
I have that code version, and I have tryed lots of adding and taking away spaces, for some reason it does not like the Where statement

WoodyRoundUp · Aug 5, 2005

Then, can you echo that query for me please?
So, I can see what's going on.

Thanks.

scitech · Aug 5, 2005

Hi Woody
Here is the echo

SELECT * FROM Addressbook WHERE Surname="Hirschfeld" LIMIT 0,1

it all looks fine to me, this query should return more than 1 record, if i want to display two records on the page it does display the two records,but if I display 1 record and then click next all I get is a blank page!

WoodyRoundUp · Aug 5, 2005

SELECT * FROM addressbook WHERE ".implode(" AND ",$queryItem)." LIMIT $eu, $limit";

You should really have:

SELECT * FROM Addressbook WHERE Surname='Hirschfeld' LIMIT 0,1

in your query.

That's why you will never get a result.

scitech · Aug 5, 2005

hi Woody
I don't understand
"SELECT * FROM addressbook WHERE ".implode(" AND ",$queryItem)." LIMIT $eu, $limit";
is my query

SELECT * FROM Addressbook WHERE Surname='Hirschfeld' LIMIT 0,1

is the echo of the query when I run the query, I might want to search for postcode or firstname not just surname

WoodyRoundUp · Aug 5, 2005

This is what you type previously.

SELECT * FROM Addressbook WHERE Surname="Hirschfeld" LIMIT 0,1

Definately you are not taking the output from the query, but you are just typing it in.

Please just do me a favor and echo it so we cound find out the problem.

scitech · Aug 5, 2005

Hi woody

Code:

$query = "SELECT * FROM Addressbook
    WHERE  ".implode(" AND ", $queryItem)."
    LIMIT $eu, $limit "
    or die(mysql_error());
echo $query;

when I run this query I get:-
SELECT * FROM Addressbook WHERE Surname="Hirschfeld" LIMIT 0,1
or if I leave the input blank I get "you have an error...." at line 2" which i thought was because I don't have a catch code yet for a blank search!

WoodyRoundUp · Aug 5, 2005

OK.
Here is your problem.

SELECT * FROM Addressbook WHERE Surname="Hirschfeld" LIMIT 0,1

You should have this:
SELECT * FROM Addressbook WHERE Surname='Hirschfeld' LIMIT 0,1

So, there is something in the $queryItem that you need to fix.

scitech · Aug 5, 2005

hi woody

Code:

$query1 = "SELECT * FROM Addressbook
        WHERE ".implode(" AND ", $queryItem)  
        or die(mysql_error());
echo $query1;
$query = "SELECT * FROM Addressbook
        WHERE ".implode(" AND ", $queryItem)  
        or die(mysql_error());
echo $query;

If I run the code above as shown, one after the other, the first gives no error but the second does, any ideas why?

WoodyRoundUp · Aug 6, 2005

How do you run it?
From where?
From PHP?
Then, I will need to see how you code for the whole thing.

scitech · Aug 6, 2005

hi woody
All I have done is just change the above code(at the top here) so that the second query was exactly the same as the first,to find some errors, echo'ed both and the first ran fine but the second gave the you have an error....line 2 error. I have looked hard at the $queryItem[] area and cannot see where the problem is!

WoodyRoundUp · Aug 6, 2005

Hi scitech.

Do you know that throughout the whole post, you have not been clear in identifying the problem?

if ($_POST['FirstName'])
{
$queryItem[] = "FirstName='".mysql_escape_string($_POST['FirstName'])."'";
}

if ($_POST['SurName'])
{
$queryItem[] = "SurName='".mysql_escape_string($_POST['SurName'])."'";
}

if ($_POST['Postcode'])
{
$queryItem[] = "Postcode='".mysql_escape_string($_POST['Postcode'])."'";
}

$query = "SELECT * FROM Addressbook
WHERE ".implode(" AND ", $queryItem)."
LIMIT $eu, $limit "
or die(mysql_error());

If you are right about the code,
if you do echo, you should get this:

SELECT * FROM Addressbook WHERE Surname='Hirschfeld' LIMIT 0,1

Not this:
SELECT * FROM Addressbook WHERE Surname="Hirschfeld" LIMIT 0,1

If you are getting this, that means that there is error in your SQL.

And also, what is your error message? You said line 2, but what exactly is your error message?

Although I know what I am doing, but I cannot see what you are facing, and I don't know your data as well.

So, if you want to solve this problem faster, I would be happy to see your code (the one you do), and error message that you have.

scitech · Aug 8, 2005

hi Woody
here is my code again, the original problem was that when I ran a query with a result that gave more than one page (from a LIMIT statement in the $query,which I have now removed) only the data for the first page would display.
Folowing your advice I have put in some error checking code. echo $query1, then a bit later :- echo $query. When I now run a blank query(no form imput)
the $query1 gives no error message(SELECT * FROM Addressbook WHERE),
but $query gives:- 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 2.

Code:

<?PHP
include 'config.php';
include 'opendb.php';
$page_name = 'update_form01.php'; //  If you use this code with a different page ( or file ) name then change this 


//create search form, 
?>
<form method="POST" action="<?php echo $_SERVER['PHP_SELF'];?>">
Enter First Name:-<INPUT NAME="FirstName" TYPE="TEXT" id="FirstName" size="50" maxlength="50"><br>
Enter Surname:-<INPUT NAME="SurName" TYPE="TEXT" id="SurName" size="50" maxlength="50"><br>
<INPUT TYPE="SUBMIT" NAME="Search" id="SUBMIT" VALUE="Search"> 
</FORM>

<?PHP
$queryItem = array();
///create the start variable that will chage when the link is clicked
	
	$start =(isset($_GET['start']))?$_GET['start']:0;


$eu = ($start - 0); 
$limit = 1;                                 // No of records to be shown per page.
$this_one = $eu + $limit; 
$back = $eu - $limit; 
$next = $eu + $limit;

if(isset($_POST['Search']))
{

	
	if ($_POST['FirstName'])
	{
   $queryItem[] = "FirstName='".mysql_escape_string($_POST['FirstName'])."'";
	}
	if ($_POST['SurName'])
	{
   $queryItem[] = "SurName='".mysql_escape_string($_POST['SurName'])."'";
	}
	if ($_POST['Postcode'])
	{
   $queryItem[] = "Postcode='".mysql_escape_string($_POST['Postcode'])."'";
	}	

 
//////////////////////////now create query statement 
$query1 = "SELECT * FROM Addressbook 
		WHERE ".implode(" AND ", $queryItem)  
		or die(mysql_error());
echo $query1;
echo "<br>";

$result1 = mysql_query($query1) or die(mysql_error());
$nume = mysql_num_rows($result1) or die(mysql_error());

/////// The variable nume above will store the total number of records in the query////
////////////// Now let us start executing the query with variables $eu and $limit  set at the top of the page///////////
$query = "SELECT * FROM Addressbook
	WHERE  ".implode(" AND ", $queryItem) 
	or die(mysql_error());
echo $query;

$result = mysql_query($query) or die(mysql_error());
// get the entry from the result

	// Print out the contents 
		do{
		echo $row['FirstName']." ".$row['SurName'];
		echo"<br>";
		echo $row['Address1']." ".$row['Address2']." ".$row['Address3'];
		echo"<br>";
		echo $row['Town']." ".$row['Postcode'];
		echo"<br>";
		echo "<em><strong>E-Mail:-</em></strong>"." ".$row['email'];
		echo"<br>";
		echo "<em><strong>Phone:-</em></strong>"." ".$row['Phone'];
		echo"<p></P>";
		}while($row = mysql_fetch_array($result)) ;
echo "<br>";
}		
////////////////////////////// End of displaying the table with records ////////////////////////
/////////////// Start the buttom links with Prev and next link with page numbers /////////////////
echo "<table align = 'center' width='50%'><tr><td  align='left' width='30%'>";
//// if our variable $back is equal to 0 or more then only we will display the link to move back ////////
if($back >0)
	{ 
	print "<a href='$page_name?start=$back'>PREV</a>"; 
	} 

echo "</td><td  align='right' width='30%'>";
///////////// If we are not in the last page then Next link will be displayed. Here we check that /////
if($this_one < $nume)
	{ 
	print "<a href='$page_name?start=$next'>NEXT</a>";
	} 
	echo "</td></tr></table>";

?>

<?PHP
include 'close.php';
?>

WoodyRoundUp · Aug 8, 2005

Your big big problem is that, you are assuming that the POST does exist in the next page, which does not happen.
You need to store the POST in the SESSION variable.
My code below should work according to what you want.

<?PHP
include 'config.php';
include 'opendb.php';
$page_name = 'update_form01.php'; // If you use this code with a different page ( or file ) name then change this

if(isset($_POST['Search']))
{
session_start();
unset ($_SESSION);
$_SESSION = $_POST;
}

if (isset($_SESSION["FirstName"]))
$FirstName = $_SESSION["FirstName"];
if (isset($_SESSION["SurName"]))
$SurName = $_SESSION["SurName"];
if (isset($_SESSION["Postcode"]))
$Postcode = $_SESSION["Postcode"];

//create search form,
?>
<form method="POST" action="<?php echo $_SERVER['PHP_SELF'];?>">
Enter First Name:-<INPUT NAME="FirstName" TYPE="TEXT" id="FirstName" size="50" maxlength="50" value="<?= (isset($FirstName) ? $FirstName : "")?>"><br>
Enter Surname:-<INPUT NAME="SurName" TYPE="TEXT" id="SurName" size="50" maxlength="50" value="<?=(isset($SurName) ? $SurName : "")?>"><br>
<INPUT TYPE="SUBMIT" NAME="Search" id="SUBMIT" VALUE="Search">
</FORM>

<?PHP
$queryItem = array();
///create the start variable that will chage when the link is clicked

$start =(isset($_GET['start']))?$_GET['start']:0;

$eu = ($start - 0);
$limit = 1; // No of records to be shown per page.
$this_one = $eu + $limit;
$back = $eu - $limit;
$next = $eu + $limit;

if(isset($_SESSION['Search']))
{
if ($FirstName)
$queryItem[] = "FirstName='".mysql_escape_string($FirstName)."'";
if ($SurName)
$queryItem[] = "SurName='".mysql_escape_string($SurName)."'";
if ($Postcode)
$queryItem[] = "Postcode='".mysql_escape_string($Postcode)."'";

//////////////////////////now create query statement
$query1 = "SELECT * FROM Addressbook
WHERE ".implode(" AND ", $queryItem)
or die(mysql_error());

$result1 = mysql_query($query1) or die(mysql_error());
$nume = mysql_num_rows($result1) or die(mysql_error());

/////// The variable nume above will store the total number of records in the query////
////////////// Now let us start executing the query with variables $eu and $limit set at the top of the page///////////
$query = "SELECT * FROM Addressbook
WHERE ".implode(" AND ", $queryItem) ."
LIMIT $eu, $limit "
or die(mysql_error());
echo $query;

$result = mysql_query($query) or die(mysql_error());
// get the entry from the result

// Print out the contents
do{
echo $row['FirstName']." ".$row['SurName'];
echo"<br>";
echo $row['Address1']." ".$row['Address2']." ".$row['Address3'];
echo"<br>";
echo $row['Town']." ".$row['Postcode'];
echo"<br>";
echo "<em><strong>E-Mail:-</em></strong>"." ".$row['email'];
echo"<br>";
echo "<em><strong>Phone:-</em></strong>"." ".$row['Phone'];
echo"<p></P>";
}while($row = mysql_fetch_array($result)) ;

echo "<br>";
}
////////////////////////////// End of displaying the table with records ////////////////////////
/////////////// Start the buttom links with Prev and next link with page numbers /////////////////
echo "<table align = 'center' width='50%'><tr><td align='left' width='30%'>";
//// if our variable $back is equal to 0 or more then only we will display the link to move back ////////
if($back >0)
{
print "<a href='$page_name?start=$back'>PREV</a>";
}

echo "</td><td align='right' width='30%'>";
///////////// If we are not in the last page then Next link will be displayed. Here we check that /////
if($this_one < $nume)
{
print "<a href='$page_name?start=$next'>NEXT</a>";
}
echo "</td></tr></table>";

?>

<?PHP
include 'close.php';
?>

scitech · Aug 9, 2005

Hi Woody
Thanks for the Session() code, I've just started looking at Sessions. But I now get these error messages:-

Warning: session_start() [function.session-start]: Cannot send session cookie - headers already sent by (output started at c:\Inetpub\

http://wwwroot\sql

stuff\address_book\update_form01.php:9) in c:\Inetpub\

http://wwwroot\sql

stuff\address_book\update_form01.php on line 16

Warning: session_start() [function.session-start]: Cannot send session cache limiter - headers already sent (output started at c:\Inetpub\

http://wwwroot\sql

stuff\address_book\update_form01.php:9) in c:\Inetpub\

http://wwwroot\sql

stuff\address_book\update_form01.php on line 16

and still
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 2

again any help would be gratefully received

WoodyRoundUp · Aug 9, 2005

Again, you need to tell me what is line 16 in your file.

And the SQL error is because there is session error.
And I think it's better to update the $page_name.

<?PHP
include 'config.php';
include 'opendb.php';
$page_name = $_SERVER["PHP_SELF"]; // If you use this code with a different page ( or file ) name then change this

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

pagination problem

Technical User

Programmer

Technical User

Programmer

Technical User

Programmer

Technical User

Programmer

Technical User

Programmer

Technical User

Programmer

Technical User

Programmer

Technical User

Programmer

Technical User

Programmer

Technical User

Programmer

Similar threads

Log in

Part and Inventory Search

Sponsor