homepage Welcome to WebmasterWorld Guest from 54.234.147.84
register, free tools, login, search, subscribe, help, library, announcements, recent posts, open posts,
Subscribe to WebmasterWorld

Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderators: physics

Databases Forum

    
mysql user variables (in the same statement)
noyearzero




msg:4353493
 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:

SELECT
blasts.id,
name,
@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
ORDER BY
percent

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

 

brotherhood of LAN




msg:4353495
 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.

noyearzero




msg:4353554
 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

SELECT
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