Mysql_insert_id () returns 0

Question

I know that there are many topics with the same name. But basically this is a query that was inserted in the wrong place. But I think that's right. So the problem is that I still get 0, even when the data is inserted into db. Does anyone know the answer, where can I be wrong?

here is my code:

mysql_query('SET NAMES utf8'); $this->arr_kolommen = $arr_kolommen; $this->arr_waardes = $arr_waardes; $this->tabel = $tabel; $aantal = count($this->arr_kolommen); //$sql="INSERT INTO `tbl_photo_lijst_zoekcriteria` ( `PLZ_FOTO` , `PLZ_ZOEKCRITERIA`,`PLZ_CATEGORIE`)VALUES ('$foto', '$zoekje','$afdeling');"; $insert = "INSERT INTO ".$this->tabel." "; $kolommen = "("; $waardes = " VALUES("; for($i=0;$i<$aantal;$i++) { $kolommen .=$this->arr_kolommen[$i].","; $waardes .="'".$this->arr_waardes[$i]."',"; } $kolommen = substr($kolommen,0,-1).")"; $waardes = substr($waardes,0,-1).")"; $insert .=$kolommen.$waardes; $result = mysql_query($insert,$this->db) or die ($this->sendErrorToMail(str_replace(" ","",str_replace("\r\n","\n",$insert))."\n\n".str_replace(" ","",str_replace("\r\n","\n",mysql_error())))); $waarde = mysql_insert_id(); 

Thank you very much, because I have been racking my brains over this for almost the whole day. (and probably it's something small and stupid)

Answer 1

According to manual mysql_insert_id returns:

The identifier generated for the AUTO_INCREMENT column by the previous success request, 0 if the previous request does not generate an AUTO_INCREMENT value or FALSE if the MySQL connection is not established.

Since it does not give you false , but not the correct number, this means that the requested table did not generate an auto-increment value.

There are two possibilities that I can think of:

• Your table does not have an auto_increment field
• Since you are not providing a reference to mysql_insert_id (), but using a link with mysql_query (), this may not be the correct table requested when retrieving the last inserted identifier.

Decision:

• Make sure it has an auto_increment field
• Specify the link: $waarde = mysql_insert_id($this->db);

Answer 2

Perhaps your INSERT request was not successful - for example, maybe you tried to insert duplicate data into a column whose data must be unique?

Answer 3

If the identifier is really set to automatically add and still gets “0”, since your answer makes the column and count value, I only experienced this later, I noticed that the number of my columns does not match the count values.

Answer 4

Codeigniter has weird behavior when calling mysql_insert_id (). The function returns 0 after the first call. Therefore, the call returns 0 twice.

Use a variable instead of calling a function more time:

 $id = mysql_insert_id();