MySQL string replace in PhpMyAdmin

The MySQL string replace command is quite handy if you need to update a string in one of your table’s columns fast.

Even if this works basically anywhere, I’m talking about replacing a string using PhpMyAdmin as a lot of people use this handy tool. Either because Cpanel already has it installed for you or just because of how easy it is to use.

My solution is basically a mysql find and replace string in column. It’s quite fast.

I often find myself using this command on a client’s product table where their content writers copy-paste image links randomly into the description. Especially bad when they copy-paste HTTP links (I know, can you believe their supplier’s website does not use HTTPS in 2022?). But it’s not great as that image must be downloaded from somewhere else and not using the CDN or at least their own server and domain name.

Let’s start.

The problem

So we have this table named ‘ps_product_lang’. And it has a column named ‘description’, where each product has its own description stored. That gets outputted to the front end. Great!

So we’ve identified about 600 descriptions that have HTTP image links to another domain somehow. (I found out using Ahrefs Site Audit feature, but there are plenty of other tools and even just manually checking for issues is a thing)

The situation above is described in this screenshot of PhpMyadmin:

• 

note: yes, it’s a Prestashop store

Enter our MySQL replace function

Replacing a string in MySQL

After you’ve determined the name of the database, table, and column we need to work on and, most importantly, the string we need to replace and what to replace it with, do this in PhpMyAdmin:

1. Select the database.
2. Select the table
3. Go to the SQL tab (upper section of your screen)
4. Modify and run the below query

So the actual query we’re going to run is this:

UPDATE your_table
SET your_field = REPLACE(your_field, 'HTTP:', 'HTTPS:')
WHERE your_field LIKE '%HTTP:%'

You need to change the bolded parts.

your_table is your table’s name, your_field is the column name and the HTTP: and HTTPS: are the actual strings.

The above query will make all the links in the selected field go from HTTP to HTTPS. You can think about it like a phpMyAdmin find and replace. Sort of :))

Easy!

Please note that in such scenarios, it is vital to also put those two dots… “:”. Or else you’ll replace the HTTP part of a HTTPS link and you’ll end up with something like HTTPSS. I know it’s logic, but it can be easily overlooked. Check it before you actually run the query. In all fairness, you should really have a test installation or a backup so… yeah.

Conclusion

So that’s about it. A nice and easy way to replace a string using a query just like you would do it in a text editor with find and replace.

And I can’t stress this enough: be wary of what and where you replace so you don’t mess things up. In my case, an old budgetless Prestashop installation, this dirty quick method does the job. This might not work for product descriptions in other complex CMS systems or whatever application it is.

It might help you in other scenarios and its a handy option to have in mind – especially as it is quite fast. Have fun!