...

View Full Version : finding and replacing strings in database



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

Fou-Lu
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).

gilgalbiblewhee
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
(
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.

Fou-Lu
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.


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

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

Fou-Lu
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.

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

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

gilgalbiblewhee
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

Fou-Lu
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!

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

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

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

Fou-Lu
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!

gilgalbiblewhee
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!"



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum