Page 1 of 1

Insert record into MySql table and get its auto id at same t

Posted: Wed Mar 30, 2011 10:31 am
by davidhopkins
Good Afternoon all !

I have a slight problem in the design of my database. At first every user had a seperate table to post records under, but then i came acrosss a problem where the ID (the primary key, autoincrement) of these records would match records stored under a seperate table.

So i came up with the solution of having a master table where all the records get saved first time round this again has an ID field as the primary key with autoincrement.

What i am trying to achieve is once a record has been saved into this master table it then retrieves the ID of that record straight away so that i can then save that record into the relevant table with an ID that will be unique.

THe code i am using to save the record into the Master table is

Code: Select all

	$qry = "INSERT INTO Master (ref, title, created, notification) VALUES ('$newRef','$newTitle',CURDATE(),'$Notification')";
	$result = @mysql_query($qry);
	
	//Check whether the query was successful or not
	if($result) {
		header("location: ../client");
		exit();
		echo("Yes");
	}else {
		die("Query failed");
	}

Basically i need to figure a way out so that in the if statement i can then retrieve the ID of this record.


Either that or figure out a way to share an auto increment over multiple tables

Any help would be great

Thanks

David

Re: Insert record into MySql table and get its auto id at sa

Posted: Wed Mar 30, 2011 3:35 pm
by tr0gd0rr
If you are using MySQL, you can simply call mysql_insert_id(). Other databases have other functions or queries you run to get the ID of the record you just inserted.

Re: Insert record into MySql table and get its auto id at sa

Posted: Wed Mar 30, 2011 3:40 pm
by klandaika
Hi there,

I believe what you need is the following sql statement:

Code: Select all

$qry = "SELECT @@IDENTITY";
If you execute it right after the insert operation it will return 1 row with 1 column containing the AUTO_INCREMENT id that was assigned during the latest INSERT operation.