imarockstar Posted August 11, 2009 Share Posted August 11, 2009 I am trying to display 2 different mysql quiries together .. but I am getting nothing ... <?php // 1 $result = mysql_query("SELECT _group, weight, SUM(desa_answer) AS total FROM des_answers GROUP BY _group ORDER BY _group ASC"); $result2 = mysql_query("SELECT maxscore FROM des INNER JOIN des_answers ON des._group = des_answers._group ") or die(mysql_error()); while($row = mysql_fetch_assoc($result) | $row2 = mysql_fetch_assoc($result2)) { ?> <div class='group'><?=$row['_group']?></div> <div class='valuesum'><?=$row['total']?></div> <div class='valuesum'><?=$row2['maxscore']?>z</div> <div class='group'><?=$row['weight']?></div> <br class="clear"> <?php // 1 } ?> if you cant see what i am trying to do then I can try to explain it .. but I think you will get it based on the above code .. do i need to do a WHILE loop inside the current WHILE loop ? Quote Link to comment https://forums.phpfreaks.com/topic/169810-solved-multiple-query-display-cant-get-the-php-to-make-this-work/ Share on other sites More sharing options...
wildteen88 Posted August 11, 2009 Share Posted August 11, 2009 You should just use one query $sql = 'SELECT da._group, da.weight, SUM(da.desa_answer) AS total, d.maxscore FROM des_answers da INNER JOIN des d ON d._group = da._group GROUP BY da._group ORDER BY da._group ASC'; $result = mysql_query($sql); while($row = mysql_fetch_assoc($result)) { echo '<pre>' . print_r($row, true) . '</pre>'; } Quote Link to comment https://forums.phpfreaks.com/topic/169810-solved-multiple-query-display-cant-get-the-php-to-make-this-work/#findComment-895815 Share on other sites More sharing options...
imarockstar Posted August 11, 2009 Author Share Posted August 11, 2009 holy crap what just happened lol .. whats da ? Im confused .. Quote Link to comment https://forums.phpfreaks.com/topic/169810-solved-multiple-query-display-cant-get-the-php-to-make-this-work/#findComment-895817 Share on other sites More sharing options...
imarockstar Posted August 11, 2009 Author Share Posted August 11, 2009 oh wait ... ok you are returning all that in an array ... ok I will try to figure this out ... Quote Link to comment https://forums.phpfreaks.com/topic/169810-solved-multiple-query-display-cant-get-the-php-to-make-this-work/#findComment-895818 Share on other sites More sharing options...
imarockstar Posted August 11, 2009 Author Share Posted August 11, 2009 ok so looks like its adding the sum of both tables .. and I am getting like some huge number ... I only need it to add the sum in the des_answers table. its adding the sum of the rows 'desa_aswers' that we grouped .. but it seams to be multiplying that by some number ... not sure why ... i dont understand your code really. but it works for the most part .. its just adding the numbers up wrong any ideas ? Quote Link to comment https://forums.phpfreaks.com/topic/169810-solved-multiple-query-display-cant-get-the-php-to-make-this-work/#findComment-895829 Share on other sites More sharing options...
imarockstar Posted August 11, 2009 Author Share Posted August 11, 2009 ok here is the page .... http://franklinspirko.com/sites/questions/view_answers.php the top is the right output .. the 'Sum Of Values' is what it should be .. and then the arrays at the bottom is what you gave me .. you can see that its multiplying it .. Quote Link to comment https://forums.phpfreaks.com/topic/169810-solved-multiple-query-display-cant-get-the-php-to-make-this-work/#findComment-895830 Share on other sites More sharing options...
wildteen88 Posted August 11, 2009 Share Posted August 11, 2009 Umm, I tested the query and it works fine. I setup four records in the des_awnsers table like, so id | _group | weight | desa_answer 1 | 115 | 5 | 10 2 | 115 | 5 | 20 3 | 135 | 5 | 20 5 | 135 | 5 | 30 And the des table like so id | _group | maxscore 1 | 115 | 30 2 | 135 | 50 The result I got from the query id | _group | weight | total | maxscore 1 | 115 | 5 | 30 | 30 2 | 135 | 5 | 50 | 50 Quote Link to comment https://forums.phpfreaks.com/topic/169810-solved-multiple-query-display-cant-get-the-php-to-make-this-work/#findComment-895840 Share on other sites More sharing options...
imarockstar Posted August 11, 2009 Author Share Posted August 11, 2009 ok it looks like its adding up all the rows, like it is supposed to . then multiplying that number by the number of rows .. desa_id userid qid desa_answer weight qvalue _group 723 297866 355 5 355 696 297866 360 4 0 0 355 697 297866 370 2 0 0 355 698 297866 380 4 0 0 355 699 297866 381 5 0 0 355 700 297866 390 4 0 0 355 701 297866 400 5 0 0 355 702 297866 410 5 0 0 355 703 297866 420 5 0 0 355 704 297866 430 2 0 0 355 705 297866 440 6 0 0 355 706 297866 450 4 0 0 355 707 297866 460 4 0 0 355 708 297866 470 6 0 0 355 709 297866 480 2 0 0 355 710 297866 490 6 0 0 355 711 297866 500 2 0 0 355 712 297866 510 5 0 0 355 713 297866 520 2 0 0 355 714 297866 530 test 0 0 355 715 297866 540 1 0 0 355 716 297866 550 test 0 0 355 717 297866 560 2 0 0 355 so its adding up all the DESA_ANSWER rows up grouped by 355. but then it is multyplying that by 23 which is th enumber or total rows with the _group of 355 .. Quote Link to comment https://forums.phpfreaks.com/topic/169810-solved-multiple-query-display-cant-get-the-php-to-make-this-work/#findComment-895852 Share on other sites More sharing options...
imarockstar Posted August 11, 2009 Author Share Posted August 11, 2009 i am confused here .... $sql = 'SELECT da._group, da.weight, SUM(da.desa_answer) AS total, d.maxscore FROM des_answers da INNER JOIN des d ON d._group = da._group GROUP BY da._group ORDER BY da._group ASC'; where did 'da' and 'd' come from ... Quote Link to comment https://forums.phpfreaks.com/topic/169810-solved-multiple-query-display-cant-get-the-php-to-make-this-work/#findComment-895853 Share on other sites More sharing options...
wildteen88 Posted August 11, 2009 Share Posted August 11, 2009 da and d a are aliases to des_awnsers and des $sql = 'SELECT da._group, da.weight, SUM(da.desa_answer) AS total, d.maxscore FROM des_answers da INNER JOIN des d ON d._group = da._group GROUP BY da._group ORDER BY da._group ASC'; The blue bits are the table names, the bits in red are the aliases (or shortened names for the tables) Quote Link to comment https://forums.phpfreaks.com/topic/169810-solved-multiple-query-display-cant-get-the-php-to-make-this-work/#findComment-895860 Share on other sites More sharing options...
imarockstar Posted August 11, 2009 Author Share Posted August 11, 2009 where are those set and how does it know ? or are you saying that that is what you used for yours ? sorry, im a fucking idoit sometimes when it comes to this hahah Quote Link to comment https://forums.phpfreaks.com/topic/169810-solved-multiple-query-display-cant-get-the-php-to-make-this-work/#findComment-895862 Share on other sites More sharing options...
imarockstar Posted August 11, 2009 Author Share Posted August 11, 2009 oh .. is this where you are setting the allias : des_answers da and des d ? so everything after that you can just type da and d ? is there a point to doing this other then maybe saving time in typing out long names ? Quote Link to comment https://forums.phpfreaks.com/topic/169810-solved-multiple-query-display-cant-get-the-php-to-make-this-work/#findComment-895866 Share on other sites More sharing options...
wildteen88 Posted August 11, 2009 Share Posted August 11, 2009 When joining tables I always place the table name in front of the field names. This makes the the query neater and easier to understand. I use aliases to shorten the table names, I normally use the tables initials or just the first two characters of the table name when using aliases. Hope that clears things up. I have tested my query again with the sample data you provided and the query does not multiply the sum of desc_awnser by the number of rows. What data type have you set the desc_awnser field to in table scheme? It should be set to INT or something similar Quote Link to comment https://forums.phpfreaks.com/topic/169810-solved-multiple-query-display-cant-get-the-php-to-make-this-work/#findComment-895885 Share on other sites More sharing options...
imarockstar Posted August 11, 2009 Author Share Posted August 11, 2009 i is set to tinytext ... cuz some are strings and some are numbers ... but it works with my original query .. i just wasnt getting the MAXscores outputed .. Quote Link to comment https://forums.phpfreaks.com/topic/169810-solved-multiple-query-display-cant-get-the-php-to-make-this-work/#findComment-895892 Share on other sites More sharing options...
imarockstar Posted August 11, 2009 Author Share Posted August 11, 2009 did you look at the page to see what i was talking about ? http://franklinspirko.com/sites/questions/view_answers.php Quote Link to comment https://forums.phpfreaks.com/topic/169810-solved-multiple-query-display-cant-get-the-php-to-make-this-work/#findComment-895893 Share on other sites More sharing options...
imarockstar Posted August 11, 2009 Author Share Posted August 11, 2009 oh hey .. i see in your test tables that you only have 2 rows per group .. so it would not multiply it.. add like a couple more rows .. to each group you have there .. like ad 2 more to one group and then 3 more to the other ... i bet it multiplies it then .. Quote Link to comment https://forums.phpfreaks.com/topic/169810-solved-multiple-query-display-cant-get-the-php-to-make-this-work/#findComment-895898 Share on other sites More sharing options...
wildteen88 Posted August 11, 2009 Share Posted August 11, 2009 No matter how many entries of the same group in the table I cannot reproduce your issue. Everything is adding up correctly. The only thing I don't understand is in some rows the dsa_awnser column has text and some has numbers. Can you post the code you're using here in full Quote Link to comment https://forums.phpfreaks.com/topic/169810-solved-multiple-query-display-cant-get-the-php-to-make-this-work/#findComment-895910 Share on other sites More sharing options...
imarockstar Posted August 11, 2009 Author Share Posted August 11, 2009 this is the code i use at the top of that page.. <?php // 1 $result = mysql_query("SELECT _group, weight, SUM(desa_answer) AS total FROM des_answers GROUP BY _group ORDER BY _group ASC"); while($row = mysql_fetch_assoc($result) ) { ?> <div class='group'><?=$row['_group']?></div> <div class='valuesum'><?=$row['total']?></div> <div class='valuesum'><?=$row2['maxscore']?>z</div> <div class='group'><?=$row['weight']?></div> <br class="clear"> <?php // 1 } ?> and if you look at that link .. that works fine .. however ... its not pulling the 'maxscore' since i have no tables joined .. Quote Link to comment https://forums.phpfreaks.com/topic/169810-solved-multiple-query-display-cant-get-the-php-to-make-this-work/#findComment-895911 Share on other sites More sharing options...
wildteen88 Posted August 11, 2009 Share Posted August 11, 2009 How are you using the code I suggested? Post all your code in full here Quote Link to comment https://forums.phpfreaks.com/topic/169810-solved-multiple-query-display-cant-get-the-php-to-make-this-work/#findComment-895916 Share on other sites More sharing options...
imarockstar Posted August 11, 2009 Author Share Posted August 11, 2009 ok it worked using this code .. <?php $sql = 'SELECT da._group, da.weight, SUM(da.desa_answer) AS total,(SELECT des.maxscore FROM des WHERE des._group = da._group LIMIT 1 ) as maxscore FROM `des_answers` as da GROUP BY da._group'; $result = mysql_query($sql); while($row = mysql_fetch_assoc($result)) { ?> <bR><br> <div class='group'><?=print_r($row['_group'], true);?></div> <div class='valuesum'><?=print_r($row['total'], true);?></div> <div class='valuesum'><?=print_r($row['maxscore'], true);?></div> <div class='group'><?=print_r($row['weight'], true);?></div> <br class="clear"> <?php } ?> Quote Link to comment https://forums.phpfreaks.com/topic/169810-solved-multiple-query-display-cant-get-the-php-to-make-this-work/#findComment-895921 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.