PHP variable variables with mysql resultset

Question

So I have a mysql table which has a id column (sid) that can be dynamically added to. Now I am doing a query to get count of all entries grouped into each of these sid's like so:

SELECT SUM(IF(sid = 71, 1, 0)) as count71,
SUM(IF(sid = 72, 1, 0)) as count72,
SUM(IF(sid = 75, 1, 0)) as count75,
SUM(IF(sid = 81, 1, 0)) as count85
FROM `table`
WHERE userid=44;

Note that all the SUM fields are created dynamically and I don't know what the actual sid's are. Now question is how do I loop through the resultset for each of the counts using PHP's variable variables? I've tried this but doesn't work.

$count_var = "rs['count71']"
$$count_var // holds number of entries where sid is 71;

Thanks!

troelskn · Accepted Answer · 2012-04-30 05:54:50Z

3

May I suggest this instead:

select sid, count(*) from `table` where userid = 44 group by sid ;

answered Apr 30, 2012 at 5:54

troelskn

118k27 gold badges135 silver badges156 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

user1181950 Over a year ago

Doh! that would solve my problem indeed. But just for knowledge's sake, how would I achieve the above if I were to design the MySQL query as it is above?

eggyal Over a year ago

@user1181950: One way would be to first perform SELECT DISTINCT sid FROM `table` then, in PHP, loop over the results constructing your SQL as you had been above.

troelskn Over a year ago

I believe you are asking about this: $sid = 71; $count_of_71 = $rs["count" . $sid];. No need to use eval here.

user1181950 Over a year ago

ok my use case above is quite bad; I apologize. I am using a framework, and it returns the resultset as an array of classes with the fields as memebers, so I'd have to access it as $rs[0]->count71. In that case, is eval the only option?

troelskn Over a year ago

You can then do: $sid = 71; $rs[0]->{"count" . $sid};

|

Sudhir Bastakoti · Accepted Answer · 2012-04-30 05:59:46Z

0

You could do:


$count_var = "rs['count71']";
echo eval('return $'. $count_var . ';');

but of course @troelskn's answer is probably the best solution for what you want

answered Apr 30, 2012 at 5:59

Sudhir Bastakoti

100k15 gold badges163 silver badges169 bronze badges

1 Comment

user1181950 Over a year ago

Yes but this is definitely helpful for my knowledge. Thanks!

Collectives™ on Stack Overflow

PHP variable variables with mysql resultset

2 Answers 2

6 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related