Q:

Update SQL multiple rows removing a part of a string

I have a sql table structured like this
|[id]--|--[    colour    ]|  
|[ 1]--|--[red_blue_green]|   
|[ 2]--|--[ green_orange ]|   
|[ 3]--|--[  pink_green  ]|   
|[ 4]--|--[     green    ]|

What I want to do is to remove green from colours, and to do this I tried this
<?php
$del='green';
//DATABASE
require('config.php');
require('connect.php');
$db_content = mysql_query("SELECT * FROM colours WHERE colour LIKE '%_".$del."' OR colour LIKE '%_".$del."_%' OR colour LIKE '".$del."_%' OR colour='$del'");
$rows  = mysql_fetch_row($db_content);
foreach($rows as $row){
  $kaboom = explode('_', $row[6]);
  $i=0;

  while($kaboom[$i]!=$del){
    $i++;
  }

  unset($kaboom[$i]);

  $db_newcontent = implode('_', $kaboom);
  mysql_query("UPDATE colours SET colour='$db_newcontent' WHERE         id='{$row[0]}'");
}

But it doesn't work. It gives me no error messages, it only takes a lot of time and then it stops trying. Any suggestions?

A:

It is possible with a single SQL statement.
The following code (assuming your table is called colour ) cross joins the table against a pair of sub queries that each return the numbers 0 to 9. This gives 100 copies of each row, with the number 0 to 99. The number is then used to get out each colour, the WHERE clause excludes those which are 'green' and then they are joined back together using GROUP_CONCAT.
UPDATE colour a
INNER JOIN
(
    SELECT id, GROUP_CONCAT(DISTINCT colours SEPARATOR '_') AS colour
    FROM
    (
        SELECT id, SUBSTRING_INDEX(SUBSTRING_INDEX(colour, '_', tens.aCnt * 10 + units.aCnt + 1), '_', -1) AS colours
        FROM colour
        CROSS JOIN 
        (
            SELECT 1 AS aCnt UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 0
        ) units
        CROSS JOIN 
        (
            SELECT 1 AS aCnt UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 0
        ) tens
        WHERE FIND_IN_SET('green', REPLACE(colour, '_', ','))
    ) sub0
    WHERE colours != 'green'
    GROUP BY id
) b
ON a.id = b.id
SET a.colour = b.colour

However having delimited fields in a relational database is normally a very bad idea. For example if you want to search for any record that has the colour green in the list then you cannot use indexes; the database would need to check every single record. You could easily split these off to a separate table with multiple rows for each record on the main table (one for each colour referring to the record on the main table). You can easily get all the values using a table join, and you can easily check for specific values using a query that will use indexes.
EDIT
If you really must use a delimited column and you want to stick with something like your original solution then it can be fixed with something like this:-
<?php
$del='green';
//DATABASE
require('config.php');
require('connect.php');
$db_content = mysql_query("SELECT id, colour 
                            FROM colours 
                            WHERE FIND_IN_SET('".mysql_real_escape_string($del)."', REPLACE(colour, '_', ','))");
while ($rows  = mysql_fetch_assoc($db_content))
{
    $kaboom = explode('_', $rows['colour']);
    unset($kaboom[array_search($del, $kaboom)]);

    $db_newcontent = implode('_', $kaboom);
    mysql_query("UPDATE colours 
                    SET colour = '".mysql_real_escape_string($db_newcontent)."' 
                    WHERE id = ".(int)$rows[0]."");
}

EDIT again
Example of normalising this.
You have a table of (say) users. This has the id field:-
CREATE TABLE users
(
    id INT(11) NOT NULL AUTO_INCREMENT,
    user_name   VARCHAR(50),
    PRIMARY KEY (`id`),
    UNIQUE KEY `user_name` (`user_name`)
);

INSERT INTO users(id, user_name) VALUES
(1, 'Fred'),
(2, 'Freda'),
(3, 'Jo'),
(4, 'Joe');

You then have the table of the colours for each user. This has one row per user per colour (or whatever they have typed in):-
CREATE TABLE user_colours
(
    id INT(11) NOT NULL AUTO_INCREMENT,
    user_id INT(11) NOT NULL,
    colour  VARCHAR(50),
    PRIMARY KEY (`id`),
    KEY `user_id` (`user_id`),
    KEY `colour` (`colour`)
);

INSERT INTO user_colours(id, user_id, colour) VALUES
(1, 1, 'red'),
(2, 1, 'blue'),
(3, 1, 'green'),
(4, 2, 'green');
(5, 2, 'orange');
(6, 3, 'pink');
(7, 3, 'green');
(8, 4, 'green');

This way if you need to add an extra colour for a user you just find the id of that user and insert a record in the 2nd table. For example say you wanted to add pink for Freda you would get the id of Freda which is 2 and then insert a row to the 2nd table like INSERT INTO user_colours(id, user_id, colour) VALUES(NULL, 2, 'pink') .
Should you need to delete it then you can easily do it with DELETE FROM user_colours WHERE user_id = 2 AND colour = 'pink'
If you want a count of how many users have each colour you could do:-
SELECT colour, COUNT(user_id)
FROM user_colours
GROUP BY colour

