Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.
Course Of The Month
7 days, 10 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
Possible to optimize/merge this kind of query scenario?
Posted on 2013-01-07
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.
*I'm very much a newbie so if there is a much better way, please don't hesitate to point it out. Thanks.
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.
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
5-
4
2
11 Comments
Active today
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)
$subscribed = mysql_num_rows($result);
mysql_free_result($result)
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.
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");
@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.
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.
Active today
<?php
$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'");
if (!$result) {
echo 'Could not find any data: ' . mysql_error();
exit;
}
$row = mysql_fetch_row($result);
echo $row[0]; // Number of subscribed
echo $row[1]; // Number of pending
echo $row[2]; // Number of unsubscribed
?>
Now you have all three Yes, Pending and No in a php variable to be used anywhere in your code.
Hope thats what you wanted
$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'");
if (!$result) {
echo 'Could not find any data: ' . mysql_error();
exit;
}
$row = mysql_fetch_row($result);
echo $row[0]; // Number of subscribed
echo $row[1]; // Number of pending
echo $row[2]; // Number of unsubscribed
?>
Now you have all three Yes, Pending and No in a php variable to be used anywhere in your code.
Hope thats what you wanted
What does the query produce
Not tested but expect something like
Array(
Yes=>4,
No=>6,
Pending=>7
);
Not tested but expect something like
Array(
Yes=>4,
No=>6,
Pending=>7
);
@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.
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.
Hi,
This is the corrected code:
Dump of db:
with contest id = 1
TextSubscribed count
no 2
pending 1
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
Featured Post
Synthetic Transaction Monitoring Vs. Real User Monitoring: When To Use Each Approach? In this article, we will discuss two major monitoring approaches: Synthetic Transaction and Real User Monitoring.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
3 proven steps to speed up Magento powered sites. The article focus is on optimizing time to first byte (TTFB), full page caching and configuring server for optimal performance.
Many old projects have bad code, but the budget doesn't exist to rewrite the codebase. You can update this code to be safer by introducing contemporary input validation, sanitation, and safer database queries.
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions.
Consider email address RFCs:
Look at HTML5 form input element (with type=email) regex pattern:
T…
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
Suggested Courses
Course of the Month7 days, 10 hours left to enroll
729 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.