• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 412
  • Last Modified:

Strange MySQL behavior

I have a table called BridgeResponse for poll-type answers of questions.


Table BridgeResponse
 
Field              Type         Null    Key
ResponseID int(10)      NO    PRI   auto_increment
QuestionID tinyint(4)   YES   MUL
Ordered tinyint(4)        YES
AnswerText varchar(256) YES
AnswerCount tinyint(4)  YES



Suddenly, my INSERTs of new answers are going to the wrong question id.

The table data looked like this when the problem started.


452  126  1  Very Good  0
453  126  2  Good  0
454  126  3  Fair  0
455  126  4  Poor  0
456  127  1  Very Good  0

Now suddenly every insert insists on using QuestionID 127.


INSERT INTO BridgeResponse Values('',128,1,'Test',0)
Result: Success

gives me

467  127  1  Test  0

and another try

INSERT INTO BridgeResponse Values('',128,2,'Test2',0)
Result: Success

468  127  2  Test2  0

This is happening exactly the same in my phpmyadmin program and in the php script that is supposed to add these new answers.  I echoed out the $questionid from the php script and it was correct before and after the mysql_query insert, so it has to be the database itself, not the code.

Any idea what might be happening here?
0
Cornelia Yoder
Asked:
Cornelia Yoder
  • 2
1 Solution
 
Dan CraciunIT ConsultantCommented:
That's exactly how it should behave.

You declared QuestionID as TINYINT, which goes from -128 to 127, so everything bigger than 127 will be stored as 127.

HTH,
Dan
0
 
Dan CraciunIT ConsultantCommented:
For the sake of completeness, the m in TINYINT(m) is used only for display purposes, does not affect the range.

So your code (tinyint(4)) asked the applications to display that value using 4 characters and left padding with spaces as needed.

https://dev.mysql.com/doc/refman/5.0/en/numeric-type-attributes.html
0
 
Dave BaldwinFixer of ProblemsCommented:
Yep ^^^ what Dan said.
0
 
Cornelia YoderArtistAuthor Commented:
Doh.  

Thanks so much to both of you.  Sometimes fresh eyes see better ....
0

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now