noyearzero - 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,
Do I really have to worry about the processing order here? Am I going about this the entire wrong way?