Solved

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

Posted on 2015-02-19
7
230 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
The purpose of this video is to demonstrate how to update a WordPress Site’s version. WordPress releases new versions of its software frequently and it is important to update frequently in order to keep your site secure, and to get new WordPress…
The purpose of this video is to demonstrate how to set up an RSS Feed on a WordPress Website. This will be demonstrated using a Windows 8 PC. Feedburner will be used for this demonstration. Go to your WordPress login page. This will look like the…

910 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

19 Experts available now in Live!

Get 1:1 Help Now