Solved

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

Posted on 2014-04-01
2
504 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
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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Creating and Managing Databases with phpMyAdmin in cPanel.
3 proven steps to speed up Magento powered sites. The article focus is on optimizing time to first byte (TTFB), full page caching and configuring server for optimal performance.
The viewer will learn how to dynamically set the form action using jQuery.
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.

778 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