1. $q="Select SQL_CALC_FOUND_ROWS * from post_resume where ind_type='$industype' and career='$careerlevel' limit $cps, $rpp";

2.$q="Select SQL_CALC_FOUND_ROWS * from post_resume limit $cps, $rpp";

hi
i am doing paging in php.the first query is not working.the second one is not working........please do tell me the error.

<html>
<head>
<title>Record Set Paging with PHP</title>
</head>

<body>
    <?php
    @ $rpp;        //Records Per Page
    @ $cps;        //Current Page Starting row number
    @ $lps;        //Last Page Starting row number
    @ $a;        //will be used to print the starting row number that is shown in the page
    @ $b;         //will be used to print the ending row number that is shown in the page
$industype=$_REQUEST['industype'];
$careerlevel=$_REQUEST['careerlevel'];
    /////////////////////////////////////////////////////////////////////////////////
    //Database connection
    /////////////////////////////////////////////////////////////////////////////////
$hostname = "localhost";
$username = "my_username";
$password = "my_password";
$dbid = "careergl_sample";
$link=mysql_connect($hostname, $username, $password);
mysql_select_db($dbid) or die("unable to connect");
    /////////////////////////////////////////////////////////////////////////////////

    /////////////////////////////////////////////////////////////////////////////////
    //Following IF Statement is used to make sure when the page is loaded for the
    //first time, Current Page's Starting row number is 0, i.e. 1st row from the
    //table is being printed. It will change as the user will click on next.
    /////////////////////////////////////////////////////////////////////////////////   
    if(empty($_GET["cps"]))
    {
        $cps = "0";
    }
    else
    {
        $cps = $_GET["cps"];
    }
    /////////////////////////////////////////////////////////////////////////////////

    $a = $cps+1;

    $rpp = "10";

    $lps = $cps - $rpp; //Calculating the starting row number for previous page

    /////////////////////////////////////////////////////////////////////////////////
    //Following IF Statement is used to make sure whether a link to Previous page is
    //needed or not. If the user is viewing the first set of data then the link will
    //be disabled, if on the next set then it will carry the $lps in its link and
    //enable the link
    if ($cps <> 0)
    {
        $prv =  "<a href='paging.php?cps=$lps'>Previous</a>";
    }
    else   
    {
        $prv =  "<font color='cccccc'>Previous</font>";
    }
    /////////////////////////////////////////////////////////////////////////////////

    /////////////////////////////////////////////////////////////////////////////////
    //Following SQL Statement uses SQL_CALC_FOUND_ROWS function to calculate total
    //number of rows found by the query excluding the limit function added at the
    //end of the SQL statement. This is followed by second query with FOUND_ROWS()
    //function which actually gives out the number of rows found.
    /////////////////////////////////////////////////////////////////////////////////
 //   $q="Select SQL_CALC_FOUND_ROWS * from post_resume where ind_type='$industype' and career='$careerlevel' limit $cps, $rpp";
$q="Select SQL_CALC_FOUND_ROWS * from post_resume where ind_type='$industype' and career='$careerlevel' limit $cps, $rpp";
    $rs=mysql_query($q) or die(mysql_error());
    $nr = mysql_num_rows($rs); //Number of rows found with LIMIT in action

    $q0="Select FOUND_ROWS()";
    $rs0=mysql_query($q0) or die(mysql_error());
    $row0=mysql_fetch_array($rs0);
    $nr0 = $row0["FOUND_ROWS()"]; //Number of rows found without LIMIT in action

    /////////////////////////////////////////////////////////////////////////////////
    //Following IF Statement is used to determine whether the user has reached the
    //last page of the records. For example, if we have 27 rows to print and we show
    //10 rows per page, then on the third and the last page it will show seven rows
    //and will say at the top that SHOWING RECORDS FROM 21 to 27. If the following
    //validator is not used then it shows SHOWING RECORDS FROM 21 to 30.
    /////////////////////////////////////////////////////////////////////////////////   
    if (($nr0 < 10) || ($nr < 10))
    {
           $b = $nr0;
    }
    else
    {
        $b = ($cps) + $rpp;
    }
    /////////////////////////////////////////////////////////////////////////////////

    ?>
<br>
<table border="0" cellpadding="4" cellspacing="1" width="90%" align="center">
<tr><td><b><font face="verdana" size=2>Records For  </b></font><b><font face="verdana" size=2 color="#9999CC"><? echo $industype;?>&nbsp;&nbsp;<? echo "and";?>&nbsp;&nbsp;<?  echo $careerlevel;?></font></b></td></tr>
  <tr><td align=left colspan="2"><b><font face="verdana" size=1 color="#9999CC"><? echo "$nr0 Records Found"; ?></font></b></td></tr>
  <tr><td align='left' colspan="2"><b><font face="verdana" size=1 color="#9999CC"><? echo "Showing Records from $a to $b"; ?></font></b></td></tr>
</tr>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;


    <?
// echo "<table>";
    while ($row=mysql_fetch_array($rs))
    {
        /////////////////////////////////////////////////////////////////////////////////
        //This is used to show the serial number on the page as well as to count it up
        //so that we can get the next page's starting row number when it exits the while
        //loop after fullfilling the above SQL criteria.
        /////////////////////////////////////////////////////////////////////////////////
        $cps = $cps +1;

        //$val=$row["resumetitle"];
        //echo "<tr><td align='center'><font face=verdana>$cps</font></td><td align='center'><font fave=verdana>$val</font></td></tr>"; 


 echo "<tr><td><font face='verdana' size=1><strong>Resume Title : </strong></font><a href='detailjob.php?id=".$row[0]."'>".$row[2]."</td><td><font face='verdana' size=1><strong>Owner: </strong></font>".$row[2]."</td></tr>";
echo "<tr><td><font face='verdana' size=1><strong>Industry Type: </strong></font>".$row[4]."</td><td><font face='verdana' size=1><strong>Career Level:  </strong></font>".$row[5]."</td></tr>";

echo "<tr><td bgcolor='#008080'></td><td bgcolor='#008080'></td></tr>";
  }
//echo "</table>";
 
   
    echo "<tr><td align='right' colspan=2>$prv";

    /////////////////////////////////////////////////////////////////////////////////
    //Following IF Statement is used to determine whether the Next link will be
    //enabled or disabled. If the user has reached the last page of the record, then
    //the Next link will be disabled.
    /////////////////////////////////////////////////////////////////////////////////
    if ($cps == $nr0)
    {     
        echo "  |  <font color='CCCCCC'>Next</font>";
    }
    else
    {
        if ($nr0 > 5)
        {
            echo "  |  <a href='paging.php?cps=$cps&lps=$lps'>Next</a>";
        }
    }
    /////////////////////////////////////////////////////////////////////////////////
    ?>
</td>
</tr>
</table>
</body>
</html>

sorry....my first query is not working,the second is working.but i want something like the first one.........

It may not be a good idea to post the password from your database in a public forum. I'll ask a mod to remove it.

$industype=$_REQUEST['industype'];
$careerlevel=$_REQUEST['careerlevel'];

The one thing I can think of is that the above statemants failes. Could you check if $industype & $carreerlevel get a value?

die($careerlevel);

Niek

ya i checked tat...it is getting the values.the above query is also working.here i am using paging concept in php.paging concept is working when the sql query does not contain the where condition.when i add where condition to my sql query it is not working.........so i want to know whether the sql is wrong or some wrong with the code.thanks for ur reply

print out the query, execute it in mysql or phpmyadmin. I think $cps and $lps are not passed correctly OR the values of $cps and $lps doesn't return anything when you limit.

<html>
<head>
</head>


<body>
<?php
@ $rpp;        //Records Per Page
@ $cps;        //Current Page Starting row number
@ $lps;        //Last Page Starting row number
@ $a;        //will be used to print the starting row number that is shown in the page
@ $b;         //will be used to print the ending row number that is shown in the page
$industype=$_REQUEST;
$careerlevel=$_REQUEST;
/////////////////////////////////////////////////////////////////////////////////
//Database connection
/////////////////////////////////////////////////////////////////////////////////
$hostname = "localhost";
$username = "";
$password = "";
$dbid = "";
$link=mysql_connect($hostname, $username, $password);
mysql_select_db($dbid) or die("unable to connect");
/////////////////////////////////////////////////////////////////////////////////


/////////////////////////////////////////////////////////////////////////////////
//Following IF Statement is used to make sure when the page is loaded for the
//first time, Current Page's Starting row number is 0, i.e. 1st row from the
//table is being printed. It will change as the user will click on next.
/////////////////////////////////////////////////////////////////////////////////
if(empty($_GET["cps"]))
{
$cps = "0";
}
else
{
$cps = $_GET["cps"];
}
/////////////////////////////////////////////////////////////////////////////////


$a = $cps+1;


$rpp = "10";


$lps = $cps - $rpp; //Calculating the starting row number for previous page


/////////////////////////////////////////////////////////////////////////////////
//Following IF Statement is used to make sure whether a link to Previous page is
//needed or not. If the user is viewing the first set of data then the link will
//be disabled, if on the next set then it will carry the $lps in its link and
//enable the link
if ($cps <> 0)
{
$prv =  "<a href='paging.php?cps=$lps"."&industype=".$_REQUEST."&careerlevel=".$_REQUEST."'>Previous</a>";
}
else
{
$prv =  "<font color='cccccc'>Previous</font>";
}
/////////////////////////////////////////////////////////////////////////////////


/////////////////////////////////////////////////////////////////////////////////
//Following SQL Statement uses SQL_CALC_FOUND_ROWS function to calculate total
//number of rows found by the query excluding the limit function added at the
//end of the SQL statement. This is followed by second query with FOUND_ROWS()
//function which actually gives out the number of rows found.
/////////////////////////////////////////////////////////////////////////////////


mysql_query("DROP TABLE TEMPORARY IF EXISTS Mytemp");
mysql_query("CREATE TEMPORARY TABLE Mytemp SELECT * from post_resume WHERE ind_type='".$industype."' and career='".$careerlevel."'");


$q="Select SQL_CALC_FOUND_ROWS * from Mytemp limit $cps, $rpp ";
$rs=mysql_query($q) or die(mysql_error());
$nr = mysql_num_rows($rs); //Number of rows found with LIMIT in actiong r


$q0="Select FOUND_ROWS()";
$rs0=mysql_query($q0) or die(mysql_error());
$row0=mysql_fetch_array($rs0);
$nr0 = $row0["FOUND_ROWS()"]; //Number of rows found without LIMIT in action


/////////////////////////////////////////////////////////////////////////////////
//Following IF Statement is used to determine whether the user has reached the
//last page of the records. For example, if we have 27 rows to print and we show
//10 rows per page, then on the third and the last page it will show seven rows
//and will say at the top that SHOWING RECORDS FROM 21 to 27. If the following
//validator is not used then it shows SHOWING RECORDS FROM 21 to 30.
/////////////////////////////////////////////////////////////////////////////////
if (($nr0 < 10) || ($nr < 10))
{
$b = $nr0;
}
else
{
$b = ($cps) + $rpp;
}
/////////////////////////////////////////////////////////////////////////////////


?>
<br>
<table border="0" cellpadding="4" cellspacing="1" width="90%" align="center">
<tr><td><b><font face="verdana" size=2>Records For  </b></font><b><font face="verdana" size=2 color="#9999CC"><? echo $industype;?>&nbsp;&nbsp;<? echo "and";?>&nbsp;&nbsp;<?  echo $careerlevel;?></font></b></td></tr>
<tr><td align=left colspan="2"><b><font face="verdana" size=1 color="#9999CC"><? echo "$nr0 Records Found"; ?></font></b></td></tr>
<tr><td align='left' colspan="2"><b><font face="verdana" size=1 color="#9999CC"><? echo "Showing Records from $a to $b"; ?></font></b></td></tr>
</tr>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;



<?
// echo "<table>";
while ($row=mysql_fetch_array($rs))
{
/////////////////////////////////////////////////////////////////////////////////
//This is used to show the serial number on the page as well as to count it up
//so that we can get the next page's starting row number when it exits the while
//loop after fullfilling the above SQL criteria.
/////////////////////////////////////////////////////////////////////////////////
$cps = $cps +1;


//$val=$row["resumetitle"];
//echo "<tr><td align='center'><font face=verdana>$cps</font></td><td align='center'><font fave=verdana>$val</font></td></tr>";



echo "<tr><td><font face='verdana' size=1><strong>Resume Title : </strong></font><a href='detailjob.php?id=".$row[0]."'>".$row[2]."</td><td><font face='verdana' size=1><strong>Owner: </strong></font>".$row[2]."</td></tr>";
echo "<tr><td><font face='verdana' size=1><strong>Industry Type: </strong></font>".$row[4]."</td><td><font face='verdana' size=1><strong>Career Level:  </strong></font>".$row[5]."</td></tr>";


echo "<tr><td bgcolor='#008080'></td><td bgcolor='#008080'></td></tr>";
}
//echo "</table>";



echo "<tr><td align='right' colspan=2>$prv";


/////////////////////////////////////////////////////////////////////////////////
//Following IF Statement is used to determine whether the Next link will be
//enabled or disabled. If the user has reached the last page of the record, then
//the Next link will be disabled.
/////////////////////////////////////////////////////////////////////////////////
if ($cps == $nr0)
{
echo "  |  <font color='CCCCCC'>Next</font>";
}
else
{
if ($nr0 > 5)
{
echo "  |  <a href='paging.php?cps=$cps&lps=$lps&industype=".$_REQUEST."&careerlevel=".$_REQUEST."'>Next</a>";
}
}
/////////////////////////////////////////////////////////////////////////////////
?>
</td>
</tr>
</table>
</body>
</html>

the above code is working.thanks for the reply

Edited 3 Years Ago by happygeek: fixed formatting

This question has already been answered. Start a new discussion instead.