Printable Version of Topic

Posted by: McMark Aug 15 2006, 06:21 PM

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?

Posted by: lapuwali Aug 15 2006, 06:34 PM

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

Posted by: SirAndy Aug 15 2006, 06:42 PM

just write a PHP script that loops through the DB and replaces the wrong entries ...

Andy

Posted by: Rand Aug 15 2006, 06:43 PM

There is a SQL "replace" command. A google search on "http://www.google.com/search?hl=en&q=sql+replace&btnG=Google+Search" 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!

Posted by: Cloudbuster Aug 15 2006, 06:46 PM

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 '%-->'

Posted by: lapuwali Aug 15 2006, 06:51 PM

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.

Posted by: swl Aug 15 2006, 07:30 PM

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?

Posted by: Cloudbuster Aug 15 2006, 07:43 PM

Oops. name = <field>. Thanks.

1st post re-edited.

Posted by: Pudge Aug 15 2006, 07:43 PM

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.

Posted by: Cloudbuster Aug 15 2006, 07:46 PM

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

???

Posted by: swl Aug 15 2006, 07:48 PM

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.

Posted by: swl Aug 15 2006, 08:05 PM

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

Posted by: Pudge Aug 15 2006, 08:05 PM

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

or

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

Posted by: swl Aug 15 2006, 08:19 PM

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.

Posted by: McMark Aug 15 2006, 09:28 PM

If anyone want to write a whole PHP script, that would work too.

Remember there are 700,000+ posts, so you have to loop through a few at a time or it'll time out.

Posted by: lapuwali Aug 15 2006, 11:00 PM

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.