?
Solved

Possible to optimize/merge this kind of query scenario?

Posted on 2013-01-07
11
Medium Priority
?
216 Views
Last Modified: 2013-01-08
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.
0
Comment
Question by:tjyoung
  • 5
  • 4
  • 2
11 Comments
 
LVL 11

Expert Comment

by:Guru Ji
ID: 38752327
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

0
 
LVL 13

Expert Comment

by:darren-w-
ID: 38752546
$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);
0
 
LVL 1

Author Comment

by:tjyoung
ID: 38752886
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.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 13

Expert Comment

by:darren-w-
ID: 38752928
Select subscribers , count(subscibers) where ContestId='$contest_id' AND TextSubscribed  IN ('Yes'","Pending","No");
0
 
LVL 1

Author Comment

by:tjyoung
ID: 38752966
@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.
0
 
LVL 11

Accepted Solution

by:
Guru Ji earned 2000 total points
ID: 38753005
<?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
0
 
LVL 13

Expert Comment

by:darren-w-
ID: 38753013
What does the query produce

Not tested but expect something like
Array(
Yes=>4,
No=>6,
Pending=>7
);
0
 
LVL 1

Author Comment

by:tjyoung
ID: 38753947
@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.
0
 
LVL 1

Author Closing Comment

by:tjyoung
ID: 38753949
Worked out of the box. Thanks!
0
 
LVL 13

Expert Comment

by:darren-w-
ID: 38754137
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
0
 
LVL 13

Expert Comment

by:darren-w-
ID: 38754145
write2mohit, are you able to explain how yours differs?, Was on my mobile last night so was not able to test it out
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.
Suggested Courses
Course of the Month13 days, 14 hours left to enroll

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question