Using SQL_CALC_FOUND_ROWS...

[jamescover]

I'm still working on a recordset paging system. I found SQL_CALC_FOUND_ROWS in the MySQL manual, and think it will suit my purposes, but I haven't been able to get a numerical value out of FOUND_ROWS.

Below are my db queries:


$makes = mysql_query("SELECT SQL_CALC_FOUND_ROWS year, make, model, mileage, stock, photos, price, link FROM dbName.tableName WHERE make = '$getMakes' ORDER BY year, make, model ASC LIMIT $x, $y");

$totRows = mysql_query("SELECT FOUND_ROWS()");

Right now, $totRows returns "Resource id#3, but I need the numerical value that corresponds with the id.

Then, I will set a conditional based upon the total number of rows returned to set up my paging system.

Here is a link to the manual regarding the above:

http://dev.mysql.com/doc/mysql/en/Information_functions.html#IDX1478


Any help is appreciated. Thanks.


-james

[raf]

i don't think there is anything wrong with your query (unless i'moverlooking a typo or so) so i canonly assume that you have an sql version that is lower then 4.0.0

[jamescover]

Hi raf:

Thanks, for responding!

My version:

MySQL 4.0.20-log running on localhost as myLogin@localhost

According to the documentation, FOUND_ROWS() returns a number. But as I already said, I'm getting: Resource id #3, using:


echo "$totRows";



-james

[raf]

hmm. i used that without any problems before. I'll check the exact syntax i used when i get home. In the meanwhile, try

$totRows = mysql_query("SELECT FOUND_ROWS()");
echo mysql_result($totRows , 0);

[jamescover]

Hey, raf...

Yeah, that returned an integer. What gives? I couldn't find any other documentation on how to use these statements, or how to get the desired result. Maybe, you have to be psychic... :confused:

So what now? I mean, shouldn't it return an integer the way that I had it coded???

At any rate, thanks for your expertise. At least, I've got something to work with now.



-james

[raf]

not psychic, but since you got a resourceid, i thought you could just acces it like any other recordset (mysql_result is just a function to display one value for 1 row).

I'm not sure iunderstand the So what now? I mean, shouldn't it return an integer the way that I had it coded???


Apparently, the
$totRows = mysql_query("SELECT FOUND_ROWS()");

is just executed like any other select statement and will return a resultset like any other mysql_query() function with a selectstatement, and this resultset can then be accessed just the same etc.

Do you mean that the
echo mysql_result($totRows , 0);
doesn't give the corect number of records?

[jamescover]

Do you mean that the
echo mysql_result($totRows , 0);
doesn't give the corect number of records?

No, not at all. It's returning the correct result. I just wonder why this wasn't mentioned in the documentation. The way it reads, what I did should work, right? Isn't that what the documentation suggests? I just thought that maybe I was missing something, but I want to make sure that I do it the correct way.

So, I guess, you recommend just doing it as you suggested? I can live with that. I'd just like to see some reference to it in the doc...

Thanks, again, for your time and assistance.


-james

[raf]

By the documentation, i assume you refer to the MySQL manual (your link above). It is completely corect there. If you run

mysql> SELECT SQL_CALC_FOUND_ROWS * FROM tbl_name
-> WHERE id > 100 LIMIT 10;
mysql> SELECT FOUND_ROWS();

in the commandline module, then you would instantly get the numer of rows from the "SELECT FOUND_ROWS();" because the result is there immedeately printed in the commandline. You are there working directly on the mysl-server
Idem dito if you would enter these querys in the querywindow of a db-front like MySQL Front or so

But you are using these sql-statements as 'embedded sql' inside PHP-code, where "SELECT FOUND_ROWS()" is treatred just like any other select-statement.
The way it works then, is that the parser will tell the webserver to open a connection to the mysql-server and will then feed it with the sql-statement (the value of the first parameter of your mysql_query() function). The MySQL server (well, the RDBM) will then execute the sql-statement just like when you fed it in the commandline BUT it will then send the result back to the webserver where it is stored (i believe as an in-memory contruct, not sure) and where it can be referenced with the result id. The mysql_query()function then returns this result id. That is the Resource id#3 that you initially got.

With this result id, you can then acces the resultset.(your mysql_result() or mysql_fetch_assoc() etc functions, will allcontain this result ID + some other parameters like the row number ect)


So it shouldn't be in the mysql documentation since it depends on how you execute the statments (from thecommandline, db front, serversidelanguage etc) how the result needs to be processed. And it also shouldn't be in the PHP docs since it's a mysql-specific feature and since you just need to run the select as any regular select and proces the results asany other select.

I feel a sticky comming up ...

[jamescover]

I feel a sticky comming up ...

Sounds like a good idea.

Thanks for the detailed explanation, and for taking your time. I've always found the MySQL site to be somewhat lacking in real-world examples, even compared to PHP.net, and other web resources...makes it hard on a newbie like me.

At any rate, your explanation makes sense. I appreciate it very much. Thanks, again!


-james