On Fri, Mar 13, 2009 at 1:24 PM, PJ <af.gourmet@xxxxxxxxxxxx> wrote:
> 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:-)
This might get you started...
SELECT * FROM authors WHERE author_id IN (SELECT author_id FROM books
WHERE book_id = ?)
HTH,
--
// Todd
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
