CODEWITHSUNDEEP
phpmysql
proph3t
proph3t

Reputation: 965

Remove Duplicate Comma-Separated Values From MySQL Column with PHP

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!

Upvotes: 4

Views: 2450

Answers (4)

proph3t
proph3t

Reputation: 965

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.

Upvotes: 1

Ajjay Arora
Ajjay Arora

Reputation: 154

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 :)

Upvotes: 1

Mihai
Mihai

Reputation: 26784

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

Upvotes: 2

Rajdeep Paul
Rajdeep Paul

Reputation: 16963

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:

Upvotes: 1

Related Questions