Link to home
Start Free TrialLog in
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.
Avatar of Guru Ji
Guru Ji
Flag of Canada image

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

$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);
Avatar of tjyoung
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");
Avatar of 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
Avatar of Guru Ji
Guru Ji
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
What does the query produce

Not tested but expect something like
Array(
Yes=>4,
No=>6,
Pending=>7
);
Avatar of 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.
Avatar of tjyoung

ASKER

Worked out of the box. Thanks!
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
write2mohit, are you able to explain how yours differs?, Was on my mobile last night so was not able to test it out