getting error when trying to get row count for pagination

[simcoweb]

I'm trying to get a count of results from a database containing category ids so I can set this variable for the pagination. It keeps giving me an error and, to me, the query looks fine:

[code]// Figure out the total number of results in DB:
$total_results = mysql_result(mysql_query("SELECT COUNT (*) as Num FROM members_cat WHERE categoryid={$cat_id}"),0);[/code]

[quote]Warning: mysql_result(): supplied argument is not a valid MySQL result resource in /home2/wwwxxxx/public_html/category-page2a.php on line 24[/quote]

Help?

[hostfreak]

Not 100% sure, but
[code]
$total_results = mysql_query("SELECT COUNT (*) as Num FROM members_cat WHERE categoryid='$cat_id'");
echo mysql_result($total_results, 0);
[/code]
Maybe?

[hostfreak]

Also just to be sure, you are getting $cat_id from somehwere right?

[simcoweb]

Yeah, getting the $cat_id from:

[code]$cat_id = is_numeric($_GET['categoryid']) ? $_GET['categoryid'] : 0;[/code]

Also, I did try that slight modification of the query code you suggested already and produced the same error.

[manichean]

I expanded it s bit in order to do checking on my local machine and the below mentioned code works

[quote]
$query = "SELECT COUNT(*) as Num FROM members_cat WHERE categoryid='{$cat_id}'";
$result = mysql_query($query) or die (mysql_error());
$row = mysql_fetch_array($result);
print($row['Num']);
[/quote]

[simcoweb]

When you say it works... what do you get? I just get errors as posted.

[manichean]

Your line:
[quote]
$total_results = mysql_result(mysql_query("SELECT COUNT (*) as Num FROM members_cat WHERE categoryid={$cat_id}"),0);
[/quote]

Should be like this:
[quote]
$total_results = mysql_result(mysql_query("SELECT COUNT(*) as Num FROM members_cat WHERE categoryid='{$cat_id}'"),0);
[/quote]

[simcoweb]

Ok, I see the ' ' and have tried those as well but still get the same error:

[quote]Warning: mysql_result(): supplied argument is not a valid MySQL result resource in /home2/wwwxxxx/public_html/category-page2a.php on line 25[/quote]

I'm using this same code (which is for pagination) in another page and it works fine. However, it does not have the WHERE clause in it. Here it is:

[code]$total_results = mysql_result(mysql_query("SELECT COUNT(*) as Num FROM plateau_pros"),0);[/code]

[manichean]

did you also remove the space between the count and the (*) ? it should read
[quote]
COUNT(*)
[/quote]

and not
[quote]
COUNT (*)
[/quote]

[simcoweb]

Yeah, that space is gone. Here's the whole batch of code. Perhaps it just needs a tweak in order for the pagination to work properly in a situation where the WHERE clause is present. Like I mentioned, I have this pagination working perfectly on a set of pages that does not specify a WHERE clause in the query. That's the only difference between the two.

Here's the code:

[code]if(!isset($_GET['page'])){
    $page = 1;
} else {
    $page = $_GET['page'];
}
// Define the number of results per page
$max_results = 5;

// Figure out the limit for the query based on the current page number.
$from = (($page * $max_results) - $max_results);

// Grab data from database, where the member has the specified category id
$sql = "SELECT p.* FROM members_cat c, plateau_pros p WHERE c.categoryid='$cat_id' "
        . "AND c.memberid=p.memberid";
$results = mysql_query($sql) or die(mysql_error());
$num_rows = mysql_num_rows( $results );

echo "<font class='bodytext'>There are <b>$num_rows</b> professionals in this category</font><br><br>\n";
// If current page number, use it
// if not, set one!

// Figure out the total number of results in DB:
$results = mysql_result(mysql_query("SELECT COUNT(*) as Num FROM members_cat WHERE categoryid='{$cat_id}'"),0);

$total_results = mysql_num_rows( $results);

// Figure out the total number of pages. Always round up using ceil()
$total_pages = ceil($total_results / $max_results);

// Build Page Number Hyperlinks
echo "<center>Select a Page<br />";

for($i = 1; $i <= $total_pages; $i++){
    echo " | ";
    if(($page) == $i){
        echo "Page $i ";
        } else {
            echo "<a class=\"body\" href=\"".$_SERVER['PHP_SELF']."?page=$i\">Page $i</a> ";
    }
}[/code]

Basically if I call the script and extend the URL to include the category ID then it displays the proper number of results but it doesn't paginate them. I have pagination set for 5 per page. The results yields 14 members and provides links to 3 pages (at 5 each) but still displays all 14 on the first page and the other pages are blank.

Check it out: [url=http://www.plateauprofessionals.com/category-page2a.php?categoryid=4]http://www.plateauprofessionals.com/category-page2a.php?categoryid=4[/url]

[manichean]

Ok I understand what you trying to do

You have
[quote]
// Figure out the total number of results in DB:
$results = mysql_result(mysql_query("SELECT COUNT(*) as Num FROM members_cat WHERE categoryid='{$cat_id}'"),0);
$total_results = mysql_num_rows( $results);
[/quote]

I dont understand why you storing the count(*) into Num if its never used or why you try call mysql_num_rows on a result that already returns the number of rows
[quote]
// Figure out the total number of results in DB:
$total_results = mysql_result(mysql_query("SELECT COUNT(*) FROM members_cat WHERE categoryid='{$cat_id}'"),0);
[/quote]

That is the only line you should need and it will return the number of results into the $total_results variable. If you get another error just post it :P

[simcoweb]

Ok, I see what you've pointed out and I removed the duplicate query. However, here's the continuing problem.

The pagination code requires these two variables placed into the main query for the data:

[code]// Grab data from plateau_pros, where the member has the specified category id
$sql = "SELECT p.* FROM members_cat c, plateau_pros p WHERE c.categoryid='$cat_id' "
        . "AND c.memberid=p.memberid LIMIT $from, $max_results";[/code]

The $from and $max_results are the two. When I do this it sets the query limit to '5' which is not what I want. I want the query to go ahead and pull all matching data...which should be 14 results for category 4. The 'LIMIT' clause is overriding this and displays just 5 results on the first page then 0 on the next two pages. If I take those variables out of the query then I get all 14 on the first page and 0 on the next two pages.

The pagination code still displays Page1 | Page 2 | Page 3 but when you click on 2 or 3 it displays "0 Results Found" when, in fact, there should be 5 results per page as the $max_results variable would dictate.

Visit that url and you'll see what I mean. I know this is just a 'tweak' where the query or a variable needs to be replaced or changed. But I can't figure it out.

[HuggieBear]

OK, the link you provided doesn't work I'm afraid.  As for the two variables in the SQL query, they're doing what they're meant to.  It should only be pulling 5 rows from the database, not 14.  Hence the pagination.

Please can you post your whole code for that page as it currently stands.  Also, if you're able to put the example back on your site that would be helpful too.

Regards
Huggie

[simcoweb]

HuggieBear, the server was down when you tried it. It's working if you want to try it again now.

Here's the code now:

[code]include 'dbconfig.php';
include 'header.php';
//connect to db
mysql_connect($dbhost, $dbuser, $dbpass) or die(mysql_error());
mysql_select_db($dbname) or die(mysql_error());

$cat_id = is_numeric($_GET['categoryid']) ? $_GET['categoryid'] : 0; // Double check for a valid value

// If current page number, use it
// if not, set one!
if(!isset($_GET['page'])){
    $page = 1;
} else {
    $page = $_GET['page'];
}
// Define the number of results per page
$max_results = 5;

// Figure out the limit for the query based on the current page number.
$from = (($page * $max_results) - $max_results);

// Figure out the total number of results in DB:
$total_results = mysql_result(mysql_query("SELECT COUNT(*) FROM members_cat WHERE categoryid='{$cat_id}'"),0);

// Figure out the total number of pages. Always round up using ceil()
$total_pages = ceil($total_results / $max_results);

// Grab data from plateau_pros, where the member has the specified category id
$sql = "SELECT p.* FROM members_cat c, plateau_pros p WHERE c.categoryid='$cat_id' "
        . "AND c.memberid=p.memberid LIMIT $from, $max_results";
$results = mysql_query($sql) or die(mysql_error());
$num_rows = mysql_num_rows( $results );

echo "<font class='bodytext'>There are <b>$num_rows</b> professionals in this category</font><br><br>\n";

// Build Page Number Hyperlinks
echo "<center>Select a Page<br />";

for($i = 1; $i <= $total_pages; $i++){
    echo " | ";
    if(($page) == $i){
        echo "Page $i ";
        } else {
            echo "<a class=\"body\" href=\"".$_SERVER['PHP_SELF']."?page=$i\">Page $i</a> ";
    }
}

echo "</center>";

if (empty($results)) {
  echo "No profiles found in this category.";
} else {
while ($a_row= mysql_fetch_array($results)){[/code]

Here's the same pagination code being used in another page on the site but does not include the WHERE clause in the query. It works perfectly:

[code]// test to display addbiz results
header ("content type: image/gif");
header ("content type: image/jpeg");
header ("content type: image/png");
include 'config.php';
include 'header.php';
$upload_dir = "http://www.plateauprofessionals.com/images/photo";

mysql_connect($dbhost, $dbuser, $dbpass) or die(mysql_error());
mysql_select_db($dbname) or die(mysql_error());

// If current page number, use it
// if not, set one!

if(!isset($_GET['page'])){
    $page = 1;
} else {
    $page = $_GET['page'];
}

// Define the number of results per page
$max_results = 5;

// Figure out the limit for the query based
// on the current page number.
$from = (($page * $max_results) - $max_results);

$sql=("SELECT * FROM plateau_pros ORDER BY memberid LIMIT $from, $max_results");
$result = mysql_query($sql) or die(mysql_error());

$mysock = getimagesize($image);
$setWidth = 125;
function imageResize($width, $height, $target) {
$newHeight = 0;
$newHeight = ($setWidth * $height) / $width;

return "width='{$setWidth}' height='{$newHeight}'";
}

// Figure out the total number of results in DB:
$total_results = mysql_result(mysql_query("SELECT COUNT(*) as Num FROM plateau_pros"),0);

// Figure out the total number of pages. Always round up using ceil()
$total_pages = ceil($total_results / $max_results);

// Build Page Number Hyperlinks
echo "<center>Select a Page<br />";

for($i = 1; $i <= $total_pages; $i++){
    echo " | ";
    if(($page) == $i){
        echo "Page $i ";
        } else {
            echo "<a class=\"body\" href=\"".$_SERVER['PHP_SELF']."?page=$i\">Page $i</a> ";
    }
}

echo "</center>";

while ($row = mysql_fetch_assoc($result)) {[/code]

And here's a peek at how it works perfectly:

[url=http://www.plateauprofessionals.com/display-page.php?page=1]http://www.plateauprofessionals.com/display-page.php?page=1[/url]

Hopefully you can spot something. Thanks for your help!

[HuggieBear]

OK, you're getting no results as you're not passing the category through the address.

Change this at the bottom of the page:
[code]
echo "<a class=\"body\" href=\"".$_SERVER['PHP_SELF']."?page=$i\">Page $i</a> ";
[/code]

To this:
[code]
echo "<a class=\"body\" href=\"".$_SERVER['PHP_SELF']."?categoryid=$cat_id&page=$i\">Page $i</a> ";
[/code]

You must pass the category id through the URL too if you're using it in your query.

Regards
Huggie

[simcoweb]

Acutally the category id IS being passed in the URL as you can see in my example links:

[url=http://www.plateauprofessionals.com/category-page2a.php?categoryid=4]http://www.plateauprofessionals.com/category-page2a.php?categoryid=4[/url]

The other link was not category specific and was pulling all the results from the entire database regardless of the category.

If you use that link you'll see it pulls 14 results from that category but does not put 5 per page and create the page links. If I take the two variables out in the LIMIT clause then it displays the links to the other pages but still shows 14 on the first page and states the others have 0.

Here's an example of that:

[url=http://www.plateauprofessionals.com/category-page2a2.php?categoryid=4]http://www.plateauprofessionals.com/category-page2a2.php?categoryid=4[/url]

In the first link, note that it displays Page 1 | Page 2 | Page 3 . This is because the $row_count = 14 and it's created the links to the other pages to accommodate results 6 -10 and 11 -14. Even though it displays '5 results found' which is incorrect and stating that because of the LIMIT clause. But when you click on them there's 0 found. The pagination is fine but the results are not.

Now, without the LIMIT clause in example 2 removed you see all 14 results on the first page, pagination to pages 2 & 3..but no results there.

The quest is to have 1-5 show on the first page, 6-10 on the 2nd, and 11-14 on the 3rd.

[HuggieBear]

[quote author=simcoweb link=topic=111171.msg453290#msg453290 date=1161111168]
Acutally the category id IS being passed in the URL as you can see in my example links:

[url=http://www.plateauprofessionals.com/category-page2a.php?categoryid=4]http://www.plateauprofessionals.com/category-page2a.php?categoryid=4[/url]
[/quote]

Actually it's NOT!!!... Although I appreciate that you may have included your category id in the link that you included in your post to the forum, you have not included it in your code...

Your code has this...

[code]
echo "<a class=\"body\" href=\"".$_SERVER['PHP_SELF']."?page=$i\">Page $i</a> ";
[/code]

Your code should look something like this...

[code]
echo "<a class=\"body\" href=\"".$_SERVER['PHP_SELF']."?categoryid=$cat_id&page=$i\">Page $i</a> ";
[/code]

Although I'm extremely pissed, I've had a go at changing the code you pasted, so change what you pasted to:

[code]
include 'dbconfig.php';
include 'header.php';
//connect to db
mysql_connect($dbhost, $dbuser, $dbpass) or die(mysql_error());
mysql_select_db($dbname) or die(mysql_error());

// Get category ID or set default
$cat_id = is_numeric($_GET['categoryid']) ? $_GET['categoryid'] : 0; // Double check for a valid value

// Get page ID or set default
$page = isset($_GET['page']) ? $page = $_GET['page'] : $page = 1;

// Define the number of results per page
$max_results = 5;

// Figure out the limit for the query based on the current page number.
$from = (($page * $max_results) - $max_results);

// Figure out the total number of results in DB:
$sql = "SELECT COUNT(*) FROM members_cat WHERE categoryid='$cat_id'";
$result = mysql_query($sql);
$total_results = mysql_num_results($result);

// Figure out the total number of pages. Always round up using ceil()
$total_pages = ceil($total_results / $max_results);

// Grab data from plateau_pros, where the member has the specified category id
$sql = "SELECT p.* FROM members_cat c, plateau_pros p WHERE c.categoryid='$cat_id' AND c.memberid=p.memberid LIMIT $from, $max_results";
$results = mysql_query($sql) or die(mysql_error());
$num_rows = mysql_num_rows($results);

echo "<font class='bodytext'>There are <b>$num_rows</b> professionals in this category</font><br><br>\n";

// Build Page Number Hyperlinks
echo "<center>Select a Page<br />";

for($i = 1; $i <= $total_pages; $i++){
    echo " | ";
    if(($page) == $i){
        echo "Page $i ";
        } else {
            echo "<a class=\"body\" href=\"".$_SERVER['PHP_SELF']."?categoryid=$cat_id&page=$i\">Page $i</a> ";
    }
}

echo "</center>";

if (empty($results)) {
  echo "No profiles found in this category.";
} else {
while ($a_row= mysql_fetch_array($results)){
[/code]

Let me know if you get an error.

Regards
Huggie

[simcoweb]

Yes, I get this error message:

[quote]Fatal error: Call to undefined function: mysql_num_results() in /home2/wwwplat/public_html/category-page2a2.php on line 24[/quote]

That's this:

[code]$total_results = mysql_num_results($result);[/code]

[akitchin]

it's probably because mysql_num_results() doesn't exist.  if you tried searching in the manual, you'd see one of its suggestions is mysql_num_rows().  could that be the vital clue?

[simcoweb]

That definitely got rid of the error. Must've been a brain fart.

Unfortunately it's still not paginating correctly. It just wants to display 5 results when there's 14 in the category. :(

[HuggieBear]

[quote author=simcoweb link=topic=111171.msg453610#msg453610 date=1161156875]
Unfortunately it's not paginating correctly. It just wants to display 5 results when there's 14 in the category. :(
[/quote]

I might be completely loosing the plot here, and if I am then somebody please shoot me as this is driving me insane, but the whole point of the pagination is that it only shows 5 results on the page, not 14!

If you click on your [url=http://www.plateauprofessionals.com/category-page2a.php?categoryid=4]link[/url] up at the top of this page, it works fine, then when you click page 2 it doesn't work, but the link to page 2 is incorrect, as it doesn't contain the 'categoryid' in the URL.  Try this link to [url=http://www.plateauprofessionals.com/category-page2a.php?categoryid=4&page=2]page 2[/url] and then this link to [url=http://www.plateauprofessionals.com/category-page2a.php?categoryid=4&page=3]page 3[/url].

Have you made the modifications to the links that I suggested so that they include the 'categoryid' in the URL?

Regards
Huggie

[simcoweb]

I'm emailing you a Valium. :)

It's not that complicated. Here's the whole thing in a nutshell:

1) Yes, it should show 5 per page and not 14. It's set to show 5 per page.
2) But, it's showing the first 5 only and the subsequent pages show 0. Meaning it's only showing a total of 5 results and not the 14 in 3 successive pages ( Page1 | Page 2| Page 3) as it should
3) I don't want it to show just 5 total results. I want it to show 14 total results but on 3 pages (hence the pagination)
4) Category ID 4 has 14 members in it. Not 5. Therefore it should show 3 pages of results based upon the fact we have it set for 5 results per page. Period.
5) I HAVE inserted your category id code into the script and uploaded it to the server and that's what is currently located at this link:

[url=http://www.plateauprofessionals.com/category-page.php?categoryid=4]http://www.plateauprofessionals.com/category-page.php?categoryid=4[/url]

Note the fact that it shows 5 results and NO page link to go to results 6-10 (page 2) or 11-14 (page 3). THAT is the problem.

The code is EXACTLY as you posted it. I copied and pasted your post directly into my page. No deviations...no edits...exactly.

[HuggieBear]

ok... That makes sense, disregard what I posted in the early hours of this morning, I thinks it's only serving to confuse matters further.  ;D

Take the code that you have on page [color=green][url=http://www.plateauprofessionals.com/category-page2a.php?categoryid=4]category-page2a.php[/url][/color] as that code is almost right, and then just change the link at the bottom from this:

[code=php:0]echo "<a class=\"body\" href=\"".$_SERVER['PHP_SELF']."?page=$i\">Page $i</a> ";
[/code]

To this:

[code=php:0]echo "<a class=\"body\" href=\"".$_SERVER['PHP_SELF']."?categoryid=$cat_id&page=$i\">Page $i</a> ";
[/code]

and I think we'll finally be there.

Regards
Huggie

[akitchin]

the total number of results found by the query is being run wrong.  you're using mysql_num_rows() which is a bit silly, because a COUNT() will only ever return one "row."  use mysql_result() to extract the correct result:

[code]$sql = "SELECT COUNT(*) FROM members_cat WHERE categoryid='$cat_id'";
$result = mysql_query($sql) or die('count query failed for some reason.');
$total_results = mysql_result($result, 0, 0);[/code]

hope this at least amends the total results found.  this line is also a little faulty:

[code]echo "<font class='bodytext'>There are <b>$num_rows</b> professionals in this category</font><br><br>\n";[/code]

$num_rows is just how many rows that page itself pulled.  $total_results is what you should be echoing in that line.

[simcoweb]

He shoots...he SCORRRRRRRRRES! :)

Ok, akitchin...that was the code 'tweak' needed for that part. Huggie, adding the category id to the link was the other part of the puzzle. Independently they wouldn't work. Collectively they do. Here's what I did:

* changed the COUNT code to what akitchin provided with 'mysql_result' instead of 'mysql_num_rows'
* changed the display of the total results from $num_rows (which actually returned the correct amount, by the way) to $total_results
* added Huggie's categoryid=$cat_id& to the URL string

Now I get 14 results, 5 per page, and the pagination displays properly and each page has the specified number of results (5 or less)

Take a look: [url=http://www.plateauprofessionals.com/category-page2a2.php?categoryid=4]http://www.plateauprofessionals.com/category-page2a2.php?categoryid=4[/url]

Whew! Time for a beer...even though it's 7:30 am :)  Thanks for the help, gentlemen!

Just a note of interest... pagination is  a BIG topic in the forums. This code works and is easy to implement. I'm going to modify the title a bit to reflect 'pagination' so it can possibly help others.