I'm not sure if this is a bug, or pilot error, but here it goes:

I fear I’m a little bit unfamiliar with the technically correct vernacular, so I’m going to use an almost identical analogy that I have personally experienced with Microsoft Excel.

Often, for long and or complicated or repeated formulas, I like to use the “concatenate” function in Excel, but there is an odd twist with that function when building macro code or formulas.
Of course Excel will not “read” a concatenated result until you first perform a Copy>Paste Special>Paste Values.
However, an extra little twist is that Excel will not “see” the pasted values as actual code or formulas either because the initial format is as “text”.
In other words, the formula just sits there, and is NOT evaluated.
However, Excel being what it is, I have discovered if I merely “click” inside the cell, and hit [enter], Excel re-evaluates the “type”, “sees” the formula, changes the cell type, and calculates the formula.
Another way I have discovered to force Excel to perform a bulk re-evaluation of the cell type is to perform a “Text to columns” step on single columns of pasted concatenated formulas being careful to select a delimiter that is not included in any of the formulas. Again, Excel examines each cell for the type of data contained in it, “sees” formulas, changes the cell types and evaluates the formulas.

OK, now that the analogy is explained, here is what’s happening to me that is REMARKABLY similar (if not identical) in a MySQL database.

We’re running UBB Threads version 6.5
In our forums, users are only allowed to use “markup” and can make links and post images and change font colors, and that is about it.

As an administrator in the forums, I have two ways of editing posts.
Firstly, to “edit” posts from within the “normal” forum view.
You get a fairly standard “post” screen and perform any edits you wish. In this screen you see ALL of the actual code for the post, including the markup code if any.
HOWEVER, the catch is that when you edit a post via this means, the forum software adds an extra comment at the bottom of the post indicating it was edited, by whom and when it was edited.
Secondly, is to open the main “control” panel by a manual SQL Command.
Having tested a few things here, there does not appear to be anything I can’t do here using standard MySQL query syntax.
Including editing posts WITHOUT leaving the “edited by” signature as happens when I use the first method above.

If I perform a query to look at a specific post in the SQL command screen that INCLUDES any markup commands, what I actually see is the markup EVALUATED result, NOT the actual base content. For example, if a post has this actually visible:
“Please see the picture here” (where the word “here” is actually a link, then I know that the actual content of the cell has to be “Please see the picture path/here”
However, again, all I can see in the SQL Command screen is the evaluated result, not the actual content.

So we have a situation where we have changed a page on the main site and not in the forums, and it actually has a new URL to the page.
There are several links throughout the forums to the ORIGINAL page that I want to update to the new path, but I would prefer to not leave the “edited by” signature in each post by performing the edits manually.
Using the SQL command screen, I can easily find each of the posts that contain the link by using the InStr function.

Now here is where things start getting like the Excel analogy.
As a test similar to this example (referencing the same above example):
UPDATE w3t_Posts SET w3t_Posts.B_Body = "Please see the picture path/there"
WHERE (((w3t_Posts.B_Number)=3300));

The SQL command screen correctly told me that 1 record was effected.

HOWEVER, when I view the post in either the forum, or via the SQL COMMAND, it is no longer evaluating the markup!!!
All the code is there, exactly as it should be, but it is not being evaluated.
And here is where it gets EXACTLY LIKE THE EXCEL ANALOGY.
If I go to the regular Forum view, and go to edit the post (as described in the first option above), THE MARKUP is perfectly and correctly evaluated without me having to change a thing!!!
Just like what happens in Excel by clicking in a cell and hitting enter with concatenated formulas.
The problem is that if I “go back” it does not save that view, and it remains the “un-evaluated version”.
So I end up back at square one having to manually edit each of the posts and leaving the edited by signature.

Before you suggest it, yes, I already guessed that I could edit the tables to remove the flag for “edited by”, I would miss this chance to try and understand what is physically happening here.

Now where things are different between Excel and MySQL is that Excel evaluates cell types on the fly, by itself, every time you hit “enter”. Sure you can “force” a cell type to be one thing, but it actually ALWAYS re-evaluates your forced type anyway (except when you force the type to be plain text).
HOWEVER, with databases, MySQL being no exception, there is no cell types. Data are forced to certain data types by the column format, and not by the cell at all.

So, can anybody help me understand what is happening here?
Is there an SQL function that will force the contents to be evaluated?

Sorry for the long winded explanation, but when I’ve discussed this with others they get a blank look and I end up giving the excel analogy anyway, so I thought I’d just get it all out in one shot.

Thanks in advance for any help or explanations.

Last edited by bilbo; 05/18/2007 7:47 PM.