[SOLVED] multiple query display ... cant get the php to make this work ..

[imarockstar]

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 ?

 

[wildteen88]

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>';
}

[imarockstar]

holy crap what just happened lol ..

 

whats da ? Im confused ..

[imarockstar]

oh wait ... ok you are returning all that in an array ... ok I will try to figure this out ...

[imarockstar]

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 ?

 

 

[imarockstar]

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 ..

[wildteen88]

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

[imarockstar]

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 ..

[imarockstar]

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 ...

 

[wildteen88]

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)

 

 

[imarockstar]

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

[imarockstar]

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 ?

[wildteen88]

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

[imarockstar]

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 ..

[imarockstar]

did you look at the page to see what i was talking about ?

 

http://franklinspirko.com/sites/questions/view_answers.php

 

 

[imarockstar]

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 ..

[wildteen88]

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

[imarockstar]

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 ..

 

 

 

[wildteen88]

How are you using the code I suggested? Post all your code in full here

[imarockstar]

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     
}

?>