transform select query into select count query

[Goat]

Hello guys

 

I want to turn some random select query into query that counts how many rows that query returns. So ideally, I would like to make a php function where you send some query as string and it returns select query. It doesn't have to cover ALL situations, but it should be as universal as possible.

 

Something like this:

echo convert_to_count("SELECT * FROM users WHERE age='23'"); /// outputs: SELECT COUNT(*) FROM users WHERE age='23'

 

I am not sure if it would always work to simply replace SELECT .... FROM with SELECT COUNT(*) . What do you think?

 

 

regards,

Goat

[JustLikeIcarus]

Why don't you just use php's mysql_num_rows on the result of the query?

$num_rows = mysql_num_rows($result)

[Goat]

Why don't you just use php's mysql_num_rows on the result of the query?

$num_rows = mysql_num_rows($result)

 

Maybe that's what I am going to do eventually, but that's not very efficient for what I am trying to do. My real goal is to create function that automatically creates html table, complete with <prev 1, 2, 3, 4, 5 next> links, using only standard sql query.

 

Here's how it might work:

/// generate_table (row labels, query, starting point, limit)
generate_table("id, name, age", "SELECT * FROM users", 50, 10); 

 

the function would then create and execute two queries 

1. "SELECT * FROM users LIMIT 50, 10" - to populate html table

2. "SELECT COUNT (*) FROM users" - to figure out how many users we have to generate <prev 1 2 3 next> links

 

I could, of course just execute  "SELECT * FROM users" query, put everything to array, and then use php array functions to count it and extract parts that I need, but that would strain database too much if there are 100's users and I only need first 10.

 

thanks for replaying, thought

 

regards,

Goat

[JustLikeIcarus]

Ok then you could do a simple preg_replace like this.  See if that works.

$sql = 'select id, name, age from users';

$sql_count = preg_replace('/^select.+from/i', 'select count(*) from', $sql);

echo $sql_count;

[Goat]

Thanks, I will. But do you think this will work on more complex queries? It doesn't have to cover all situations, but it should cover as much as possible.

 

Goat

[JustLikeIcarus]

Well you will likely run into problems with joins if you use that solution.  You are better off running your query for the data then stripping off the limit section and running it again.  Then you can use mysql_num_rows on the second result to get the total.

[ignace]

Your looking for FOUND_ROWS http://dev.mysql.com/doc/refman/5.5/en/information-functions.html#function_found-rows

 

SELECT SQL_CALC_FOUND_ROWS * FROM users LIMIT 0, 10

SELECT found_rows() as found_rows  -- OR -- SET @found_rows = found_rows() -- allows for later use

[fenway]

SQL_CALC_FOUND_ROWS() is much, much slower than COUNT(*) -- don't return the actual result set if all you need in a count.