HELP: Stupid Display Bug Fix, I think I've got it, need SQL scripter Options

[McMark]

The stupid problem where post content doesn't appear looks like it's caused by improper HTML quotes. The erroneous quotes appear like this <!--XXXXXX--> and they should appear like this <!-- XXXXXX --> (notice the extra whitespace). So we need a SQL query that will search for <!-- followed by a character and add in a space, and another query to search for --> preceded by a character and add in a space. I have a bunch of other stuff to do, can someone savvy write the queries for me so I can run them?

[lapuwali]

Alas, SQL by itself doesn't really handle strings well enough to do what you want. I could write this in Perl in a few minutes. I *think* MySQL my also have some tricks to handle this, let me look in the MySQL docs...

[SirAndy]

Alas, SQL by itself doesn't really handle strings well enough to do what you want. I could write this in Perl in a few minutes. I *think* MySQL my also have some tricks to handle this, let me look in the MySQL docs...

(IMG:style_emoticons/default/agree.gif) just write a PHP script that loops through the DB and replaces the wrong entries ...

(IMG:style_emoticons/default/type.gif) Andy

[Rand]

There is a SQL "replace" command. A google search on "sql replace" will give you some ideas.

Thank you McMark!! I will be happy to not have to jump from Firefox to IE when the HTML comment bug hits! (IMG:style_emoticons/default/smilie_pokal.gif)

[eeyore]

MS-SQL can do

update <table> set <field> =
'<!-- ' + right(<field>, len(<field>) - len('<!--')) where <field> like '<!--%'

update <table> set <field> =
left(<field>, len(<field>) - len('-->')) + ' -->' where <field> like '%-->'

[lapuwali]

For MySQL, you can do regular expressions. This will find all of the rows where column foo has the bad comment:

SELECT * FROM table WHERE foo REGEXP '<!--[^ ]';

To fix these is hard in MySQL using just SQL, since MySQL doesn't do sub-queries (unless you have a REALLY recent version, and I'm not even sure then).

I'd just write this in Perl or PHP. It would be far easier. Just select out the rows you need as above (I presume there's a row id of some sort, like a post ID), and write the corrected field back out.

[swl]

MS-SQL can do

update <table> set <field> =
'<!-- ' + right(<field>, len(<field>) - len('<!--')) where name like '<!--%'

update <table> set <field> =
left(<field>, len(<field>) - len('-->')) where name like '%-->'

That looks pretty good. Only thing I don't quite get is the 'where name like' - shouldn't that be the field name - <field> in your typography?

[eeyore]

Oops. name = <field>. Thanks.

1st post re-edited.

[Pudge]

MS-SQL can do

update <table> set <field> =
'<!-- ' + right(<field>, len(<field>) - len('<!--')) where name like '<!--%'

update <table> set <field> =
left(<field>, len(<field>) - len('-->')) where name like '%-->'

If there is already a space in the string following '<!--' won't this add another space? not sure if this matters for the final output.

[eeyore]

...and <field> not like '<-- %'

???

[swl]

oops - second thought it won't work - there can be many occurrances of the quotes within the field not just a single quote that takes up the entire field.

[swl]

ok - how about the 'replace' function

update <table> set <field> = replace(<field>, '<!--','<!-- ')

That looks too easy?

http://mysql.com/doc/refman/5.0/en/string-functions.html

[Pudge]

...and <field> not like '<-- %'

???

Yeah, that's what i came up with too.

or

update DBO.mytable set [myfield] = replace([myfield],'<!--','<!-- ') where [myfield] like '<!--%' and [myfield] <> '<!-- %'

[swl]

update DBO.mytable set [myfield] = replace([myfield],'<!--','<!-- ') where [myfield] like '<!--%' and [myfield] <> '<!-- %'

yep. That would avoid updating records that did not have any comments without the space. On records that had at least one without a space all occurrences of comments would get a space added including ones that already have the space. Don't think having the double space would hurt the html at all though.

[McMark]

If anyone want to write a whole PHP script, that would work too. (IMG:style_emoticons/default/smiley_notworthy.gif)

Remember there are 700,000+ posts, so you have to loop through a few at a time or it'll time out. (IMG:style_emoticons/default/wink.gif)

[lapuwali]

I could do that, but I think it might be a lot simpler to rework the code that displays a post to fix this bug, by inserting the whitespace on the fly, as necessary. I'm betting this would be one line of code, just a simple string match/replace call. This could be submitted back to the Invision people, as it's clearly a bug if it works on IE but not on Firefox.