Avatar of tjyoung
tjyoung
 asked on

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);

Open in new window


*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

Avatar of undefined
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);

Open in new window

darren-w-

$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.
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
darren-w-

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.
ASKER CERTIFIED SOLUTION
Guru Ji

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
darren-w-

What does the query produce

Not tested but expect something like
Array(
Yes=>4,
No=>6,
Pending=>7
);
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
tjyoung

ASKER
@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 count
FROM subscribers
WHERE  `ContestId` = $contest_id
AND  `TextSubscribed` 
IN (
 "Yes",  "pending",  "No"
)

Open in new window


Dump of db:

CREATE TABLE IF NOT EXISTS `subscribers` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `ContestId` int(11) unsigned NOT NULL,
  `TextSubscribed` varchar(30) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=7 ;


INSERT INTO `subscribers` (`id`, `ContestId`, `TextSubscribed`) VALUES
(1, 3, 'yes'),
(2, 3, 'pending'),
(3, 1, 'no'),
(4, 1, 'pending'),
(5, 2, 'pending'),
(6, 1, 'no');

Open in new window


with contest id = 1

TextSubscribed  count
no            2
pending   1
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
darren-w-

write2mohit, are you able to explain how yours differs?, Was on my mobile last night so was not able to test it out