从MySQL中保存的Php Comma分离值数组中删除一个值

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.

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!

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.

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,', ',')