Solved

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

Posted on 2015-02-19
7
303 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
Get Database Help Now w/ Support & Database Audit

Keeping your database environment tuned, optimized and high-performance is key to achieving business goals. If your database goes down, so does your business. Percona experts have a long history of helping enterprises ensure their databases are running smoothly.

 

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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL several years ago, it seemed like now was a good time to update it for object-oriented PHP.  This article does that, replacing as much as possible the pr…
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 reset a WordPress password if you are locked out and cannot reset the password. A typical use would be if you cannot access the email to which WordPress would send the password recovery email to…
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.

739 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