phpmyadmin Question posted in
The official documentation can be found here.

Replacing some URLs with another – Phpmyadmin

I have a MySQL 5.7.29 database on which a website is built. I need to construct a query to find all table rows containing lines such as

https://example.com/index.php?topic=7989.0

or similar and replace them with

https://example.com/my-redirect-page/?7989.0

The wildcard here is the ?topic=7989.0 as this can be something like ?topic=1234 or even ?topic=3456.0#anchor

I can display the rows they appear in (in PHPMyAdmin) using this (thanks to ‘sticky bit’ below) :

SELECT * FROM `abc_posts` WHERE `post_content` LIKE '%example.comindex.php?topic%'

My problem is that I then need to change just that URL when there is also text content around it.

Thanks in advance.

Tags:

2

Answers


  1. 0

    The question mark doesn’t need to be escaped.

    But 'https://example.com/index.php?topic=7989.0' isn’t LIKE '%example.com?topic%' as the question mark doesn’t immediately follow the host name.

    Try:

    ...
post_content LIKE '%example.com/index.php?topic%'
...
    Login or Signup to reply.
  2. 0

    You could do something like thin to find them

    SELECT 'https://example.com/index.php?topic=7989.0'
WHERE 'https://example.com/index.php?topic=7989.0' REGEXP 'example.com/index.php?topic=';

    Which would find all rows. But for replacing it, you must also tell which database version youh have mysql 5.x have not many regex fucntions mariadb and mysql 8 have much more

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search