[Resolved] solving speed problem, but created new database text problem

[happyme]

I have a database driven website which has started to run really slowly.

It displays the first 250 characters of large text fields and so I thought I would speed things up by putting the 250 characters into a separate field as each entry was added or amended. To get the existing entries up to date I wrote a script which adds and fills the new column.

It generates errors when the text inludes punctuation like ( or )

this is the script:

<?php

//connect to database here


//get names of all tables to update

$result = mysql_query("SELECT tablename FROM tablelist") or die("Technical error 12, ".mysql_error());
while ($myrow1 = mysql_fetch_array($result))

$tablename= $myrow1["tablename"];
echo "<p>$tablename</p>";
{

//add new column to each table

$sql = "ALTER TABLE $tablename ADD `summary` TEXT NOT NULL AFTER `content`";
echo "<p>$sql</p>";
$result2 = mysql_query($sql) or die("Technical error 16, ".mysql_error());

//create and add text for new field

$result3 = mysql_query("SELECT id,content FROM $tablename") or die("Technical error 21, ".mysql_error());
while ($myrow2 = mysql_fetch_array($result3))
{
	  $id = $myrow2["id"];
	  $length = 250;
 	  $mySubstring = ereg_replace("^(.{1,$length})[ .,].*", "\\1",$myrow2["content"] );

$sql = "UPDATE $tablename SET summary = '$mySubstring' WHERE id = $id";
echo "<p>$sql</p>";
$result4 = mysql_query($sql) or die("Technical error 28, ".mysql_error());

}//end of update table while loop

}//end of tablename while loop

echo"all done";

?>

any ideas? I don't understand how the text got in there in the first place without any problem.

Also any ideas on speeding up my scripts?

TIA

Julia

[happyme]

I used

mysql_real_escape_string()

and

set_time_limit(0);

[mtimdog]

An easier way to do it for all tables would be using this.

update $tablename set summary = Left(content,250)

That way you shouldn't have to worry about sending back to php (all of it is done in mysql) and this will probly take care of your ( and ) errors.