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 13 of 13
  1. #1
    Regular Coder
    Join Date
    Mar 2005
    Posts
    735
    Thanks
    4
    Thanked 1 Time in 1 Post

    finding and replacing strings in database

    I'm trying to find which function fits the script. My objective is to find all the records where there are   including the misspellings (nbsp;, nbsp, &nbsp) and replace them with the space bar " ":
    PHP Code:
    $sql "SELECT * FROM ".$acronym."_pages WHERE content LIKE '%nbsp%'";
    $result mysql_query($sql) OR exit( 'Error: ' mysql_error() );
        
    //echo $sql;
        
    function nbspSearch(){
            
    //insert code here
        
    }
        while(
    $row mysql_fetch_array($result)){
            
    sql2 "UPDATE ".$acronym."_pages SET content = '".str_replace(nbspSearch(), " "$row['content'])."' WHERE content = '".$row['content']."'";
        }
    mysql_close($con); 
    If anyone has a better suggestion than mine let me know. Thanks.

  • #2
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,979
    Thanks
    4
    Thanked 2,659 Times in 2,628 Posts
    I'm pretty sure that mysql has a search and replace text function, but my sql has become quite rusty. Personally, I'd do it how you have it, but if you have a unique identifier on the record I would update where that record exists instead of comparing the content - the indexing will really speed it up if you can use it. I wouldn't be too worried unless you are dealing with 100,000+ records than you may want to consider changing it: 3 minutes versus 2 hours is quite a difference (no idea if thats a practical time, but optimization of sql really makes a difference).
    PHP Code:
    header('HTTP/1.1 420 Enhance Your Calm'); 

  • #3
    Regular Coder
    Join Date
    Mar 2005
    Posts
    735
    Thanks
    4
    Thanked 1 Time in 1 Post
    how do you make your if statement to see if the table is created you skip the process of creating the table else you you create the table?
    PHP Code:
        if($acronym."_correction_nbsp" == false){
            
    $sql_record "CREATE TABLE ".$acronym."_correction_nbsp
            (
            cor_ID int(6) NOT NULL AUTO_INCREMENT,
            PRIMARY KEY(cor_ID),
            "
    .$acronym."_pages_id int(6),
            content_before longtext,
            content_after longtext
            )"
    ;
            
    mysql_query($sql_record,$con) or die(mysql_error());
        } 
    This doesn't seem to work.

  • #4
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,979
    Thanks
    4
    Thanked 2,659 Times in 2,628 Posts
    You need to query for the existence of the tables, and only construct if there is no table created.
    PHP Code:
    <?php
    $constructed 
    = array();
    $sQry "SHOW TABLES FROM yourdbname";
    $qry mysql_query($sQry) or die(mysql_error());
    while (
    $tables mysql_fetch_row($qry))
    {
        
    $constructed[] = $tables[0];
    }

    if (!
    in_array('yournewtablename'$constructed))
    {
        
    // Do you're create table
    }
    SQL will reject any new table creation with that name, so theoretically you can build a program to ignore any new table creations and simply issue a warning indicating that a new table was not created for you. How this is handled is really up to the developer and what they want it to do. When I write an installer I scan for all currently existing tables, check to see if any of the tables I need are in use, and terminate the installer indicating that a new prefix needs to be selected for the db tables if the table(s) are not available.

    The big thing to remember is that SQL and PHP are not related in any way whatsoever. So, with you're check: if($acronym."_correction_nbsp" == false){ , this will only work if the variable $acronym."_correction_nbsp" table was retrieved and stored in this variable from the record set.
    PHP Code:
    header('HTTP/1.1 420 Enhance Your Calm'); 

  • #5
    Regular Coder
    Join Date
    Mar 2005
    Posts
    735
    Thanks
    4
    Thanked 1 Time in 1 Post
    Ok. The next problem is that it's stuck on the 2nd line where there are strange squares which are not interpreted as any letters, numbers, or punctuations:
    Code:
    CREATE TABLE bwas_correction_nbsp ( cor_ID int(6) NOT NULL AUTO_INCREMENT, PRIMARY KEY(cor_ID), bwas_pages_id int(6), content_before longtext, content_after longtext )
    You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' CHAPTER 1  ,  CHAPTER 1  )' at line 2
    This was a pdf converted into html and then placed in a database. So there are a lot of these squares that need to be eliminated.

  • #6
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,979
    Thanks
    4
    Thanked 2,659 Times in 2,628 Posts
    I've never stored a PDF in a DB before, but I'm guessing its looking to store it in a binary format. Try using a LONGBLOB instead of LONGTEXT. See if that makes mysql happy
    Oh, since you've converted it to HTML, it may be unhappy unless you've stored it in base64. I wouldn't expect that it would need that, but the conversion may be using an incompatible charset that makes sql unhappy.
    PHP Code:
    header('HTTP/1.1 420 Enhance Your Calm'); 

  • #7
    Regular Coder
    Join Date
    Mar 2005
    Posts
    735
    Thanks
    4
    Thanked 1 Time in 1 Post
    it's giving the same result. But I'm not sure if that's the problem.

  • #8
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,979
    Thanks
    4
    Thanked 2,659 Times in 2,628 Posts
    Can you post you're insert command and where the variables are coming from to populate it with?
    PHP Code:
    header('HTTP/1.1 420 Enhance Your Calm'); 

  • #9
    Regular Coder
    Join Date
    Mar 2005
    Posts
    735
    Thanks
    4
    Thanked 1 Time in 1 Post
    I don't understand. It's still giving problems:
    PHP Code:
    $new preg_replace('#&?nbsp;?#i'' '$row['content']);
            
    $new .= preg_replace("/[^ -]/"""$new);
                echo 
    $new."<br />\n";
                
    $sql_record2 "INSERT INTO correction_nbsp (".$acronym."_pages_id, content_before, content_after)
                VALUES ("
    .$row['id'].", ".$row['content'].", ".$new.")";
                
    mysql_query($sql_record2,$con) or die(mysql_error());
                echo 
    $sql_record2."<br />\n"
    You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' CHAPTER 1  ,  CHAPTER 1  CHAPTER 1 )' at line 2

  • #10
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,979
    Thanks
    4
    Thanked 2,659 Times in 2,628 Posts
    The syntax error is that the inserted values need commas around them. However, I think you'll have problems actually going through it - I don't think you're preg will actually read through the binary data correctly, but there is only one way to find out!
    PHP Code:
    header('HTTP/1.1 420 Enhance Your Calm'); 

  • #11
    Regular Coder
    Join Date
    Mar 2005
    Posts
    735
    Thanks
    4
    Thanked 1 Time in 1 Post
    Single quotes were missing:
    PHP Code:
    ".$row['content']."".$new.")"; 
    should be:
    PHP Code:
    '".$row['content']."''".$new."')"; 

  • Users who have thanked gilgalbiblewhee for this post:

    Fou-Lu (07-16-2008)

  • #12
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,979
    Thanks
    4
    Thanked 2,659 Times in 2,628 Posts
    Quote Originally Posted by gilgalbiblewhee View Post
    Single quotes were missing:
    PHP Code:
    ".$row['content']."".$new.")"; 
    should be:
    PHP Code:
    '".$row['content']."''".$new."')"; 
    Lol, thanx mate, I was just about to say 'wtf thats just what I said!!!'. But I messed up the above post, I said commas not quotes. Thanks for the fix!
    PHP Code:
    header('HTTP/1.1 420 Enhance Your Calm'); 

  • #13
    Regular Coder
    Join Date
    Mar 2005
    Posts
    735
    Thanks
    4
    Thanked 1 Time in 1 Post
    Yeah. I was scratching my head as I was reading commas. I was saying, "I don't understand. I have commas!"


  •  

    Posting Permissions

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