SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Enthusiast
    Join Date
    Apr 2005
    Posts
    81
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Search MySQL Tree String

    Hello I have a column that stores ids separated by a comma, ex:

    ID..................TREE

    1...................1,57,51,52
    2...................1,23,58
    3...................1,57,51,52,53
    4...................1,56,23
    5...................1,57,51,52,55
    6...................1,34
    7...................1,60,5289
    8...................1,61,527
    9...................1,45852

    The purpose of the TREE column is to serve as a breadcrumb pointer in a herarchical tree structure... so each id separated by comma is parent to the previous one.

    What I am trying to accomplish is to search through the breadcrumbs to identify which of them contains a certain id and then explode them into an array using php.

    For example, if i wanted to know which contains the number 52.

    I have used LIKE and REGEXP but they show me not only the records that contain 52 but also any number that begins or ends with 52 like 527, 5289 or 45852.

    How can I filter only the records with the number 52 (In this case for example ids: 1,3,5 .... and not 1,3,5,7,8,9 because ids 7,8 and 9 just have numbers that begin or end with 52 but not 52 itself).

    I also tried full text search but I might not be using it correctly.

    Here are some sql queries i have used without success:

    PHP Code:
    USING LIKE
    "SELECT * FROM table WHERE (tree LIKE '%52%')";
    "SELECT * FROM table WHERE (tree LIKE '%52,%') or (section_arbol LIKE '%,52%')";

    USING REGEXP
    "SELECT * FROM table  WHERE tree REGEXP '{52}'";
    "SELECT * FROM table  WHERE tree REGEXP BINARY '{52}'";
    "SELECT * FROM table  WHERE tree REGEXP '[[:<:]]{5}' AND '[[:>:]]{2}'";

    USING FULL TEXT SEARCH
    "SELECT * FROM table WHERE MATCH(tree) AGAINST('52')";
    "SELECT * FROM table WHERE MATCH(tree) AGAINST('\"52\"' IN BOOLEAN MODE)"
    What am I doing wrong?

    thanks !!! :0)

  2. #2
    SitePoint Addict SirAdrian's Avatar
    Join Date
    Jul 2005
    Location
    Kelowna, BC
    Posts
    289
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Use the find_in_set() function.
    Adrian Schneider - Web Developer

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,340
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    don't forget that FIND_IN_SET (or similar approaches) means the query will not scale, i.e. the more rows you have, the slower the query gets, because it has to do a table scan

    this is one of the problems of violating first normal form (storing multiple values in a single column)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Enthusiast
    Join Date
    Apr 2005
    Posts
    81
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The TREE column is used as reference to map the node hierarchy for that record, the complete structure of the table is:

    NODE_ID | NODE_NAME | NODE_PARENT | NODE_TREE

    By using NODE_TREE as breadcrumbs is easier to know which records are related in a parent / child structure.

    Using a record's id as a starting point I was able to follow up the parents in order to get the values for NODE_TREE with the help of PHP. But if I want to show which records are childs of a certain NODE, I don't know how to use the NODE_PARENT column to device a better way of following down all the childs, that is why i figured that if I search the value within the NODE_TREE using LIKE, REGEXP, FIND_IN_SET or a FULL TEXT SEARCH, I could retrieve all the NODE_TREE values and the break them into an array so PHP could be of further help.

    Is there a better way?
    What is faster than FIND_IN_SET when it comes to many records?
    Should I create another table to store each of the NODE_TREE elements with another column referencing each id's hierarchy?

    Thanks

    PS. This works as well:

    PHP Code:
    "SELECT * FROM table WHERE tree REGEXP '[[:<:]](52)[[:>:]]'" 

  5. #5
    SitePoint Wizard PHPycho's Avatar
    Join Date
    Dec 2005
    Posts
    1,201
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Why don't you use preorder tree traversal model instead.

  6. #6
    SitePoint Enthusiast
    Join Date
    Apr 2005
    Posts
    81
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I donīt know if using a left and right approach (http://articles.sitepoint.com/articl...-data-database) may complicate things when it comes to millions of records... for example if you move a node to another parent everything else needs to be updated... however I am not an expert and I might be wrong in my asessment... any further thoughts would be appreciated

  7. #7
    SitePoint Wizard PHPycho's Avatar
    Join Date
    Dec 2005
    Posts
    1,201
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by raml View Post
    I donīt know if using a left and right approach (http://articles.sitepoint.com/articl...-data-database) may complicate things when it comes to millions of records... for example if you move a node to another parent everything else needs to be updated... however I am not an expert and I might be wrong in my asessment... any further thoughts would be appreciated
    This is what you call: preorder tree traversal model

  8. #8
    SitePoint Enthusiast
    Join Date
    Apr 2005
    Posts
    81
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Exactly that is why I made a reference to that article, hence my question was intended to know how will the preorder tree traversal model behave with millions of records given that if you want to move a node to another parent everything else needs to be updated in a left /right order and it might make things pretty slow... but again it is just a thought because I am no expert that is why further advice will be appreciated... thanks


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
  •