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?
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...
SirAndy
Aug 15 2006, 06:42 PM
QUOTE(lapuwali @ Aug 15 2006, 05: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...
just write a PHP script that loops through the DB and replaces the wrong entries ...
Andy
Rand
Aug 15 2006, 06:43 PM
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!
eeyore
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 '%-->'
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.
swl
Aug 15 2006, 07:30 PM
QUOTE(Cloudbuster @ Aug 15 2006, 04:46 PM)
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
Aug 15 2006, 07:43 PM
Oops. name = <field>. Thanks.
1st post re-edited.
Pudge
Aug 15 2006, 07:43 PM
QUOTE(Cloudbuster @ Aug 15 2006, 04:46 PM)
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
Aug 15 2006, 07:46 PM
...and <field> not like '<-- %'
???
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.
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
Pudge
Aug 15 2006, 08:05 PM
QUOTE(Cloudbuster @ Aug 15 2006, 05:46 PM)
...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
Aug 15 2006, 08:19 PM
QUOTE(Pudge @ Aug 15 2006, 06:05 PM)
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
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.
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.
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please
click here.