I'm having trouble getting the select count to avoid counting rows where "x" field is empty...
This code is used to display page navigation and numbering, see the bottom for the full code.
Here is the code I'm using for my select count:
$query = "SELECT COUNT(id) AS numrows FROM case_plans WHERE lg2d IS NOT NULL";
$result = mysql_query($query) or die('Error, query failed');
$row = mysql_fetch_array($result, MYSQL_ASSOC);
$numrows = $row['numrows'];
At this time there are 5 rows and only 1 with lgpic field not null.
Here is the full code, just for refference:
<?php
include ('db.inc');
// how many rows to show per page
$rowsPerPage = 3;
// 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;
//WHERE lgpic IS NOT NULL, doesn't appear to effect the results..
$query = "SELECT * FROM case_plans LIMIT $offset, $rowsPerPage";
$result = mysql_query($query) or die('Error, query failed');
while ( $row = mysql_fetch_array($result) ) {
$id = $row["id"];
$model = $row["model"];
$category = $row["category"];
$sqft = $row["sqft"];
$title = $row["title"];
$name = $row["name"];
$location = $row["location"];
$about = $row["about"];
$need = $row["need"];
$solution = $row["solution"];
$pdf = $row["pdf"];
$lgpic = $row["lgpic"];
$smpic = $row["smpic"];
$lg2d = $row["lg2d"];
$sm2d = $row["sm2d"];
$lg3d = $row["lg3d"];
$sm3d = $row["sm3d"];
if ($lg2d === NULL):
echo "";
else:
echo("<BR><TABLE class=\"main\" summary=\"Floor Plan -"."$model"." \">".
"<TBODY>".
"<TR>".
"<TD valign=\"top\">".
"<TABLE border=\"0\" width=\"100%\" cellpadding=\"2\" cellspacing=\"1\" summary=\"layout\">".
"<TBODY>".
"<TR bgcolor=\"#1b4f76\">".
"<TD colspan=\"2\">".
"<P class=\"bannerhead\">"."$category"." - "."$model"." - "."$sqft"." SQ.FT.</P>".
"</TD>".
"</TR>".
"<TR bgcolor=\"#ffffff\">".
"<TD align=\"center\">".
"<P>".
"<A onclick=\"MM_openBrWindow('"."$lg3d"."','','scrollbars=yes,width=535,height=400')\" href=\"javascript:;\">".
"<IMG src=\""."$sm3d"."\" style=\"border: 0px\"></A>".
"</P>".
"</TD>".
"<TD align=\"center\">".
"<P>".
"<A onclick=\"MM_openBrWindow('"."$lg2d"."','','scrollbars=yes,width=535,height=400')\" href=\"javascript:;\">".
"<IMG src=\""."$sm2d"."\" style=\"border: 0px\"></A>".
"</P>".
"</TD>".
"</TR>".
"<TR valign=\"top\">".
"<TD colspan=\"2\" rowspan=\"1\" class=\"footer\">".
"<A href=\"http://\">View Case Study</A> | <A href=\""."$pdf"."\">PDF Brochure</A>".
"</TD>".
"</TR>".
"</TBODY>".
"</TABLE>".
"</TD>".
"</TR>".
"</TBODY>".
"</TABLE>");
endif;
}
// how many rows we have in database
//WHERE lgpic IS NOT NULL, doesn't appear to effect the results..
$query = "SELECT COUNT(id) AS numrows FROM case_plans WHERE lg2d IS NOT NULL";
$result = mysql_query($query) or die('Error, query failed');
$row = mysql_fetch_array($result, MYSQL_ASSOC);
$numrows = $row['numrows'];
// how many pages we have when using paging?
$maxPage = ceil($numrows/$rowsPerPage);
$self = $_SERVER['PHP_SELF'];
// creating 'previous' and 'next' link
// plus 'first page' and 'last page' link
// print 'previous' link only if we're not
// on page one
if ($pageNum > 1)
{
$page = $pageNum - 1;
$prev = " <a href=\"$self?page=$page\">[Prev]</a> ";
$first = " <a href=\"$self?page=1\">[First]</a> ";
}
else
{
$prev = ' [Prev] '; // we're on page one, don't enable 'previous' link
$first = ' [First] '; // nor 'first page' link
}
// print 'next' link only if we're not
// on the last page
if ($pageNum < $maxPage)
{
$page = $pageNum + 1;
$next = " <a href=\"$self?page=$page\">[Next]</a> ";
$last = " <a href=\"$self?page=$maxPage\">[Last]</a> ";
}
else
{
$next = ' [Next] '; // we're on the last page, don't enable 'next' link
$last = ' [Last] '; // nor 'last page' link
}
// print the page navigation link
echo ("<p align=\"center\">"."$first "." $prev "." Page <strong>"."$pageNum"."</strong> of <strong>"."$maxPage"."</strong> pages "."$next"."$last"."</p><BR>");
include ('db_close.inc');
?>
