Update vs Insert logic but the last key is always inserted?

Question

I have added a custom db table which looks like usermeta table, just storing some key - value pairs. But now i am stuck with the following logic

1. a/ if key exist for a user use an update query else an insert query
2. b/ if an key doesn't exist ANYMORE delete the key - value row

Now for part a i have this function, but it looks like the last key is skipped and always inserted.

`function insertUserShoppingMetaData($params) {
        global $wpdb;
        $shopping_meta_table = 'wp_shopping_metavalues';
        $wp_user_id = $_POST['wp_user_id'];
        foreach ($params as $key => $val) {

            if ($key != "wp_user_id") {
                if (is_array($val)) {
                    $val = json_encode($val);
                }
                $shopping_meta_values = array(
                    'wp_user_id' => $wp_user_id,
                    'meta_shopping_key' => $key,
                    'meta_shopping_value' => $val
                );
                $shopping_meta_where = array('meta_shopping_key' => $key, 'wp_user_id' => $wp_user_id);
            }
         $result = $wpdb->get_results("SELECT * FROM $shopping_meta_table WHERE meta_shopping_key = '".$key."' AND wp_user_id = '$wp_user_id'") ;
         if(count($result) > 0){//KEY ALREADY EXISTS FOR USER
                $return = $wpdb->update($shopping_meta_table, array('meta_shopping_value' => $val), $shopping_meta_where).'<br/>';
            } else {//NEW KEY VALUE SO INSERT
                $return = $wpdb->insert($shopping_meta_table, $shopping_meta_values).'<br/>';
            }           
         }
        echo 'Test return: ' . $return;
    }`

Where does my loop go wrong?

OR should i just delete all key-value pairs and use an insert query? One drawback i can think of is the meta_id table field which is auto-increment and can get really ugly

Answer 1

Ok here's my altered function but this one doesn't work at all but maybe you can see wher i go wrong

function insertUserShoppingMetaData($params) {
        global $wpdb;
        $shopping_meta_table = 'wp_shopping_metavalues';
        $wp_user_id = $params['wp_user_id'];
        $checkKeyValues = $wpdb->get_results("SELECT meta_shopping_key FROM $shopping_meta_table WHERE wp_user_id = '$wp_user_id'");
        //print_r($checkKeyValues);
        foreach ($params as $key => $val) {

            foreach($checkKeyValues as $check){
                //UPDATE OR INSERT
                if (($check->meta_shopping_key == $key) AND ($key != "wp_user_id")) {
                    if (is_array($val)) {
                        $val = json_encode($val);
                    }
                    $shopping_meta_values = array(
                        'wp_user_id' => $wp_user_id,
                        'meta_shopping_key' => $key,
                        'meta_shopping_value' => $val
                    );
                    $shopping_meta_where = array('meta_shopping_key' => $key, 'wp_user_id' => $wp_user_id);

                    $result = $wpdb->get_results("SELECT * FROM $shopping_meta_table WHERE meta_shopping_key = '" . $key . "' AND wp_user_id = '$wp_user_id'");
                    if (count($result) > 0) {//KEY ALREADY EXISTS FOR USER
                        $return = $wpdb->update($shopping_meta_table, array('meta_shopping_key' => $key, 'meta_shopping_value' => $val), $shopping_meta_where) . '<br/>';
                    } else {//KEY IS NEW
                        $return = $wpdb->insert($shopping_meta_table, $shopping_meta_values) . '<br/>';
                    }
                }//.end $key
                else {
                     //KEY IS NOT SELECTED ANYMORE SO DELETE key value pair row
                    $qdel = $wpdb->delete($shopping_meta_table,array('wp_user_id'=>$id));
                    $return = 'Deleted: '.$qdel;
                }//.end else {
            }//.end foreach checkKeyValue

        }//.end each
        echo 'Test return: ' . $return;
    }