[SOLVED] Pagination

[flowingwindrider]

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.

[teng84]

$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

[flowingwindrider]

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.

[teng84]

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

 

[teng84]

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

[flowingwindrider]

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);

?>

[teng84]

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

[flowingwindrider]

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

[teng84]

dude you have an error on your sql check it maybe print the query and run it on your db

[flowingwindrider]

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);

?>

[lightningstrike]

Umm if you don't have register_globals on, then shouldn't you use

 

$page = $_GET["page"];

[flowingwindrider]

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);

?>