Hello everyone,
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? 
Thanks,
Tyler
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.
Also, what character encoding are you using in your database columns?
Hello, cydewaze, and thanks for your reply.
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 : tinytext
age : tinytext
gender : tinytext (Will change this to CHAR once I modify the PHP to write only a ‘M’ or an ‘F’ to the database)
country : tinytext
category : tinytext
jointime : datetime
-Tyler
it might be easier to have a table of age ranges, and then let the html <SELECT> pass in a numeric id value, rather than the text value
this helps to enforce data integrity, as well as simplifying detection of sql injection (since it has to be an integer), especially if you’re already restricting users to a dropdown to choose from
by the way, you might want to consider VARCHAR for your data types instead of TINYTEXT…
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:
id | description
0 | -unspecified-
1 | 18-35
2 | 36-55
3 | 55+
4 | 17 or younger
<label for="age" class="fixedwidth">Select your age range:</label>
<select name="age" id="age" class="inputwidth">
<option selected="selected" value="0">Select an Age Range</option>
<option value="1">18-35</option>
<option value="2">36-55</option>
<option value="3">55+</option>
<option value="4">17 or younger</option>
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.
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.
What was this talk about setting up a view?
I don’t know where to go from here as of this point. 
-Tyler
new host
I don’t know if it is against forum rules to continue this thread now, but I do have a new host now, and InnoDB is the current database engine, and foreign keys are supported.
I need to finish figuring this out, while at the same time, I am trying to find a solution to save a timestamp when the new row is written to the table.
do you still have your agerangelookup table with 2 columns, id and description?
i would’ve used a pair of integer columns, for low age and high age
where did you want to go with this?
Just created agerangelookup with this architecture:
ID will be an INT (not null, unsigned) column and description will be a VARCHAR(60).
id | description
0 | -unspecified-
1 | 18-35
2 | 36-55
3 | 55+
4 | 17 or younger
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.
-Ty