如何更新mysql中的文本字段

Question

in my wordpress blog i have a table consist of users and their products seperated by ";" for example :

       mytable
----------------------
userid  | products
----------------------
   1    | camera
   2    | books;keyboard;computer
   3    | mouse;printer
   4    | scanner

now in my script the user will add a new product so i need to check if that user has more than 3 products because in this case i will do nothing and will not add this product for him but if he has less than 3 products i need to add the new product to his products seperated by ";" something like :

$userid = 3;
$newproduct = "ball"


if (pruduct field for that user has more than 2 ";" ) {

   do nothing

 }else{


$wpdb->update( 
'mytable', 
array( 'product' => concat(product, ";", $newproduct)), 
array( 'userid ' => $userid ), 
array( '%s' ), 
array( '%d' ) 
);

so the result in this example would be :

       mytable
----------------------
userid  | products
----------------------
   1    | camera
   2    | books;keyboard;computer
   3    | mouse;printer;ball
   4    | scanner

Answer 1

Despite the data normalization arguments, which are incredibly valid, if for some reason you cannot correct that and must work with what exists.

Select the products column as a string and use the PHP explode() and implode() functions. You'd end up with something like this:

$current_products = explode(';', $user['products']);
// $current_products is now an array of the user's products.
if !(count($current_products) >= 3) {
    $current_products[] = $new_product;
}
$user_products = implode(';', $current_products);
// Insert $user_products into your table via UPDATE

The implode and explode functions convert strings to and from arrays based on given delimiters.