PJ wrote:
am resending as this was erroneously ccd to mysql. sorry!
Ashley Sheridan wrote:
On Thu, 2009-02-26 at 11:27 -0500, PJ wrote:
Jerry Schwartz wrote:
Being rather new to all this, I understood from the MySql manual that
the auto_increment is to b e used immediately after an insertion not
intermittently. My application is for administrators (the site owner &
designates) to update the database from and administration directory,
accessed by user/password login... so there's really very little
possibility of 2 people accessing at the same time.
By using MAX + 1 I keep the id number in the $idIn and can reuse it in
other INSERTS
[JS] Are you looking for something like LAST_INSERT_ID()? If you
INSERT a
record that has an auto-increment field, you can retrieve the value
that got
inserted with "SELECT LAST_INSERT_ID()". It is connection-specific, so
you'll always have your "own" value. You can then save it to reuse,
either
as a session variable or (more easily) as a hidden field on your form.
Thanks, Jerry,
You hit the nail on the head.:)
To refine my problem (and reduce my ignorance),here's what is happening
on the form page:
There is a series of INSERTs. The first inserts all the columns of
"book" table except for the id, which I do not specify as it if
auto-insert.
In subsequent tables I have to reference the book.id (for transitional
tables like "book_author"(refers authors to book) etc.
If I understand it correctly, I must retrieve ("SELECT
LAST_INSERT_ID()") after the first INSERT and before the following
insert; and save the id as a string ($id)...e.g. $sql = "SELECT
LAST_INSERT_ID() AS $id"
I need clarification on the "AS $id" - should this be simply id(does
this have to be turned into a value into $id or does $id contain the
value? And how do I retrieve it to use the returned value for the next
$sql = "INSERT ... - in other words, is the id or $id available for the
next directive or do I have to do something like $id = id?
I'm trying to figure this out with some trials but my insert does not
work from a php file - but it works from command-line... that's another
post.
Here's how I mostly do it (albeit simplified):
$query = "INSERT INTO `sometable`(`title`,`content`)
VALUES('$title','$content')";
$result = mysql_query($query);
$autoId = mysql_insert_id($result);
$query = "INSERT INTO `another_table`(`link_id`,`value`)
VALUES($autoId,'$value');
$result = mysql_query($query);
No need to call another query to retrieve the last inserted id, as it is
tied to the last query executed within this session.
Ash
www.ashleysheridan.co.uk
For some reason or other $autoId = mysql_insert_id($result); just does
not work for me... Yet some of the data is inserted correctly...
I did find that it does not work on tables that are empty... so you
can't start with an empty table. I entered data & it still did not work.
I tried on another duplicate database... doesn't work.
I have checked & double checked the database, I have added checks to see
what is returned and the returns are 0 or null - as I get different
responses for slightly different functions.
sessions is on
mysql is 5.1.28
php5
here's what is parsed:
else { $sql1 = "INSERT INTO book
( title, sub_title, descr, comment, bk_cover,
copyright, ISBN, language, sellers )
VALUES
('$titleIN', '$sub_titleIN', '$descrIN',
'$commentIN', '$bk_coverIN', '$copyrightIN',
'$ISBNIN', '$languageIN', '$sellersIN')";
$result1 = mysql_query($sql1, $db);
$autoid = mysql_insert_id($result1);
$sql2 = "INSERT INTO author (first_name, last_name) VALUES
('$first_nameIN', '$last_nameIN')";
$result2 = mysql_query($sql2, $db);
$authorID = mysql_insert_id($result2);
$sql2a = "INSERT INTO book_author (authID, bookID, ordinal)
VALUES ( '$authorID', '$autoid', '1')";
$result2a = mysql_query($sql2a, $db);
$sql2b = "INSERT INTO author (first_name, last_name) VALUES
('$first_name2IN', '$last_name2IN')";
$result2b = mysql_query($sql2b, $db);
$author2ID = mysql_insert_id($result2b);
$sql2c = "INSERT INTO book_author (authID, bookID, ordinal)
VALUES ( '$author2ID', '$autoid', '2')";
$result2c = mysql_query($sql2c, $db);
$sql3 = "INSERT INTO publishers (publisher) VALUES
('$publisherIN')";
$result3 = mysql_query($sql3, $db);
$publisherID = mysql_insert_id($result3);
$sql3a = "INSERT INTO book_publisher (bookID, publishers_id)
VALUES ( '$autoid', '$publisherID' )";
$result3a = mysql_query($sql3a, $db);
foreach($_POST['categoriesIN'] as $category){
$sql4 = "INSERT INTO book_categories (book_id, categories_id)
VALUES ($autoid, $category)";
$result4 = mysql_query($sql4,$db);
}
echo $autoid; // shows: "blank"
echo $authorID; // shows: "blank"
echo $author2ID; // shows: "blank"
echo $publisherID; // shows: "blank"
echo "<br>autoid = $autoid<br>";// shows: autoid = "blank"
echo "authorID = $authorID<br>";// shows: authorID = "blank"
echo "author2ID = $author2ID<br>";// shows: author2ID = "blank"
echo "publisherID = $publisherID<br>";// shows: publisherID =
"blank"
here is what is inserted:
book is correctly inserted
author is correctly inserted
book_author is correctly inserted
book_categories is correctly inserted
book_publisher bookID = 0 publishers_id = 0 (not correct)
publishers is correctly inserted
You need to take a closer look at this page:
http://php.net/manual/en/function.mysql-insert-id.php
Under the parameters section, look at the description of the link_identifier. It talks about the
link_identifier being the database connection handler, not the resulting resource that is returned
from a call to mysql_query().
Change the value you are passing to mysql_insert_id() to be the $db variable instead of the $results
variable and you will probably get the results you are looking for.
Also, reading further tells me that since you did not pass mysql_insert_id() a valid connection
handler, you should have gotten a E_WARNING notice. Did you by chance check your logs for this error?
Honestly, sounds like you need to turn on full error reporting to the screen.
Place the following at the top of your script and you might see the problem a little clearer.
error_reporting(E_ALL);
ini_set('display_errors', 1);
This will make it so that every error is reported to the screen and not just logged.
--
Jim Lucas
"Some men are born to greatness, some achieve greatness,
and some have greatness thrust upon them."
Twelfth Night, Act II, Scene V
by William Shakespeare
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
