>>$CATEGORY=str_replace("'","''",$_REQUEST['CATEGORY']);
>>
>>I convert html entities to text i can use on the web pages.
>>I use this for being able to retrieve it back as normal characters.
>>I use this on all my inserts.
>>
>>And, then i want to put it in the category field in the table.
>>Let's say the category field is 20 characters in length.
>>
>>If user is writing a lot of " the text will expand, even if it is set to stop at 20 characters. Because it adds extra characters to the converted variable.
>>and if i strip the characters to fit the db field, it can be that i loose one ' character, and then it is fucked up because it needs to of the ' for each ".
>>How can i best do this? How can i make the text fit in the db field the easiest way without getting the truncated string error?
>>
>>Is there another easier way?
>
>Just keep your inserts as they are and then run
>
>UPDATE <table>
>SET CATEGORY = SUBSTRING(CATEGORY FROM 1 FOR 19)
>WHERE PrimaryKey = :NewPK AND --or some other way to only update recently inserted rows
> (SUBSTRING(CATEGORY FROM 20 FOR 1) = '''' AND
> SUBSTRING(CATEGORY FROM 19 FOR 2) <> '''''') OR
> (SUBSTRING(CATEGORY FROM 18 FOR 3) = '''''''' AND
> SUBSTRING(CATEGORY FROM 17 FOR 4) <> '''''''''')
>
>to delete orphan ' at the last position.
>
>This should cover those situations where the html contains a ' or '' as the last characters to be stored (if the html can contain ''' or '''' at the end, you'll have to add another pair or two of lines).
>
>I've never done this myself, but I think it should work.

Just noticed that things can be simplified a bit:

UPDATE <table>
SET CATEGORY = SUBSTRING(CATEGORY FROM 1 FOR 19)
WHERE PrimaryKey = :NewPK AND
SUBSTRING(CATEGORY FROM 20 FOR 1) = '''' AND
(SUBSTRING(CATEGORY FROM 19 FOR 1) <> '''') OR
(SUBSTRING(CATEGORY FROM 18 FOR 3) = '''''''' AND
SUBSTRING(CATEGORY FROM 17 FOR 1) <> '''')

Maybe things can be even more simplified, though I cannot think of anything now...

Set