TIF Posted January 6, 2017 Share Posted January 6, 2017 (edited) Unable to insert a record with an alpha key value. Structure: name - id type - VARCHAR length - 20 default - None collation - latin1_general_ci attributes - blank null - blank auto incr - blank id is the primary key This works for unique values of $ident: $ident = "12345"; $result = mysql_query("INSERT INTO purchase (id) VALUE ($ident)"); This does not work for values of $ident containing an alpha character: $ident = "ABCDE"; $result = mysql_query("INSERT INTO purchase (id) VALUE ($ident)"); Edited January 6, 2017 by TIF Link to comment Share on other sites More sharing options...
Larry Posted January 7, 2017 Share Posted January 7, 2017 That shouldn't be happening if id is a varchar. That being said, I always use auto incrementing integers for primary keys, never strings. I'd think twice about what you're doing in that regard. In any case, to solve this problem, first run a DESCRIBE query to confirm what ID is as a column. Second, when you say "it does not work", what does that mean? Do you get an error message? Link to comment Share on other sites More sharing options...
TIF Posted January 9, 2017 Author Share Posted January 9, 2017 I agree with your observation as to the Primary Key. I did have an AI field and had the same symptoms. I'll change it back. The ID field, now the primary key, is indeed VARCHAR(20), not null. BTW, I was rooting for my alma mater, USC, in the Rose Bowl. It was the most exciting bowl game of the year. Link to comment Share on other sites More sharing options...
TIF Posted January 9, 2017 Author Share Posted January 9, 2017 To clarify, entering a numeric value for id results in the row being added. Entering an alpha value for id results in an INSERT error. The insert command follows: $result = mysql_query("INSERT LOW_PRIORITY INTO purchase (uniq, id) VALUE (NULL, $ident)") or die('Duplicate entry. Please choose another ID.'); Link to comment Share on other sites More sharing options...
Larry Posted January 9, 2017 Share Posted January 9, 2017 I assume you know I was rooting for PSU! That was a crazy-good game. What is the exact INSERT error you get? Link to comment Share on other sites More sharing options...
TIF Posted January 9, 2017 Author Share Posted January 9, 2017 I predict that USC & PSU will meet again next year in the Championship Game! We're both that promising. // get id from html form$ident = $_POST['identity']; // Add a blank record saving a spot$result = mysql_query("INSERT LOW_PRIORITY INTO purchase (uniq, id) VALUE (NULL, $ident)") or die('Duplicate entry. Please choose another ID.'); Link to comment Share on other sites More sharing options...
Larry Posted January 9, 2017 Share Posted January 9, 2017 Sorry, but what error does MySQL report? Or are you seeing the "Duplicate entry..." error? If it's the latter, then the query is working regardless of varchar vs int. Link to comment Share on other sites More sharing options...
Recommended Posts