Jump to content


Photo

getting error when trying to get row count for pagination


  • Please log in to reply
24 replies to this topic

#1 simcoweb

simcoweb
  • Members
  • PipPipPip
  • Advanced Member
  • 1,102 posts
  • LocationCA

Posted 11 October 2006 - 04:13 AM

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:

// 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);

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


Help?

#2 hostfreak

hostfreak
  • Members
  • PipPipPip
  • Advanced Member
  • 581 posts

Posted 11 October 2006 - 04:55 AM

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

#3 hostfreak

hostfreak
  • Members
  • PipPipPip
  • Advanced Member
  • 581 posts

Posted 11 October 2006 - 04:56 AM

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

#4 simcoweb

simcoweb
  • Members
  • PipPipPip
  • Advanced Member
  • 1,102 posts
  • LocationCA

Posted 11 October 2006 - 05:11 AM

Yeah, getting the $cat_id from:

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

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

#5 manichean

manichean
  • Members
  • PipPip
  • Member
  • 29 posts

Posted 11 October 2006 - 05:13 AM

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

$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']);



#6 simcoweb

simcoweb
  • Members
  • PipPipPip
  • Advanced Member
  • 1,102 posts
  • LocationCA

Posted 11 October 2006 - 06:59 AM

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

#7 manichean

manichean
  • Members
  • PipPip
  • Member
  • 29 posts

Posted 11 October 2006 - 08:18 AM

Your line:

$total_results = mysql_result(mysql_query("SELECT COUNT (*) as Num FROM members_cat WHERE categoryid={$cat_id}"),0);


Should be like this:

$total_results = mysql_result(mysql_query("SELECT COUNT(*) as Num FROM members_cat WHERE categoryid='{$cat_id}'"),0);



#8 simcoweb

simcoweb
  • Members
  • PipPipPip
  • Advanced Member
  • 1,102 posts
  • LocationCA

Posted 11 October 2006 - 02:10 PM

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

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


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:

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


#9 manichean

manichean
  • Members
  • PipPip
  • Member
  • 29 posts

Posted 15 October 2006 - 08:08 AM

did you also remove the space between the count and the (*) ? it should read

COUNT(*)


and not

COUNT (*)



#10 simcoweb

simcoweb
  • Members
  • PipPipPip
  • Advanced Member
  • 1,102 posts
  • LocationCA

Posted 16 October 2006 - 02:06 AM

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:

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> ";
    }
}

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: http://www.plateaupr...hp?categoryid=4

#11 manichean

manichean
  • Members
  • PipPip
  • Member
  • 29 posts

Posted 16 October 2006 - 06:10 AM

Ok I understand what you trying to do

You have

// 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);


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

// 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);


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

#12 simcoweb

simcoweb
  • Members
  • PipPipPip
  • Advanced Member
  • 1,102 posts
  • LocationCA

Posted 17 October 2006 - 06:30 AM

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:

// 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";

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.

#13 HuggieBear

HuggieBear
  • Members
  • PipPipPip
  • Advanced Member
  • 1,899 posts
  • LocationEngland, UK

Posted 17 October 2006 - 10:14 AM

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


Advice to MySQL users: Get phpMyAdmin and test your queries work there first, take half the hassle out of diagnosis, also check the reserved words list.

Links: PHP Docs :: RegEx's :: MySQL :: DevGuru :: w3schools

#14 simcoweb

simcoweb
  • Members
  • PipPipPip
  • Advanced Member
  • 1,102 posts
  • LocationCA

Posted 17 October 2006 - 02:38 PM

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:

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)){

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:

// 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)) {

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

http://www.plateaupr...page.php?page=1

Hopefully you can spot something. Thanks for your help!


#15 HuggieBear

HuggieBear
  • Members
  • PipPipPip
  • Advanced Member
  • 1,899 posts
  • LocationEngland, UK

Posted 17 October 2006 - 03:59 PM

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

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

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

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

Regards
Huggie
Advice to MySQL users: Get phpMyAdmin and test your queries work there first, take half the hassle out of diagnosis, also check the reserved words list.

Links: PHP Docs :: RegEx's :: MySQL :: DevGuru :: w3schools

#16 simcoweb

simcoweb
  • Members
  • PipPipPip
  • Advanced Member
  • 1,102 posts
  • LocationCA

Posted 17 October 2006 - 06:52 PM

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

http://www.plateaupr...hp?categoryid=4

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:

http://www.plateaupr...hp?categoryid=4

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.

#17 HuggieBear

HuggieBear
  • Members
  • PipPipPip
  • Advanced Member
  • 1,899 posts
  • LocationEngland, UK

Posted 18 October 2006 - 02:31 AM

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

http://www.plateaupr...hp?categoryid=4


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...

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

Your code should look something like this...

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

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

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)){

Let me know if you get an error.

Regards
Huggie
Advice to MySQL users: Get phpMyAdmin and test your queries work there first, take half the hassle out of diagnosis, also check the reserved words list.

Links: PHP Docs :: RegEx's :: MySQL :: DevGuru :: w3schools

#18 simcoweb

simcoweb
  • Members
  • PipPipPip
  • Advanced Member
  • 1,102 posts
  • LocationCA

Posted 18 October 2006 - 04:51 AM

Yes, I get this error message:

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


That's this:

$total_results = mysql_num_results($result);


#19 akitchin

akitchin
  • Staff Alumni
  • Advanced Member
  • 2,516 posts
  • LocationCalgary, AB, Canada

Posted 18 October 2006 - 04:56 AM

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?

#20 simcoweb

simcoweb
  • Members
  • PipPipPip
  • Advanced Member
  • 1,102 posts
  • LocationCA

Posted 18 October 2006 - 07:34 AM

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. :(




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users