insert into 2 tables

Manat

Hello all,

If I have a form, and that form must post to two tables linked by a custID primary key.

How do u post data in such a scenario?  Two insert into's?

I'm just wondering because perhaps it's different since there is a table relationship between the two tables.

Thanks
MK

amycrystal123

Hi Manat,
Please be more specific. Can you give an idea of the fields in both tables where the data needs to be inserted. Is the Cust_Id, the primary key in both the tables?

Amy.

AstroTeg

Yup, you do 2 inserts. It gets a little interesting if you have auto IDs to keep track of your records and the 2nd table needs that auto ID. There's tricks to doing this. But once you have the ID (assuming you're using IDs), you just form your 2nd insert and go.

Manat

Righhtttt.
I forgot about that.
I have two tables, customer table and registration table.
custID in customer is a primary key autonumber.
but it's not an autonumber in the registration table, or things could get messy.

So I post to the customer table, then get the custID and then post to the registration table?

So how would I do that? insert into all fields into customer table.
then select custID where fname, lname == what was in the form?

then post to registration table?

I'm sure my where clause is not the most efficient way of doing this, what would be the best way?

Thanks

dnast

[man]mysql_insert_id[/man] would make things a little easier 😉

Manat

thanks alot, this function will do fine.
However is this true:

Just a reminder, mysql_insert_id() should be called after 'mysql_affected_rows()', but BEFORE 'mysql_query("COMMIT")'.

Someone wrote that on the reply post at the link you sent me.
Actually it seems this might be a somewhat dangerous function to use. Is what I pasted true?

Or should I do this:
$query = "select last_insert_id() as $IDfinal";

Thanks

thanks

AstroTeg

I have my own insert function I built that has a couple tricks in it. But the core is this:

	$result = mysql_query($query);
	if($result)
		{
		// query was a success - find the auto ID
		return mysql_insert_id();
		}  // end if($result)

Run your insert query. Then issue the mysql_insert_id().

Manat

Hi Astrotech,

I'm a little confused as to what and how you are returning?

You have a return function in a if statement, where are you returning it to?

Thanks

AstroTeg

Its an insert function - meaning the function name is "insert" and you pass it a query. In a more basic form:

function Insert($query, $dupcheck = '')
{

$result = mysql_query($query);
if($result)
	{
	// query was a success - find the auto ID
	return mysql_insert_id();
	}  // end if($result)
else
	{
	EmailError('Failed to execute query: ' . $query . "\n\n" . mysql_error());
	return -1;
	}  // end else
}  // end function Insert($query)

I've used this code in enough projects and I'm happy with it, but your mileage may vary. You will find "EmailError()" does not exist. You may wish to pull this function out or build your own. All EmailError() does is take 2 strings and builds an email message which gets sent to my email account. It describes the time, the script, the query, and the problem involved in an email and sends it to me. This allows me to keep track of any bugs that might crop up in relation to the query itself. But this is up to you to implement...

The insert function returns either a positive number which is the automatic insert ID if its and when its available. It'll return a negative number if there was a failure. All you do is when you're ready to insert something and you need the ID, you just call insert('[your insert query]') and you grab its return value. Test it to make sure it is a positive number and then you're good to go...