Solved

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

Posted on 2015-02-19
7
352 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 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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 

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

[Webinar] Learn How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Question has a verified solution.

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

Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
The purpose of this video is to demonstrate how to Import and export files in WordPress. This will be demonstrated using a Windows 8 PC. Go to your WordPress login page. This will look like the following: mywebsite.com/wp-login.php : Click on Too…
The is a quite short video tutorial. In this video, I'm going to show you how to create self-host WordPress blog with free hosting service.

635 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