Possible to optimize/merge this kind of query scenario?
Hi,
I have 3 queries and they are doing the exact same thing other than one difference (The TextSubscribed column).
Seems odd to do 3 different ones when all I'm trying to do is know how many of each.
Can the code be combined to query once and somehow break it out to 3 different variables instead of 3 separate requests? I'm just trying to find out how many 'Yes, No and Pendings there are.
$result = mysql_query("SELECT * FROM subscribers WHERE ContestId='$contest_id' AND TextSubscribed = 'Yes'");$subscribed = mysql_num_rows($result);mysql_free_result($result);$result = mysql_query("SELECT * FROM subscribers WHERE ContestId='$contest_id' AND TextSubscribed = 'Pending'");$pending = mysql_num_rows($result);mysql_free_result($result);$result = mysql_query("SELECT * FROM subscribers WHERE ContestId='$contest_id' AND TextSubscribed = 'No'");$unsubscribed = mysql_num_rows($result);mysql_free_result($result);
*I'm very much a newbie so if there is a much better way, please don't hesitate to point it out. Thanks.
PHPMySQL Server
Last Comment
darren-w-
8/22/2022 - Mon
Guru Ji
You can just do one statement to show the count of all Yes, No and Pending like this
$result = mysql_query("SELECT Count(CASE WHEN TextSubscribed = 'Yes' THEN 1 END) as NumOfYes, Count(CASE WHEN TextSubscribed = 'Pending' THEN 1 END) as NumOfPending, Count(CASE WHEN TextSubscribed = 'No' THEN 1 END) as NumOfNo FROM subscribers WHERE ContestId='$contest_id'");$unsubscribed = mysql_num_rows($result);mysql_free_result($result);
$result = mysql_query("SELECT * FROM subscribers WHERE ContestId='$contest_id' AND TextSubscribed IN ('Yes'","Pending","No");
$subscribed = mysql_num_rows($result);
mysql_free_result($result);
tjyoung
ASKER
I think it must be the way I worded my issue: the solutions seem to give me a solitary 'how many of X' but they don't give me: how many Yes, how many No and how many Pendings.
I'm trying to figure out how many of each of the 3 possible answers.
Select subscribers , count(subscibers) where ContestId='$contest_id' AND TextSubscribed IN ('Yes'","Pending","No");
tjyoung
ASKER
@darren-w-
Within my original question, if I was to run those 3 queries, I would have ended up with a count for how many of each (numbers are just for example):
$subscribed = 8
$unsubscribed = 4
$pending = 2
Problem was it seemed kind of redundant to run 3 queries on the same table one after the other to get a value for each
I'm trying to figure out how many of EACH "Yes, No and Pending" there are and assign each to a variable so I can output to my page.
Kind of like:
"Last week you had <php echo $subscribed> subscribers, <?php echo $unsubscribed?> people unsubscribed and there are still <?php echo $pending?> people who are pending.
@darren-w-
I appreciate your efforts but the code simply failed. I noticed a number of quotation errors that I think I fixed but nevertheless couldn't get anything meaningful out of it. Maybe it was just me.
tjyoung
ASKER
Worked out of the box. Thanks!
darren-w-
Hi,
This is the corrected code:
SELECT TextSubscribed, COUNT( * ) AS countFROM subscribersWHERE `ContestId` = $contest_idAND `TextSubscribed` IN ( "Yes", "pending", "No")
Open in new window