Replacing part of a value...

[jazztie]

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:

    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

[jazztie]

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...

[jazztie]

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:

<?    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);
      $rubidsnew1= str_replace(",$sectionid","",$rubidsnew);
      $rubidsnew2= str_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);
      $rubidsnew1= str_replace(",$sectionid","",$rubidsnew);
      $rubidsnew2= str_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...