Getting database results and spreading over pages issue. HELP !! :(

[scotchegg78]

Guys

 

Having an issue with handling the query data and spreading over pages.

 

If i have a SELECT query run with LIMIT $offset, $rowsPerPage but check the potential number of rows in the table to discover the pages vs limits, then i get pages for all items in the table ignoring the select conditions results. Or if i use the total rows from the select its not true as its cut by the LIMIT?

 

You with me???

 

So say I have 100 table enties

A select condtion gets 50 of these

and I limit it by 20 at a time per page.

 

At the moment I still get 5 pages as 20 limit goes into 100 5 times, but it should be against the 50.

 

I could run the select twice, with and without the LIMIT to discover the total rows, but running a sql statement twice seems a bad idea?

 

any suggestions?

 

thanks

 

 

[Ken2k7]

I'm so not following that. So you want it to show 50 at a time and not 20?

[Jessica]

You'll have to do two select statements, yes.

One to get the number: Use COUNT()

And then one to get the 50 rows you want.

[scotchegg78]

@ KEN

 

ok sorry I will try again

 

I have a search SELECT which gets 100 rows that match. Perfect.

now this is too big for one page, so instead I add sql LIMIT etc. ok fine so far.

 

Now I have page 1 with the first 20 selection that match my query, and if i click page 2 it will go away and get the following 20 etc.

 

However the issue is to determine the page counts its finding the number of total table rows, not the query rows (which would always be 20 as its limited?)

any better?!

 

Jesi , surely 2 select statments can not be the correct way forward?! Seems madness for the overhead? If we are talking a table of 40000 rows?

Is there not away to get the mysql select size it would of been without the limit, or someway in php to be clever?

 

thanks guys/girls.

[Jessica]

Did you lookup COUNT to see what it was before saying it won't work?

[scotchegg78]

Sorry I did not say it would not work, I said I did not think it was the way forward if it works like I think it does.

I have two pages, search page which also shows results and the search script page..

 

page 1 companydata_search.php (its in index.php and called as an include...)

search / form page with code to handle multiple pages...

 

$maxRows_qryPostCodes = 20;
$pageNum_qryPostCodes = 0;
if (isset($_GET['pageNum_qryPostCodes'])) {
  $pageNum_qryPostCodes = $_GET['pageNum_qryPostCodes'];
}
$startRow_qryPostCodes = $pageNum_qryPostCodes * $maxRows_qryPostCodes;
$nav  = '';

for($page = 1; $page <= $maxPage; $page++)
{
   if ($page == $pageNum)
   {
      $nav .= " $page "; // no need to create a link to current page
   }
   else
   {
      $nav .= " <a href=\"$self?page=$page\">$page</a> ";
   } 
} 

if ($pageNum > 1)
{
   $page  = $pageNum - 1;
   $prev  = " <a href=\"$self?page=$page\">[Prev]</a> ";

   $first = " <a href=\"$self?page=1\">[First Page]</a> ";
} 
else
{
   $prev  = ' '; // we're on page one, don't print previous link
   $first = ' '; // nor the first page link
}

$self = $_SERVER['PHP_SELF'];

if ($pageNum < $maxPage)
{
   $page = $pageNum + 1;
   $next = " <a href=\"$self?page=$page\">[Next]</a> ";

   $last = " <a href=\"$self?page=$maxPage\">[Last Page]</a> ";
} 
else
{
   $next = ' '; // we're on the last page, don't print next link
   $last = ' '; // nor the last page link
}

// print the navigation link
//echo $first . $prev . $nav . $next . $last;
echo $first . $prev . 
" Showing page $pageNum of $maxPage pages " . $next . $last;

 

page 2 scripts with sql search..

 

// how many rows to show per page
$rowsPerPage = 20;

// by default we show first page
$pageNum = 1;

// if $_GET['page'] defined, use it as page number
if(isset($_GET['page']))
{
    $pageNum = $_GET['page'];
}

// counting the offset
$offset = ($pageNum - 1) * $rowsPerPage;


//$_POST['expirestart'] = "";

$query_qryData = sprintf("SELECT company.CompanyID, company.CompanyName, company.CompanyExpire, company.CompanyBillExact, company.CompanyBillRange, company.CompanyHandsets, company.NetworkID, company.CompanyMinutes, company.CompanyZoneID, company.Happyness, company.Status FROM company"); 

...


// how many pages we have when using paging?
$maxPage = ceil($numrows/$rowsPerPage);

mysql_select_db($database_tariff, $tariff);
$qryData = mysql_query($query_qryData, $tariff) or die(mysql_error());
$row_qryData = mysql_fetch_assoc($qryData);
$totalRows_qryData = mysql_num_rows($qryData);

// how many rows we have in database
$query   = "SELECT COUNT(CompanyID) AS numrows FROM company";
$result  = mysql_query($query) or die('Error, Row count query failed');
$row     = mysql_fetch_array($result, MYSQL_ASSOC);
$numrows = $row['numrows'];

...
// just code to get me back to search page.
$_GET['p'] = "companydata_search.php"; 
$file = "index.php";
include $file;

 

I am using count already, only its getting the entire table size, and not my sql query size.

 

[Jessica]

"I am using count already, only its getting the entire table size, and not my sql query size."

 

What? What are you trying to get? The number of rows in the table, yes? If you want the number of rows in a specific query, do it.

SELECT COUNT(*) AS numrows FROM company WHERE whatever else you want

 

[Ken2k7]

Uh dude, you can just put them all in one php page with a pagination system

 

But why do you need the number of total table rows? You said yourself all you need is 100, so set up a while loop and a count variable.

 

$count = 0;

while ($row = mysql_fetch_assoc($query) && count < 100){

  // do something

  // increment count

}

 

You mean that? I still don't know what you want exactly.

 

[scotchegg78]

"I am using count already, only its getting the entire table size, and not my sql query size."

 

What? What are you trying to get? The number of rows in the table, yes? If you want the number of rows in a specific query, do it.

SELECT COUNT(*) AS numrows FROM company WHERE whatever else you want

 

 

sorry i am not sure i grasp this, are you saying I can inlcude count in my sql statement i use to get data?

so for example...

 

$query_qryData = sprintf("SELECT company.CompanyID, company.CompanyName, company.CompanyExpire, company.CompanyBillExact, company.CompanyBillRange, company.CompanyHandsets, company.NetworkID, company.CompanyMinutes, company.CompanyZoneID, company.Happyness, company.Status FROM company"); 

 

can become

 

$query_qryData = sprintf("SELECT COUNT(CompanyID) AS numrows , company.CompanyID, company.CompanyName, company.CompanyExpire, company.CompanyBillExact, company.CompanyBillRange, company.CompanyHandsets, company.NetworkID, company.CompanyMinutes, company.CompanyZoneID, company.Happyness, company.Status FROM company"); 

mysql_select_db($database_tariff, $tariff);
$qryData = mysql_query($query_qryData, $tariff) or die(mysql_error());
$row_qryData = mysql_fetch_assoc($qryData);
$totalRows_qryData = mysql_num_rows($qryData);
$numrows = $qryData['numrows']

 

and i still get the query results in $query_qryData aswell as numrows?

 

thanks again

 

 

[schme16]

Not sure if this will help or not but when I need a pagination system this worked for me:

 

 

if($_GET['page']==0 or !is_numeric($_GET['page']))

{

$x = 0; $y = $system['posts_per_page'];

}

 

else

{

$y = ($_GET['page']*20);  $x = $y - 20;}

 

$get_archive = @mysql_query("select * from posts order by id asc Limit $x, $y");

if(@mysql_num_rows($get_archive) != 0)

{

 

while($archive = @mysql_fetch_array($get_archive))

{

$archive['body'] = substr($archive['body'], 0, 100);

 

 

 

}

 

 

$get_num_posts = @mysql_query("select * from posts");

$num_posts = (@mysql_num_rows($get_num_posts) / 20);

 

$num_posts = (float) sprintf("%1.0f", $num_posts);

 

$z = 1;

$i = 0;

if(($_GET['page']-1) > 0){

print'<a href="index.php?content=archives&page='.($_GET['page']-1).'" ><<Prev</a> ';}

else{print'<<Prev';}

 

while($i != $num_posts)

{

print'<a href="index.php?content=archives&page='.$z.'" >'.$z.'</a> ';

$i++;

$z++;

}

if(($_GET['page']+1) <= $num_posts)

{print' <a href="index.php?content=archives&page='.($_GET['page']+1).'" >Next>></a>';}

else

{print'Next>>';}

print'<br /><hr />Pages</center></p>';

 

 

}

[Jessica]

Ken, he wants to know how many pages he'll need.

 

Look.

Do one statement to get the number of rows you would have. Which according to your EXISTING code, is every row in the table. You're not selecting any specific ones, you're selecting all of them.

"SELECT COUNT(CompanyID) AS total_entries FROM company";

total_entries will be how many entries are in the table.

 

Then divide it by however many you want per page and you've got your number of pages.

 

Then select the rows you want.

 

There are also a billion pagination tutorials out there for you to read, some of them on phpfreaks.com already.