mysql problem with count query in pagination

Question

4.00/5 (1 vote)

See more:

I am fetching my data using GROUP BY but now I added pagination in my code and it require COUNT query to count the number of rows in table.so i have also used GROUP BY in that COUNT query .. but it is not working properly.. can anyone help me out?? here is my code

XML

$query ="SELECT COUNT(*)
         FROM import
         {$WHERE} GROUP BY mobno,telecaller";
$result = mysql_query($query);
$total_records = mysql_result($result,0);
$total_pages = ceil($total_records / $records_per_page);
//Set page number
$pageno = (isset($_GET['pageno'])) ? intval($_GET['pageno']) : 1;
$pageno = min(max($pageno, 1), $total_pages);
$LIMITSTART = ($pageno - 1) * $records_per_page;
$query = "SELECT date, mobno, city, state, type, telecaller
          FROM import
          {$WHERE}
          GROUP BY mobno,telecaller
          ORDER BY date DESC
          LIMIT  $LIMITSTART, $records_per_page";
$result = mysql_query($query);
print"<div id='print'>";
echo"<center>List of Mobile Numbers for Telecaller <font color='#FF00FF'><U> $_POST[select]\r</U> </font> <center> <br/>";
echo "<table border='1' cellspacing='1' cellpadding='6'>
<tr bgcolor='#82CAFF'>
<th>Sr.No</th>
<th>Date</th>
<th>Mobile No</th>
<th>City</th>
<th>State</th>
<th>Type</th>
<th>CIExe.</th>
</tr>";
//if(isset($_POST['submit']))
//{
$srno1=0;
while($row=mysql_fetch_array($result))
{
    $srno1=$srno1+1;
echo "<tr>";
echo "<td>". $srno1. "</td>";
echo "<td>" . $row['date'] . "</td>";
echo "<td>".  $row['mobno'] ."</td>";
echo "<td>" . $row['city'] . "</td>";
echo "<td>" . $row['state'] . "</td>";
echo "<td>" . $row['type'] . "</td>";
echo "<td>" . $row['telecaller'] . "</td>";
echo "</tr>";
}
echo"</table>";

THANKS IN ADVANCE..

Posted 2-Oct-12 20:05pm

project virus

Add a Solution

Comments

Mehdi Gholam 3-Oct-12 2:17am

"not working properly" is not a question that can be answered meaningfully.

project virus 3-Oct-12 2:34am

not working properly means.. when I use above count query without GROUP BY it shows/display all record as main query... but last some pages are blank .. which not perfect.
but when I use COUNT query using GROUP BY it display only first page not other pages..

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

SquidSK · Answer 1 · 2012-10-18T05:17:00

Change the query to:

$query ="SELECT mobno, telecaller, COUNT(*)<br />
         FROM import<br />
         {$WHERE} GROUP BY mobno,telecaller";

The reason being that the GROUP BY (and HAVING) clause can only be applied to columns selected for output.

From the MySQL online reference at http://dev.mysql.com/doc/refman/5.0/en/select.html[^]:

Columns selected for output can be referred to in ORDER BY and GROUP BY clauses using column names, column aliases, or column positions. Column positions are integers and begin with 1: