Re: RE: non-auto increment question

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