Error in mysql_insert_id() [MAIL TAG]

Question

     $headers  = 'MIME-Version: 1.0' . "\r\n";
     $headers .= 'Content-type: text/html; charset=iso-8859-1' . "\r\n";
     $headers .= 'From: [email protected]';
     $to = $email;
     $subject = "Confirmation";
     $message = "Hi, $username. You're registred in Site. Link for confirmation:     http://site.com/confirm/**".mysql_insert_id()."**";
     mail($to, $subject, $message, $headers);
     $query = mysql_query("insert into usr_users(username, password, email, avator, pin)          values('$username', '$password', '$email', '$img', '$pin')");
     $query2 = mysql_query("insert into usr_confirm(user_name, user_id, active, data) values('$username', '".mysql_insert_id()."', '$active', '$senha')");
     echo"Success!</br>";

The second mysql_insert_id() (line 9) is correct! But the first mysql_insert_id() (line 6) return 0 in e-mail box.

Answer 1

Per the manual, mysql_insert_id() will return 0 if the last statement doesn't generate an AUTO_INCREMENT value:

The ID generated for an AUTO_INCREMENT column by the previous query on success, 0 if the previous query does not generate an AUTO_INCREMENT value, or FALSE if no MySQL connection was established.

You're first using the mysql_insert_id() call before you've inserted any records, so the result has to be 0 (unless there's a call before this somewhere else in your code -t hen you'd actually be getting skewed results). Try moving your mail-related calls to below your insert statements, giving you:

// insert the records
$query = mysql_query("insert into usr_users(username, password, email, avator, pin)          values('$username', '$password', '$email', '$img', '$pin')");
$query2 = mysql_query("insert into usr_confirm(user_name, user_id, active, data) values('$username', '".mysql_insert_id()."', '$active', '$senha')");

// send the email
$headers  = 'MIME-Version: 1.0' . "\r\n";
$headers .= 'Content-type: text/html; charset=iso-8859-1' . "\r\n";
$headers .= 'From: [email protected]';
$to = $email;
$subject = "Confirmation";
$message = "Hi, $username. You're registred in Site. Link for confirmation:     http://site.com/confirm/**".mysql_insert_id()."**";
mail($to, $subject, $message, $headers);
echo"Success!</br>";

If you still receive the error after this change, you will need to confirm that your primary-key field on the usr_confirm table is correctly configured with auto_increment. Also, if it is setup properly, try passing the connection-variable to the function, such as mysql_insert_id($conn); where $conn is the result of your mysql_connect() call.

Site note (not answer specific)
Your code is extremely prone to SQL Injection; you're not escaping any of the values. Rather than go into what SQL Injection is, I would much rather direct you to use Prepared Statements.

In addition, PHP is deprecating the mysql_ methods in favor for the mysqli_ and PDO methods. Both of these support prepared statements and I would recommend for you to look into these.

In the interim, please consider wrapping your variables with mysql_real_escape_string(), such as:

... values ('" . mysql_real_escape_string($username) . "', ...