PHP/MySQL query (hopefully quick)
PHP/MySQL query (hopefully quick)
Author
Discussion

escargot

Original Poster:

17,123 posts

248 months

Wednesday 5th May 2010
quotequote all
Scenario:

I have a hosted MySQL database that serves data to my corporate website, the data is inputted via a password protected admin area using a HTML form.

Now, when I call this data to be displayed on the main site, it displays it as one chunk of text with all formatting removed, what I'd like to be able to do is for example, copy a word document into the text field and it retain the paragraphs and spacing etc. So infact, a system much like this forum where pressing enter in the 'start discussion' box is retained and displayed in the post itself.

Any idea how i'd go about this?

Don

28,378 posts

315 months

Wednesday 5th May 2010
quotequote all
First of all type some HTML into your HTML form with some formatting in it.

If it is rendered with the formatting when you view the resulting page then the contents of the text in the MYSQL database are just being output into the HTML stream. Piece of piss now: take your Word document (or whatever) and save it as HTML then cut and paste that into the field. Bingo. Job done.

If on the other hand the HTML formatting is shown literally on the page and you can see the tags then some funny business is going on and you'll need to find out what.

escargot

Original Poster:

17,123 posts

248 months

Wednesday 5th May 2010
quotequote all
DrTre said:
At a rough guess you'd have to use a BLOB datatype for the field in question. A pro will be along shortly though...
Hohum. It's currently a VARCHAR datatype.

DrTre

12,957 posts

263 months

Wednesday 5th May 2010
quotequote all
Arse, balls. I tried to edit my response (skim read what you wanted ...you're not after inputting a Word doc in)

Don is the man, ignore me.

Edited by DrTre on Wednesday 5th May 16:33

escargot

Original Poster:

17,123 posts

248 months

Wednesday 5th May 2010
quotequote all
Don said:
First of all type some HTML into your HTML form with some formatting in it.

If it is rendered with the formatting when you view the resulting page then the contents of the text in the MYSQL database are just being output into the HTML stream. Piece of piss now: take your Word document (or whatever) and save it as HTML then cut and paste that into the field. Bingo. Job done.

If on the other hand the HTML formatting is shown literally on the page and you can see the tags then some funny business is going on and you'll need to find out what.
Cheers Don, it's slightly convoluted doing it that way and would piss my consultants off if they had to continually save to HTML then cut & paste (plus it might fk the website formatting/CSS up). It's really only basic layout formatting that I require and of that, just paragraphs and bullet points.

Edited by escargot on Wednesday 5th May 16:33

cs02rm0

13,966 posts

222 months

Wednesday 5th May 2010
quotequote all
escargot said:
copy a word document into the text field
I think when you paste from Word, into a browser text field, all formatting is removed.

escargot

Original Poster:

17,123 posts

248 months

Wednesday 5th May 2010
quotequote all
cs02rm0 said:
escargot said:
copy a word document into the text field
I think when you paste from Word, into a browser text field, all formatting is removed.
Yes, font, italics, bold etc is removed - and that's fine. What isn't removed is the paragraphs and basic bullet points, that's all i'm after really.

-DeaDLocK-

3,368 posts

282 months

Wednesday 5th May 2010
quotequote all
I'm guessing it's not formatting you want to maintain per se (fonts, weights, sizes, styles etc), but just paragraph and line breaks, right?

If so, first thing to ensure is that the field in the database is a TEXT or BLOB type. Then just post the data to the database as you currently do. When you retrieve it to display on the main site though, run the nl2br() function on the string to convert all the line breaks to the <br> HTML tag. If your DOCTYPE is XHTML, you can specify an extra parameter to make it convert to XHTML-compliant tags instead (<br />).

escargot

Original Poster:

17,123 posts

248 months

Wednesday 5th May 2010
quotequote all
Spot on chap.

However, if I now change the datatype to text, is it going to delete all the existing data in the field?

-DeaDLocK-

3,368 posts

282 months

Wednesday 5th May 2010
quotequote all
And sorry mate, bullet points can only be done with server code to run text search and replacing. There is no standard function to do this.

Before the data is posted to the database, you would look for the bullet character and replace it with a special code, and then when it's retrieved, you could run a script to look for blocks of that code to convert them to unordered HTML lists. It's a bit fiddly though, and to be honest, if you want to go down this route, it's better to spend time integrating something like TinyMCE (http://tinymce.moxiecode.com/).

cs02rm0

13,966 posts

222 months

Wednesday 5th May 2010
quotequote all
escargot said:
However, if I now change the datatype to text, is it going to delete all the existing data in the field?
Probably. I thought you were using VARCHAR though, does MySQL have a text type too? I would think either should be fine.

Also would have thought CLOB more appropriate than BLOB (assuming MySQL has a CLOB type), but depending on how much data you're inputting neither may be appropriate.

escargot

Original Poster:

17,123 posts

248 months

Wednesday 5th May 2010
quotequote all
To be honest, I'm not even that fussed about the bullets, not if it's going to take a shed load of re-coding etc.

-DeaDLocK-

3,368 posts

282 months

Wednesday 5th May 2010
quotequote all
escargot said:
However, if I now change the datatype to text, is it going to delete all the existing data in the field?
Nope, it shouldn't, but back it up first anyway just to be safe.

escargot

Original Poster:

17,123 posts

248 months

Wednesday 5th May 2010
quotequote all
cs02rm0 said:
escargot said:
However, if I now change the datatype to text, is it going to delete all the existing data in the field?
Probably. I thought you were using VARCHAR though, does MySQL have a text type too? I would think either should be fine.

Also would have thought CLOB more appropriate than BLOB (assuming MySQL has a CLOB type), but depending on how much data you're inputting neither may be appropriate.
Not much, usually less than 1000 characters.

cs02rm0

13,966 posts

222 months

Wednesday 5th May 2010
quotequote all
I would think varchar could cope with up to at least 2048.

cs02rm0

13,966 posts

222 months

Wednesday 5th May 2010
quotequote all
(2^16)-1 characters apparently from 5.0.3, 255 before that.

-DeaDLocK-

3,368 posts

282 months

Wednesday 5th May 2010
quotequote all
cs02rm0 said:
I would think varchar could cope with up to at least 2048.
varchar will do 64k, but it's just standard practice to make free text fields text. I guess it depends how particular you want to be about performance. If it's just retrieving text, and not indexing, the performance hit will be unnoticeable.

escargot

Original Poster:

17,123 posts

248 months

Wednesday 5th May 2010
quotequote all
That works a treat actually, it still looks crap but that's now a CSS issue, at least the code works.

Thanks all smile

-DeaDLocK-

3,368 posts

282 months

Wednesday 5th May 2010
quotequote all
By the way, I am assuming you are guarding against injection and XSS attacks? If it's an internal site, it's less risky. But still, it's a clear situation for these simple security measures.

escargot

Original Poster:

17,123 posts

248 months

Wednesday 5th May 2010
quotequote all
The actual input fields are held behind a 'secure' password area (though i've not doubt that if someone tried hard enough they could break in) so to that end, I've not put any protection (that I recall) on the input forms themselves

</phpnewb>