mysql_insert_id()

[BigCroyd]

Hi,

 

Can anyone help me im trying to use the mysql_insert_id() to give me the value of last auto-incremented value created in my database but the value always returns as 0. The information is entering into the database fine but i just cant seem to get the ID back out:

 

$sql3="INSERT INTO `kf`.`order` (`order_id` ,`cust_id`,`order_date`,`order_status`,`order_total`) VALUES (0,'".$cust_id."','".$today."','".$status."','".$order_tot."')";

 

$order_ID = mysql_insert_id();

 

echo "$order_ID";

 

Result off the echo is always = 0 even though the query runs fine.

 

Any help would be appreciated

 

Thanks

 

 

[Maq]

I don't see where you actually insert anything, you create the string and that's it, unless you didn't paste all the code...

 

You have to use mysql_query() and mysql_insert_id() gets the resource id from the last inserted record.

[timmah1]

Is order_id set to auto increment?

If not, it will always be defined as 0

[BigCroyd]

Yes, Sorry the mysql_query is in the code i just forgot to paste it in :

 

$sql3="INSERT INTO `kf`.`order` (`order_id` ,`cust_id`,`order_date`,`order_status`,`order_total`) VALUES (0,'".$cust_id."','".$today."','".$status."','".$order_tot."')";

$result = mysql_query($sql3, $con)

$order_ID = mysql_insert_id();

or die ("Unable to perform query");

echo "$order_ID";

}

 

And yes, the order_ID feild is set to auto-increment in the database

 

Thanks

[premiso]

$sql3="INSERT INTO `kf`.`order` (`order_id` ,`cust_id`,`order_date`,`order_status`,`order_total`) VALUES ('','".$cust_id."','".$today."','".$status."','".$order_tot."')";

 

You have it set to "0" in your sql query. Change it to '' (as seen above) and it should work.

[BigCroyd]

Hi premiso

 

I changed the query to what you suggested but i am still getting the same problem. The value is still 0. The database order_ID field is auto-incrementing properly

 

Thanks for the help so far.

[Maq]

Can you post exactly what you have?

[BigCroyd]

require "connect.php";

$sql3="INSERT INTO `kf`.`order` (`order_id` ,`cust_id`,`order_date`,`order_status`,`order_total`) VALUES (0,'".$cust_id."','".$today."','".$status."','".$order_tot."')";

$result = mysql_query($sql3, $con)

$order_ID = mysql_insert_id();

or die ("Unable to perform query");

echo "$order_ID";

}

 

The result from the echo gives 0.

 

Just to make it clear - the query runs ok and inserts the correct data into the database and the order_id field in the database is auto-incrementing ok e.g 1,2,3,4 etc.

 

Hope this is clearer

[Maq]

You're still inserting order_id as 0.

 

Please read premiso's post.

[BigCroyd]

it doesnt seem to make a difference what i put in the insert statement for the auto-incrementing field i have tried both the following insert queries with the same result:

 

$sql3="INSERT INTO `kf`.`order` (`order_id` ,`cust_id`,`order_date`,`order_status`,`order_total`) VALUES (0,'".$cust_id."','".$today."','".$status."','".$order_tot."')";

 

$sql3="INSERT INTO `kf`.`order` (`order_id` ,`cust_id`,`order_date`,`order_status`,`order_total`) VALUES ('','".$cust_id."','".$today."','".$status."','".$order_tot."')";

 

$sql3="INSERT INTO `kf`.`order` (`order_id` ,`cust_id`,`order_date`,`order_status`,`order_total`) VALUES ("NULL",'".$cust_id."','".$today."','".$status."','".$order_tot."')";

[premiso]

Then your table is setup wrong.

 

Post your table structure and note:

mysql_insert_id() converts the return type of the native MySQL C API function mysql_insert_id() to a type of long (named int in PHP). If your AUTO_INCREMENT column has a column type of BIGINT, the value returned by mysql_insert_id()  will be incorrect. Instead, use the internal MySQL SQL function LAST_INSERT_ID() in an SQL query.

 

[Psycho]

You shouldn't even include the ORDER_ID in the insert statement at all. Also, that is the oddest use of an "or die()" [on line all by itself, previous line was ended with a semi-colon] I have ever seen. I don't even know how that would process. But it could be the fact that you have your "$order_ID = mysql_insert_id()" sandwiched in-between the query and the "or die()" that is causing the problem.

 

Try this:

require "connect.php";

$sql3="INSERT INTO `kf`.`order` (`cust_id`,`order_date`,`order_status`,`order_total`)
       VALUES ('{$cust_id}','{$today}','{$status}','{$order_tot}')"; 

$result = mysql_query($sql3, $con) or die ("Unable to perform query");

$order_ID = mysql_insert_id();

echo "$order_ID";

 

Also, pleas use CODE or PHP tags when posting code.

[BigCroyd]

hi premiso.

 

My table structure is shown below:

 

CREATE TABLE IF NOT EXISTS `order` (

  `order_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,

  `cust_id` int(5) NOT NULL,

  `order_date` varchar(40) NOT NULL,

  `order_status` varchar(30) NOT NULL,

  `order_total` varchar(30) NOT NULL,

  PRIMARY KEY (`order_id`),

  UNIQUE KEY `order_id` (`order_id`)

) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

 

I have noticed that my auto-increment is a Bigint as suggested in your post.

 

Can you point me in the right direction about how i could get the value into a variable useing LAST_INSERT_ID()?

 

Thank you

[PFMaBiSmAd]

From the mysql manual -

LAST_INSERT_ID(), LAST_INSERT_ID(expr)

 

LAST_INSERT_ID() (with no argument) returns the first automatically generated value that was set for an AUTO_INCREMENT column by the most recently executed INSERT statement to affect such a column. For example, after inserting a row that generates an AUTO_INCREMENT value, you can get the value like this:

 

mysql> SELECT LAST_INSERT_ID();

        -> 195

 

 

[Psycho]

Did you see my response? The code you're posting can't be working. I tested it and there is a parse error because there is no semicolon after

$result = mysql_query($sql3, $con)

 

When I ran the same code (modified for a test DB) I get this error

Parse error: parse error, unexpected T_VARIABLE in C:\xampp\htdocs\test\test.php on line 14

(14 being the line after the line with the missing semi-colon, as expected)

So, I don't see how what you posted is the actual code you are using.

 

Did you try the code I posted (which works for me)?