In SQL tables, should I, for example, have “é” or should I have “e´”?

I have tried in vain to look up relevant questions. They are beyond my pay scale. I am not a professional. To explain this a bit more: in the HTML that I wrote, the em dash would be "& #151;" (that space inserted so it would not show up as an actual em dash). It ended up in the tables (someone else was doing that work) as "—". Those are not showing up correctly when searches are done using PHP. I only get the image with a question mark. I do have my SQL account set to Unicode.

A couple years ago i wrote a website with PHP with MySql, i just let the data goes to the table as it's inserted by the user, and when it's called again everything worked just fine.
– Tonner Mààn
Aug 1 at 15:15

All of the data in my tables is inserted by me. There are no user accounts. The tables exist only to allow the users to search them.
– Kaw
Aug 1 at 15:21

well... I guess it's the same thing, only line breaks must be entered as the unicode form, everything else will be fine. i still have 3 hours before going back home, if i don't forget i'll search for that project again and see if i have some codes for you to avoid errors.
– Tonner Mààn
Aug 1 at 15:37

Either way, I have the text in the tables not using the codes, and they're not showing up the same when searches are done. If all the people here voting this question down were as capable of writing clearly as they are of clicking and scrolling performing slave labor for their "tech" giant overlords, that would be great. Then again, simple issues like this are often not dealt with well by programmers/ developers precisely b/c they don't care.
– Kaw
Aug 1 at 17:23

I've tried using "e´" for example and it seems to show up fine, but I'm not sure if that's because of my browser's specs or my computer's specs. The question of course is what actually would result in the correct result most of the time.
– Kaw
Aug 1 at 17:27

1 Answer
1

Take a philosophical stand: The datastore (database table) should contain data, not some special encoding of the data.

The "data" is é

é

When you display that in HTML, you might need to convert it to e´. However, all modern browsers don't have a problem if é is encoded UTF-8.

e´

é

If you choose to use "html entities", then have your application do the conversion after fetching é from the table. PHP has the function htmlentities() specifically for that task.

é

htmlentities()

But, I still have not addressed what byte(s) are in the table to represent é. These days, you 'should' use UTF-8 (aka MySQL's utf8mb4). That would be two hex bytes C3A9, which can be discovered using SELECT HEX(col) .... If you use the old default, latin1, the hex would show C9.

é

C3A9

SELECT HEX(col) ...

C9

A related question is whether you should store html 'tags' or construct the html on the fly after fetching the data. So, let me give you three philosophies; you pick which to apply:

BLOB

TEXT

Also, the first choice is much cleaner for searching. This may lead you to pick it. However, another approach is to have two columns -- one aimed at delivering mostly-formatted ouput; the other for searching (tags removed, no entities, etc); it would be mostly text, but you probably could not generate a web page (with links, paragraphs, etc) from it.

é -- different strokes for different folks

é

u00E9

é

htmlentities()

%C3%A9

urlencode()

Yes, I'd do the first option. As far as I know, I've got my whole thing set to UTF-8.
– Kaw
Aug 22 at 19:12

I've got a lot of data with the special characters as they are, not codes. This would mean I need to do the PHP "htmlentities"? Does this go in the same php file as the other search commands?
– Kaw
Aug 22 at 19:13

@Kaw - Does my addition answer your question?
– Rick James
Aug 22 at 19:29

You have to realize I do no understand PHP, and, secondly, many of the tasks to which these PHP functions seem to have been designated are beyond my purview. I have data in tables with, for example, é, among the characters. Many of them. They are showing up as question marks currently. If I put them in the tables as & eacute; then the browser will show them correctly but the user cannot search for that unless he types in & eacute; as well. So I need to know what to put into my index.php document to make the é show up correctly if, for example, a user searches for "Jose."
– Kaw
Aug 30 at 21:26

The "question mark" problem is a common one, and it has been addressed many times on this forum.. é is sufficiently representative; em-dash would act similarly. If you don't understand PHP, you probably need someone to work one-on-one with you. A forum is too slow and clumsy.
– Rick James
Aug 30 at 22:20

é

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

搜尋此網誌

Dfyjkt