Replacing a string of HTML in a database field

[facarroll]

Hi all.

I am trying to replace a string of HTML contained in a field. I inherited this data as it is. I want to format a table displaying the data in Result so that the columns display with controlled widths.

The field is named Result and is a mediumtext type. The table is named quiz.

Here is my code.

UPDATE quiz SET Result = REPLACE(Result, '

<table border=1 bordercolor=#88BBDD cellpadding=0 cellspacing=0 width=100% style=\'border-collapse: collapse\'>
  <tr bgcolor=#B1D2E9>
    <td><font size=2>Question</font></td>
    <td><font size=2>Question Type</font></td>
    <td><font size=2>Points</font></td>
    <td><font size=2>User\'s Response(s)</font></td>
    <td><font size=2>Correct Answer</font></td>
    <td><font size=2>Result</font></td>
  </tr>'
,

'<table border=1 bordercolor=#88BBDD cellpadding=0 cellspacing=0 width=100% style=\'border-collapse: collapse\'>
  <tr bgcolor=#B1D2E9>
   <td width=\'29%\'><font size=2>Question</font></td>
   <td width=\'13%\'><font size=2>Question Type</font></td>
   <td width=\'5%\'><font size=2>Points</font></td>
   <td width=\'29%\'><font size=2>User\'s Response(s)</font></td>
   <td width=\'29%\'><font size=2>Correct Answer</font></td>
   <td width=\'5%\'><font size=2>Result</font></td>
</tr>'

) WHERE userId LIKE 'Mary%';`

There must be something wrong because it doesn't work. The query is accepted by mysql but the data does not change. I suspect I have to escape some of the HTML somewhere.

Can anyone help?

[floridaflatlander]

This is how I do it

UPDATE table_name set column_name = replace (column_name, 'this_content', 'with_this_content');

remember to back it up first.

[fenway]

UPDATE without a WHERE clause? Dangerous.

[floridaflatlander]

UPDATE without a WHERE clause? Dangerous.

 

Yeah that would be nice if you need it.

[facarroll]

It might be dangerous if it worked, but it doesn't. That's the problem. Why doesn't it work?

[facarroll]

And anyway, there is a WHERE clause in the original code.

[Illusion]

Everything with-in the quotes matters . Are you sure..... you have two new lines before table tag?

 

It is always good idea to trim extra spaces and lines .....while searching for strings.

[facarroll]

Thanks illusion. I have since trimmed the spaces out, but to no avail. The script does not seem to be able to select a record, thus there is no REPLACE made.

[facarroll]

I revised the code to this.

UPDATE quiz SET Result = REPLACE(Result,
'<table border=1 bordercolor=#88BBDD cellpadding=3 cellspacing=0 width=100% style=\'border-collapse: collapse\'>
  <tr bgcolor=#B1D2E9>
    <td><font size=2>Question</font></td>
    <td><font size=2>Question Type</font></td>
    <td><font size=2>Points</font></td>
    <td><font size=2>User\'s Response(s)</font></td>
    <td><font size=2>Correct Answer</font></td>
    <td><font size=2>Result</font></td>
  </tr>',
'<table border=2 bordercolor=#88BBDD cellpadding=3 cellspacing=0 width=100% style=\'border-collapse: collapse\'>
  <tr bgcolor=#B1D2E9>
    <td width=30%><font size=2>Question</font></td>
    <td width=10%><font size=2>Question Type</font></td>
    <td width=3%><font size=2>Points</font></td>
    <td width=27%><font size=2>User\'s Response(s)</font></td>
    <td width=27%><font size=2>Correct Answer</font></td>
    <td width=3%><font size=3>Result</font></td>
  </tr>')
where managerId='1'

It still does not work. Is there a syntax error?

[Illusion]

I guess all the new lines and carriage returns need to be replaced with '\n' and '\r'.

 

[facarroll]

I've tracked the problem to the existence of the three occurrances of backslash. How can I deal with that?

[Illusion]

This is issue you normally face when you are not inserting the data properly into the table /

 

DId you use mysql_real_escape_string or addslashes  before inserting the HTML in the table?

 

Can you post the result  column data of the row where you are trying to replace HTML.

 

We don't need entire data ...  few tags above and below the HTML you are trying to replace

[facarroll]

I have found a very very ugly solution. I cannot do anything about the improper insertion of the data as it comes from a second party, so Illusion's solution, though correct, was not an option.

The solution was to write several REPLACE scripts and run them in sequence.

I did this;

UPDATE quiz SET 
Result = REPLACE(Result,
'<table border=1 bordercolor=#88BBDD cellpadding=0 cellspacing=0 width=100% ',
'<table border=1 bordercolor=#88BBDD cellpadding=3 cellspacing=0 width=100% '
)
WHERE id = '92';

UPDATE quiz SET 
Result = REPLACE(Result,
'<td><font size=2>Question',
'<td width=32%><font size=2>Question')
WHERE id = '92';

UPDATE quiz SET 
Result = REPLACE(Result,
'<td><font size=2>Question Type',
'<td width=10%><font size=2>Question Type')
WHERE id = '92';

UPDATE quiz SET 
Result = REPLACE(Result,
'<td><font size=2>Points',
'<td width=2%><font size=2>Points')
WHERE id = '92';

UPDATE quiz SET 
Result = REPLACE(Result,
'<td><font size=2>User',
'<td width=27%><font size=2>User')
WHERE id = '92';

UPDATE quiz SET 
Result = REPLACE(Result,
'<td><font size=2>Correct Answer',
'<td width=27%><font size=2>Correct Answer')
WHERE id = '92';

UPDATE quiz SET 
Result = REPLACE(Result,
'<td><font size=2>Result',
'<td width=2%><font size=2>Result')
WHERE id = '92';

 

This enabled me to simply not replace the parts of the HTML that included backslashes, thus bypassing the problem.

Thanks for everyone's help.

[facarroll]

Actually had to tweak this a bit more, because on cycling through, a later Replace was working off the wrong code. I had to do this;

	//Updates Result data with formatting to fix column widths in results display

$sql = "UPDATE quiz SET"; 
$sql = $sql." Result = REPLACE(Result,";
$sql = $sql." '<table border=1 bordercolor=#88BBDD cellpadding=0 cellspacing=0 width=100% ',";
$sql = $sql." '<table border=1 bordercolor=#88BBDD cellpadding=3 cellspacing=0 width=100% '";
$sql = $sql." )";
$sql = $sql." WHERE `managerId` = '$managerId' AND `userIdRec` = '$userIdRec' AND `quizId` = '$quizId' AND `quizTitle` = '$quizTitle' ";
$rs = mysql_query($sql);

$sql = "UPDATE quiz SET";
$sql = $sql." Result = REPLACE(Result,";
$sql = $sql." '<td><font size=2>Question<',";
$sql = $sql." '<td width=34%><font size=2>Query<')";
$sql = $sql." WHERE `managerId` = '$managerId' AND `userIdRec` = '$userIdRec' AND `quizId` = '$quizId' AND `quizTitle` = '$quizTitle' ";
$rs = mysql_query($sql);

$sql = "UPDATE quiz SET ";
$sql = $sql." Result = REPLACE(Result,";
$sql = $sql." '<td><font size=2>Question Type<',";
$sql = $sql." '<td width=8%><font size=2>Question Type<')";
$sql = $sql." WHERE `managerId` = '$managerId' AND `userIdRec` = '$userIdRec' AND `quizId` = '$quizId' AND `quizTitle` = '$quizTitle' ";
$rs = mysql_query($sql);

$sql = "UPDATE quiz SET";
$sql = $sql." Result = REPLACE(Result,";
$sql = $sql." '<td><font size=2>Points',";
$sql = $sql." '<td width=2%><font size=2>Points')";
$sql = $sql." WHERE `managerId` = '$managerId' AND `userIdRec` = '$userIdRec' AND `quizId` = '$quizId' AND `quizTitle` = '$quizTitle' ";
$rs = mysql_query($sql);

$sql = "UPDATE quiz SET";
$sql = $sql." Result = REPLACE(Result,";
$sql = $sql." '<td><font size=2>User',";
$sql = $sql." '<td width=27%><font size=2>User')";
$sql = $sql." WHERE `managerId` = '$managerId' AND `userIdRec` = '$userIdRec' AND `quizId` = '$quizId' AND `quizTitle` = '$quizTitle' ";
$rs = mysql_query($sql);

$sql = "UPDATE quiz SET";
$sql = $sql." Result = REPLACE(Result,";
$sql = $sql." '<td><font size=2>Correct Answer',";
$sql = $sql." '<td width=27%><font size=2>Correct Answer')";
$sql = $sql." WHERE `managerId` = '$managerId' AND `userIdRec` = '$userIdRec' AND `quizId` = '$quizId' AND `quizTitle` = '$quizTitle' ";
$rs = mysql_query($sql);

$sql = "UPDATE quiz SET ";
$sql = $sql." Result = REPLACE(Result,";
$sql = $sql." '<td><font size=2>Result',";
$sql = $sql." '<td width=2%><font size=2>Result')";
$sql = $sql." WHERE `managerId` = '$managerId' AND `userIdRec` = '$userIdRec' AND `quizId` = '$quizId' AND `quizTitle` = '$quizTitle' ";
$rs = mysql_query($sql);
//Feedback ...
if ($rs) {
  echo "feedMsg=Data has been posted successfully";	
  }
else {
  echo "feedMsg=Failed to post data to database";
}
?>