Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 6 of 6
  1. #1
    Regular Coder
    Join Date
    Apr 2004
    Posts
    682
    Thanks
    24
    Thanked 1 Time in 1 Post

    Finding & replacing text at the start of a string..

    I have some entries in my database which contains certain characters through each entry, however I wanted to remove these characters if they appeared at the start of the entry; I tried the below code but ended up screwing up my DB where most of the updated fields ended up being blank & now I have to get a backup of my DB..

    PHP Code:
    <?php

    $count 
    0;

    mysql_connect($dbhost$dbuser$dbpass) or die("Couldn't connect to server.");
    @
    mysql_select_db($dbname) or die("Couldn't connect to database.");

    $q="SELECT id,lyrics FROM lyrics WHERE lyrics LIKE '<br><br>%' ORDER BY id ASC";
    $sql=mysql_query($q);

    while (list(
    $lid,$lyrics)=mysql_fetch_array($sql)) {

        if (
    preg_match('/^<br><br>/'$lyrics)) {

    $lyrics preg_replace('/<br><br>/','',$lyrics,1);
    $lyrics addslashes($lyrics);

    $q2="UPDATE lyrics SET lyrics='$lyrics' WHERE id='$lid' LIMIT 1";
    $sql2=mysql_query($q2);

    $count++;

        }

    }

    mysql_close();

    echo 
    "Done! Updated $count results!";

    ?>

  • #2
    Senior Coder NancyJ's Avatar
    Join Date
    Feb 2005
    Location
    Bradford, UK
    Posts
    3,169
    Thanks
    19
    Thanked 65 Times in 64 Posts
    I think the issue is you need to escape your <>
    PHP Code:

        
    if (preg_match('/^\<br\>\<br\>/'$lyrics)) {

    $lyrics preg_replace('/\<br\>\<br\>/','',$lyrics,1); 

  • #3
    Regular Coder
    Join Date
    Apr 2004
    Posts
    682
    Thanks
    24
    Thanked 1 Time in 1 Post
    According to this page you don't: http://weblogtoolscollection.com/regex/regex.php

    In order to be taken literally, you must escape the characters "^.[$()|*+?{\" with a backslash ('\'), as
    they have special meaning.

  • #4
    Regular Coder
    Join Date
    Apr 2004
    Posts
    682
    Thanks
    24
    Thanked 1 Time in 1 Post
    Anyone know?

  • #5
    Regular Coder
    Join Date
    Jun 2003
    Location
    Silicon Forest
    Posts
    155
    Thanks
    0
    Thanked 5 Times in 5 Posts
    I see a few things that may or may not be of consequence. I noticed that $q2="UPDATE lyrics SET lyrics='$lyrics' WHERE id='$lid' LIMIT 1"; is referring to the id column as text, when normally most people use a number, if that is incorrect, things simply won't be updating in your database, not being blank. Other than that I don't see anything blatantly obvious with your code. It would help if you had samples of the text you are attempting the regexes on, just for reference.

    I have an idea for you to try out, why not next time when you decide to update the database (after having restored it), disable the update query and output all the results that you are working with. Then you can see what you are changing at every step and then when you have it exactly the way you want it, re-enable the update query and it should work just fine. Mainly verify that the SQL queries look right and aren't just inserting blanks.
    Whats the point of a signature?

  • #6
    raf
    raf is offline
    Master Coder
    Join Date
    Jul 2002
    Posts
    6,589
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by CrzySdrs
    I see a few things that may or may not be of consequence. I noticed that $q2="UPDATE lyrics SET lyrics='$lyrics' WHERE id='$lid' LIMIT 1"; is referring to the id column as text, when normally most people use a number, if that is incorrect, things simply won't be updating in your database, not being blank.
    Thats not the reason. MySQL will ignore quotes around numeric values.

    i don't see any reason to use regex though. Simply
    PHP Code:
    if (strpos($lyrics'<br><br>') === 0) {
      
    $lyrics substr($lyrics8); 
    ... 
    would be faster.

    i also don't relly understand why you check this at all, sinde your select already has a LIKE '<br><br>%' ...
    you should be able to do it directly in sql like this
    UPDATE lyrics SET lyrics=SUBSTRING(lyrics,9) WHERE lyrics LIKE '<br><br>%'
    Posting guidelines I use to see if I will spend time to answer your question : http://www.catb.org/~esr/faqs/smart-questions.html


  •  

    Posting Permissions

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