Solved

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

Posted on 2014-04-01
2
475 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
2 Comments
 
LVL 34

Accepted Solution

by:
Dan Craciun earned 500 total points
Comment Utility
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
Comment Utility
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Creating and Managing Databases with phpMyAdmin in cPanel.
Nothing in an HTTP request can be trusted, including HTTP headers and form data.  A form token is a tool that can be used to guard against request forgeries (CSRF).  This article shows an improved approach to form tokens, making it more difficult to…
The purpose of this video is to demonstrate how to prevent comment spam on a WordPress Website. This will be demonstrated using a Windows 8 PC. Plugin Akismet will be used. Go to your WordPress login page. This will look like the following: myw…
The viewer will learn how to count occurrences of each item in an array.

763 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

12 Experts available now in Live!

Get 1:1 Help Now