Jump to content

[SOLVED] Pagination

flowingwindrider

By flowingwindrider
in PHP Coding Help

 Share

Recommended Posts

flowingwindrider Member

flowingwindrider

Posted
Posted

This process of paginating is breaking my brain! For the past several hours I have been following a tutorial that was not overly specific to my needs and turned out to have multiple errors in it. I have followed its process as best as I could, but it doesn't work. The script doesn't return an error, it just fails to paginate the results. Can someone give me a clue as to where I need to begin fixing this code?

 

<?php

$Per_page = 10;
$dbh = mysql_connect("localhost", "user", "password") or die ("Error Connecting: ". mysql_error());

mysql_select_db("bransone_classifieds") or die ("Could not select the database: " . mysql_error());

$sql= mysql_query("SELECT COUNT(Ad_ID) AS Total From employment") or die ("Error in query" . mysql_error());
$sql_result = mysql_fetch_assoc($sql) or die ("Error in query" . mysql_error());
$total = $sql_result['Total'];

if (empty($_GET['Result_Set']))
{
$Result_Set = 0;
$sql .= " LIMIT $Result_Set, $Per_Page";
}
else
{
$Result_Set = $_GET['Result_Set'];
$sql .= " LIMIT $Result_Set, $Per_Page";
}

$sql = mysql_query("SELECT Ad_Id, `Date`, Type_Job FROM employment ORDER BY `Date` DESC, Ad_Id DESC") or die ("Error in query: " . mysql_error());
$sql_rows = mysql_num_rows($sql) or die ("Error in query: " . mysql_error()); 

for ($a = 0; $a < $sql_rows; $a++)
{
$sql_array = mysql_fetch_assoc($sql) or die ("Error in query: " . mysql_error());
$ad_id = $sql_array['Ad_Id'];
$date = $sql_array['Date'];
echo "$ad_id - $date<BR><BR>";
}

if ($Total > 0)
{
if ($Result_Set < $Total && Result > 0)
	{
	$res1 = $Result_Set-$Per_Page;
	echo "<a href = \"test.php?Result_Set=$res1\">Previous 10 Listings</a>";
	}

$Pages = $Total / $Per_Page;

if ($Pages > 1)
	{
	for ($b = 0, $c = 1; $b < $Pages; $b++, $c++)
		{
		$Res1=$Per_Page * $b;
		echo "<a href=\"test.php?Result_Set=$Res1\">$c</a>n";
		}
	}

if ($Result_Set >= 0 && $Result_Set < $Total)
	{
	$Res1=$Result_Set + $Per_Page;
	if ($Res1 < $Total)
		{
		echo "<a href=\"test.php?Result_Set=$Res1\">Next Page</a>";
		}
	}
}

mysql_close($dbh);

?>

 

Thanks as always.

Link to comment
Share on other sites
teng84 Member

teng84

Posted
Posted 

$sql .= " LIMIT $Result_Set, $Per_Page";
}

$sql = mysql_query("SELECT Ad_Id, `Date`, Type_Job FROM employment ORDER BY `Date` DESC, Ad_Id DESC") or die ("Error in query: " . mysql_error());

look carefully

 

the value of sql is not what you want it is simply

$sql = mysql_query("SELECT Ad_Id, `Date`, Type_Job FROM employment ORDER BY `Date` DESC, Ad_Id DESC") or die ("Error in query: " . mysql_error());

 

why? you over write the 1st $sql

 

so maybe

$sql = mysql_query("SELECT Ad_Id, `Date`, Type_Job FROM employment ORDER BY `Date` DESC, Ad_Id DESC $sql") or die ("Error in query: " . mysql_error());

 

something like that

 

or place the condition after the 2nd sql

Link to comment
Share on other sites
teng84 Member

teng84

Posted
Posted

I've altered the name of the 2nd $sql to a different name. The tutorial that I was (unfortunately) following had me reusing the variable.

 

Anyway, even after this change the script still fails to paginate the results of the query.

 

i told you to look at the posted code of mine carefully but it seems you still dont get it any way

ill dot it much better for wait dude ill do my best to explain

 

Link to comment
Share on other sites
teng84 Member

teng84

Posted
Posted 
//try
$sql = mysql_query("SELECT Ad_Id, `Date`, Type_Job FROM employment ORDER BY `Date` DESC, Ad_Id DESC") or die ("Error in query: " . mysql_error());
if (empty($_GET['Result_Set']))
{
$Result_Set = 0;
$sql .= " LIMIT $Result_Set, $Per_Page";/
}
else
{
$Result_Set = $_GET['Result_Set'];
$sql .= " LIMIT $Result_Set, $Per_Page";
}
$sql_rows = mysql_num_rows($sql) or die ("Error in query: " . mysql_error()); 

// you try to concatenate right but it has no sense maybe you only have that code fron somewhere
//

// or maybe you can have this
if (empty($_GET['Result_Set']))
{
$Result_Set = 0;
$sql .= " LIMIT $Result_Set, $Per_Page";/
}
else
{
$Result_Set = $_GET['Result_Set'];
$sql = " LIMIT $Result_Set, $Per_Page";
}
$sql_rows = mysql_num_rows($sql) or die ("Error in query: " . mysql_error()); 
$sql = mysql_query("SELECT Ad_Id, `Date`, Type_Job FROM employment ORDER BY `Date` DESC, Ad_Id DESC $sql ");//see the $sql here

Link to comment
Share on other sites
flowingwindrider Member

flowingwindrider

Posted
  • Author
Posted

Ok, let me repost my code. I have altered the name of the 2nd $sql variable to avoid confusion (mine, not yours). I see what you were saying about my re-writing the variable AFTER I tried to concatenate the LIMIT string onto it, and I think this new order (and variable name) fixes that problem. However, now the scripts errors at line 36:

 

$sql_array = mysql_fetch_assoc($query) or die ("Error in query: " . mysql_error());

 

saying: Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource

 

Now I have even less of a clue then before, and I didn't think that was possible. Below is my full script, thanks for sticking with me.

 

<?php

$Per_page = 10;
$dbh = mysql_connect("localhost", "user", "password") or die ("Error Connecting: ". mysql_error());

mysql_select_db("bransone_classifieds") or die ("Could not select the database: " . mysql_error());

$sql= mysql_query("SELECT COUNT(Ad_ID) AS Total From employment") or die ("Error in query" . mysql_error());
$sql_result = mysql_fetch_assoc($sql) or die ("Error in query" . mysql_error());
$total = $sql_result['Total'];

$query = mysql_query("SELECT Ad_Id, `Date`, Type_Job FROM employment ORDER BY `Date` DESC, Ad_Id DESC") or die ("Error in query: " . mysql_error());
$sql_rows = mysql_num_rows($query) or die ("Error in query: " . mysql_error());

if (empty($_GET['Result_Set']))
{
$Result_Set = 0;
$query .= " LIMIT $Result_Set, $Per_Page";
}
else
{
$Result_Set = $_GET['Result_Set'];
$query .= " LIMIT $Result_Set, $Per_Page";
}

for ($a = 0; $a < $sql_rows; $a++)
{
$sql_array = mysql_fetch_assoc($query) or die ("Error in query: " . mysql_error());
$ad_id = $sql_array['Ad_Id'];
$date = $sql_array['Date'];
echo "$ad_id - $date<BR><BR>";
}

if ($Total > 0)
{
if ($Result_Set < $Total && Result > 0)
	{
	$res1 = $Result_Set-$Per_Page;
	echo "<a href = \"test.php?Result_Set=$res1\">Previous 10 Listings</a>";
	}

$Pages = $Total / $Per_Page;

if ($Pages > 1)
	{
	for ($b = 0, $c = 1; $b < $Pages; $b++, $c++)
		{
		$Res1=$Per_Page * $b;
		echo "<a href=\"test.php?Result_Set=$Res1\">$c</a>n";
		}
	}

if ($Result_Set >= 0 && $Result_Set < $Total)
	{
	$Res1=$Result_Set + $Per_Page;
	if ($Res1 < $Total)
		{
		echo "<a href=\"test.php?Result_Set=$Res1\">Next Page</a>";
		}
	}
}

mysql_close($dbh);

?>

Link to comment
Share on other sites
teng84 Member

teng84

Posted
Posted

i see your error

 

<?php

$Per_page = 10;
$dbh = mysql_connect("localhost", "user", "password") or die ("Error Connecting: ". mysql_error());

mysql_select_db("bransone_classifieds") or die ("Could not select the database: " . mysql_error());

$sql= mysql_query("SELECT COUNT(Ad_ID) AS Total From employment") or die ("Error in query" . mysql_error());
$sql_result = mysql_fetch_assoc($sql) or die ("Error in query" . mysql_error());
$total = $sql_result['Total'];

$query = mysql_query("SELECT Ad_Id, `Date`, Type_Job FROM employment ORDER BY `Date` DESC, Ad_Id DESC") or die ("Error in query: " . mysql_error());
$sql_rows = mysql_num_rows($query) or die ("Error in query: " . mysql_error());
if (empty($_GET['Result_Set']))
{
$Result_Set = 0;
$query .="LIMIT ".$Result_Set.", ".$Per_Page."";
}
else
{
$Result_Set = $_GET['Result_Set'];
$query .="LIMIT ".$Result_Set.", ".$Per_Page."";
}
$query = mysql_query("SELECT Ad_Id, `Date`, Type_Job FROM employment ORDER BY `Date` DESC, Ad_Id DESC ".$query."");
for ($a = 0; $a < $sql_rows; $a++)
{
$sql_array = mysql_fetch_assoc($query) or die ("Error in query: " . mysql_error());
$ad_id = $sql_array['Ad_Id'];
$date = $sql_array['Date'];
echo "$ad_id - $date<BR><BR>";
}

if ($Total > 0)
{
if ($Result_Set < $Total && Result > 0)
	{
	$res1 = $Result_Set-$Per_Page;
	echo "<a href = \"test.php?Result_Set=$res1\">Previous 10 Listings</a>";
	}

$Pages = $Total / $Per_Page;

if ($Pages > 1)
	{
	for ($b = 0, $c = 1; $b < $Pages; $b++, $c++)
		{
		$Res1=$Per_Page * $b;
		echo "<a href=\"test.php?Result_Set=$Res1\">$c</a>n";
		}
	}

if ($Result_Set >= 0 && $Result_Set < $Total)
	{
	$Res1=$Result_Set + $Per_Page;
	if ($Res1 < $Total)
		{
		echo "<a href=\"test.php?Result_Set=$Res1\">Next Page</a>";
		}
	}
}

mysql_close($dbh);

?>

 

try

Link to comment
Share on other sites
flowingwindrider Member

flowingwindrider

Posted
  • Author
Posted

Sorry, that didn't work either. It still flubs up at Line 36, but now with the error:

 

Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource. 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 'Resource id #3LIMIT 0,' at line 1

Link to comment
Share on other sites
flowingwindrider Member

flowingwindrider

Posted
  • Author
Posted

Well, I'm quite a bit closer now then I was before. I gave up on the old script and found a new tutorial that made sense (mostly). Now everything works except that the links at the bottom that should take you to the next results page leave you right where you started. There are 25 records in the table, and I can only see the first 10 regardless of what page I am on. I'm so close...can anyone help?

 

<?php

@$dbh = mysql_connect("localhost", "user", "password") or die ("Error Connecting: " . mysql_error());
@mysql_select_db("bransone_classifieds") or die ("Can't find database: " . mysql_error());
$limit = 10;
$query_count = "SELECT COUNT(Ad_Id) AS Total FROM employment";
$result_count = mysql_query($query_count) or die ("Error in query" . mysql_error());
$fetch_result  = mysql_fetch_assoc($result_count) or die ("Error in query" . mysql_error());
$totalrows = $fetch_result['Total'];

if(empty($page))
{
$page = 1;
}

$limitvalue = $page * $limit - ($limit);

$query  = "SELECT Ad_Id, `Date`, Type_Job FROM employment ORDER BY `Date` DESC, Ad_Id DESC LIMIT $limitvalue, $limit";
$result = mysql_query($query) or die("Error: " . mysql_error());

if(mysql_num_rows($result) == 0)
{
echo ("There are currently no ads in this category.");
}

while($row = mysql_fetch_array($result))
{
$ad_id = $row['Ad_Id'];
$date = $row['Date'];
$type = $row['Type_Job'];
echo "$ad_id - $date - $type<BR><BR>";
}

if($page != 1)
{
$pageprev = $page--; 
echo("<a href=\"test3.php?page=$pageprev\">Previous ".$limit." Listings - </a>");
}
else
{
echo("Previous ".$limit." Listings - ");
}

$numofpages = $totalrows / $limit;
for($i = 1; $i <= $numofpages; $i++)
{
if($i == $page)
	{
	echo($i." ");
	}
	else
	{
	echo("<a href=\"test3.php?page=$i\">$i</a> ");
	}
}

if(($totalrows % $limit) != 0)
{
if($i == $page)
	{
	echo($i." ");
	}
	else
	{
	echo("<a href=\"test3.php?page=$i\">$i</a> ");
	}
}

if(($totalrows - ($limit * $page)) > 0)
{
$pagenext = $page++;
echo(" - <a href=\"test3.php?page=$pagenext\">Next ".$limit." Listings</a>");
}
else
{
echo(" - Next ".$limit." Listings");
}

mysql_close($dbh);

?>

Link to comment
Share on other sites
flowingwindrider Member

flowingwindrider

Posted
  • Author
Posted

That variable existed in the first script that I was working on, the one that I gave up as being a lost cause. Please have a look at this script instead, it is my current attempt (and I think I'm pretty close).

 

<?php

@$dbh = mysql_connect("localhost", "user", "password") or die ("Error Connecting: " . mysql_error());
@mysql_select_db("bransone_classifieds") or die ("Can't find database: " . mysql_error());
$limit = 10;
$query_count = "SELECT COUNT(Ad_Id) AS Total FROM employment";
$result_count = mysql_query($query_count) or die ("Error in query" . mysql_error());
$fetch_result  = mysql_fetch_assoc($result_count) or die ("Error in query" . mysql_error());
$totalrows = $fetch_result['Total'];

if(empty($page))
{
$page = 1;
}

$limitvalue = $page * $limit - ($limit);

$query  = "SELECT Ad_Id, `Date`, Type_Job FROM employment ORDER BY `Date` DESC, Ad_Id DESC LIMIT $limitvalue, $limit";
$result = mysql_query($query) or die("Error: " . mysql_error());

if(mysql_num_rows($result) == 0)
{
echo ("There are currently no ads in this category.");
}

while($row = mysql_fetch_array($result))
{
$ad_id = $row['Ad_Id'];
$date = $row['Date'];
$type = $row['Type_Job'];
echo "$ad_id - $date - $type<BR><BR>";
}

if($page != 1)
{
$pageprev = $page--; 
echo("<a href=\"test3.php?page=$pageprev\">Previous ".$limit." Listings - </a>");
}
else
{
echo("Previous ".$limit." Listings - ");
}

$numofpages = $totalrows / $limit;
for($i = 1; $i <= $numofpages; $i++)
{
if($i == $page)
	{
	echo($i." ");
	}
	else
	{
	echo("<a href=\"test3.php?page=$i\">$i</a> ");
	}
}

if(($totalrows % $limit) != 0)
{
if($i == $page)
	{
	echo($i." ");
	}
	else
	{
	echo("<a href=\"test3.php?page=$i\">$i</a> ");
	}
}

if(($totalrows - ($limit * $page)) > 0)
{
$pagenext = $page++;
echo("<a href=\"test3.php?page=$pagenext\">Next ".$limit." Listings</a>");
}
else
{
echo(" - Next ".$limit." Listings");
}

mysql_close($dbh);

?>

Link to comment
Share on other sites
This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
 Share
Go to topic listing
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.