Welcome to WebmasterWorld Guest from

Forum Moderators: open

Message Too Old, No Replies

mysql user variables (in the same statement)



4:19 pm on Aug 19, 2011 (gmt 0)

5+ Year Member

According to mysql's documentation, you're not supposed to use user variables (@var_name) in the same statement because of undefined processing order of the variables.

See: [dev.mysql.com ]

I've done a query that does this. It seems ok, but I'm worried. Basically I'm pulling back a list of email blasts and for each one I'm showing number of emails sent, email opened, and opened percentage.

To obtain the value for emails sent and emails opened i have do a subquery for each. then i do the math on those two results to obtain the percentage. I also want to be able to order by any of these three values. This means I have to store the results for the subqueries per row so i can create a percentage field.

So here is an example of my statement:

@sent := (SELECT COUNT(*) FROM emails WHERE emails.blast_id = blasts.id AND status = 'sent') AS sent,
@opened := (SELECT COUNT(*) FROM emails WHERE emails.blast_id = blasts.id AND status = 'sent' AND opened = '1') AS opened,
(@opened / @sent * 100) AS percent,
FROM blasts

Do I really have to worry about the processing order here? Am I going about this the entire wrong way?

brotherhood of LAN

4:24 pm on Aug 19, 2011 (gmt 0)

WebmasterWorld Administrator brotherhood_of_lan is a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month

You could alternatively LEFT JOIN on emails, use COUNT(*) to get the equivalent @sent,@opened values and GROUP BY blasts.id

Ordering on any row will be fine.


6:33 pm on Aug 19, 2011 (gmt 0)

5+ Year Member

Thanks for your suggestion. I did a few tests and found this method is the fastest and provides the best caching results over the user variable method and doing subqueries instead of the join.

The only downside is I still have to repeat the bits of logic in the calculation for percentage, but its not that big of a deal with the help of PHP

COUNT(IF(emails.status = 'sent', 1, NULL)) AS sent,
COUNT(IF(emails.opened = '1', 1, NULL)) AS opened,
(COUNT(IF(emails.opened = '1', 1, NULL)) / COUNT(IF(emails.status = 'sent', 1, NULL)) * 100) AS PERCENT

Featured Threads

Hot Threads This Week

Hot Threads This Month