SitePoint Sponsor

User Tag List

Results 1 to 12 of 12
  1. #1
    SitePoint Evangelist jazztie's Avatar
    Join Date
    Mar 2001
    Location
    the Netherlands
    Posts
    519
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Well...
    I have a new marvelous problem.

    I have two tables

    rubrieken (id, title) and
    pages (id, txt, rubrieken)
    pages.rubrieken is a foreign key to rubrieken.id

    Now the fun part starts...
    pages.rubrieken can have zero, one or more rubrieken.
    for instance; '1,23,48' (this also the way it is stored in the database)

    Now, when I delete an entry in table rubrieken
    ("DELETE FROM rubrieken WHERE id='$id'")
    I also want that particular rubrieken.id removed from pages.rubrieken. But only that particular one... not the others.

    so for instance... when I remove rubrieken.id '23' the pages.rubrieken would read; '1,48'

    Could someone help me with this problem? I don't know how to tackle this problem...
    Last edited by jazztie; May 18, 2001 at 03:53.

  2. #2
    SitePoint Columnist Skunk's Avatar
    Join Date
    Jan 2001
    Location
    Lawrence, Kansas
    Posts
    2,066
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You will need to grab the value of the rubrieken field using a SQL select statement, then modify that field within PHP (probably by exploding it into an array, removing the array value of the Id you want to get rid of and then imploding the array again) and finally use the SQL update statement to replace the information for that field stored in the database/

  3. #3
    SitePoint Evangelist jazztie's Avatar
    Join Date
    Mar 2001
    Location
    the Netherlands
    Posts
    519
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    okay... but how should I do that exploding-deleting-replacing part?

    That's kind of a problem...

  4. #4
    ********* Callithumpian silver trophy freakysid's Avatar
    Join Date
    Jun 2000
    Location
    Sydney, Australia
    Posts
    3,798
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Well the thing to do here is to remember that a rubrieken (in table pages) is a string. So the REPLACE() function of MySQL should come in handy for this. This solutions should work:

    Lets say we want to remove rubrieken 12 from the table pages.
    Now one record may contain a rubrieken field "12, 13, 24"
    and another rubrieken "13, 24, 12".

    Lets assume that the variable $rub holds the value we want to remove. The following SQL should deal with the first example, followed by the second. Should work (fingers crossed )

    UPDATE pages
    SET rubrieken = REPLACE(rubrieken, '$rub,', ''),
    rubrieken = REPLACE(rubrieken, '$rub', '')


    What this is doind is first we replace all instances of 12, with an empty string. Then we replace all instances of 12 with an empty string.

    Pour that into a bowl and see if the cat licks it

    PS It would have been nice if vBulletin would let me wrap that sql in code or php tags but I've noticed that sometimes it just won't accept a post that uses them. Odd.
    Last edited by freakysid; May 18, 2001 at 05:56.

  5. #5
    SitePoint Evangelist jazztie's Avatar
    Join Date
    Mar 2001
    Location
    the Netherlands
    Posts
    519
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I've used your code, but it slaps me around the ears with a parse error...

    This is the code I'm using:

    PHP Code:
        $sql3    "UPDATE pages SET rubrieken = REPLACE(rubrieken,'$rubid',''), rubrieken = REPLACE(rubrieken,'$rubid','')";
        
    result3 mysql_query($sql3); 
    $sql3 is line number 25
    $result3 is line number 26

    The parse error reads that there's something wrong with line 26.

    I get the same error if I leave out the second REPLACE statement.
    If I would want to check first which pages have the $rubid in them... and then execute the replace-statement, how would I go about doing that?

  6. #6
    ********* Callithumpian silver trophy freakysid's Avatar
    Join Date
    Jun 2000
    Location
    Sydney, Australia
    Posts
    3,798
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Give yourself another slap for good measure.

    result3*=*mysql_query($sql3);

    should be

    $result3 =*mysql_query($sql3);

  7. #7
    SitePoint Evangelist jazztie's Avatar
    Join Date
    Mar 2001
    Location
    the Netherlands
    Posts
    519
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ARGH...



    I think I need another slap... That's for sure.


    And about the second part of my question?
    Selecting all the pages that have in rubrieken a $rubid... afterwhich using the replace statement?

    Pages can have upto 4 or 5 rubrieken in them...

  8. #8
    SitePoint Evangelist jazztie's Avatar
    Join Date
    Mar 2001
    Location
    the Netherlands
    Posts
    519
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The replacing part seems to be working...
    I have however just one more problem...

    before: '1,4,35' $rubid 35 -> remove
    after: '1,4, '

    How do I remove the last comma?

  9. #9
    SitePoint Evangelist jazztie's Avatar
    Join Date
    Mar 2001
    Location
    the Netherlands
    Posts
    519
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Never mind...

    I've found the solution myself... for the last problem

    REPLACE(rubrieken,',$rubid','')

    *grin*... wasn't that hard after all...

  10. #10
    ********* Callithumpian silver trophy freakysid's Avatar
    Join Date
    Jun 2000
    Location
    Sydney, Australia
    Posts
    3,798
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)


    Well, I've just been reading the manual http://www.mysql.com/doc/S/t/String_functions.html and discovered the function FIND_IN_SET() which is exactly what you want!

    Code:
    FIND_IN_SET(str,strlist) 
    Returns a value 1 to N if the string str is in the list strlist consisting of N
     substrings. A string list is a string composed of substrings separated by `,'
     characters. If the first argument is a constant string and the second is a column of
     type SET, the FIND_IN_SET() function is optimized to use bit arithmetic! Returns 0
     if str is not in strlist or if strlist is the empty string. Returns NULL if either
     argument is NULL. This function will not work properly if the first argument
     contains a `,': 
    
    
    mysql> SELECT FIND_IN_SET('b','a,b,c,d');
            -> 2
    So something like this might be handy,

    SELECT id, text, rubrieken, FIND_IN_SET('$rub', rubrieken) AS position
    FROM pages
    WHERE FIND_IN_SET('$rub', rubrieken)


    Now you will have a result set with records that contain that rubrieken. You also have the position in the set where the rubrieken appears - which if that is useful to you.
    Eg, say the value of the rubrieken field is "10,11,12" and you are searching for "12" then position will have the value 3 because "12" was found in the third element of the string (as a comma delimited set).

    Lets say you have extracted the row from the result set as an associateive array, you could do this:

    $row = mysql_fetch_array($result)
    $rubriekens = explode(",", $row["ruberiek"]); // put rubrieks into an array

    You now know that the relevent rubrieken (12) occupies $row["position"] - 1 element of the array $rubriekens. So, for example you could over-ride that value if you wanted to (say change it from 12 to 13) like so:

    $i = $row["position"] - 1;
    $rubriekens[$i] = 13;

    Make sense ?!? Anyway, I'm just thinking out aloud.
    Last edited by freakysid; May 18, 2001 at 09:24.

  11. #11
    SitePoint Evangelist jazztie's Avatar
    Join Date
    Mar 2001
    Location
    the Netherlands
    Posts
    519
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Okay this is what I get...

    SELECT id, text, rubrieken, FIND_IN_SET('$rub', rubrieken) AS position
    FROM pages
    WHERE FIND_IN_SET('$rub', rubrieken)
    It will select id, text, rubrieken AS position from pages where pages.rubrieken contain the value in $rub...

    I don't understand why the first FIND_IN_SET is there. (in the select-statement). And why don't you use * in the SELECT statement? You can extract the pages.id later with a 'mysql_result($sql,0,"id");' statement right?

    the FIND_IN_SET-statement searches in pages if it can find the $rub, right? So, if there are two pages with the same $rub than it will return two values?

    So, reading these last values, you can use them in the REPLACE-statement... am I still correct?
    So, how would the loop look like if you have zero, one or more pages with the requested rubrieken-id?

  12. #12
    ********* Callithumpian silver trophy freakysid's Avatar
    Join Date
    Jun 2000
    Location
    Sydney, Australia
    Posts
    3,798
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    There was a slight error in the code I just posted, which I've fixed. Don't worry about it, I was just thinking out loud and it probably isn't what you need anyway - so don't worry. All I was doing with that sql was saying that you can work out in which position in the set the rubrieken is that you are looking for.

    Eg you are searching for records with rubrieken of 12 then in this example "10, 11, 12" position will have a value of 3 (because "12" is the third element of the comma dilimeted set). No big deal - it may not even be useful to you.

    As you say this will return all the rows that contain the rubrieken $rub

    SELECT *
    FROM pages
    WHERE FIND_IN_SET('$rub', rubrieken)


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
  •