View Full Version : finding and replacing strings in database

07-15-2008, 04:42 PM
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 " ":

$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']."'";
If anyone has a better suggestion than mine let me know. Thanks.

07-15-2008, 09:29 PM
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).

07-15-2008, 09:42 PM
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?

if($acronym."_correction_nbsp" == false){
$sql_record = "CREATE TABLE ".$acronym."_correction_nbsp
".$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.

07-15-2008, 09:58 PM
You need to query for the existence of the tables, and only construct if there is no table created.

$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.

07-15-2008, 10:51 PM
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:

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.

07-15-2008, 11:07 PM
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.

07-15-2008, 11:25 PM
it's giving the same result. But I'm not sure if that's the problem.

07-16-2008, 12:20 AM
Can you post you're insert command and where the variables are coming from to populate it with?

07-16-2008, 12:35 AM
I don't understand. It's still giving problems:

$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

07-16-2008, 01:21 AM
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!

07-16-2008, 04:49 AM
Single quotes were missing:

".$row['content'].", ".$new.")";
should be:

'".$row['content']."', '".$new."')";

07-16-2008, 05:20 AM
Single quotes were missing:

".$row['content'].", ".$new.")";
should be:

'".$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!

07-16-2008, 06:00 AM
Yeah. I was scratching my head as I was reading commas. I was saying, "I don't understand. I have commas!"