mysql_insert_id()
Hi there,
I'm using Dreamweaver to creat PHP code. I'm not that expert in PHP.
Dreamweaver has some limitations. For example I can't insert data into more than one table at the same time.
I need to insert the auto increment value from the first table in mysql to other tables using mysql_insert_id().
I couldn't do this by my self because I'm not good at PHP. Here is the code I got from Dreamweaver:
<?php
require_once('Connections/index.php');
function GetSQLValueString($theValue, $theType, $theDefinedValue = \"\", $theNotDefinedValue = \"\")
{
$theValue = (!get_magic_quotes_gpc()) ? addslashes($theValue) : $theValue;
switch ($theType) {
case \"text\":
$theValue = ($theValue != \"\") ? \"'\" . $theValue . \"'\" : \"NULL\";
break;
case \"long\":
case \"int\":
$theValue = ($theValue != \"\") ? intval($theValue) : \"NULL\";
break;
case \"double\":
$theValue = ($theValue != \"\") ? \"'\" . doubleval($theValue) . \"'\" : \"NULL\";
break;
case \"date\":
$theValue = ($theValue != \"\") ? \"'\" . $theValue . \"'\" : \"NULL\";
break;
case \"defined\":
$theValue = ($theValue != \"\") ? $theDefinedValue : $theNotDefinedValue;
break;
}
return $theValue;
}
$editFormAction = $_SERVER['PHP_SELF'];
if (isset($_SERVER['QUERY_STRING'])) {
$editFormAction .= \"?\" . htmlentities($_SERVER['QUERY_STRING']);
}
// DW Team Insert and Retrieve ID
if ((isset($HTTP_POST_VARS[\"MM_insert\"])) && ($HTTP_POST_VARS[\"MM_insert\"] == \"form1\")) {
$insertSQL = sprintf(\"INSERT INTO names (indexNo, `Date`, Numbers, Name) VALUES (%s, %s, %s, %s)\",
GetSQLValueString($_POST['indexNo'], \"int\"),
GetSQLValueString($_POST['Date'], \"text\"),
GetSQLValueString($_POST['Numbers'], \"text\"),
GetSQLValueString($_POST['Name'], \"text\"));
mysql_select_db($database_index, $index);
$Result1 = mysql_query($insertSQL, $index) or die(mysql_error());
}
?>Is there any one can help me doing this?
s0da posted this at 06:51 — 25th April 2004.
He has: 157 posts
Joined: Mar 2004
$Id1 = mysql_insert_id(); # this will get the last AUTOINCREMENTed `ID` inserted into the database
AYYASH posted this at 06:57 — 25th April 2004.
He has: 10 posts
Joined: Jan 2004
Thanks,
But how can I use "$Id1" to insert its value into other tables?
Suzanne posted this at 07:17 — 25th April 2004.
She has: 5,507 posts
Joined: Feb 2000
What are your other tables like? I assume that that ID is in them -- you just INSERT your data into those tables with that ID set to the value you get... ?
Can you provide more explanation of the other tables?
So... INSERT INTO othertable SET id = '$ID1', field = '$value'
Does that make sense?
AYYASH posted this at 08:01 — 25th April 2004.
He has: 10 posts
Joined: Jan 2004
Well,
I have master table called "initiation". The index field called "indexNo" and it's auto incremented. I want the value of this filed to be inserted automatically to the other index fields for the other four tables (estimate.estimateID, printreq.preqID, printquot.quotID, request.reqID) every time I insert a new record. Later I want to be able to make relations between these tables by using the value that has been inserted.
In MySQL 5, they solve this problem by adding procedures and triggers. But we have to wait until it's officially released.
In the mean while, I have to do it using PHP. I know how to write a good SQL syntax but I can't integrate it with the PHP code because I'm not good at it. As you can see in the code I posted ubove, this is difficult for me to understand.
Thanks in advance
s0da posted this at 08:04 — 25th April 2004.
He has: 157 posts
Joined: Mar 2004
you grab the last ID inserted; then you insert or update it intot he database
1. insert ...
2. grab insert id ...
3. insert/update using insert id ...
AYYASH posted this at 10:32 — 25th April 2004.
He has: 10 posts
Joined: Jan 2004
Thank you s0da and Suzanne for your help.
I've finally solved the problem.
Suzanne posted this at 16:07 — 25th April 2004.
She has: 5,507 posts
Joined: Feb 2000
I'm glad you got it sorted out! Dreamweaver has condensed a lot of the code, which is fine, but makes it difficult for human types to read through it easily unless they are very fluent.
AYYASH posted this at 06:27 — 27th April 2004.
He has: 10 posts
Joined: Jan 2004
Yes Suzanne, you are right. It looks so complicated to me to understand.
Any way, thanks again for your help. You are very remarkable in this forum.
Want to join the discussion? Create an account or log in if you already have one. Joining is fast, free and painless! We’ll even whisk you back here when you’ve finished.