Delete a value from Php Comma seperated value array saved in MySQL

Question

I have a script which works great for saving users in comma seperated value to MySQL 'reserve' field.

eg '510,465,8552'

I then have the script below which echos the users name from the comma seperated 'reserve' field. The bit i am struggling with is to be able to click on a delete image next to one of the users and it will delete the users id from the comma seperated value.

    $entries = get_records_sql ("SELECT * FROM development ORDER BY pdorder ASC");  
    foreach($entries as $lesson) {

    $waiting = explode(",", $lesson->reserve);
    foreach($waiting as $unlucky) {
    $reserveuser = get_record("user", "id", $unlucky);

    if($reserveuser->id>1){
echo '<li id="lstno'.$lesson->id.'" name="'.$reserveuser->id.'">'.$reserveuser->firstname.'&nbsp;'.$reserveuser->lastname.' ';
echo '<a href="removereserve.php?id='.$lesson->id.'&reserve='.$reserveuser->id.'"><img title="unenrol" src="delete.gif" /></a>';
        echo '</li>';
        }
    }

Any guidance would be much appreciated, thanks in advance.

Why are you storing user ids in a comma-separated field? Anyways, just get the record and use string manipulation to remove the substring and update the database with the new value — scibuff
– scibuff, Commented Feb 28, 2012 at 12:00
After exploding you can just unset the part of the array you wish to delete, then implode the array to a string again and update the database with the new string — Sondre
– Sondre, Commented Feb 28, 2012 at 12:03

Pankucins · Accepted Answer · 2012-02-28 12:05:50Z

4

Well, if all of the user id's are saved in one field my approach would be something like this http://codepad.org/KTAJ3ROb

Hope that helps figuring it out!

answered Feb 28, 2012 at 12:05

Pankucins

1,7301 gold badge16 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Jobin Over a year ago

This contain some bug try with a value that not exists in the list it will remove first one. ?? you should use in_array first for checking that then only unset().

Rok Kralj · Accepted Answer · 2012-02-28 12:19:20Z

1

This is probably bad database design, unless you know what you are doing.

The one way to delete just one value is to read whole cell, remove it with php and write it back.

$waiting = explode(",", $lesson->reserve);
//delete desired one
unset($waiting[3]);
//implode it back
$store = implode(',', $waiting);
mysql_query("UPDATE table SET reserve='$store'");

If comma separated IDs are unique (you dont have say 11,20,11), then you can use mysql REPLACE function.

mysql_query("UPDATE table SET
reserve=REPLACE(reserve, ',$oldid', ',$newid'),
reserve=REPLACE(reserve, '$oldid,', '$newid,')");

There is a litle trick involved to specially handle first and last value.

edited Feb 28, 2012 at 12:19

answered Feb 28, 2012 at 12:01

Rok Kralj

49k10 gold badges75 silver badges80 bronze badges

1 Comment

user1027167 Over a year ago

what is "bad database design"? If referential integrity is not (so) important than a comma seperatetd list might be a good way to avoid a 'join' and speed up the db-query. Also ther are some databases (No-SQL) that do not support referential integrity...

user1027167 · Accepted Answer · 2012-02-28 12:28:05Z

0

If you want to make it directly in SQL (MySQL) then you can do it like this:

UPDATE ... SET USERS = TRIM(REPLACE(' ' + USERS + ' ', ' 7 ', ' '))

This will delete the user with the ID 7. The separator is not a ',' but a ' ' (blank). The Problem is, there is no function for removing ',' at the beginning or end like TRIM() for blanks.

Or you have to accept always a leading and tailing ',' in the USERS-field and do it like this:

UPDATE ... SET USERS = REPLACE(USERS, ',7,', ',')

answered Feb 28, 2012 at 12:28

user1027167

4,5088 gold badges36 silver badges42 bronze badges

1 Comment

Alex Sarnowski Over a year ago

I'm confused as to why this got a down-vote. This worked perfect for me! Thank you very much, exactly what i've been looking for.

Collectives™ on Stack Overflow

Delete a value from Php Comma seperated value array saved in MySQL

3 Answers 3

1 Comment

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related