Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Help, I need 3 phpmyadmin sql queries for wordpress database (search & delete etc....)

Posted on 2014-04-01
2
Medium Priority
?
539 Views
Last Modified: 2014-04-02
Help, I need 3 sql queries for phpmyadmin:


I have a wordpress database

1. I need an sql query to search: "wp_terms" & "wp_term_taxonomy" tables
and delete every row that has different term_id except the rows with term_id: 78,32,61,44

Here is 2 pictures from the tables:
wp_terms wp_terms_taxonomy

2. I need an sql query to search "wp_postmeta" table for rows with meta_key "price" and meta_value more than 1 thousand (eg 1.084.51) and delete the first dot of the meta_value.
before: 1.084.51 -> after: 1084.51
I dont know if it is possible to do this with an sql query, anyway I hope it is possible.

Here is a picture:
price

3. I need an sql query to search "wp_postmeta" table and delete all rows that has the same post_id that do not include or include partial of the following list of meta_key column:
extraship_Austria, extraship_Belarus, extraship_Belgium, extraship_Brazil, extraship_Canada
extraship_Cyprus, extraship_Czech Republic, extraship_Denmark, extraship_Estonia, extraship_Finland
extraship_France, extraship_Germany, extraship_Greece, extraship_Hungary, extraship_Iceland
extraship_India, extraship_Ireland, extraship_Israel, extraship_Italy, extraship_Latvia
extraship_Luxembourg, extraship_Malta, extraship_Monaco, extraship_Netherlands, extraship_New Zealand
extraship_Norway, extraship_Poland, extraship_Portugal, extraship_Qatar, extraship_Russia
extraship_San Marino, extraship_Spain, extraship_Sweden, extraship_Switzerland, extraship_Turkey
extraship_Ukraine, extraship_United Arab Emirates, extraship_United Kingdom, extraship_United States
extraship_Uruguay, extraship_Vatican City State (Holy See), extraship_Australia

Open in new window

I want to keep only post_id's that have the full list of meta_key column. It is 42 meta_key records.
Also the post_id record it is included also in wp_posts table in one row only.
I want that row also to be deleted from wp_posts table.
The column header of wp_postmeta table is "post_id" and in wp_posts table is "ID"
I don't know if this is possible but I hope it is.

Here is a big picture edited by me to make you understand the structure:
posts post meta

I know I'm asking a lot and sorry about this. I will try to find what I'm looking for, even with php code but I hope to find a solution here.

Thanks in advance, Nicolas...
0
Comment
Question by:Nicolas Lagios
[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
2 Comments
 
LVL 35

Accepted Solution

by:
Dan Craciun earned 2000 total points
ID: 39971526
I'm curious if someone finds the time to answer all your questions.
Since these are different questions, you should ask in 3 separate posts.

I'll answer the easy one :)
DELETE FROM `wp_terms` WHERE term_id NOT IN (78,32,61,44);
DELETE FROM `wp_term_taxonomy` WHERE term_id NOT IN (78,32,61,44);

Open in new window


PS: I advise to always do a SELECT before you delete something, so you can test what data will be deleted. In this case, the test would be:
SELECT * FROM `wp_terms` WHERE term_id NOT IN (78,32,61,44);

Open in new window

HTH,
Dan
0
 

Author Comment

by:Nicolas Lagios
ID: 39972671
Ok thanks for the solution of the first question, I separated my other 2 questions:

here is the second question:
http://www.experts-exchange.com/Database/MySQL/Q_28403373.html

Open in new window


and here is the third question:
http://www.experts-exchange.com/Database/MySQL/Q_28403380.html

Open in new window



thank you Dan
0

Featured Post

Simplify Your Workload with One Tool

How do you combat today’s intelligent hacker while managing multiple domains and platforms? By simplifying your workload with one tool. With Lunarpages hosting through Plesk Onyx, you can:

Automate SSL generation and installation with two clicks
Experience total server control

Question has a verified solution.

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

In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
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…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

636 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