Important mysql quries


Taking backup of all mysql databases

>mysqldump -uroot -p --all-databases >> all_database_dump.sql;

for single database

mysqldump -uroot -p public_wordpressmu wp_1_posts > one.sql

database import from sql file

mysql –verbose -uroot -p public_wordpressmu wp_1_posts < one.sql

Creating Trigger

create trigger
DROP TRIGGER wordpress.after_wp_1_post_update;

delimiter //

CREATE TRIGGER wordpressmu.after_wp_1_post_update

AFTER UPDATE ON wordpressmu.wp_1_posts

FOR EACH ROW

IF new.post_status = ‘publish’ THEN

INSERT INTO .article_posts (id,ref_id, title, status,
comments_on, created_at, updated_at) values (new.id,new.id,
new.post_title, new.post_status, 1, new.post_date, new.post_date);

END IF;

//

delimiter ;

Insert from different table (migration)

INSERT INTO database_anme.TableName(id, ref_id, title, status, comments_on, created_at, updated_at) select id, id, post_title, post_status, 1, post_date,post_date from wordpress.wp_1_posts

Update multiple rows in through Sql query

Update user set status=”ok” where id in (2 ,3 ,4 ,5,6)

changing all post path and image path
UPDATE wp_1_posts SET post_content = REPLACE(post_content, ‘test.purab.com’, ‘www.purab.com’);

WordPress -fetching category through sql queryfrom post

SELECT wp_1_term_taxonomy.taxonomy, wp_1_term_relationships.object_id, wp_1_terms.name, wp_1_posts.post_title
FROM wp_1_posts INNER JOIN ((wp_1_terms INNER JOIN wp_1_term_taxonomy ON wp_1_terms.term_id = wp_1_term_taxonomy.term_id) INNER JOIN wp_1_term_relationships ON wp_1_term_taxonomy.term_taxonomy_id = wp_1_term_relationships.term_taxonomy_id) ON wp_1_posts.ID = wp_1_term_relationships.object_id
WHERE wp_1_term_taxonomy.taxonomy=’category’ and wp_1_term_relationships.object_id=’217′
ORDER BY wp_1_term_relationships.object_id ;

Advertisements
This entry was posted in Linux Fedora8, MySql and tagged , , by Purab Kharat. Bookmark the permalink.

About Purab Kharat

I am WordPress and Drupal Architect. I worked on many technologies like Ruby on Rails, Flex, PHP etc... I am Purab Kharat from India, Pune. I love to play chess and listen music and read books. I work in SY Smartsourcing Global Pvt Ltd in Princeton, NJ, USA. Now I am living at Plainsboro, 164 Hampshire Drive, Princeton Meadows, NJ. I have more then Ten years experience in Web Application Development (Specially in Php 5.0, Ruby on Rails, Mysql 5.0, RIA, Wordpress, Drupal, Joomla, Ecommerce)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s