Possible to optimize/merge this kind of query scenario?
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.
$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);
$subscribed = mysql_num_rows($result);
mysql_free_result($result)
I'm trying to figure out how many of each of the 3 possible answers.
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.
Not tested but expect something like
Array(
Yes=>4,
No=>6,
Pending=>7
);
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.
This is the corrected code:
SELECT TextSubscribed, COUNT( * ) AS count
FROM subscribers
WHERE `ContestId` = $contest_id
AND `TextSubscribed`
IN (
"Yes", "pending", "No"
)
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');
with contest id = 1
TextSubscribed count
no 2
pending 1
Gain unlimited access to on-demand training courses with an Experts Exchange subscription.
Get AccessWhy Experts Exchange?
Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.
When asked, what has been your best career decision?
Deciding to stick with EE.
Being involved with EE helped me to grow personally and professionally.
Did You Know?
We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE
Ask ANY Question
Connect with Certified Experts to gain insight and support on specific technology challenges including:
- Troubleshooting
- Research
- Professional Opinions
Join our community and discover your potential
Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.
*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.