SitePoint Sponsor

User Tag List

Results 1 to 13 of 13
  1. #1
    SitePoint Guru etidd's Avatar
    Join Date
    May 2011
    Location
    atlanta, ga
    Posts
    632
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Exclamation ASCII Character Misread on Database

    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

  2. #2
    SitePoint Wizard bronze trophy cydewaze's Avatar
    Join Date
    Jan 2006
    Location
    Merry Land, USA
    Posts
    1,096
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    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?
    <cfset myblog = "http://cydewaze.org/">

  3. #3
    SitePoint Guru etidd's Avatar
    Join Date
    May 2011
    Location
    atlanta, ga
    Posts
    632
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    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

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,339
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,339
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    by the way, you might want to consider VARCHAR for your data types instead of TINYTEXT...
    Quote Originally Posted by da manual
    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. -

    -- http://dev.mysql.com/doc/refman/5.0/en/blob.html
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Guru etidd's Avatar
    Join Date
    May 2011
    Location
    atlanta, ga
    Posts
    632
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    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 &ndash; 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.

  7. #7
    SitePoint Guru etidd's Avatar
    Join Date
    May 2011
    Location
    atlanta, ga
    Posts
    632
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    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

    HTML Code:
                <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.

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,339
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by etidd View Post
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Guru etidd's Avatar
    Join Date
    May 2011
    Location
    atlanta, ga
    Posts
    632
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Thumbs down

    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

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,339
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by etidd View Post
    I don't know where to go from here as of this point.
    new host
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  11. #11
    SitePoint Guru etidd's Avatar
    Join Date
    May 2011
    Location
    atlanta, ga
    Posts
    632
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    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.

  12. #12
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,339
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by etidd View Post
    I need to finish figuring this out...
    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?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  13. #13
    SitePoint Guru etidd's Avatar
    Join Date
    May 2011
    Location
    atlanta, ga
    Posts
    632
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    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


Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •