homepage Welcome to WebmasterWorld Guest from
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member

Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

mysql user variables (in the same statement)

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

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)

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)

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

Global Options:
 top home search open messages active posts  

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved