SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Zealot amit290's Avatar
    Join Date
    Oct 2001
    Posts
    158
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Unhappy wrong records extracted

    PHP Code:
    $query mysql_query("SELECT LinkName_$lang_choice, LinkUrl_$lang_choice FROM is_links
                          WHERE LinkCategoryId LIKE '%
    $linkcatid%'
                          ORDER BY LinkName_
    $lang_choice") or die("Error in Query"); 
    Hi,

    The code above extracts the data ok, but extracts too much/ or the wrong ones.

    The field "LinkCategoryId" holds data like this "1,3,15,17" etc. If I want to extract only the fields which has "1" in it then I have a problem as it extracts all records with "1, 15, 17". How can I change it to extract only records with "1" ?

    If I remove the wildcards I dont get any records back!


    Hope you can help

    thx in advance
    Last edited by amit290; Oct 16, 2002 at 00:14.

  2. #2
    Sultan of Ping jofa's Avatar
    Join Date
    Mar 2002
    Location
    SvÝ■jˇ­
    Posts
    4,080
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    1.
    Don't store data like "1,3,15,17" in the field LinkCategoryId - use a separate table!

    *** LinkCategory
    link_id (FK, the ID in is_links)
    cat_id (the category id)

    Example:
    link_id, cat_id
    1, 1
    1, 3
    1, 15
    1, 17

    2.
    Use this query to get the links:
    SELECT DISTINCT LinkName_$lang_choice, LinkUrl_$lang_choice
    FROM is_links il INNER JOIN LinkCategory lc ON il.id = lc.link_id
    WHERE lc.cat_id IN($linkcatid)
    ORDER BY LinkName_$lang_choice

    $linkcatid must be a string with comma separated numbers, like "1, 3, 15, 17"

  3. #3
    SitePoint Zealot amit290's Avatar
    Join Date
    Oct 2001
    Posts
    158
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi, thanks for the reply.

    Here is my database structure:

    Tbl 1 = is_depts
    DepartmentId
    DeptName

    Tbl 2 = is_category
    CategoryId
    CategoryName

    Tbl 3 = is_links

    LinkId
    DepartmentId (links to tbl 1 field: DepartmentId)
    LinkCategoryId (links to tbl 2 field: CategoryId)
    LinkName
    LinkUrl

    -------------------------

    What would you suggest I do different. Dont know why I should be storing data differently, thought thats how it was done

    BTW I fixed the problem by removing the wild card at the front, and adding a comma after the $variable.

    PHP Code:
    $query mysql_query("SELECT LinkName_$lang_choice, LinkUrl_$lang_choice FROM is_links
                          WHERE LinkCategoryId LIKE '
    $linkcatid,%'
                          ORDER BY LinkName_
    $lang_choice") or die("Error in Query"); 
    thx
    Last edited by amit290; Oct 16, 2002 at 00:13.

  4. #4
    Sultan of Ping jofa's Avatar
    Join Date
    Mar 2002
    Location
    SvÝ■jˇ­
    Posts
    4,080
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    If LinkCategoryId contains only one CategoryId, then your db structure is OK
    If LinkCategoryId contains a string like "1, 3, 15, 17", then it's not OK

    The relation Link - Category is M:M, i.e. for each link there are one or more categories, for each category there are one or more links. That's why you need the 4th table LinkCategory.


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
  •