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
--
unheralded genius: "A clean desk is the sign of a dull mind. "
-------------------------------------------------------------
Phil Jourdan --- pj@xxxxxxxxxxxxx
http://www.ptahhotep.com
http://www.chiccantine.com/andypantry.php
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
