ridiculous Posted April 3, 2007 Share Posted April 3, 2007 Hello. I am attempting to join multiple select statements into a single query. However, I am not able to extract the result of any of the UNIONED selects. $query1 = mysql_query (" ( SELECT SUM(deposits) AS total_deposits FROM table1 ) UNION ALL ( SELECT SUM(withdrawals) AS total_withdrawals FROM table1 ) ") or die (mysql_error()); $result1 = mysql_fetch_array($query1); extract($result1); When I echo the results of the second select statement I get nothing. echo $total_withdrawals; Quote Link to comment Share on other sites More sharing options...
dough boy Posted April 3, 2007 Share Posted April 3, 2007 Try: SELECT SUM(deposits) AS deposits, w.withdrawls FROM table1, (SELECT SUM(withdrawls) AS withdrawls FROM table1) AS w Quote Link to comment Share on other sites More sharing options...
ridiculous Posted April 3, 2007 Author Share Posted April 3, 2007 Thanks, but I don't think your code came through in its entirety. Quote Link to comment Share on other sites More sharing options...
dough boy Posted April 3, 2007 Share Posted April 3, 2007 Nope. That is the query that you should run. It should return 2 fields, deposits, and withdrawls. What happens when you run it? Quote Link to comment Share on other sites More sharing options...
ridiculous Posted April 3, 2007 Author Share Posted April 3, 2007 Darn. When I echo the first variable (deposits), I get a result. When I echo the second variable (withdrawals), I get nothing. When I reverse the order of the code, I receive the same result. I'm running MySQL 5 and PHP 5. Any thoughts on a test I could run? Its definitely in the way I've formed the UNION. Quote Link to comment Share on other sites More sharing options...
dough boy Posted April 3, 2007 Share Posted April 3, 2007 try print_r($result1) and what do you get? Quote Link to comment Share on other sites More sharing options...
btherl Posted April 3, 2007 Share Posted April 3, 2007 Since both queries are on the same table, you should be able to use this: SELECT SUM(deposits) AS total_deposits, SUM(withdrawals) AS total_withdrawals FROM table1 There's no need for unions or subqueries. Union is for a different situation - it's for when you have some rows from table 1 and some more rows from table2 which look the same, and you want to merge them into one huge blob of rows. Quote Link to comment Share on other sites More sharing options...
ridiculous Posted April 3, 2007 Author Share Posted April 3, 2007 That's really interesting. Here's what I get: Array ( [0] => 7025.23 [total_deposits] => 7025.23 ) Pretty cool trick, that print idea. Now I'm just wondering how to analyze this result. Quote Link to comment Share on other sites More sharing options...
dough boy Posted April 3, 2007 Share Posted April 3, 2007 btherl is correct. What I gave you is if you have 2 different tables. Quote Link to comment Share on other sites More sharing options...
ridiculous Posted April 3, 2007 Author Share Posted April 3, 2007 Thanks guys. Since my usage was all wrong, would you guys have any advice on how to join these separate select statements into one mysql_query? Here is my code. $query1 = mysql_query (" ( SELECT SUM(deposits) AS total_deposits ) UNION ALL ( SELECT SUM(withdrawals) AS total_withdrawals ) UNION ALL ( SELECT EOD_Equity AS EOD_Equity_at_inception WHERE record=(Select MIN(record) from superfund_historicals) ) UNION ALL ( SELECT EOD_Equity AS EOD_Equity_now WHERE record = (select Max(record) from superfund_historicals) ) UNION ALL ( SELECT EOD_Equity AS EOD_Equity_tminus1 WHERE record = (select Max(record)-1 from superfund_historicals) ) ") or die (mysql_error()); $result1 = mysql_fetch_array($query1); //get a row from our result set extract($result1); Quote Link to comment Share on other sites More sharing options...
dough boy Posted April 3, 2007 Share Posted April 3, 2007 If they share the same common id, then 1 query should be able to do it. But if you have 2 tables, 1 for deposits, and 1 for withdrawls, then you should theoretically have to do what I gave you. Quote Link to comment Share on other sites More sharing options...
ridiculous Posted April 3, 2007 Author Share Posted April 3, 2007 Ok I'm working on it. I'll post what I come up with and see what you think. Quote Link to comment Share on other sites More sharing options...
ridiculous Posted April 3, 2007 Author Share Posted April 3, 2007 Here's what I came up with. I get an error in the 3rd paragraph: EOD_Equity AS EOD_Equity_at_inception WHERE record=(Select MIN(record) from superfund_historicals), $query1 = mysql_query (" SELECT SUM(deposits) AS total_deposits, SUM(withdrawals) AS total_withdrawals, ----> EOD_Equity AS EOD_Equity_at_inception ----> WHERE record=(Select MIN(record) ----> from superfund_historicals), EOD_Equity AS EOD_Equity_now WHERE record = (select Max(record) from superfund_historicals), EOD_Equity AS EOD_Equity_tminus1 WHERE record = (select Max(record)-1 from superfund_historicals) FROM superfund_historicals ") or die (mysql_error()); $result1 = mysql_fetch_array($query1); //get a row from our result set extract($result1); Of course, it does work just fine for this: $query1 = mysql_query (" SELECT SUM(deposits) AS total_deposits, SUM(withdrawals) AS total_withdrawals, FROM superfund_historicals ") or die (mysql_error()); $result1 = mysql_fetch_array($query1); //get a row from our result set extract($result1); ...but I've got to use a where statement. Quote Link to comment Share on other sites More sharing options...
ridiculous Posted April 3, 2007 Author Share Posted April 3, 2007 I also tried this, but it doesn't seem to work: $query1 = mysql_query (" SELECT SUM(deposits) AS total_deposits FROM superfund_historicals, SUM(withdrawals) AS total_withdrawals FROM superfund_historicals, EOD_Equity AS EOD_Equity_at_inception FROM superfund_historicals WHERE record=(Select MIN(record) from superfund_historicals), EOD_Equity AS EOD_Equity_now FROM superfund_historicals WHERE record = (select Max(record) from superfund_historicals), EOD_Equity AS EOD_Equity_tminus1 FROM superfund_historicals WHERE record = (select Max(record)-1 from superfund_historicals) ") or die (mysql_error()); $result1 = mysql_fetch_array($query1); //get a row from our result set extract($result1); Quote Link to comment Share on other sites More sharing options...
btherl Posted April 3, 2007 Share Posted April 3, 2007 Can I ask the obvious question.. why are you merging your queries instead of using seperate queries? The difficulty in merging the queries you've given are that each one has different "where" conditions. And they have outputs of a different nature as well. Given those two facts, I don't see why you would want to merge them. The other argument against merging the queries is that you would replace several simple queries with one complex one. A complex query is much more difficult to debug. Quote Link to comment Share on other sites More sharing options...
ridiculous Posted April 3, 2007 Author Share Posted April 3, 2007 To be honest, I've learned PHP in bits and pieces...and looking at my 5 separate MySQL queries simply didn't look right. Also, I am under the impression that running a bunch of separate queries is less desirable than bundling them into one mysql_query call. Tell me if I'm wrong. $query1 = mysql_query ("SELECT SUM(deposits) AS totalin, SUM(withdrawals) AS totalout FROM superfund_historicals ;") or die (mysql_error()); $result1 = mysql_fetch_array($query1); //get a row from our result set extract($result1); $netdeposits = $totalin - $totalout; // // // //-----------------EQUITY OF COMPOSITE FUND AT INCEPTION ($epoch) $query2 = mysql_query ("SELECT * FROM superfund_historicals WHERE record=(Select MIN(record) from superfund_historicals) ;") or die (mysql_error()); $result2 = mysql_fetch_array($query2); //get a row from our result set extract($result2); $epoch = $EOD_equity; // // // //-----------------EQUITY OF FUND AT END OF TODAY INCLUDES YESTERDAYS DEPOSITS // WITHDRAWALS BUT BACKS OUT THOSE FROM TODAY ($t0) $query3 =mysql_query ("SELECT * FROM superfund_historicals WHERE record = (select Max(record) from superfund_historicals);") or die (mysql_error()); $result3 = mysql_fetch_array($query3); //get a row from our result set extract($result3); $t0=$EOD_equity-$deposits+$withdrawals; $date_of_t0 = $date; // // // //-----------------EQUITY OF FUND AT END OF YESTERDAY/INCLUDES DEPOSITS,WITHDRAWALS ($tminus1) $query4 =mysql_query ("SELECT * FROM superfund_historicals WHERE record = (select Max(record)-1 from superfund_historicals);") or die (mysql_error()); $result4 = mysql_fetch_array($query4); extract($result4); It just looks wrong. Would you do your code like this? Quote Link to comment Share on other sites More sharing options...
btherl Posted April 3, 2007 Share Posted April 3, 2007 It depends what you mean by "less desirable" A single query is probably more efficient. Multiple queries are more simple. When given a choice between efficiency and simplicity, I almost always go for simplicity. Simple code means fewer bugs, and if you're dealing with peoples' investments, less bugs is VERY important. Much more important than having your code run a little bit faster. The way the code is written in your latest post is how I would do it. Every query is accompanied by a description of what it does, and every query is simple to understand. That to me is very good code. There is one situation that I would change it to a single query, and that is if the system runs too slowly AND it is not feasible to purchase better hardware. It also depends on the cost of your time vs the cost of better hardware. If your time is cheap, then by all means spend more time optimizing the code and more time maintaining the optimized query. But if your time is valuable, you may find it cheaper to buy a faster machine and leave the code as it is. Quote Link to comment Share on other sites More sharing options...
ridiculous Posted April 3, 2007 Author Share Posted April 3, 2007 Frankly, I'm surprised by your response. I am not too familiar with putting SELECT statements together and have been very surprised to see that I can't call something like $unified_query = mysql_query ("SELECT ----> FROM TABLE (EOD_equity AS starting_equity WHERE record=(Select MIN(record) from superfund_historicals)), (date AS date_t_0, EOD_equity AS t_0, deposits AS daily_deposits, withdrawals AS daily_withdrawals WHERE record = (select Max(record) from superfund_historicals)) ;") or die (mysql_error()); $result2 = mysql_fetch_array($query2); //get a row from our result set extract($result2); Or something to this effect, where you simple call multiple selects on the same table (but with results having different columns) under one mysql_query. I guess you can't, because I haven't found documentation to this affect anywhere. Quote Link to comment Share on other sites More sharing options...
ridiculous Posted April 3, 2007 Author Share Posted April 3, 2007 Nevertheless, thanks for all the help guys. I really appreciate it. Quote Link to comment Share on other sites More sharing options...
btherl Posted April 3, 2007 Share Posted April 3, 2007 It's not really something you would want to do normally. If you want to combine related data together then you can use joins. And if you want to combine similar result sets into one result set you would use union. But combining several unrelated (in the sense of how they are calculated) data items into one result set is just not "logical" in SQL. You an look at an SQL query this way. It starts with an entire table or tables: ------- ------- ------- Then it uses the "where" condition to pick certain elements from that - - - - Then it either returns those items as they are (as many rows), or aggregates them, such as taking a sum or count. When you have different "where" conditions in one query, there's no natural way for SQL to deal with that. Usually it chooses items based on the "where", and then operates on them. But if you have different conditions for each of your different outputs, then which conditions does it use? Or does it use each condition in turn, starting from the full set of data each time (which is basically the same as doing individual queries) ? So you might as well use several queries, each with its own "where" condition. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.