Solved

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

Posted on 2015-02-19
7
216 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
  • 4
  • 3
7 Comments
 
LVL 142

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 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

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 142

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

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This video teaches viewers how to create their own website using cPanel and Wordpress. Tutorial walks users through how to set up their own domain name from tools like Domain Registrar, Hosting Account, and Wordpress. More specifically, the order in…
The purpose of this video is to demonstrate how to add AdSense Ads to a WordPress Website, and how to set up WordPress to automatically place Ads in Sidebars. This will be demonstrated using a Windows 8 PC. Log into your AdSense account. : Cli…

758 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now