How do I search and replace in Phpmyadmin

Posted on 2016-07-23
Last Modified: 2016-07-29
Hi Phpmyadmin  experts!

I have to search and replace one color with another.  

What is the procedure in Phpmyadmin

To be very specific, the color I have is  #536895    and I want to replace it with #1e4b87

And, yes, I have created and downloaded a backup of the mysql file.


Question by:Rowby Goren
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 41725892
color of what/where?
please clarify

Author Comment

by:Rowby Goren
ID: 41725900
Hi Guy,

I have a website with colors that are controlled by a mysql database.

So I want to go into Phpmyadmin and do a search and replace of the entire mysql database and anywhere it finds this exact string #536895  
to replace it with this exact string: #1e4b87

Those strings are color codes representing a two different specific color.    So by replacing  #536895   with #1e4b87  will globally replace the first color with the second throughout the entire site.

LVL 22

Assisted Solution

by:Kim Walker
Kim Walker earned 250 total points
ID: 41725904
I'm with Guy Hengel. You're talking about a color which sounds as if you're working in CSS but you'ure using phpMyAdmin which is a database administration tool?

Do you have a table with a column that contains a color that you want to update with a different color? If so, you would need to execute an SQL statement for that.
UPDATE `TableName` SET `ColumnName` = "#1e4b87" WHERE `ColumnName` = "#536895"

Open in new window

BEWARE that this will replace the color in EVERY record that it matches.

And, yes, I have created and downloaded a backup of the mysql file.
How did you do this and what are you referring to as a "mysql file" -- the table? Did you export the table?

This sounds like you're playing with dynamite. Are you sure this is what you want to do and why are you trying to do it in a database? Or have I misunderstood your question? The question is very vague so I could have misinterpreted it.
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

LVL 29

Accepted Solution

Olaf Doschke earned 250 total points
ID: 41725906
If you expect a general script going through all data of a database, this is not how databases work, a database is not a document you can change with search&replace. There is no aspect of phpMyAdmin helping you with such an idea.

There is SHOW TABLES; to get a list of all tables, similarly SHOW COLUMNS FROM mydb.mytable; to find all columns and types and then you might generate a list of UPDATEs replacing the old with new color in all textual columns.

Most likely you will rather find colors defined in CSS files than in the database of the CMS and you'll be able to change colors in templates or CMS settings in its administration. I'd recommend you rather first find out details about the system and where it stores what info. phpMyAdmin is not your site administration tool, it's 'merely' a database administration tool and such a hack is never a good solution.

Bye, Olaf.

Edit: To give you a hint on how a good CMS or any system works: You have base classes, derived classes, settings files, templates, anything making up the system and you want all this system to work as is and to be updatable. There any change you do in its stem data, in it's base CSS and HTML files, etc, will be lost, if you update the CMS. Such systems therefore typically give you the opportunity to add further files, subclass classes or add your user templates to override or replace defaults, so that happens with additional files or additional data, not replaced, updated or altered with a CMS update.

Alone because of this nature of a good CMS or any other system, you should never even attempt such a hack but read enough documentation to know where and how to change look & feel with what official ways than hack into the data. You'll just have a temporary success and while in this example you don't have to fear much side effects, but more generally speaking this is how you lose warranties.
LVL 109

Expert Comment

by:Ray Paseur
ID: 41725919
Rowby: Does your site use a CMS like Joomla or similar?

Author Comment

by:Rowby Goren
ID: 41725951
Hi, all

Latest update.  Thanks for all the suggestions, warning and overall engagement with my "little" switch from one color to another.

And yes, this site is using Joomla.  So the color markup is all over the place. Some is in the database, some is in the global stylesheet.  Some components and modules have stylesheets -- not reflected in the database.

Taking the cautious side here's what I did.

1)  I used Phpmyadmin just to search for the old color.  Then when I found where the color was used, I went into my Joomla installation.  I have a great database replacer (Called DB Replacer by a long time joomla developer, named "Regular Labs".

DB Replacer has many options and can get me to the table level where I did some cautious replacing.  I went to the table where PHpmyadmin told me the old color was, and using DB Replacer fixed the colors in the database -- rather than doing it via Phpmyadmin.

Then I used Joomla's Akeeba backup to backup the site -- specifically all the files in the installation.  I downloaded the backup and extracted it into a folder.  

Then I used Windows Grep to search for "bad" color.   It found the colors in several css files scattered throughout the Joomla files (in components and modules, as well as the overall template for the site.

Then I went back into joomla and used an excellent and amazingly free built in filemanager called "Profiles" and went to each of those 6 files and replaced those colors.

That pretty much found everything -- although I see a few stray colors left on the site-- which I figure was caused by those colors not exactly matching my original search colors. Just a few.  Using Firebug I'll track those culprits down.

LVL 29

Expert Comment

by:Olaf Doschke
ID: 41726000
Joomla supports templates, which
a) define the full look of a site, including any color
b) are located in one template folder

So, I don't know why you would search and replace in so many places to change some color. Not even mentioning data.
You would just adjust all files belonging to the template in its folder. No more, no less.

Bye, Olaf.

Author Closing Comment

by:Rowby Goren
ID: 41735360
Thanks everyone.  As it turned out my css was all over the place. In the database, assorted files in multiple folders.  Astrogrep was also a big help

Featured Post

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQl Query to find x consecutive Nbrs in a Table 30 97
What is wrong with this PDO Delete Query? 2 17
Custom Wordpress Loop 22 38
Procedure syntax 5 38
These days socially coordinated efforts have turned into a critical requirement for enterprises.
This article discusses four methods for overlaying images in a container on a web page
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

809 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