SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Evangelist jazztie's Avatar
    Join Date
    Mar 2001
    Location
    the Netherlands
    Posts
    519
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Replacing part of a value...

    Argh... I want to delete a part of a function... People can choose either to delete a foreign key from table Pages. (radiobutton == 1) Or to delete the section in all, including the foreign keys found in pages.

    database tablestructure:
    pages(id,subcat,txt,sections) sections foreign key sections.id
    sections(id,title)
    links(id,section,txt,title) section foreign key sections.id

    pages.sections can contain 0,1 or more sectionids.
    (db sucks, I know... cannot help it at this time)

    so this is what I came up with:

    PHP Code:
        if($radiobutton == '1'){
          
    $sql     "UPDATE pages SET sections = 
              REPLACE(sections,'
    $sectionid,',''), // removes i.e. sectionid 12 if value = 12,13,14
              REPLACE(sections,',
    $sectionid',''), // removes i.e. sectionid 12 if value 11,12,13 or 10,11,12
              REPLACE(sections,'
    $sectionid','')   // removes i.e. sectionid 12 if value 12
              WHERE subcat = '
    $subcatid2'";
          
    $result mysql_query($sql);
          
          print 
    "Section number <b>$sectionid</b> has been removed from this page <br>";
        }
        else {
          
    $sql     "UPDATE pages SET sections = 
              REPLACE(sections,'
    $sectionid,',''), // removes i.e. sectionid 12 if value = 12,13,14
              REPLACE(sections,',
    $sectionid',''), // removes i.e. sectionid 12 if value 11,12,13 or 10,11,12
              REPLACE(sections,'
    $sectionid','')   // removes i.e. sectionid 12 if value 12
              WHERE subcat = '
    $subcatid2'";
          
    $result mysql_query($sql);
          
          
    $sql2     "DELETE FROM sections WHERE id = '$sectionid'";
          
    $result2  mysql_query($sql2);
          
          
    $sql3        "DELETE FROM links WHERE section = '$sectionid'";
          
    $result3  mysql_query($sql3);
          
          print 
    "Section number <b>$sectionid</b> has been removed from the database <br>";
        } 
    However, when I execute this script, it doesn't remove the sectionid from the pages-table... and when I want to remove it completely, it only removes it from the sections-table... not the pages table.

    Where did I go wrong? Please advise

  2. #2
    SitePoint Evangelist jazztie's Avatar
    Join Date
    Mar 2001
    Location
    the Netherlands
    Posts
    519
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I've just read in a MySQL document that you can't access the values of the old row from a REPLACE statement...

    So, naturally the above statement won't work... How can I read out the value of 'pages.sections' and delete the $sectionid without deleting all others?

    i.e. say that I have pages.section = 12,13,14
    and I want to delete $sectionid (12)

    How do I do this? I know how to add... which is simple:

    $pages_sectionnew = $pages_sectionold . ',' . $sectionid

    But I have no clue how to replace a value...

  3. #3
    SitePoint Evangelist jazztie's Avatar
    Join Date
    Mar 2001
    Location
    the Netherlands
    Posts
    519
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I found the solution... little hard work gets you somewhere

    This is how I solved the problem. There is a php-function called str_replace.
    Using the above example, the solution looks like this:
    PHP Code:
    <?    if($radiobutton == '1'){
          
    $sql  "SELECT * FROM pages WHERE subcat = '$subcatid2'";
          
    $result mysql_query($sql);
          
    $rubids mysql_result($result,0,'sections'); 
          
          
    $rubidsnew str_replace("$sectionid,","",$rubids);
          
    $rubidsnew1str_replace(",$sectionid","",$rubidsnew);
          
    $rubidsnew2str_replace("$sectionid","",$rubidsnew1);
            
          
    $update "UPDATE pages SET sections = '$rubidsnew2' WHERE subcat = '$subcatid2'";
          
    $res_up mysql_query($update); 
          
          
    $sql1  "SELECT * FROM sections WHERE id = '$sectionid'";
          
    $result1  mysql_query($sql1);
          
    $rubtitel mysql_result($result1,0,'titel');
          print 
    "subcat id = $subcatid2";
        }
        
    else {
          
    $sql "SELECT * FROM pages WHERE subcat = '$subcatid2'";
          
    $result mysql_query($sql);
          
    $rubids mysql_result($result,0,'sections'); 
          
          
    $rubidsnew str_replace("$sectionid,","",$rubids);
          
    $rubidsnew1str_replace(",$sectionid","",$rubidsnew);
          
    $rubidsnew2str_replace("$sectionid","",$rubidsnew1);
            
          
    $update "UPDATE pages SET sections = '$rubidsnew2' WHERE subcat = '$subcatid2'";
          
    $res_up mysql_query($update);
          
          
    $sql1 "SELECT * FROM sections WHERE id = '$sectionid'";
          
    $result1  mysql_query($sql1);
          
    $rubtitel mysql_result($result1,0,'titel');
          
          
    $sql2 "DELETE FROM sections WHERE id = '$sectionid'";
          
    $result2  mysql_query($sql2);
          
          
    $sql3 "DELETE FROM links WHERE section = '$sectionid'";
          
    $result3  mysql_query($sql3);
    }
    print 
    "it worked";
    ?>
    This works like a charm...


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
  •