Topic: [SOLVED] Getting the number of rows

[chantown]

Hi,

Let's say i have a query:

"SELECT topic_name FROM mytable WHERE user='$username' LIMIT $Start, $pageSize";

Now i can display the topic_name in a table.

However, I want to know how many TOTAL topics there are from this user, so I can make pages and calculate how many topics on each page (pagination)

How do I do that without using another query like this?--
"SELECT count(topic_name) FROM mytable WHERE user = '$username'";

fank you

[~n[EO]n~]

through mysql_numrows, it retrieves the number of rows from your query.

[chantown]

Yes, but that will give me the number of rows with the LIMIT applied

I'm looking for the total number of rows

[~n[EO]n~]

See if you are doing for pagination then you need 2 queries for e.g.
First
query = SELECT topic_name FROM mytable WHERE user='$username';
numrows = mysql_numrows(query)

here you will get total rows for pagination purpose, then you add the limit again
new_query = query."LIMIT $Start, $pageSize";
result = mysql_query(new_query)

And you will get the limit from here and total rows from above. Hope you got the idea
Tell me if I am wrong...

[chantown]

lol no you're absolutely right

but that's 2 queries (and if it's a full-text search, it's very cpu intensive)

is there any way to do it with 1 query?

[fenway]

Yes... use SQL_CALC_FOUND_ROWS.

[~n[EO]n~]

Yes... use SQL_CALC_FOUND_ROWS.

and what is the equivalent in PHP... i didn't find in the manual 

[fenway]

It's not in the manual.  Read the mysql manual -- it's a modifier to SELECT, like DISTINCT; after you issue your LIMIT query, call SELECT FOUND_ROWS(), and get it back.

[chantown]

thank you thank you thank you