A column in my table, "age", is set to tinytext as the data type. A hyphen is being passed through from the PHP script to the table, indicating a range of ages (18-35, 36-55, 55+), but what is written into the table replaces the hyphen with a couple of different, odd characters. It looks like this: 18â€“35. How can I change the structure of the table in phpMyAdmin to not alter the hyphen or the + sign when and if that is what is being written into the database?

This might be a stupid question, but are these values being typed in, or are they being pasted in from something like MS Word? I had a similar problem with people pasting straight from an MS Word document into a form field, and the hyphens weren't your normal hyphens. The newer versions of Word use annoying things like "smart" quotes, and long hyphens that use high ascii values that my database didn't like.

This field with the hyphens is a <select> drop-down element, so the values are already pre-set in the HTML.

The character encoding I'm using:

emailaddress : VARCHAR(60)name : tinytextage : tinytextgender : tinytext (Will change this to CHAR once I modify the PHP to write only a 'M' or an 'F' to the database)country : tinytextcategory : tinytextjointime : datetime

by the way, you might want to consider VARCHAR for your data types instead of TINYTEXT...

da manual said:

Instances of BLOB or TEXT columns in the result of a query that is processed using a temporary table causes the server to use a table on disk rather than in memory because the MEMORY storage engine does not support those data types (see Section 8.8.5, “How MySQL Uses Internal Temporary Tables”). Use of disk incurs a performance penalty, so include BLOB or TEXT columns in the query result only if they are really needed. For example, avoid using SELECT *, which selects all columns.

Each BLOB or TEXT value is represented internally by a separately allocated object. This is in contrast to all other data types, for which storage is allocated once per column when the table is opened. -

Okay, so these are called look-up tables apparently, and I'll need to join the tables, emailcollection and agerangelookup. I set up agerangelookup with 2 columns, id and description. I've got someone who can help me build the query to do this, but I may need to keep asking questions in this thread.

I'll post my solution as I find out.

As a temporary fix, the HTML I had for that form had – for the hyphens. That was what was creating the problem, but I'd like to learn how to accomplish this because of the benefits you mentioned.

So, here's what I have done. A number, 0-4, is being written to the emailcollection table instead of the actual value that the user selects from. I made a new table in the same database, with the name of agerange, which contains four rows of data inside its two columns, id and description. It looks like this:

Then, my friend had me do a JOIN query on the two tables, which apparently I have to do each and every time to get the actual age range to show.

What I want is for the age ID (The 0-4 digit) to be converted to its corresponding age range and have the actual age range, say 18-35, be written to the emailcollection table in a way that promotes data integrity. I want this to occur every single time without doing a JOIN query. Maybe all I should do is check the value received from the form in PHP and then change that number to the age range I want to see in the database (I care nothing for the 0-4 digit), accomplishing all of this without the need for a 2nd table, and doing everything in PHP before anything is written to the database.

What I want is for the age ID (The 0-4 digit) to be converted to its corresponding age range and have the actual age range, say 18-35, be written to the emailcollection table in a way that promotes data integrity.

if you want the age range itself to be stored, don't use the integer at all, but do keep the age ranges table

data integrity is ensured using a foreign key

do some research on foreign keys and then ask again if you don't understand how a foreign key will help in this situation

Ugh! So, I spoke with my web host (FatCow) today, and I was told they don't support foreign keys. I tried to follow some tutorials to set up some foreign keys, starting by switching the storage engine to InnoDB (which, apparently, they don't support either )

Now, I need a new method to go about this, or maybe this whole objective is just dissolving before our eyes.

I've changed the HTML form from a drop-down menu to a write in, so the user will actually input their age (if they choose to do so). That may change how this is solved, but I'm pretty sure SQL can be used to determine what age range it falls under.