SitePoint Sponsor

User Tag List

Results 1 to 13 of 13
  1. #1
    SitePoint Zealot trigger8's Avatar
    Join Date
    Nov 2000
    Location
    Dallas, TX
    Posts
    133
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Replace word in MySQL

    How do I replace a word that appears many times in a table? (I've got more than 7000 entries in that table)

    Is there anyway that I can accomplish that at once?
    Trigger8

    **************************
    I killed a 6-pack just to watch it die!

  2. #2
    :) delemtri's Avatar
    Join Date
    Jun 2001
    Posts
    579
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Try getting the data from the table, using eregi_replace, and then writing back to the table?

  3. #3
    SitePoint Zealot trigger8's Avatar
    Join Date
    Nov 2000
    Location
    Dallas, TX
    Posts
    133
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'm not familiar with that. Here's an example of what I want to do:

    How to replace all the 'old' in field1 to 'new'?

    table name: TABLE

    id field1 field2
    --------------------
    1 old1 blah
    2 old2 blah
    2 old3 blah
    Trigger8

    **************************
    I killed a 6-pack just to watch it die!

  4. #4
    :) delemtri's Avatar
    Join Date
    Jun 2001
    Posts
    579
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    PHP Code:
    $result mysql_query("select * from table");
    while (
    $row mysql_fetch_assoc($result))
    {
    $id $row["id"]; $field1 $row["field1"];
    $field1 eregi_replace("old""new"$field1);
    $result mysql_query("update table set field1='$field1' where id='$id'");


  5. #5
    SitePoint Zealot trigger8's Avatar
    Join Date
    Nov 2000
    Location
    Dallas, TX
    Posts
    133
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Can I just use
    eregi_replace("old", "new", $field1); ?
    Trigger8

    **************************
    I killed a 6-pack just to watch it die!

  6. #6
    :) delemtri's Avatar
    Join Date
    Jun 2001
    Posts
    579
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Don't think so. It doesn't really make that much of a difference, does it?

  7. #7
    SitePoint Zealot trigger8's Avatar
    Join Date
    Nov 2000
    Location
    Dallas, TX
    Posts
    133
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Are you familiar with REPLACE() ?
    Trigger8

    **************************
    I killed a 6-pack just to watch it die!

  8. #8
    :) delemtri's Avatar
    Join Date
    Jun 2001
    Posts
    579
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    No...

  9. #9
    midnight coder
    Join Date
    Dec 2000
    Location
    The flat edge of the world
    Posts
    838
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Since it's only a simple replacement, it is recommanded that you use str_replace instead of eregi_replace since str_replace is waay faster.

    http://php.net/manual/en/function.str-replace.php

  10. #10
    SitePoint Zealot trigger8's Avatar
    Join Date
    Nov 2000
    Location
    Dallas, TX
    Posts
    133
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I already checked out the site but still kinda confuse with the command. Can you please show me how to use it in regards to the above example?
    Trigger8

    **************************
    I killed a 6-pack just to watch it die!

  11. #11
    SitePoint Zealot alexk's Avatar
    Join Date
    Nov 2000
    Location
    Sydney, Australia
    Posts
    134
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Code:
    UPDATE your_table SET col_name=REPLACE(col_name, 'old string', 'new string');
    col_name should be VARCHAR, BLOB, or TEXT

    HTH

  12. #12
    SitePoint Zealot trigger8's Avatar
    Join Date
    Nov 2000
    Location
    Dallas, TX
    Posts
    133
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yes, this is exactly what I wanted. Thanks!!
    Trigger8

    **************************
    I killed a 6-pack just to watch it die!

  13. #13
    SitePoint Zealot alexk's Avatar
    Join Date
    Nov 2000
    Location
    Sydney, Australia
    Posts
    134
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    you're welcome!


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •