Cornelia Yoder
asked on
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?
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Yep ^^^ what Dan said.
ASKER
Doh.
Thanks so much to both of you. Sometimes fresh eyes see better ....
Thanks so much to both of you. Sometimes fresh eyes see better ....
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