This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.
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.
*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.
Asked:
5-
4
2
1 Solution
$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.
<?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
Question has a verified solution.
Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.
Have a better answer? Share it in a comment.
Join & Write a Comment Already a member? Login.
Featured Post
Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.
One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.
Tackle projects and never again get stuck behind a technical roadblock.
Join Now
Open in new window