?
Solved

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

Posted on 2014-04-01
2
Medium Priority
?
532 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

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

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 article, I’ll talk about multi-threaded slave statistics printed in MySQL error log file.
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.
Suggested Courses

752 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