Remove Duplicate Comma-Separated Values From MySQL Column with PHP

Question

I have a db with columns like the following:

id     options
1      Website,Website,Newspaper,Newspaper,TV,TV,Radio,Radio
2      Website,Website,Newspaper,Newspaper
3      Website,Website,TV,TV

The goal is to remove the duplicate entries and normalize the options column to:

id     options
1      Website,Newspaper,TV,Radio
2      Website,Newspaper
3      Website,TV

I have developed the following PHP code:

$sql = "SELECT id, options FROM table";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
    // output data of each row
    while($row = $result->fetch_assoc()) {
        $id = $row['id'];
        $values_array = explode( ',' , $row['options'] );
        if(count($values_array) != count(array_unique($values_array)))
        {
            $likes = array_unique($values_array);
            $new = implode(',', $likes);
            $sql = "UPDATE table SET options=".$new." WHERE id = '$id'";
        }
    }
} else {
    echo "0 results";
}
$conn->close();

This doesn't get the job done. Everything seems to work but the attempt to update the options columns with the new array data.

This doesn't seem too difficult, just looking for a little guidance on how to make it work.

Thanks in advance!

Possible duplicate of http://stackoverflow.com/questions/11321491/when-to-use-single-quotes-double-quotes-and-backticks — Funk Forty Niner, Dec 31 '15 at 21:57
Your headed down a dark road. You should normalize these tables. — Dan, Dec 31 '15 at 21:58
This really doesn't help you `echo "0 results";` you should be checking for the *real* error but you're not using those tools. Look up "check for errors mysqli. — Funk Forty Niner, Dec 31 '15 at 21:58

score 2 · Answer 1 · edited May 23 '17 at 10:27

You can do it directly in mysql

UPDATE T
 JOIN
 (SELECT id,GROUP_CONCAT(DISTINCT SUBSTRING_INDEX(SUBSTRING_INDEX(t.options, ',', sub0.aNum), ',', -1)) AS ids
FROM t
INNER JOIN
(
    SELECT 1 + units.i + tens.i * 10 AS aNum, units.i + tens.i * 10 AS aSubscript
    FROM (SELECT 0 AS i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) units
    CROSS JOIN (SELECT 0 AS i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) tens
) sub0
ON (1 + LENGTH(t.options) - LENGTH(REPLACE(t.options, ',', ''))) >= sub0.aNum
GROUP BY id)x
ON x.id=t.id
SET t.options=x.ids

FIDDLE

Inspired by this answer

score 1 · Accepted Answer · edited Dec 31 '15 at 22:23

Try This:

$sql = "SELECT id, options FROM table";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
// output data of each row
    while ($row = $result->fetch_assoc()) {
        $id = $row['id'];
        $values_array = explode(',', $row['options']);
        if (count($values_array) != count(array_unique($values_array))) {
            $likes = array_unique($values_array);
            $new = implode(',', $likes);
            $sql = "UPDATE table SET options=" . $new . " WHERE id = '$id'";
            /* seems you missed this */
            if ($conn->query($sql) === TRUE) {
                echo "Record updated successfully";
            } else {
                echo "Error updating record: " . $conn->error;
            }
            /* you declared sql query but not executed it */
        }
    }
} else {
    echo "0 results";
}
$conn->close();

hope it was helpful :)

*"Try this"* answers mean nothing to anyone, except if the OP just wants to get their answer and go, like a cheap hooker on a street corner and gotten her money from doing tricks. Why not give an explanation and keep that hooker off the street and maybe she'll end up in University. — Funk Forty Niner, Dec 31 '15 at 22:00

score 1 · Answer 3 · answered Dec 31 '15 at 22:05

As stated in other answers, your quotes are wrong and you didn't execute the UPDATE query, there's another thing you need to know.

table is a reserved keyword in MySQL, so you can't use it like that in your query. Use backticks to escape it.

So your code should be like this:

$sql = "SELECT id, options FROM `table`";
$result = $conn->query($sql);

if ($result->num_rows > 0){
    // output data of each row
    while($row = $result->fetch_assoc()) {
        $id = $row['id'];
        $values_array = explode( ',' , $row['options'] );
        if(count($values_array) != count(array_unique($values_array))){
            $likes = array_unique($values_array);
            $new = implode(',', $likes);
            $sql = "UPDATE `table` SET options='".$new."' WHERE id = '$id'";
            $conn->query($sql);
            if($conn->affected_rows){
                echo "success<br />";
            }else{
                echo "error<br />";
            }
        }
    }
}else{
    echo "0 results";
}
$conn->close();

Here's the reference:

Keywords and Reserved Words

score 1 · Answer 4 · answered Jan 04 '16 at 15:00

I added the following code to my PHP as showcased by Ajjay Aroraa --

if ($conn->query($sql) === TRUE) {
echo "Record updated successfully";
} else {
echo "Error updating record: " . $conn->error;
}

The final code for my entire application:

$sql = "SELECT id, options FROM tdata";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
// output data of each row
while($row = $result->fetch_assoc()) {
$id = $row['id'];
$values_array = explode( ',' , $row['options'] );
if(count($values_array) != count(array_unique($values_array)))
{
// find duplicate values in the array
$likes = array_unique($values_array);
$new = implode(',', $likes);
$sql = "UPDATE tdata SET options='".$new."' WHERE id = '$id'";
// execute update query
if ($conn->query($sql) === TRUE) {
echo "Record updated successfully";
} else {
echo "Error updating record: " . $conn->error;
}
}
}
} else {
echo "0 results";
}
$conn->close();

Thanks to all who replied -- this is a quick fix as I work to normalize the tables.

Remove Duplicate Comma-Separated Values From MySQL Column with PHP

4 Answers4