CODEWITHSUNDEEP
phpmysql
Ron Paulfan
Ron Paulfan

Reputation: 31

PHP/SQL - Returning query results and counts

SQL/PHP noob. I've looked up a number of questions, but none seem to help get me what I need.

I have a front-end for a database that runs SQL queries based off the state of a checkbox. I want the SQL query to return both the results and the result count (into a variable) with a single query.

If I run:

SELECT DISTINCT
event.event_id as 'Event ID',
event.event_group_id as 'Group ID',
abs_path.abs_path as 'Path\Filename',

FROM 
event.event_group
LEFT JOIN event.event using (event_group_id)
LEFT JOIN file_info.abs_path using (abs_path_id)

WHERE event.timestamp >= DATE_SUB(CURDATE(),INTERVAL 1 DAY)
LIMIT 40

I get all the results to the query. But if I run:

SELECT DISTINCT
count(event.event_id) as 'rowcount',
event.event_id as 'Event ID',
event.event_group_id as 'Group ID',
abs_path.abs_path as 'Path\Filename',

FROM 
event.event_group
LEFT JOIN event.event using (event_group_id)
LEFT JOIN file_info.abs_path using (abs_path_id)

WHERE event.timestamp >= DATE_SUB(CURDATE(),INTERVAL 1 DAY)
LIMIT 40

I only get a single result with 'rowcount' being the first column, but if I take it out, I get all my results:

sql

I want to be able to get the total row count and save it into a variable, while also pulling down the results to display on the screen.

I'm using the following to fetch the query:

$result = $database->query($sql);

How can I do the query and the count at the same time while saving just the total results into $rowcount ? If you look at the bottom left corner of the 2nd image, SQL Query Browser displays the total number in the query. I want that number. How can I get it?

Thanks

Upvotes: 0

Views: 167

Answers (3)

Ezenhis
Ezenhis

Reputation: 1007

When using COUNT function your query is being automatically grouped.
Try using $rowcount = $result->fetchColumn(); and don't COUNT it in SQL.

Upvotes: 0

Blank
Blank

Reputation: 12378

This is just a solution by sql, may not a better solution, but try it;)

SELECT DISTINCT
event.event_id as 'Event ID',
event.event_group_id as 'Group ID',
abs_path.abs_path as 'Path\Filename',
@cnt:=@cnt + 1 as cnt

FROM 
event.event_group
LEFT JOIN event.event using (event_group_id)
LEFT JOIN file_info.abs_path using (abs_path_id),
(SELECT @cnt:=0) var

WHERE event.timestamp >= DATE_SUB(CURDATE(),INTERVAL 1 DAY)
LIMIT 40

And you can get count of query result by cnt of the last record.

Upvotes: 0

undefined_variable
undefined_variable

Reputation: 6228

You can get number of rows and result in PHP. After executing the query you will get result let's say $res.

You can use that to fetch number of rows. If using mysqli library use

mysqli_num_rows ( $res );

If you are using PDO you will have to run 2 queries one to fetch the values and other to fetch the count.

MySQLi

PDO

Upvotes: 0

Related Questions