How to locate if session var is in comma separated column?

Question

I have comma separated column in my database. That column called 'access' it handles number such as 1,2,3,4,5,6,7. The requirement that I need to compare the value of my session is called kt_department with one of the comma separated value like for example the value of kt_department is 4, then the rest of the data will show in the page. But if not it will proceed to the next row, but if again it is not found it should proceed to the next row until it found its value in comma separated. But if they found nothing, a message like "no available record" will appear. And also the record found should be count.

I tried to wright my own;

<?php 
            $news_connect = mysqli_connect("localhost","root","pwd", "dbase");
            if (mysqli_connect_errno())
                {
                    echo "Failed to connect to MySQL: " . mysqli_connect_error();
                }
            $kt_dept  = $_SESSION['kt_department'] . " ";
            $accessrights = explode(',', $row_news['accessrights']);
            $news_query = "SELECT * FROM memo
                           WHERE (access LIKE '%{$accessrights}%' AND status='1' AND idcategory='1')";
            $news_result = mysqli_query($news_connect, $news_query);
            $row_news = mysqli_fetch_array($news_result);
            echo $kt_dept; 
            ?>
            <div style="margin-top:30px;">
            <table width="98%"  border="0" cellspacing="5" cellpadding="0" class="curveborderboxnobg">
                  <tr>
                    <td height="70" colspan="2" class="brandtitle" style="padding-left:20px;line-height:60px; font-size:18px;"><img src="images/icons/news-announcement.png" width="20" height="20" style="margin-right:10px;"><strong>News and Announcement </strong></td>
                  </tr>
                  <tr>
                    <td height="40" colspan="2" style=" padding:0px 20px 0 20px;">
                    <table width="95%"  border="0" style="margin-left:45px;" class="bortopbot" cellpadding="0" cellspacing="5">
                      <tr>
                        <td height="30" class="tabledetails" style="padding-top:20px;" ><strong><a href="news_bulletin.php?idmemonewsbulletin=<?php echo $row_news['idmemo']; ?>" class="lightbox" data-width="800" data-height="500"><?php echo $row_news['title']; ?></a></strong></td>
                      </tr>
                      <tr>
                        <td class="tabledetails" style="padding-bottom:20px;"><span style="font-size:12px;"><?php echo $row_news['shortdesc']; ?></span></td>
                      </tr>
                    </table>
                    </td>
                  </tr>
                  <tr>
                    <td height="40" class="tabledetails" style="padding-left:10px;">&nbsp; </td>
                    <td class="tabledetails" style="padding-right:20px; ">
                    <table width="263" border="0" align="right" class="tabledetails">
                      <tr>
                        <td align="center"><div align="right"><a href="news_bulletin_list.php?newsbul=<?php echo $row_news['idcategory']; ?>"><strong>View All </strong></a></div></td>
                      </tr>
                    </table>
                    </td>
                  </tr>
              </table>            
            </div>
                <table width="98%"  border="0" cellspacing="5" style="margin-top:30px; " cellpadding="0" class="curveborderboxnobg">
                  <tr>
                    <td height="70" class="brandtitle" style="padding-left:20px;line-height:60px; font-size:18px;"><img src="images/icons/news-announcement.png" width="20" height="20" style="margin-right:10px;"><strong>News and Announcement </strong></td>
                  </tr>
                  <tr>
                    <td height="40" class="bortopbot" style=" padding:0px 20px 0 20px;">
                      <div align="center">- No News and Announcement - </div></td>
                  </tr>
              </table>
              <?php mysqli_close($news_connect); ?>

Thank you guys. Please help me. thanks!

normalize your database and get rid of comma separation first http://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad — Abhik Chakraborty, May 16 '14 at 14:43
You could try `$news_query = "SELECT * FROM memo WHERE (access IN '%{$accessrights}%' AND status='1' AND idcategory='1')";` — dcclassics, May 16 '14 at 14:57

score 0 · Answer 1 · answered May 16 '14 at 15:09

To provide a actual answer to your question (feel free to take note Sharma):

Get rid you the username and password to your database from the question. Even though it's localhost it hurts my eyes.

To answer your question. I would stongly suggest you created a second table with the columns access and kt_dept - or what ever you want to call them. That way you can connect access integers with a kt_dept - meaning one kt_dept has zero to many access.

With that imlementation you would only have to run a query like SELECT kt_dept FROM newTable WHERE access = '{$access}' and check if you get any rows back or not.

How to locate if session var is in comma separated column?

1 Answers1