mysql results getting repeated when comparing with the results of loop

Question

i am have user options stored in database separated by comma and i have the full list of options from which user selected these options . i am trying to get the options user selected then separate it breaking each value after comma and then compare it with full options and make the users option checked out of all the options using a check box. and keep the rest of he options unchecked . but the problem is my results are getting repeated . this is my code

function 

     get_religion_pref($page,$user_id)

        {
            $getrow=mysql_query("select * from religion");
            while($results=mysql_fetch_array($getrow))
            {
                $main_values=$results['religion'];
                $query=mysql_query("select * from partner_prefrences where uid=$user_id");
                    $row=mysql_fetch_array($query);

                    $string =$row['religion'];
                    $string = preg_replace('/\.$/', '', $string); //Remove dot at end if exists                                                       

                   $array = explode(',', $string); //split string into array seperated by ', '
                  foreach($array as $value) //loop over values
                        {
                        ?>
                        <li>
                            <label class="checkbox">
                            <input type="checkbox" value="<?php echo $main_values; ?>" 
                     <?php if($main_values==$value){?> checked <?php } ?>><?php echo $main_values; ?>

                            </label>
                            </li>
                            <?php
                            }
                            }
                            }
                                    ?>

They are repeated as you recieve one and the same result for every checkbox? It would be because your second fetch is not iterated, and you retrieve only the first row, but multiple times because of the other loops where it's put — Ivan Yonkov
– Ivan Yonkov, Commented May 5, 2013 at 7:14

Joachim Isaksson · Accepted Answer · 2013-05-05 07:35:41Z

1

Since you're having trouble with the loops, why not just do the entire thing in MySQL instead of making multiple queries;

SELECT r.id, r.religion,IFNULL(p.uid,0) selected
FROM religion r
LEFT JOIN partner_preferences p
  ON p.uid=1 
 AND CONCAT(',',p.religion,',') 
   LIKE CONCAT('%,',r.religion,',%');

An SQLfiddle to test with.

This will return a row for each religion, using a LEFT JOIN to see if it's listed in the user's religion list.

What you really should do though is to not store comma separated values in a column, and instead break the religion list out into a separate table. That will allow the database to do quite a bit more efficient queries against the data. The query above does not use any indexes for the string matching, and that may become a problem in the future if the number of religions grows.

answered May 5, 2013 at 7:35

Joachim Isaksson

182k28 gold badges298 silver badges308 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user1001176 Over a year ago

thanks for this solution through mysql itself but i want other options too so that user can again choose from rest of options and there would be his previous options pre selected in the checkbox with the left option to be selected further

Joachim Isaksson Over a year ago

@user1001176 What is missing from the query result to solve that? It'll list all religions, so you get a complete list of the options, and it will show which are selected by the user and should be checked.

user1001176 Over a year ago

thanks there was a spelling mistake i fixed it and used your solution it worked like a charm thank you

Collectives™ on Stack Overflow

mysql results getting repeated when comparing with the results of loop

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related