Jump to content
Larry Ullman's Book Forums
Sign in to follow this  
TIF

Select Statement Operation

Recommended Posts

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 by TIF

Share this post


Link to post
Share on other sites

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?

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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.');

Share this post


Link to post
Share on other sites

I assume you know I was rooting for PSU! :) That was a crazy-good game. 

 

What is the exact INSERT error you get? 

Share this post


Link to post
Share on other sites

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.');
 

 

Share this post


Link to post
Share on other sites

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. 

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
Sign in to follow this  

×
×
  • Create New...