Jim Lucas wrote:
> 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.
>
Thanks much for your input. This is great. Now I can seeeeeee!
It would have solved a lot of headaches earlier in the process. :-[
I was able to catch several "Notice:"s and correct them.
Now things work pretty well...
Only problem now is to understand the use of subqueries...
and how to display several authors and categories for books.
I'm not trying to reinvent the wheel; just trying to make round and not
ssquare. :-) O:-)
--
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
