0

I'm a PHP and MySQL newbie, and the behavior of mysql_insert_id() has become a brick wall for me when one of the database fields is UNIQUE. Greatly simplified, I have 2 tables.

property table

PropertyID PK, AI

//many property descriptors

UserID FK to user table

user table

UserID PK, AI

email UNIQUE

password

After validation, I insert the user's email into the users table and see if the email is already present, and thus a returning user. If not present, it is inserted and is a new user.

$sql = "INSERT IGNORE INTO user ( email ) VALUES ( '$email' )";
//INSERT to a UNIQUE email causes an error & abort. With IGNORE, no INSERT happens (affected_rows=0)
$UserID = mysql_insert_id(); // is 0 if email exists in its UNIQUE field
if ( mysql_affected_rows() == 0 ) {//0 is existing user, else is new user}

This works fine to separate new and returning users, who get different messages later. And it sets up 2 logic channels - one to input the new password, and the second to check if the input password matches.

However, the UserID of a second entry by a user (or third, and beyond) is always 0, zero because the email field is UNIQUE. Thus I can't make useful queries in the property table against UserID because all returning user rows have UserID=0

Despite a lot of searching, I haven't found or figured out a solution. How can I get the real UserID, not the 0 return, into the property table for both new and returning users?

Try to be specific with responses. I don't follow general directions well in this area.

Mike_Laird
  • 1,124
  • 4
  • 16
  • 40

2 Answers2

0

With something like this:

$sql = "INSERT IGNORE INTO user ( email ) VALUES ( '$email' )";
mysql_query($sql);
//INSERT to a UNIQUE email causes an error & abort. With IGNORE, no INSERT happens   (affected_rows=0)
$UserID = mysql_insert_id(); // is 0 if email exists in its UNIQUE field

if($UserID==0) //no insert
{
  $email = mysql_real_scape_string($email);
  $sql = "select * from user where email = '$email'"; //check for user on table user
  $result = mysql_query($sql);
  $row = mysql_fetch_assoc($result);
  $UserID = $row['UserID']; //return the user id that matches de email
}

PS: Use mysql or PDO...the mysql extension it's on the process to become deprecated....try mysqli extension...the procedural way of mysqli it's very similar to the way you use mysql extension on PHP.

Hackerman
  • 12,139
  • 2
  • 34
  • 45
  • Thanks for your attention to my problem. The other solution gets the job done with just one trip through the database. – Mike_Laird Apr 12 '13 at 17:00
0

Taken from this question/answer - getting mysql_insert_id() while using ON DUPLICATE KEY UPDATE with PHP

Try changing from INSERT IGNORE to INSERT .. ON DUPLICATE KEY UPDATE

$sql = "INSERT INTO user ( email ) VALUES ( '$email' ) ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id) ";

Using id=LAST_INSERT_ID(id) will then get the id of the returning user in mysql_insert_id().

mysql docs - LAST_INSERT_ID(expr) , INSERT ... ON DUPLICATE KEY UPDATE

Community
  • 1
  • 1
Sean
  • 12,443
  • 3
  • 29
  • 47
  • Works like a charm on the 1st try. Thanks so much. I had found and tried LAST_INSERT_ID(), but somehow I did not use it correctly. Thanks for a good example. – Mike_Laird Apr 12 '13 at 16:56