0

I have a table album that contains about 1000 rows. I want to update all rows. Table album have a column named path. See full table structure below. 

    +---------------------------------------------------------------------------------------------+
    |   id    |   name    |                              path                                     |
    +---------------------------------------------------------------------------------------------+
    |    1    |  Believe  | <a href="http://example.com/bollywood/Believe-1.html">Believe</a>     |
    +---------+-----------+-----------------------------------------------------------------------+
    |    2    | A Promise | <a href="http://example.com/bollywood/A-Promise-2.html">A Promise</a> |
    +---------+----------+------------------------------------------------------------------------+
    |    3    |  Forever  | <a href="http://example.com/bollywood/Forever-3.html">Forever</a>     |
    +---------------------------------------------------------------------------------------------+

I want to update path here. Want to add album in path for SEO friendly URLs. table should look like this for that goal.

    +--------------------------------------------------------------------------------------------------+
    |   id    |   name    |                             path                                           |
    +--------------------------------------------------------------------------------------------------+
    |    1    |  Believe  | <a href="http://example.com/bollywood/Believe-album-1.html">Believe</a>    |
    +---------+-----------+----------------------------------------------------------------------------+
    |    2    | A Promise | <a href="http://example.com/bollywood/A-Promise-album-2.html">A Promise</a>|
    +---------+----------+-----------------------------------------------------------------------------+
    |    3    |  Forever  | <a href="http://example.com/bollywood/Forever-album-3.html">Forever</a>    |
    +--------------------------------------------------------------------------------------------------+

I can't figure out how to use LIKE here, what to insert between % %. Please help me. Thanks in advance.

Pradeep
  • 39
  • 9
  • LIKE will help you select which records to update, but you will probably need to use REGEX to perform the string replacement. Lookup MySQL REGEX. – Sloan Thrasher Apr 11 '17 at 16:42
  • Can you suggest me a way how to perform regexp here, please. – Pradeep Apr 11 '17 at 17:05
  • Please show us the regex you are trying and describe how it does not meet your expectations. – lit Apr 11 '17 at 18:38
  • http://stackoverflow.com/questions/986826/how-to-do-a-regular-expression-replace-in-mysql So I would say you can't do it easy. maybe it is easier for you to scan through table and do it using php or python, or any other language outside MySQL server. And by the way - that is not very best idea to store html tag in database. – Alex Apr 11 '17 at 18:48
  • But storing it in database become mendetry in my case. `$_SERVER['PATH_INFO']` not working and I don't wanna depend on that. I developing a project on shared hosting and latter I will choose dedicated. Then different Apache version will create a problem and I don't want puzzle like that. – Pradeep Apr 12 '17 at 00:45

0 Answers0