Page 1 of 1

PHP Pagination not working for MySQL Join Queries

Posted: Thu Mar 08, 2012 4:37 am
by Manoj Kumar
Hi all . . I've a page function and the pagination code which works fine for normal sql queries such as "select * from tablename" but when i use the join queries, my pagination is not working at all . . I've attached my pagefunction and also the code which am using as of now . . I'm not able to fix the issue . . Any help would be greatly appreciated . . Thanks in advance for the help . .

pagefunction.php :

Code: Select all

<?php
/**************************************start page nation function ************************************************/
function Pagenation($primaryid,$query,$condition,$max_record,$pagelinks)
{
/**************************************************************************************/
if (isset($_GET["page"])) { $page = $_GET["page"]; } else { $page=1; };
$DeviQuery=explode("from",$query);

$SubDeviQuery=explode("Order",$DeviQuery[1]);

if(empty($SubDeviQuery[1]))
{
$SubDeviQuery=explode("limit",$SubDeviQuery[0]);
}

$condition=getcondition();
//echo $condition;

/**************************************************************************************/
$sql ="SELECT COUNT($primaryid) From ".$SubDeviQuery[0];

// echo $sql;


// $condition=$_SERVER['QUERY_STRING'];

// echo $query;

//echo $newcondition;
$rs_result=mysql_query($sql);
$row =mysql_fetch_row($rs_result);
$total_records = $row[0];
$total_pages = ceil($total_records / $max_record);
if($page < $pagelinks){ $plimit=$pagelinks; $start=1;}else{ $plimit=$page+5; $start=$page-5;}

/**************************************************************************************/
if($page >1 )
{
$pre=$page-1;
echo "&nbsp;&nbsp;<a class=page href=".$_SERVER['PHP_SELF']."?page=$pre&$condition><b>Previous</b></a>&nbsp;";
}
/*if($page >1 )
{
$pre=$page-1;
echo "&nbsp;&nbsp;<a class=page href=".$_SERVER['PHP_SELF']."?page=$pre&$condition></a>&nbsp;";
}*/
/**************************************************************************************/
for ($i=1; $i<=$total_pages; $i++)
{
if($page==$i) { echo "&nbsp;&nbsp;<b>$i</b>";}
else{
if($i==$total_pages)
{
echo "&nbsp;&nbsp;<a class=page href=".$_SERVER['PHP_SELF']."?page=$i&$condition></a>&nbsp;";
}
else{
if($i >= $start)
{
if( $i <= $plimit ){ echo "&nbsp;&nbsp;<a class=page href=".$_SERVER['PHP_SELF']."?page=$i&$condition><b>$i</b></a>&nbsp;"; }
}
}
}
}

/**************************************************************************************/
if($page < $total_pages )
{
$next=$page+1;
echo "&nbsp;<a class=page href=".$_SERVER['PHP_SELF']."?page=$next&$condition><b>Next</b></a>&nbsp;";
}

}

/*************************************End Function Pagenation*************************************************/


/***************************************start function display data function***********************************************/

function Datadisplay($table_name,$Primaryid,$start_from,$max_record,$Pcondition,$sortorder,$pagelinks)
{
//Note: Dont change as text From ,Order
$query="select * From $table_name ".$Pcondition." Order by $Primaryid $sortorder limit $start_from,$max_record";
//$Exqutequery=mysql_query($query) or die("ExecuteError".mysql_error());
return $query;
}

function getcondition()
{
$qstring=$_SERVER['QUERY_STRING'];
$Execon=explode("&",$qstring);
if(isset($_GET['page'])) { $sp=1; }else{ $sp=0;}
for($p=$sp; $p < count($Execon); $p++)
{
$condtion= $Execon[$p]."&";

}
return $condtion;
}


?>


pagination with normal query:

<?php
include('header.php');
error_reporting(0);
include('config.php');
include('pagefunction.php');
/**************************************************************************************/

$table_name="category_info"; //Mysql Table name
$Primaryid="category_id"; //primary idfrom data base from countin number recored.
$max_record=30; //Number record display per page
$sortorder= "Asc"; //Order by Asc or Desc
$pagelinks=10; // How many page will display intially
$Pcondition=""; //put your condition WHERE `status` =1
//$newcondition="Question=Display&&tblid=$tblid";

/**************************************************************************************/
//general code
if (isset($_GET["page"])) { $page = $_GET["page"]; } else { $page=1; };
$start_from = ($page-1) *$max_record;
$recno=$start_from;
/************************************************************************************/
if(isset($_GET['action'])&&($_GET['action']=="delete"))
{
$did =$_GET['city_id'];
$delsql="delete from category_info where category_id='$did'";
$delquery=mysql_query($delsql) or die("Category Information delete Error".mysql_error());
if($delquery) { echo "<script>window.location.href='manage_category.php';</script>";}
//@header("location:index.php?userdisplay")

}
?>

<!--<script type="text/javascript" src="js/validation.js"></script>-->
<tr>
<td width="216" height="450" valign="top" bgcolor="#688000"><table width="186" border="0" align="center" cellpadding="0" cellspacing="0">
<tr>
<td height="5"></td>
</tr>
<tr>
<td background="images/d_menu_bg.gif"><table width="96%" border="0" align="center" cellpadding="0" cellspacing="0">
<tr>
<td bgcolor="#FFFFFF"><? include("menu.php");?></td>
</tr>
</table></td>
</tr>
<tr>
<td height="2">&nbsp;</td>
</tr>
</table></td>
<td width="1" valign="top" ></td>
<td width="959" valign="top" ><table width="100%" border="0" cellspacing="3" cellpadding="3">
<tr>
<td class='content'><b><a href="home.php" style="text-decoration:none">Home</a></b>&nbsp;&nbsp;>>&nbsp;&nbsp;<b> Paid Doctors</b><br>
<hr size='1' color="#688000"></td>
</tr>

<tr>
<td align="center" class="content"><!-------------------------------------------------------------------->

<table width="100%" border="0" align="center" cellpadding="0" cellspacing="0">
<tr>
<td align="right" height="25"><a href='#' class='menu'><b>Toatl Category:
<?php
$sql1=mysql_query("select * from category_info") or die("select error".mysql_error());
echo $viewresult = mysql_num_rows($sql1);?></b></a></td>
</tr>
<tr>
<td align="left" height="25"><a href='add_category.php' class='menu'><b>Add Category
</b></a></td>
</tr>
</table>


<table width="100%" border="0" cellpadding="0" cellspacing="0">
<thead>
<tr bgcolor="#cdcf9d" height='22'>
<tr bgcolor="#cdcf9d" height='22'>
<td width="" class='heading1'>&nbsp;<b>Category Id</b></td>
<td width="" class='heading1'>&nbsp;<b>Category Name</b></td>
<td class='heading1' width="" align="center"><b>Edit</b></td>
<td class='heading1' width="" align="center"><b>Delete</b></td>

</tr>
</thead>
<tbody>
<table width="100%" border="1" bgcolor="#cdcf9d" align="center" cellpadding="1" cellspacing="1" >
<tr align="center">


<?php


$searchsql="select * from category_info ";


$pagequery=explode("where",$searchsql);
//print($pagequery[1])."<br><br>";

$rep= str_replace("%","<-->",$pagequery[1]);
//echo $rep.'<br><br>';

/******************************************************************************/
$searchsql .=" limit $start_from,$max_record";

$viewuser=mysql_query($searchsql) or die("Result View Error".mysql_error());
$check=mysql_num_rows($viewuser);
if($check==0)
{ $Msg="No Records Found!";
echo "<table height='300' widht='100%'><tr><td style='color:#FF0000' align='center'>". $Msg ."</td></tr></table>";
}else{


$recno=$start_from;
//$recno=0;

while($row=mysql_fetch_array($viewuser))
{
$recno++;
$category_id=($row['category_id']);
$categoryname=($row['categoryname']);






echo "
<tr>
<td><small>$category_id</small></td>
<td><small>$categoryname</small></td>









<td align='center'> <a href=\"edit_category.php?category_id=$category_id\" class='ico edit' \"><img src='images/edit.png' alt='' title='' border='0' /></a>

</td>
<td align='center'>
<a href=\"manage_category.php?action=delete&category_id=$category_id\"onClick=if(confirm('Are You Confirm to Delete?')) { return true; } else { return false; }
' \"><img src='images/delete1.gif' alt='' title='' border='0' /></a> </td>



</tr>";


}
}

?>





</table>
<br/><br/>
<div class="pagination">
<a href="#"><b>First Page</b></a>
<?php echo Pagenation($Primaryid,$searchsql,$Pcondition,$max_record,$pagelinks);?>
</div>



</td>
</tr>

<tr>
<td>&nbsp;</td>
</tr>
</table></td>

<td width="15" valign="top" bgcolor=""></td>
</tr>

<tr bgcolor="">
<td height="25" colspan="4"><? include ("footer.php");?></td>
</tr>
</table>
</td>
</tr>
</table>
</td>
</tr>
<tr>
<td>&nbsp;</td>
</tr>
</table>

The above code is working fine and it fulfills my needs . .
But the following one is not working at all . .

<?php
error_reporting(0);
include('header.php');
include('config.php');
include('pagefunction.php');
/**************************************************************************************/

$table_name="city_info"; //Mysql Table name
$Primaryid="city_id"; //primary idfrom data base from countin number recored.
$max_record=3; //Number record display per page
$sortorder= "Asc"; //Order by Asc or Desc
$pagelinks=10; // How many page will display intially
$Pcondition=""; //put your condition WHERE `status` =1
//$newcondition="Question=Display&&tblid=$tblid";

/**************************************************************************************/
//general code
if (isset($_GET["page"])) { $page = $_GET["page"]; } else { $page=1; };
$start_from = ($page-1) *$max_record;
$recno=$start_from;
/************************************************************************************/
if(isset($_GET['action'])&&($_GET['action']=="delete"))
{
$did =$_GET['city_id'];
$delsql="delete from city_info where city_id='$did'";
$delquery=mysql_query($delsql) or die("City Information delete Error".mysql_error());
if($delquery) { echo "<script>window.location.href='manage_city.php';</script>";}
//@header("location:index.php?userdisplay")

}

?>


<tr>
<td width="216" height="450" valign="top" bgcolor="#688000"><table width="186" border="0" align="center" cellpadding="0" cellspacing="0">
<tr>
<td height="5"></td>
</tr>
<tr>
<td background="images/d_menu_bg.gif"><table width="96%" border="0" align="center" cellpadding="0" cellspacing="0">
<tr>
<td bgcolor="#FFFFFF"><? include("menu.php");?></td>
</tr>
</table></td>
</tr>
<tr>
<td height="2">&nbsp;</td>
</tr>
</table></td>
<td width="1" valign="top" ></td>
<td width="959" valign="top" ><table width="100%" border="0" cellspacing="3" cellpadding="3">
<tr>
<td class='content'><b><a href="home.php" style="text-decoration:none">Home</a></b>&nbsp;&nbsp;>>&nbsp;&nbsp;<b>Manage City</b><br>
<hr size='1' color="#688000"></td>
</tr>
<tr>
<td align="center" class="content"><!-------------------------------------------------------------------->
<form name="frm" method="post" action="">

<table width="620" border="0" align="center" cellpadding="0" cellspacing="0">
<tr>
<td align="right" height="25"><a href='add_city.php' class='menu'><b>Add City</b></a></td>
</tr>
<tr>
<td align="left" height="25"><a href='#' class='menu'><b>Total City:
<?
$query=mysql_query("select * from city_info")or die("select error".mysql_error());
echo $viewresult=mysql_num_rows($query);
?></b></a></td>
</tr>
</table>

<table width="100%" bgcolor="000000" cellpadding="1" cellspacing="1" border="0" align="center">
<tr bgcolor="#cdcf9d" height='22'>
<td width="10" class='heading1'>&nbsp;<b>Id</b></td>
<td width="215" class='heading1'>&nbsp;<b>City Name</b></td>
<td width="215" class='heading1'>&nbsp;<b>State Name</b></td>
<td class='heading1' width='101' align="center"><b>Manage</b></td>
</tr>


<!-- <tr bgcolor="#FFFFFF">
<td class="content" height="25" colspan="4" align="center"><font color="#FF0000">--- No Records ---</font></td>
</tr>-->
</table>

<table width="100%" border="1" bgcolor="#cdcf9d" align="center" cellpadding="1" cellspacing="1" >
<tr align="center">

<?php


$searchsql="SELECT c.city_id,c.cityname, s.statename, s.state_id
FROM city_info c, state_info s
WHERE c.state_id = s.state_id";





$pagequery=explode("where",$searchsql);
//print($pagequery[1])."<br><br>";

$rep= str_replace("%","<-->",$pagequery[1]);
//echo $rep.'<br><br>';

/******************************************************************************/
$searchsql .=" limit $start_from,$max_record";

$viewuser=mysql_query($searchsql) or die("Result View Error".mysql_error());

$check=mysql_num_rows($viewuser);

if($check==0)
{ $Msg="No Records Found!";
echo "<table height='300' widht='100%'><tr><td style='color:#FF0000' align='center'>". $Msg ."</td></tr></table>";
}else{


$recno=$start_from;



while($row=mysql_fetch_array($viewuser))
{
$recno++;
$city_id=($row['city_id']);
$cityname=($row['cityname']);
$statename=($row['statename']);





echo "
<tr>
<td><small>$city_id</small></td>
<td><small>$cityname</small></td>
<td><small>$statename</small></td>








<td align='center'> <a href=\"edit_city.php?city_id=$city_id\" class='ico edit' \"><img src='images/edit.png' alt='' title='' border='0' /></a>

</td>
<td align='center'>
<a href=\"manage_city.php?action=delete&city_id=$city_id\"onClick=if(confirm('Are You Confirm to Delete?')) { return true; } else { return false; }
' \"><img src='images/delete1.gif' alt='' title='' border='0' /></a> </td>



</tr>";


}
}

?>





</table><br/><br/>

<div class="pagination">
<a href="manage_city.php" style="text-decoration:none"><b>First Page</b></a>
<?php echo Pagenation($Primaryid,$searchsql,$Pcondition,$max_record,$pagelinks);?>
</div>





</td>
</tr>

<tr>
<td>&nbsp;</td>
</tr>
</table></td>
<td width="15" valign="top" bgcolor=""></td>
</tr>

<tr bgcolor="">
<td height="25" colspan="4"><? include ("footer.php");?></td>
</tr>
</table>
</td>
</tr>
</table>
</td>
</tr>
<tr>
<td>&nbsp;</td>
</tr>
</table>
Please help me out guys . . Am struck with this for almost one day . . .

Re: PHP Pagination not working for MySQL Join Queries

Posted: Thu Mar 08, 2012 4:03 pm
by Christopher
From experience I know that posts with that much code will not get many responses. Could you focus the question down to the specific query and code where you think the problem is please

Re: PHP Pagination not working for MySQL Join Queries

Posted: Wed Mar 14, 2012 2:36 am
by Manoj Kumar
Pagination is working properly for the following query:

$searchsql="select * from category_info ";

But when i use the following query, the pagination is not working at all.

$searchsql="SELECT c.city_id,c.cityname, s.statename, s.state_id
FROM city_info c, state_info s
WHERE c.state_id = s.state_id";

I don't know where i went wrong . Not able to figure out the problem too. Any help would be greatly appreciated. Thanks in advance.

Re: PHP Pagination not working for MySQL Join Queries

Posted: Wed Mar 14, 2012 9:31 am
by JoeCommodore
Not reading the long source, here is my suggestion - might be the pagination routine uses it's own WHERE clause...

how about try this for your query:
$searchsql="SELECT c.city_id,c.cityname, s.statename, s.state_id
FROM city_info c
LEFT JOIN state_info s ON c.state_id = s.state_id"

that does not use a WHERE clause, so it might go through.