imarockstar Posted July 22, 2009 Share Posted July 22, 2009 I am not sure that this is a ph question or a mysql question but here it goes ... I am pulling some data from my db, I am doing a JOIN statement. Everything is pulling correctly, all the info is there. However its being repeated 5 times ... There are 4 rows in the table where _group = 95, so its repeating 5 times ... not sure why ... code: <?php $sql2=("SELECT * FROM edu_answers JOIN edu ON edu._group = edu_answers._group WHERE edu_answers._group = '95' "); //$sql2="SELECT * FROM edu WHERE _group = ".$rows['qid']." "; $result2=mysql_query($sql2); while($rows2=mysql_fetch_array($result2)){ ?> <div class='subquestionview'></div> <div class='subquestiontext'> </div> <div class='subquestionweight'><?php echo $rows2['edua_answer']; ?></div> <Br class="clear"> <? } ?> the 5 rows the output repeats is this ... Southern Methodist University Dallas Us 2009 2009 Southern Methodist University Dallas Us 2009 2009 Southern Methodist University Dallas Us 2009 2009 Southern Methodist University Dallas Us 2009 2009 Southern Methodist University Dallas Us 2009 2009 my question is how can i just get it to output the results one time and not 5 ? Quote Link to comment Share on other sites More sharing options...
ignace Posted July 22, 2009 Share Posted July 22, 2009 I am pulling some data from my db, I am doing a JOIN statement. Everything is pulling correctly, all the info is there. However its being repeated 5 times ... There are 4 rows in the table where _group = 95, so its repeating 5 times ... not sure why ... Cartesian product of both tables probably. Wouldn't an outer join suffice? SELECT * FROM edu e LEFT JOIN edu_answers ea ON e._group = ea._group WHERE ea._group = '95' Quote Link to comment Share on other sites More sharing options...
jcrew Posted July 22, 2009 Share Posted July 22, 2009 Err. Just remove the while statement. It'll only show once. Quote Link to comment Share on other sites More sharing options...
WolfRage Posted July 22, 2009 Share Posted July 22, 2009 But you do not need the while. <?php while($rows2=mysql_fetch_array($result2)){ //should be while($rows2==mysql_fetch_array($result2)){ ?> Damn beat to it. Quote Link to comment Share on other sites More sharing options...
ignace Posted July 22, 2009 Share Posted July 22, 2009 Damn beat to it. It's not a contest Quote Link to comment Share on other sites More sharing options...
imarockstar Posted July 22, 2009 Author Share Posted July 22, 2009 i removed the while and nothing shows up ... what does the double = do ? Quote Link to comment Share on other sites More sharing options...
jcrew Posted July 22, 2009 Share Posted July 22, 2009 i removed the while and nothing shows up ... what does the double = do ? Well, you still need to do: $rows2=mysql_fetch_array($result2); Quote Link to comment Share on other sites More sharing options...
imarockstar Posted July 22, 2009 Author Share Posted July 22, 2009 the == .. did not work .. nothing showed up at all Quote Link to comment Share on other sites More sharing options...
imarockstar Posted July 22, 2009 Author Share Posted July 22, 2009 what ? use both .. i am totally confused ??? Quote Link to comment Share on other sites More sharing options...
jcrew Posted July 22, 2009 Share Posted July 22, 2009 what ? use both .. i am totally confused ??? <?php $sql2=("SELECT * FROM edu_answers JOIN edu ON edu._group = edu_answers._group WHERE edu_answers._group = '95' "); //$sql2="SELECT * FROM edu WHERE _group = ".$rows['qid']." "; $result2=mysql_query($sql2); $rows2=mysql_fetch_array($result2); ?> <div class='subquestionview'></div> <div class='subquestiontext'> </div> <div class='subquestionweight'><?php echo $rows2['edua_answer']; ?></div> <Br class="clear"> <? } ?> Try that.. Quote Link to comment Share on other sites More sharing options...
WolfRage Posted July 22, 2009 Share Posted July 22, 2009 Double "=" is for comparison of two values. While is a logical comparison statement, yet you were setting an object. Therefore the outcome would always be true. Quote Link to comment Share on other sites More sharing options...
thebadbad Posted July 22, 2009 Share Posted July 22, 2009 Therefore the outcome would always be true. No. mysql_fetch_array() returns false when there are no more rows, and thus $rows2=mysql_fetch_array($result2) would return false. Quote Link to comment Share on other sites More sharing options...
imarockstar Posted July 22, 2009 Author Share Posted July 22, 2009 that worked but I need to run a loop and display each row with the _group value. i was prob not very clear in my initial posting ... I have a bunch of qiestions and answers .... and they are grouped together by the _group column . .. so i need to display all the answers based on the _group . here is the full code ... <?php $sql=("SELECT * FROM edu_answers INNER JOIN edu ON edu_answers.qid = edu.edu_id WHERE edu_answers.userid = '297869' ORDER BY qid ASC") //$sql=("SELECT * FROM edu_answers INNER JOIN edu ON edu_answers.qid = edu.edu_id WHERE edu_answers.userid = " . $_SESSION['membersInfo']['id'] . " ORDER BY qid ASC ") //297869 or die(mysql_error()); $result=mysql_query($sql); while($rows=mysql_fetch_array($result)){; ?> <?php if ( $rows['edu_question'] != '' ) { ?> <div class='resultholder blue'> <div class='questionview'>[<a href="javascript:unhide('<?=$rows['qid']; ?>');">+</a>]</div> <div class='questiontext'> <?php echo $rows['edu_question']; ?> </div> <!-- #questiontext --> <div class='questionweight'><?=$rows['weight']; ?></div> <br class="clear"> <div id="<?=$rows['qid']; ?>" class="hidden"> <?php $sql2=("SELECT * FROM edu_answers JOIN edu ON edu._group = edu_answers._group WHERE edu_answers._group = '95' "); //$sql2="SELECT * FROM edu WHERE _group = ".$rows['qid']." "; $result2=mysql_query($sql2); $rows2=mysql_fetch_array($result2); ?> <div class='subquestionview'></div> <div class='subquestiontext'> </div> <div class='subquestionweight'><?php echo $rows2['edua_answer']; ?></div> <Br class="clear"> </div> <!-- #qid div --> </div> <!-- #resultholder --> here is my db sceme edua_id userid qid edua_answer weight _group 1 297869 100 Southern Methodist University 1 95 2 297869 110 Dallas 0 95 3 297869 120 Us 0 95 4 297869 130 2009 0 95 5 297869 140 2009 0 95 6 297869 150 Please Choose 0 145 7 297869 160 Please Choose 0 145 8 297869 170 Please Choose 0 145 9 297869 180 Please Choose 0 145 10 297869 190 Please Choose 0 145 11 297869 200 1 195 12 297869 210 0 195 13 297869 220 0 195 14 297869 221 0 195 15 297869 230 0 195 16 297869 240 2009 0 195 17 297869 250 2009 0 195 18 297869 260 1 255 19 297869 261 0 255 20 297869 270 0 255 21 297869 280 0 255 22 297869 290 0 255 23 297869 300 2009 0 255 24 297869 310 2009 0 255 Quote Link to comment Share on other sites More sharing options...
ignace Posted July 22, 2009 Share Posted July 22, 2009 But you do not need the while. <?php while($rows2=mysql_fetch_array($result2)){ //should be while($rows2==mysql_fetch_array($result2)){ ?> Damn beat to it. ==? I believe that has the effect of $rows2=null and the while to fail (unless mysql_fetch_array($result2) returns false). Edit: Oeps a bit to late thebadbad already said so Quote Link to comment Share on other sites More sharing options...
ignace Posted July 22, 2009 Share Posted July 22, 2009 so i need to display all the answers based on the _group . I am not entirely sure if this is the best way but as 1 question has many answers (even on these forums ) you should first retrieve all questions: SELECT * FROM edu WHERE edu._group = 95 And then while you are looping over the questions you retrieve the possible answers: SELECT * FROM edu_answer WHERE edu_answer.qid = $qid But I think their must be a faster, more cleaner way of doing so, maybe? SELECT * FROM edu_answer WHERE edu_answer.qid IN (SELECT id FROM edu WHERE edu._group = 95) AND edu_answer._group = 95 This retrieves all answers for all current questions. But I'm sure it can be even simpler and faster. 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.