Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

MySql Query to Insert New Meta_Key and Meta_value in table ONCE for each User_ID

Posted on 2015-02-19
7
Medium Priority
?
389 Views
Last Modified: 2015-02-21
I'm looking to generate a SQL query that ADDS a new meta_key & meta_value to our wp_usermeta table for each instance of a unique user id.  

Case:  We're attempting to quickly add a meta data value for each or our wordpress users that will be checked when an onsite action take place (registration, checkout, etc).

Query Structure:
columns:  umeta_ID (auto-increment) , user_id , meta_key , meta_value
- checks value of user_id
- checks if meta_key = "pmpro_trial_level_used" already exists for user_id (no duplicates)
- If not, adds meta_key = "pmpro_trial_level_used"
- adds meta_value="1"
- repeats for ALL unique user_id values in the table.  

Any suggestions?
0
Comment
Question by:michent1
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
7 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 40620597
I am not sure what the issue is, as the action seems to be "per user", and not for the full db

anyhow, are you looking for this syntax?
http://dev.mysql.com/doc/refman/5.7/en/insert-on-duplicate.html
0
 

Author Comment

by:michent1
ID: 40620611
Further explanation:
We have a subscription based plugin that handles whether or not a free trial is applied at check out by checking if the meta_value of the "pmpro_trial_level_used" meta_key is "1".  That specific meta_key & value is created in the user's meta data automatically when they sign up for the free trial for the first time preventing multiple applications of a free trial thereafter should they:
A: Upgrade/Downgrade to a New Membership level
B: Cancel their current membership & then resubscribe to the same level

The issue is that this is a new code implementation and NONE of our users have that meta_key & meta_value field in their user meta (as they've initiated their memberships long before we added the new trial function).  

So simply put,  I'm just trying to figure out to add that meta key and value for each user and thought an SQL query would be the simplest way to accomplish this.   Except... I have no clue how to write it.  :)

`I
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 total points
ID: 40620657
for this one-shot, this shall be the syntax:

insert into yourtable (user_id , meta_key , meta_value)
select  u.user_id, 'pmpro_trial_level_used', '1'
  from your_users_table u
 where not exists( select null from yourtable  x
                          where x.user_id = u.user_id
                              and x.meta_key = 'pmpro_trial_level_used'
 )

Open in new window

0
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 

Author Comment

by:michent1
ID: 40621788
Cool, thanks.  Will run some tests this afternoon and see how things go.  Out of curiosity, what is the "u" variable in, "u.user_id" ?
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 40622114
0
 

Author Comment

by:michent1
ID: 40622684
Thanks for the extra info.  After some testing, this did not insert the new meta key & values as we wanted.  This created multiple instances of the same key within a single user id.  We need the script to only insert ONE instance of meta_key 'pmpro_trial_level_used' with met_value '1' per user_id.

I don't know if this needs to be noted, but the purpose behind us executing this is to update all of the current users in the db.  As our php script will handle adding the key & value for any new users that may register in the future.
0
 

Author Comment

by:michent1
ID: 40622746
I dug a bit deeper and that did work after all.  I originally missed changing both the alias field & the users field to match the users table.  Thanks for the help!!!
0

Featured Post

10 Questions to Ask when Buying Backup Software

Choosing the right backup solution for your organization can be a daunting task. To make the selection process easier, ask solution providers these 10 key questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
The purpose of this video is to demonstrate how to manually back up a WordPress Database. This will be demonstrated using a Windows 8 PC. The Host used will be IPage.com Log into your Hosting account. IPage will be used for demonstration : Locat…
The purpose of this video is to demonstrate how to set up basic WordPress SEO. This will be demonstrated using a Windows 8 PC. The plugin used will be WordPress SEO by Yoast. Go to your WordPress login page. This will look like the following: myw…
Suggested Courses

661 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question