MySQL data storage and retrieval with PHP

should I abbreviate data for storage, how to convert

[knotworking]

So, I've got my script submitting all data to my dB properly. What I am at a loss for is how I should store that data. I think I understand VARCHAR and CHAR now, but does it make a whole lot of speed and size difference if I store my data abbreviated ie., "HS" instead of "HIGH SCHOOL". I can knock down several fields from 10-20 VARCHAR into 1-3 CHAR lengths if I abbreviate.

I think the answer is to do this but, how do I convert the abbreviated data as it is pulled back out of the database? I want to display user profiles and have an active search returning results in readable form, not dB values.

Would I do this by creating a seperate PHP file that serves as a legend of sorts, defining the various dB values returned and converting them into user friendly words? Is it feasable to do this conversion on every profile page? Could it be standardized?

I appreciate any and all help. Thanks!

[Salsa]

Knotworking, Welcome to Webmaster World!

It's only my opinion, but I wouldn't even consider using abbreviations for storing the data if I had to convert it back and forth at every INSERT, UPDATE or SELECT. You might save a tiny bit in storage space and MySQL processing time, but that's cheap compared to the relatively enormous PHP processing time that would be required for doing all of those conversions. And that doesn't consider where you would store the conversion factors. If they were in a separate MySQL table, which would be a sensible place, you would also have to be querying that all the time.

I wouldn't do it, but one model doesn't fit all, and someone else may have a different perspective.

I hope this helps.

[edited]Looking at this again, if a field is going to hold a limited number of predefined options, like high school, college, masters..., then what you will want to do is to use the ENUM column type. ENUM will store and process its contents as numbers, and will be even more efficient than abbreviations in a CHAR column.[/edit]

[knotworking]

Man, I didn't even think of that! I was used to yes/no or male/female for ENUM. Almost all the fields that I would abbreviate are dropdown lists, that makes sense.

Thanks!

[Salsa]

It's easy to get lost in our own focus', and not see the obvious at first. When I was answering you, for example, I was focused on your specific question, and it wasn't until immediately after I posted that I freed myself to see what you were probably looking for.

ENUM column type [dev.mysql.com] can take up to 65,535 elements(!) If any of your dropdown lists provide for multiple choices, consider the SET column type.