ansaurus

Question

Displaying number of results from a MySQL db...

Answer 1

+2 A:

SELECT field , COUNT( id ) 
FROM db
GROUP BY field;

Brock Batsell 2010-02-02 21:30:16

+1, this is why we have databases: data transformation. Sure, querying makes more sense than searching flatfiles, to be sure, but they're more than just a storage engine; any chance you get to make the database do the job it was built for, do it!

Dereleased 2010-02-02 21:32:12

Answer 2

A:

Query all of the data at once and pass it off to PHP. Once you have it in PHP set up three separate loops.. Eg:

foreach($data AS $row)
{
    if($row['type'] == 'company')
    {
        // LOOP THROUGH COMPANY DATA
    }
}

foreach($data AS $row)
{
    if($row['type'] == 'private')
    {
        // LOOP THROUGH PRIVATE DATA
    }
}

This way you only pull your data set once, but you can surgically show the data you want from the set in each tab separately.

If you are just wanting counts use a GROUP BY clause when you select your counts.

angryCodeMonkey 2010-02-02 21:30:24

This is pretty horrible from a large-scale data perspective. When you have 100,000+ rows of data, and a user is not looking at all ads, you will be returning massive volumes of data that you know will not be needed. Let the DB do its job instead, returns counts and only the data you know you need.

macabail 2010-02-02 22:47:50

@macabail you are correct, I beleive I actually misread the question and didn't realize we were only talking about counts. I anticipated that he wanted to show counts on tabs as well as have the body listed (like in a table) inside of the tabs. If that were the case then a single query would be the way to go. I don't know WHERE I got the impression he was showing data on tabs.. Must have been sleepy.. =) Good catch on my thinking though!

angryCodeMonkey 2010-02-03 02:35:15

Answer 3

+3 A:

You could use a ROLLUP:

SELECT
    IFNULL( field , 'All ads' ) AS 'Type',
    COUNT( * )
FROM
    `table`
GROUP BY
    field
WITH ROLLUP

So with four Company and one Private ad you would see:

Type       COUNT( * )
Company    4
Private    1
All ads    5

martin clayton 2010-02-02 21:38:23

+1 I have never seen this clause before and just checked the documentation. I can imagine, that this is extremely powerful! Thanks!

Cassy 2010-02-02 21:47:33

I, too, didn't know about ROLLUP. That's pretty neat. I do wonder, though, if IFNULLing every single row would result in slower performance than simply adding up the two numbers in PHP afterward.

Brock Batsell 2010-02-03 17:27:49

@Brock - I doubt the IFNULL would have much of an impact. By the time it's applied the dataset will be down to the grouped rows only. Perhaps more likely is that the WITH ROLLUP itself might be expensive. One advantage though is that the calling code is simplified.

martin clayton 2010-02-03 21:12:25

ansaurus

tags:

views:

answers:

Displaying number of results from a MySQL db...

related questions