Re: RE: non-auto increment question

On Fri, Mar 13, 2009 at 1:24 PM, PJ <af.gourmet@videotron.ca> 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 )e���(��c >>>> �:7�zanother_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