[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