Kimler Adventure Pages (http://randsco.com)

After hunting down the syntax for a global search-and-replace in PMA (phpMyAdmin), I decided to start a list of useful phpMyAdmin SQL queries. (Mostly so I can find them quickly, but maybe someone else will find them helpful?)

Here's a short, but growing list of things I've needed or used in PMA (phpMyAdmin). I commonly search the Internet to find such jewels, as I need them, but figured it might be a good idea to post any I've found useful. If I needed them again, they'd be here and - what the heck - maybe someone else will benefit.

A (slowly-growing) list of phpMyAdmin techniques, tricks, snippits, methods that I've used:

1. Global Search and Replace
2. Bookmarking a Global String Search

- -

(1) Global Search and Replace

Some tutorials recommended using phpMyAdmin to export a text file, operating on it in their editor of choice, then re-importing it. Seems like a lot of extra work to me. Just do it in phpMyAdmin? (I tested and used the following in phpMyAdmin (v2.9.0.2) with MySQL (v4.1.10) and it worked as advertised).

UPDATE [table_name] SET [table_field1] = replace( [table_field1],'find','replace')

 

By adding a WHERE clause, one can more narrowly target the "global" search and replace:

UPDATE [table_name] SET [table_field1] = replace( [table_field1],'<p>','') WHERE [title_field2] like '%string%'

This will get rid of all paragraph tags in the 'table_field1', where 'table_field2' contains a particular 'string'.

Source

 

(2) Bookmarking a Global String Search

Sometimes, I need to find how many records contain a certain alphanumric string. I've generated MySQL queries for this before, but this is the first time that I've saved it as a bookmark and made it available to others.

The SQL query isn't fancy, it goes something like:

SELECT * FROM `[table]` WHERE `[field]' LIKE '%[string]%'

The tricky part, is to allow the string to be a variable. (Under the SQL tab, in phpMyAdmin, it allows one to bookmark a particular query.)

To allow the "[string]" part of the above SQL query to be a variable, one needs to change the entire SQL query to:

SELECT * FROM `[table]` WHERE `[field]` /* LIKE '%[VARIABLE]%' */

Note: the '%[VARIABLE]%' part is literal ... (i.e., you need the square brackets)

Give the bookmark a name, checking a box if you want "every user to access the bookmark" and/or whether you want to replace the existing bookmark (useful if you're testing) or not.

To RUN the bookmarked SQL query, simply hit the "SQL" tab, select the named bookmark from the drop-down list in the last section, add the name of the alphanumeric string you want to find in the "variable" box, then hit "Go". (You might wish to view the query first, to expand upon it, or see what it is doing, which is always a good thing).