MySql Snippets

Perform global search and replace of partial text

 UPDATE `tablename` SET `column` = REPLACE( COLUMN, 'searchstring', 'replacestring' );

Set timestamp to current and update on db update

ALTER TABLE `tablename` [MODIFY|ADD] `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
 

Query for next and previous records in table

e.g return the id of the next record, sorted by title

SELECT id, title FROM my_table WHERE title > (SELECT title FROM my_table WHERE id = my_id) ORDER BY title ASC LIMIT 1

and previous record:

SELECT id , title FROM my_table WHERE title < (SELECT my_table FROM my_table WHERE id  = my_id)  ORDER BY title DESC LIMIT 1

Leave a Reply

You must be logged in to post a comment.