| SitePoint Sponsor |
Hello
I've created a php page that inserts data into three tables. The first two tables have auto-increment IDs, which I want grab and insert into a third table. I understand how to insert the second of these auto-increment IDs, using LAST_INSERT_ID(), but I don't know how to insert the first generated ID. Is there a php function for doing this? I've tried searching, but can't find anything.
Thanks
Sean
You could SELECT LAST_INSERT_ID() and store it in a variable to use later.
There is a wrapper function for this, mysql_insert_id().
But since all it does is run SELECT LAST_INSERT_ID(), you still need to call it before you run another INSERT to save that ID.
17-29% of paid ad clicks are fraudulent. Get protected with Improvely, your online marketing dashboard.
→ Conversion tracking, click fraud detection, A/B testing and more.
Thanks Dan. I've put the wrapper function $this->db->insert_id() after the first table (tblPerson), but I'm not sure how to put that in my third table.
sean
<HTML>
<HEAD>
<TITLE>Data entry</TITLE>
</HEAD>
<BODY>
<P><?php $con = mysql_connect('localhost', 'username', 'password');
if (!$con)
{
die('Could not connect: ' . mysql_error());
}
mysql_select_db('newpolitics_com_pol', $con);
$sql="INSERT INTO tblPerson (RelationshipID, FirstName, RestofName, StateID)
VALUES
('$_POST[RelationshipID]','$_POST[FirstName]','$_POST[RestofName]', '$_POST[StateID]')";
$this->db->insert_id()
if (!mysql_query($sql,$con))
{
die('Error: ' . mysql_error());
}
$sql="INSERT INTO tblAsset (AssetTypeID, Location)
VALUES
('$_POST[AssetTypeID]','$_POST[Location]')";
if (!mysql_query($sql,$con))
{
die('Error: ' . mysql_error());
}
$sql="INSERT INTO tblPersonAsset (BeneficialInterest, AssetID, PersonID)
VALUES
('$_POST[BeneficialInterest]', 'LAST_INSERT_ID()','WHERE I WANT TO PUT SECOND LAST INSERT ID]')";
if (!mysql_query($sql,$con))
{
die('Error: ' . mysql_error());
}
echo "Data added";
mysql_close($con);
?></P>
<p><a href="/data">Enter more data</a></p>
</BODY>
</HTML>
The function returns the ID, so store it somewhere:
$inserted_id = $this->db->insert_id();
Then in your other query, use $inserted_id
17-29% of paid ad clicks are fraudulent. Get protected with Improvely, your online marketing dashboard.
→ Conversion tracking, click fraud detection, A/B testing and more.
Dan
Thanks for this. I've made the following changes in bold, but I still get a blank page. Have I got something out of place?
Sean
<HTML>
<HEAD>
<TITLE>Data entry</TITLE>
</HEAD>
<BODY>
<P><?php $con = mysql_connect('localhost', 'username', 'password');
if (!$con)
{
die('Could not connect: ' . mysql_error());
}
mysql_select_db('newpolitics_com_pol', $con);
$sql="INSERT INTO tblPerson (RelationshipID, FirstName, RestofName, StateID)
VALUES
('$_POST[RelationshipID]','$_POST[FirstName]','$_POST[RestofName]', '$_POST[StateID]')";
$inserted_id = $this->db->insert_id();
if (!mysql_query($sql,$con))
{
die('Error: ' . mysql_error());
}
$sql="INSERT INTO tblAsset (AssetTypeID, Location)
VALUES
('$_POST[AssetTypeID]','$_POST[Location]')";
if (!mysql_query($sql,$con))
{
die('Error: ' . mysql_error());
}
$sql="INSERT INTO tblPersonAsset (BeneficialInterest, AssetID, PersonID)
VALUES
('$_POST[BeneficialInterest]', 'LAST_INSERT_ID()','$inserted_id')";
if (!mysql_query($sql,$con))
{
die('Error: ' . mysql_error());
}
echo "Data added";
mysql_close($con);
?></P>
<p><a href="/data">Enter more data</a></p>
</BODY>
</HTML>
You have to execute the insert query before you can get the generated ID. You're doing it in the opposite order.
You also need to remove the single quotes around functions and numbers.
You should turn up error reporting so you can see the errors instead of a blank page.
17-29% of paid ad clicks are fraudulent. Get protected with Improvely, your online marketing dashboard.
→ Conversion tracking, click fraud detection, A/B testing and more.
Sorry, what is back to front? should it be:
$this->db->insert_id();
$inserted_id = $this->db->insert_id();
This is your current code:
mysql_query() is the function which sends the query to the databasePHP Code:$inserted_id = $this->db->insert_id();
if (!mysql_query($sql,$con))
So you're asking for the inserted ID before you run the INSERT query
That doesn't make sense
17-29% of paid ad clicks are fraudulent. Get protected with Improvely, your online marketing dashboard.
→ Conversion tracking, click fraud detection, A/B testing and more.
does this make sense?
if (!mysql_query($sql,$con))
{
die('Error: ' . mysql_error());
}
$sql=mysql_query("SELECT LAST_INSERT_ID()");
$inserted_id = $this->db->LAST_INSERT_ID();
No
And what is "$this->db" anyway? Did you copy and paste that from somewhere???
Is that what you wanted to do?
PHP Code:<?php
$con = mysql_connect('localhost', 'username', 'password');
if (!$con) die('Could not connect: ' . mysql_error());
mysql_select_db('newpolitics_com_pol', $con);
$sql = "INSERT INTO tblPerson (RelationshipID, FirstName, RestofName, StateID) VALUES (" . (int)($_POST['RelationshipID']) . ", '" . mysql_real_escape_string($_POST['FirstName']) . "', '" . mysql_real_escape_string($_POST['RestofName']) . "', '" . mysql_real_escape_string($_POST['StateID']) . "')";
//Execute the INSERT query
$result = mysql_query($sql, $con);
if (!$result) die('Error: ' . mysql_error());
//Store the ID of the row that was just inserted
$inserted_id = mysql_insert_id();
$sql = "INSERT INTO tblAsset (AssetTypeID, Location) VALUES (" . (int)($_POST['AssetTypeID']) . ", '" . mysql_real_escape_string($_POST['Location']) . "')";
//Execute the second INSERT query
$result = mysql_query($sql, $con);
if (!$result) die('Error: ' . mysql_error());
$sql = "INSERT INTO tblPersonAsset (BeneficialInterest, AssetID, PersonID) VALUES ('" . mysql_real_escape_string($_POST['BeneficialInterest']) . "', LAST_INSERT_ID(), $inserted_id)";
//Execute the 3rd INSERT query
$result = mysql_query($sql, $con);
if (!$result) die('Error: ' . mysql_error());
echo "Data added";
mysql_close($con);
17-29% of paid ad clicks are fraudulent. Get protected with Improvely, your online marketing dashboard.
→ Conversion tracking, click fraud detection, A/B testing and more.
Dan
I found "$this->db" on some website in relation to the wrapper function.
Anway, I've followed your suggestion and amended as follows. Still not working. Sorry to be a pain.
Sean
PHP Code:<HTML>
<HEAD>
<TITLE>Data entry</TITLE>
</HEAD>
<BODY>
<P><?php $con = mysql_connect('localhost', 'username', 'password');
if (!$con)
{
die('Could not connect: ' . mysql_error());
}
mysql_select_db('newpolitics_com_pol', $con);
$sql="INSERT INTO tblPerson (MemberofID, RelationshipID, FirstName, RestofName, PartyID, ElectorateID, StateID, Portfolio)
VALUES (" . (int)($_POST['MemberofID']) . ", ". (int)($_POST['RelationshipID']) . ", '" . mysql_real_escape_string($_POST['FirstName']) . "','" . mysql_real_escape_string($_POST['RestofName']) . "','" . mysql_real_escape_string($_POST['PartyID']) . "','" . mysql_real_escape_string($_POST['ElectorateID']) . "', '" . mysql_real_escape_string($_POST['StateID']) . "','" . mysql_real_escape_string($_POST['Portfolio']) . "')";
//Execute the INSERT query
$result = mysql_query($sql, $con);
if (!$result) die('Error: ' . mysql_error());
//Store the ID of the row that was just inserted
$inserted_id = mysql_insert_id();
$sql="INSERT INTO tblFinancialInterest (FinancialInterestTypeID, Location, Purposeowned)
VALUES (" . (int)($_POST['FinancialInterestTypeID']).",'". mysql_real_escape_string($_POST['Location']) . "','" . mysql_real_escape_string($_POST['Purposeowned']."')";
//Execute the second INSERT query
$result = mysql_query($sql, $con);
if (!$result) die('Error: ' . mysql_error());
$sql="INSERT INTO tblPersonFinancialInterest (BeneficialInterest, FinancialInterestID, PersonID)
VALUES
('" . mysql_real_escape_string($_POST['BeneficialInterest'])."',LAST_INSERT_ID(),$inserted_id)";
//Execute the 3rd INSERT query
$result = mysql_query($sql, $con);
if (!$result) die('Error: ' . mysql_error());
if (!mysql_query($sql,$con))
echo "Data added";
mysql_close($con);
?></P>
</BODY>
</HTML>
Last edited by Dan Grossman; Jun 10, 2009 at 22:52. Reason: Added PHP tags to make it readable
You have to be more specific than "not working"
Look in your error_log
And look at the tables to see if rows were inserted
17-29% of paid ad clicks are fraudulent. Get protected with Improvely, your online marketing dashboard.
→ Conversion tracking, click fraud detection, A/B testing and more.
I just get the same blank screen, Dan.
Here's my error log
[Thu Jun 11 05:47:06 2009] [error] [client 66.249.68.105] File does not exist: /home/virtual/site376/fst/var/www/html/index.html
[Thu Jun 11 07:56:00 2009] [error] [client 124.115.4.191] Directory index forbidden by Options directive: /home/virtual/site376/fst/var/www/html/, referer: http://www.newpolitics.com/
[Thu Jun 11 07:56:00 2009] [error] [client 124.115.4.191] Directory index forbidden by Options directive: /home/virtual/site376/fst/var/www/html/, referer: http://www.newpolitics.com/
[Thu Jun 11 09:02:18 2009] [error] [client 38.99.44.101] File does not exist: /home/virtual/site376/fst/var/www/html/robots.txt
[Thu Jun 11 09:13:54 2009] [error] [client 114.80.93.71] Directory index forbidden by Options directive: /home/virtual/site376/fst/var/www/html/, referer: http://www.newpolitics.com/
[Thu Jun 11 09:29:10 2009] [error] [client 220.101.97.42] PHP Notice: Undefined variable: rcptaddress in /var/www/squirrelmail/src/login.php on line 146
[Thu Jun 11 09:29:15 2009] [error] [client 220.101.97.42] File does not exist: /home/virtual/site376/fst/var/www/html/favicon.ico
[Thu Jun 11 10:29:22 2009] [error] [client 220.101.97.42] File does not exist: /home/virtual/site376/fst/var/www/html/favicon.ico
[Thu Jun 11 10:29:25 2009] [error] [client 220.101.97.42] File does not exist: /home/virtual/site376/fst/var/www/html/favicon.ico
[Thu Jun 11 10:30:16 2009] [error] [client 220.101.97.42] PHP Fatal error: Using $this when not in object context in /var/www/html/insert.php on line 18, referer: http://newpolitics.com/data/
[Thu Jun 11 10:30:20 2009] [error] [client 220.101.97.42] File does not exist: /home/virtual/site376/fst/var/www/html/favicon.ico
[Thu Jun 11 10:32:21 2009] [error] [client 220.101.97.42] PHP Fatal error: Using $this when not in object context in /var/www/html/insert.php on line 18, referer: http://newpolitics.com/data/
[Thu Jun 11 10:39:41 2009] [error] [client 220.101.97.42] PHP Parse error: syntax error, unexpected '(', expecting ']' in /var/www/html/insert.php on line 38
[Thu Jun 11 10:40:18 2009] [error] [client 220.101.97.42] PHP Parse error: syntax error, unexpected '(', expecting ']' in /var/www/html/insert.php on line 38, referer: http://www.newpolitics.com/data/
[Thu Jun 11 10:56:47 2009] [error] [client 114.80.93.71] Directory index forbidden by Options directive: /home/virtual/site376/fst/var/www/html/, referer: http://www.newpolitics.com/
[Thu Jun 11 11:48:17 2009] [error] [client 220.101.97.42] PHP Parse error: syntax error, unexpected $end in /var/www/html/insert.php on line 57, referer: http://www.newpolitics.com/data/
[Thu Jun 11 11:48:55 2009] [error] [client 220.101.97.42] PHP Parse error: syntax error, unexpected $end in /var/www/html/insert.php on line 57, referer: http://www.newpolitics.com/data/
[Thu Jun 11 11:57:28 2009] [error] [client 220.101.97.42] PHP Parse error: syntax error, unexpected $end in /var/www/html/insert.php on line 57, referer: http://www.newpolitics.com/data/
[Thu Jun 11 12:05:19 2009] [error] [client 216.129.119.49] File does not exist: /home/virtual/site376/fst/var/www/html/robots.txt
[Thu Jun 11 12:09:33 2009] [error] [client 220.101.97.42] PHP Parse error: syntax error, unexpected T_VARIABLE in /var/www/html/insert.php on line 20, referer: http://www.newpolitics.com/data/
[Thu Jun 11 12:11:31 2009] [error] [client 220.101.97.42] PHP Parse error: syntax error, unexpected T_VARIABLE in /var/www/html/insert.php on line 20, referer: http://www.newpolitics.com/data/
[Thu Jun 11 12:18:28 2009] [error] [client 220.101.97.42] PHP Parse error: syntax error, unexpected T_VARIABLE in /var/www/html/insert.php on line 24, referer: http://www.newpolitics.com/data/
[Thu Jun 11 12:21:16 2009] [error] [client 220.101.97.42] PHP Parse error: syntax error, unexpected T_VARIABLE in /var/www/html/insert.php on line 22, referer: http://www.newpolitics.com/data/
[Thu Jun 11 12:37:40 2009] [error] [client 220.101.97.42] PHP Parse error: syntax error, unexpected $end in /var/www/html/insert.php on line 59, referer: http://www.newpolitics.com/data/
[Thu Jun 11 12:55:55 2009] [error] [client 220.101.97.42] PHP Parse error: syntax error, unexpected ')' in /var/www/html/insert.php on line 19, referer: http://www.newpolitics.com/data/
[Thu Jun 11 12:56:46 2009] [error] [client 220.101.97.42] PHP Parse error: syntax error, unexpected ')' in /var/www/html/insert.php on line 19, referer: http://www.newpolitics.com/data/
[Thu Jun 11 13:01:35 2009] [error] [client 220.101.97.42] PHP Parse error: syntax error, unexpected ')' in /var/www/html/insert.php on line 19, referer: http://www.newpolitics.com/data/
[Thu Jun 11 13:03:14 2009] [error] [client 220.101.97.42] PHP Parse error: syntax error, unexpected $end in /var/www/html/insert.php on line 56, referer: http://www.newpolitics.com/data/
[Thu Jun 11 13:11:19 2009] [error] [client 220.101.97.42] PHP Parse error: syntax error, unexpected T_STRING in /var/www/html/insert.php on line 27, referer: http://www.newpolitics.com/data/
[Thu Jun 11 13:11:36 2009] [error] [client 220.101.97.42] PHP Parse error: syntax error, unexpected T_STRING in /var/www/html/insert.php on line 27, referer: http://www.newpolitics.com/data/
[Thu Jun 11 13:14:01 2009] [error] [client 220.101.97.42] PHP Parse error: syntax error, unexpected T_VARIABLE in /var/www/html/insert.php on line 27, referer: http://www.newpolitics.com/data/
[Thu Jun 11 13:15:49 2009] [error] [client 220.101.97.42] PHP Parse error: syntax error, unexpected $end in /var/www/html/insert.php on line 61, referer: http://www.newpolitics.com/data/
[Thu Jun 11 13:17:41 2009] [error] [client 220.101.97.42] PHP Parse error: syntax error, unexpected $end in /var/www/html/insert.php on line 61, referer: http://www.newpolitics.com/data/
[Thu Jun 11 13:19:17 2009] [error] [client 220.101.97.42] PHP Notice: Use of undefined constant INSERT_ID - assumed 'INSERT_ID' in /var/www/html/insert.php on line 19, referer: http://www.newpolitics.com/data/
[Thu Jun 11 13:23:36 2009] [error] [client 220.101.97.42] PHP Notice: Use of undefined constant INSERT_ID - assumed 'INSERT_ID' in /var/www/html/insert.php on line 24, referer: http://www.newpolitics.com/data/
[Thu Jun 11 13:25:20 2009] [error] [client 220.101.97.42] PHP Notice: Use of undefined constant LAST_INSERT_ID - assumed 'LAST_INSERT_ID' in /var/www/html/insert.php on line 24, referer: http://www.newpolitics.com/data/
[Thu Jun 11 13:26:27 2009] [error] [client 220.101.97.42] PHP Notice: Use of undefined constant LAST_INSERT_ID - assumed 'LAST_INSERT_ID' in /var/www/html/insert.php on line 24, referer: http://www.newpolitics.com/data/
[Thu Jun 11 13:29:20 2009] [error] [client 220.101.97.42] PHP Parse error: syntax error, unexpected ')' in /var/www/html/insert.php on line 37, referer: http://www.newpolitics.com/data/
[Thu Jun 11 13:30:44 2009] [error] [client 220.101.97.42] PHP Fatal error: Using $this when not in object context in /var/www/html/insert.php on line 24, referer: http://www.newpolitics.com/data/
[Thu Jun 11 13:32:21 2009] [error] [client 220.101.97.42] PHP Fatal error: Using $this when not in object context in /var/www/html/insert.php on line 24, referer: http://www.newpolitics.com/data/
[Thu Jun 11 13:33:16 2009] [error] [client 220.101.97.42] PHP Fatal error: Using $this when not in object context in /var/www/html/insert.php on line 19, referer: http://www.newpolitics.com/data/
[Thu Jun 11 13:35:40 2009] [error] [client 220.101.97.42] PHP Parse error: syntax error, unexpected ')' in /var/www/html/insert.php on line 21, referer: http://www.newpolitics.com/data/
[Thu Jun 11 13:36:57 2009] [error] [client 220.101.97.42] PHP Fatal error: Using $this when not in object context in /var/www/html/insert.php on line 19, referer: http://www.newpolitics.com/data/
[Thu Jun 11 13:38:34 2009] [error] [client 220.101.97.42] PHP Fatal error: Using $this when not in object context in /var/www/html/insert.php on line 24, referer: http://www.newpolitics.com/data/
[Thu Jun 11 13:57:10 2009] [error] [client 220.101.97.42] PHP Fatal error: Using $this when not in object context in /var/www/html/insert.php on line 24, referer: http://www.newpolitics.com/data/
[Thu Jun 11 13:58:54 2009] [error] [client 193.238.230.166] Directory index forbidden by Options directive: /home/virtual/site376/fst/var/www/html/
[Thu Jun 11 14:01:02 2009] [error] [client 220.101.97.42] PHP Fatal error: Call to undefined function INSERT INTO tblPerson (MemberofID, RelationshipID, FirstName, RestofName, PartyID, ElectorateID, StateID, Portfolio), referer: http://www.newpolitics.com/data/
[Thu Jun 11 14:01:02 2009] [error] [client 220.101.97.42] VALUES , referer: http://www.newpolitics.com/data/
[Thu Jun 11 14:01:02 2009] [error] [client 220.101.97.42] ('1','423','Kevin','Rudd','1','215','7','Prime Minister')() in /var/www/html/insert.php on line 23, referer: http://www.newpolitics.com/data/
[Thu Jun 11 14:02:25 2009] [error] [client 220.101.97.42] PHP Fatal error: Call to undefined function INSERT INTO tblPerson (MemberofID, RelationshipID, FirstName, RestofName, PartyID, ElectorateID, StateID, Portfolio), referer: http://www.newpolitics.com/data/
[Thu Jun 11 14:02:25 2009] [error] [client 220.101.97.42] VALUES , referer: http://www.newpolitics.com/data/
[Thu Jun 11 14:02:25 2009] [error] [client 220.101.97.42] ('1','423','Kevin','Rudd','1','215','7','Prime Minister')() in /var/www/html/insert.php on line 23, referer: http://www.newpolitics.com/data/
[Thu Jun 11 14:03:39 2009] [error] [client 220.101.97.42] PHP Fatal error: Call to undefined function last_insert_id() in /var/www/html/insert.php on line 24, referer: http://www.newpolitics.com/data/
[Thu Jun 11 14:06:48 2009] [error] [client 58.61.164.39] File does not exist: /home/virtual/site376/fst/var/www/html/robots.txt, referer: http://www.newpolitics.com/robots.txt
[Thu Jun 11 14:36:55 2009] [error] [client 66.249.68.105] File does not exist: /home/virtual/site376/fst/var/www/html/robots.txt
[Thu Jun 11 14:36:56 2009] [error] [client 66.249.68.105] Directory index forbidden by Options directive: /home/virtual/site376/fst/var/www/html/
[Thu Jun 11 15:11:50 2009] [error] [client 204.62.53.203] File does not exist: /home/virtual/site376/fst/var/www/html/robots.txt
[Thu Jun 11 15:11:51 2009] [error] [client 204.62.53.203] Directory index forbidden by Options directive: /home/virtual/site376/fst/var/www/html/
[Thu Jun 11 15:12:04 2009] [error] [client 220.101.97.42] PHP Parse error: syntax error, unexpected T_ENCAPSED_AND_WHITESPACE, expecting T_STRING or T_VARIABLE or T_NUM_STRING in /var/www/html/insert.php on line 15, referer: http://www.newpolitics.com/data/
[Thu Jun 11 15:14:55 2009] [error] [client 220.101.97.42] PHP Parse error: syntax error, unexpected T_ENCAPSED_AND_WHITESPACE, expecting T_STRING or T_VARIABLE or T_NUM_STRING in /var/www/html/insert.php on line 15, referer: http://www.newpolitics.com/data/
[Thu Jun 11 15:21:36 2009] [error] [client 220.101.97.42] PHP Parse error: syntax error, unexpected T_STRING in /var/www/html/insert.php on line 26, referer: http://www.newpolitics.com/data/
[Thu Jun 11 15:42:52 2009] [error] [client 220.101.97.42] PHP Parse error: syntax error, unexpected T_STRING in /var/www/html/insert.php on line 23, referer: http://www.newpolitics.com/data/
[Thu Jun 11 15:44:57 2009] [error] [client 220.101.97.42] PHP Parse error: syntax error, unexpected T_STRING in /var/www/html/insert.php on line 23, referer: http://www.newpolitics.com/data/
[Thu Jun 11 16:19:55 2009] [error] [client 220.101.97.42] File does not exist: /home/virtual/site376/fst/var/www/html/favicon.ico
[Thu Jun 11 16:19:58 2009] [error] [client 220.101.97.42] File does not exist: /home/virtual/site376/fst/var/www/html/favicon.ico
Dan, just wanted to let you know I've got it to work. Thanks for your help. Apologies for my ignorant questions. Have been reading a php textbook this morning.
Sean
Posting Permissions
Bookmarks