CODEWITHSUNDEEP
phpmysql
Uffo
Uffo

Reputation: 10046

MYSQL count rows instead of showing results

So I have the following query, which I use it to get some analytics stats.

SELECT count(*) as total,CONCAT(YEAR(created),'-',MONTH(created),'-',DAY(created))  
as date_only  FROM logs where action = 'banner view'  
and created BETWEEN '2015-07-03 21:03'
AND '2017-08-02 21:03' group by date_only order by created asc

This works, and it gives me this:

enter image description here

So what I actually need is, the total count of the rows in this case is 20, this is a dummy example, but I need to use this count to check before showing the stats if the data is too big to be displayed on a graphic.

Can this be achieved?

//LE

So the process will be like this: 1. Get a count of the total rows, if the count of rows is smaller than X(number will be in config and it will be a basic if statement), then go ahread and run the above query.

More info: I actually use this query to display the stats, I just need to adapt it in order to show the total count rows

So the result of thquery should be

total | 20 in this case

Upvotes: 0

Views: 177

Answers (4)

JakeParis
JakeParis

Reputation: 11210

I think you would want to use a derived table. Just wrap your original query in parenthesis after the FROM and then give the derived table an alias (in this case tmp). Like so:

SELECT count(*) FROM (
   SELECT count(*) as total,CONCAT(YEAR(created),'-',MONTH(created),'-',DAY(created))  

   as date_only  FROM logs where action = 'banner view'  
   and created BETWEEN '2015-07-03 21:03'
   AND '2017-08-02 21:03' group by date_only order by created asc
) as tmp;

If I understand what you want to do correctly, this should work. It should return the actual number of results from your original query.

What's happening is that the results of the parenthesized query are getting used as a sort of virtual table to query against. The parenthesized query returns 20 rows, so the "virtual" table has 20 rows. The outer count(*) just counts how many rows there are in that virtual table.

Upvotes: 2

vjy tiwari
vjy tiwari

Reputation: 861

You can try this way .

    SELECT COUNT(*) FROM ( SELECT count(*) as total,CONCAT(YEAR(created),'-',MONTH(created),'-',DAY(created))  
    as date_only  FROM logs where action = 'banner view'  
    and created BETWEEN '2015-07-03 21:03'
    AND '2017-08-02 21:03' group by date_only HAVING total >=20 ) temp

Upvotes: 0

Mary
Mary

Reputation: 197

This is an small query but works fine, and give me the total number of rows, you just need add your conditions.

SELECT COUNT(*) FROM table WHERE field LIKE '%condition%'

The group by I think you need to eliminated, becouse, this instead of count the records, divide in all your group by, example: records = 4, with group by you have
1 1 1 1

I hope this help you

Upvotes: 0

manassehkatz-Moving 2 Codidact
manassehkatz-Moving 2 Codidact

Reputation: 3034

Based on the PHP tag, I assume you are using PHP to send the queries to MySQL. If so, you can use mysqli_num_rows to get the answer.

If your query result is in $result then:

$total = mysqli_num_rows($result);

Slightly different syntax for Object Oriented style instead of procedural style.

The best part is you don't need an extra query. You perform the original query and get mysqli_num_rows as an extra without running another query. So you can figure out pagination or font size or whatever and then display without doing the query again.

Upvotes: 1

Related Questions