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

Sorry, Viggo, too tired, parentheses above are incorrect. A more correct version will be:

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 1) = '''' AND
(SUBSTRING(CATEGORY FROM 17 FOR 1) <> '''' OR
(SUBSTRING(CATEGORY FROM 16 FOR 1) = '''' AND
(SUBSTRING(CATEGORY FROM 15 FOR 1) <> '''' OR
(SUBSTRING(CATEGORY FROM 14 FOR 1) = '''' AND
(SUBSTRING(CATEGORY FROM 13 FOR 1) <> '''')))))))

(added support for ''' and '''' in the html)

Set