CODEWITHSUNDEEP
phpmysql
user1001176
user1001176

Reputation: 1165

mysql results getting repeated when comparing with the results of loop

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
                            }
                            }
                            }
                                    ?>

Upvotes: 0

Views: 103

Answers (1)

Joachim Isaksson
Joachim Isaksson

Reputation: 181047

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.

Upvotes: 1

Related Questions