Deleting the selected ids from comma separated numbers

[watsmyname]

hello friends,

 

well i have a table name tbl_cart, with fieldname service_ids. this field holds ids in comma separated pattern like 2,7,8,2,10 [note here 2 is repeated].

In one of my page, these value are exploded and in a loop with each id, i query another table to get service name and service amount.  The services are listed and in the beginning of each service, there is a checkbox.

For example, the services are listed in pattern below, where # is a checkbox

#service no 2

#service no 7

#service no 8

#service no 2

#service no 10

 

All i need to do is when user select checkboxes and clicks button, it will remove the selected ids from table tbl_cart's service_ids field. So for example if I select first and third, we should remove 2 and 8 from  2,7,8,2,10 and  the remaining ids should be 7,2,10

if there is no repeat of the ids, it would have been easier, but there is a repeat.

 

can anyone give me a hint?

 

Thanks

[ialsoagree]

You can do this via an explode() statement, producing a list of the existing services, then use the user submitted information to remove the indexes of services the user doesn't want, then you can use implode() to rebuilding the string and save it to the database.

 

So if the services a user wanted to remove were saved to $_POST['remove_servers'] (IE. <input name="remove_servers[]" value="1" ...) and service 1 was the first (instead of 0, as will be the case when you use explode) you could do the following:

 

$array_cur_services = explode(',', $string_cur_services);
foreach ($_POST['remove_servers'] as $value) {
   $value = $value - 1;
   unset($array_cur_services[$value]);
}
$string_new_services = implode(',', $array_cur_services);

 

A much easier way to do all this would be to save each service as it's own row in the table and then use a column to link services to the user's session or cart. Then you don't have to worry about dealing with a text list, you're dealing with individual table rows which are easily manipulated and deleted.

[ialsoagree]

I just want to mention, it concerns me that you're using the order things appear in as the way you relate them. While, if done with care, this is fine and won't cause any issues, it's always better to link things explicitly rather than implicitly.

 

Just as an example, if there was some other method by which a service was removed from the list, or the order of the list was changed, all while the user was choosing which services they wanted to remove, you end up with a nightmare scenario with services that aren't meant to be removed are removed, and services that are meant to be removed are still there! You'll also have an end user whose freaking out because they have no idea what happened.

 

Using some kind of ID (especially as would be the case if each service was it's own table row) then the addition/removal of services is via ID rather than by order. If something happens to the order of the list while the user is choosing, it doesn't effect that user at all. Because his selections are based on a unique ID, there's no way they can accidentally delete something other than what they want to.

 

[edit]Alternatively: You could set up a system that always "builds a list" rather than trying to manipulate an existing list. That is, ALWAYS build a new list from user input, NEVER try to edit the existing list. If you do things this way, then when a user wants to remove services, you build a list based on the services the user DOESN'T want to remove, rather than removing the ones they do from an existing list.

 

This has the effect that, if the order of the list changes, or items are removed, it doesn't effect the end user at all. That "modified" list that the user wasn't working with is complete deleted when the user submits, and a brand new list is generated based on exactly what the user wants to do.[/edit]

[watsmyname]

Hello ialsoagree,

 

Thanks a ton for your generous help, You  are right i think i should go with each row for each services. Thanks again.

 

watsmyname

[Psycho]

Just to add to ialsoagree, storing the ids as a comma separated list defeats the who purpose of having a relational database. For a normalized database you would need three tables:

tbl_cart

tbl_services

tbl_cartSvcs

 

The first tables stores just the cart information, the second stores the service types and the third stores the associations between the carts and services. You then access/manipulate the information using appropriate JOINs. By the fact that you are gathering the comma separated list, and then looping through it to do additional queries leads me to believe you aren't familiar or comfortable using JOINs.

 

You should never do queries within loops unless there is absolutely no other way (and that is almost never the case). Whit small databases/usage you will not notice much. But as soon as you start putting more records and more users on the system these looping queries will bring your application to a crawl.

 

I suggest looking for some tutorials on MySQL JOINs

[watsmyname]

Thanks mjdamato,

 

I have created separate table for cart and services. i have made just two tables in service table i have cart_id as foreign key.

 

Thanks a ton for help ialsoagree and mjdamato