0

Hi,

I have created what i think is correct is one mysql_query with multiple counts. Could someone please tell me if i have done it rite ? plus how would i be able to access each count query using PHP so i can display each count query result?

If it's one query i know what to do but as i'm making multiple count queries in one query i don't know how to access each individual total count returned by mysql.

$CountQuery = mysql_query("
                    SELECT
                    (SELECT COUNT(*) as `referrer_uid`)
                    FROM ".constant("TBL_USER_REFERRALS")."
                    WHERE `referrer_uid` = '$referrer_uid'
                    AND `status` = '".constant('REFERRAL_STATUS_COMPLETED')."' ,

                    (SELECT COUNT(*) as `referrer_uid`) 
                    FROM ".constant("TBL_USER_REFERRALS")."
                    WHERE `referrer_uid` = '$referrer_uid'
                    AND `status` = '".constant('REFERRAL_STATUS_DECLINED')."',
                    
                    (SELECT COUNT(*) as `referrer_uid`)
                    FROM ".constant("TBL_USER_REFERRALS")."
                    WHERE `referrer_uid` = '$referrer_uid'
                    AND `status` = '".constant('REFERRAL_STATUS_REFERRED')."'
                    ");

Thanks
PHPLOVER

Edited by phplover: n/a

3
Contributors
10
Replies
12
Views
6 Years
Discussion Span
Last Post by phplover
Featured Replies
  • [code] $result=mysql_query("SELECT count(*) total , sum(if(`status` = '".constant('REFERRAL_STATUS_COMPLETED')."',1,0)) com_count , sum(if(`status` = '".constant('REFERRAL_STATUS_DECLINED')."',1,0)) dec_count, , sum(if(`status` = '".constant('REFERRAL_STATUS_REFERRED')."',1,0)) ref_count FROM ".constant("TBL_USER_REFERRALS")." where `referrer_uid`='$referrer_uid' "); [/code] Read More

  • there is extra comma after dec_count, it should look like dec_count (remove comma) Read More

0

Sorry code was meant to be:

$CountQuery = mysql_query("
                    SELECT
                    (SELECT COUNT(*) as `referrer_uid`)
                    FROM ".constant("TBL_USER_REFERRALS")."
                    WHERE `referrer_uid` = '$referrer_uid',

                    (SELECT COUNT(*) as `referrer_uid`)
                    FROM ".constant("TBL_USER_REFERRALS")."
                    WHERE `referrer_uid` = '$referrer_uid'
                    AND `status` = '".constant('REFERRAL_STATUS_COMPLETED')."' ,

                    (SELECT COUNT(*) as `referrer_uid`) 
                    FROM ".constant("TBL_USER_REFERRALS")."
                    WHERE `referrer_uid` = '$referrer_uid'
                    AND `status` = '".constant('REFERRAL_STATUS_DECLINED')."',
                    
                    (SELECT COUNT(*) as `referrer_uid`)
                    FROM ".constant("TBL_USER_REFERRALS")."
                    WHERE `referrer_uid` = '$referrer_uid'
                    AND `status` = '".constant('REFERRAL_STATUS_REFERRED')."'
                    ");

I want to access each total count so i can use in a table something like

Total Referalls xx (1st select count query in above code) | Total Signups xx (completed) | Total Declined xx (declined) | Total Pending xx (referred)

Thanks

(where xx is that would be the total count for each select count(*) )

Edited by phplover: n/a

0

Well, I don't think there's much difference between executing 4 different queries in one query and executing 4 different queries apart from eachother.

I've never tried it, but I don't think executing 4 counts in one query will work. Besides that, COUNT just counts the rows that are selected, so if your first query counts 3 rows, and the second one counts 6 rows, the result will be 6, not both 3 and 6 (for as far as I know).

So I'd say: just execute the 4 queries apart from eachother. That'll work for sure.

0

Hi,

I know i could improve the query but not sure how plus not sure how i would access each individual select count(8) result. So if anyone knows please reply?

Thanks

0
$result=mysql_query("SELECT count(*) total, sum(if(`referrer_uid`='abc',1,0) abc_count,
, sum(if(`referrer_uid`='pqr',1,0) pqr_count,
, sum(if(`referrer_uid`='xyz',1,0) xyz_count
FROM ".constant("TBL_USER_REFERRALS"));

output

total, abc_count, pqr_count, xyz_count
45, 10, 15, 20


You can access result as you access any other mysql result by using column name

0

Hi,

I tried to access the individually results and echo them out but don't get any errors or results. I have all PHP errors on and to display them.

Maybe i am doing it wrong ?

$sql=mysql_query("
            SELECT count(*) total, sum(if(`referrer_uid`='$referrer_uid',1,0) abc_count,
            , sum(if(`referrer_uid`='$referrer_uid',1,0) pqr_count,
            , sum(if(`referrer_uid`='$referrer_uid',1,0) xyz_count
            FROM ".constant("TBL_USER_REFERRALS"));
           
            while ($row = mysql_fetch_array($sql)){
                echo $row['abc_count'];
                echo $row['xyz_count'];
                echo $row['pgr_count'];
            }

Thanks for any help and sorry if i am getting it wrong.

The only difference between each select count(*) query in my original code is that the status i am checking for is different.

total referrals (1st query in my original code) | completed (2nd query in original code) | declined ( 3rd query in original code) | referred (4th query in original code)

This is how i can then display like total referrals, total completed referrals, total declines referrals and so on.

Currently i do it like this which is tedious as you could imagine:

// count total referrals user has made
        $referral_total_count = mysql_query("
            SELECT count(*) as `referrer_uid`
            FROM ".constant("TBL_USER_REFERRALS")."
            WHERE `referrer_uid` = '$referrer_uid'");

        // count total referral signup
        $referral_total_signup_count = mysql_query("
            SELECT count(*) as `referrer_uid`
            FROM ".constant("TBL_USER_REFERRALS")."
            WHERE `referrer_uid` = '$referrer_uid'
            AND `status` = '".constant('REFERRAL_STATUS_COMPLETED')."' ");

        // count total referral declined
        $referral_total_declined_count = mysql_query("
            SELECT count(*) as `referrer_uid`
            FROM ".constant("TBL_USER_REFERRALS")."
            WHERE `referrer_uid` = '$referrer_uid'
            AND `status` = '".constant('REFERRAL_STATUS_DECLINED')."' ");

        // count total pending referrals (referred)
        $referral_total_pending_count = mysql_query("
            SELECT count(*) as `referrer_uid`
            FROM ".constant("TBL_USER_REFERRALS")."
            WHERE `referrer_uid` = '$referrer_uid'
            AND `status` = '".constant('REFERRAL_STATUS_REFERRED')."' ");

Edited by phplover: n/a

0

post your mysql table script with some sample data of all four types

Edited by urtrivedi: n/a

1
$result=mysql_query("SELECT count(*) total
, sum(if(`status` = '".constant('REFERRAL_STATUS_COMPLETED')."',1,0)) com_count
, sum(if(`status` = '".constant('REFERRAL_STATUS_DECLINED')."',1,0)) dec_count,
, sum(if(`status` = '".constant('REFERRAL_STATUS_REFERRED')."',1,0)) ref_count
FROM ".constant("TBL_USER_REFERRALS")." where `referrer_uid`='$referrer_uid' ");

Edited by urtrivedi: n/a

Votes + Comments
Works Perfectly Thank you! :)
0

Hi,

Using this code you gave (last code suggestion you made)

$sql=mysql_query("SELECT count(*) total
            , sum(if(`status` = '".constant('REFERRAL_STATUS_COMPLETED')."',1,0)) com_count
            , sum(if(`status` = '".constant('REFERRAL_STATUS_DECLINED')."',1,0)) dec_count,
            , sum(if(`status` = '".constant('REFERRAL_STATUS_REFERRED')."',1,0)) ref_count
            FROM '".constant("TBL_USER_REFERRALS")."' WHERE `referrer_uid` ='$referrer_uid' ") or die(mysql_error());

I get this:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' sum(if(`status` = 'referred',1,0)) ref_count FROM user_referrals wh' at line 4

My database is like shown in image below:

http://img820.imageshack.us/f/sampledb.jpg/

It should print out the following which is correct based on referred_uid 806

http://img600.imageshack.us/f/sampledbresults.jpg/

Thanks for all your help!

0

Thank you! :) It works great.

Much appreciate all your time and effort in helping me :)

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.