SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    Almeaty Member
    Join Date
    Jul 2000
    Location
    Nowhere
    Posts
    278
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Checking if integer is contained in a column?

    Hello,

    I'm searching for a specific MySQL query command with which the script can check if a number is contained in a column or not. I have a forum that allows multiple categories for topics, so I'm storing the category integers in a single column, seperated by comma (like this: 1,4,7)

    So when calling up the page it looks e.g. this way:
    /forum.php?category=3

    After trying with LIKE '%category%' without success (or with wrong results) I hope to find some helpful advice here. Is this the best method I'm doing to allow multiple categories?

    Thanks

  2. #2
    SitePoint Addict zoordaan's Avatar
    Join Date
    Feb 2001
    Location
    NYC/Texas
    Posts
    348
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The IN operator should work

    expr IN (value,...)
    Returns 1 if expr is any of the values in the IN list, else returns 0. If all values are constants, then all values are evaluated according to the type of expr and sorted. The search for the item is then done using a binary search. This means IN is very quick if the IN value list consists entirely of constants. If expr is a case-sensitive string expression, the string comparison is performed in case-sensitive fashion:
    mysql> select 2 IN (0,3,5,'wefwf');
    -> 0
    mysql> select 'wefwf' IN (0,3,5,'wefwf');
    -> 1

    http://mysql.com/doc/C/o/Comparison_Operators.html

  3. #3
    Almeaty Member
    Join Date
    Jul 2000
    Location
    Nowhere
    Posts
    278
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi, I looked at the URL you provided... but in that query "mysql> select 'wefwf' IN (0,3,5,'wefwf');" what is the column's name and what is the value it's querying for? Sorry for asking but it's hard for me to 'encode' it. Maybe you can post an easier query string for me

  4. #4
    SitePoint Addict zoordaan's Avatar
    Join Date
    Feb 2001
    Location
    NYC/Texas
    Posts
    348
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Actually I don't thinK IN will work right away, because you have to specify the values. I believe you could use it with two querys:

    Code:
    <?php
    //connect to db
    
    $query1= mysql_query("Select categorylist from forums where forum_id=3");
    
    while ($cats= mysql_fetch_array($query1)) {
    
    $catlist=$cats['categorylist'];
    
    $query2= mysql_query("Select category_name from category where id= '$findcatid' 
    and '$findcatid' IN (.$catlist.)");
    
    while ($catname= mysql_fetch_array($query2)) {
    
    $cname=$catname['category_name'];
    echo $cname;
    }
    
    }
    ?>
    Or there could be another way.
    Last edited by zoordaan; Nov 5, 2001 at 16:28.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •