Re: [PHP] RE: non-auto increment question
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 brautoid =
Re: [PHP] RE: non-auto increment question
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. -- Phil Jourdan --- p...@ptahhotep.com http://www.ptahhotep.com http://www.chiccantine.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: [PHP] RE: non-auto increment question
-Original Message- From: PJ [mailto:af.gour...@videotron.ca] Sent: Thursday, February 26, 2009 11:27 AM To: Jerry Schwartz Cc: a...@ashleysheridan.co.uk; 'Gary W. Smith'; 'MySql'; php- gene...@lists.php.net Subject: Re: [PHP] RE: non-auto increment question 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.:) [JS] I'm glad to hear it. 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. [JS] Okay. 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 [JS] You are confusing database column names with PHP variable names. You don't need an alias at all, unless you feel like it for reasons of convenience or style. Assume that $title is your book title, and that the first column is an auto-increment field. The first two queries should look like $query_insert = INSERT INTO book VALUES (NULL, '$title', ...); and $query_select_id = SELECT LAST_INSERT_ID(); Of course, you need to actually execute the two queries. The first one doesn't return anything (check for errors, of course). The second one retrieves the ID of the record you just inserted. Now retrieve the value returned by the SELECT statement and put it into a variable. You'll use something like $row_selected = mysql_query($query_select_id) or die($query_select_id failed); $last_id = mysql_fetch_array($row_selected) or die(Unable to fetch last inserted ID); and you have what you want. You can now use $last_id anywhere you want, until your script ends. This is all very simplified, but I think you can get my drift. Regards, Jerry Schwartz The Infoshop by Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 www.the-infoshop.com www.giiexpress.com www.etudes-marche.com 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. -- Phil Jourdan --- p...@ptahhotep.com http://www.ptahhotep.com http://www.chiccantine.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: [PHP] RE: non-auto increment question
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 [JS] Ashley is absolutely right, I'd forgotten about the mysql_insert_id shorthand. (I'm a one-man band, and for the last week or two I've been immersed in VB for Access forms.) Not only is she right, but her way is better. Presumably a language's internal code is maintained as the specific database changes. You can make yourself more independent of the specific database by using the PDO abstraction, although I would save that for a rainy weekend. Regards, Jerry Schwartz The Infoshop by Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 www.the-infoshop.com www.giiexpress.com www.etudes-marche.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: [PHP] RE: non-auto increment question
Ashley Sheridan wrote: On Thu, 2009-02-26 at 13:44 -0500, Jerry Schwartz wrote: 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 [JS] Ashley is absolutely right, I'd forgotten about the mysql_insert_id shorthand. (I'm a one-man band, and for the last week or two I've been immersed in VB for Access forms.) Not only is she right, but her way is better. Presumably a language's internal code is maintained as the specific database changes. You can make yourself more independent of the specific database by using the PDO abstraction, although I would save that for a rainy weekend. Regards, Jerry Schwartz The Infoshop by Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 www.the-infoshop.com www.giiexpress.com www.etudes-marche.com I just checked, and yep, I'm definitely still a he ;) I never thought otherwise... but then I was wondering... there are too many actresses with the same name... ;-) -- Phil Jourdan --- p...@ptahhotep.com http://www.ptahhotep.com http://www.chiccantine.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: [PHP] RE: non-auto increment question
Sorry, I should know better. -Original Message- From: Ashley Sheridan [mailto:a...@ashleysheridan.co.uk] Sent: Thursday, February 26, 2009 1:51 PM To: Jerry Schwartz Cc: 'PJ'; 'Gary W. Smith'; 'MySql'; php-gene...@lists.php.net Subject: RE: [PHP] RE: non-auto increment question On Thu, 2009-02-26 at 13:44 -0500, Jerry Schwartz wrote: 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 [JS] Ashley is absolutely right, I'd forgotten about the mysql_insert_id shorthand. (I'm a one-man band, and for the last week or two I've been immersed in VB for Access forms.) Not only is she right, but her way is better. Presumably a language's internal code is maintained as the specific database changes. You can make yourself more independent of the specific database by using the PDO abstraction, although I would save that for a rainy weekend. Regards, Jerry Schwartz The Infoshop by Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 www.the-infoshop.com www.giiexpress.com www.etudes-marche.com I just checked, and yep, I'm definitely still a he ;) Ash www.ashleysheridan.co.uk -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: [PHP] RE: non-auto increment question
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 -- The statement is confusing at best. For the casual user auto_increment is the way to do. I say for the casual user. That is typical me and you. Basically if you do an insert a unique value is inserted at the time of the insert. As mentioned, there are ways to get this value back in the return. Now why I say it's for the casual user is because if you are using triggers then you can do things prior to this value being used and then the statement above is correct. But you are not going to be using triggers... So, put an auto_increment on the key field and find one of the 2^16 samples of how this works with PHP. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: [PHP] RE: non-auto increment question
Ashley Sheridan wrote: On Wed, 2009-02-25 at 14:10 -0800, Gary W. Smith wrote: Not sure that this is the problem BUT you should probably qualify the name of the variable such that SELECT MAX(id) AS id FROM book. But you don't want max(id) as id but rather max(id) + 1 as id. With that you can then just return the final value. Also, if you don't want to alias the value (or whatever it's called) you should use $row[0] to get it by ordinal posistion. As for now wanting to use autoincrement, you can run into a race condition where two people are inserting at the same time, thus having the same generated id. Hope that helps. From: PJ [mailto:af.gour...@videotron.ca] Sent: Wed 2/25/2009 2:01 PM To: MySql; php-gene...@lists.php.net Subject: non-auto increment question I want to insert a new table entry 1 number higher than the highest in the field (id). I cannot use auto-increment. And I want to show the value of the field to be added in an input field on the web page: if (isset($_REQUEST[AddNewBooksRequest])) { $SQL = SELECT MAX(id) FROM book; $result = mysql_query($sql, $db); $bookCount = mysql_num_rows($result); for ($i=0; $i $bookCount; $i++) { $row = mysql_fetch_array($result); $idIN = $row[id]+1; Actually, I am wondering how to get rid of some of the code here as it seems a little bloated How do I get rid of the row counting - since there can never be more than one row returned with this query. } $idIN = $_POST[idIN]; $titleIN = $_POST[titleIN]; ...snip... td colspan=2 ? echo input type='text' name='titleIN' value='$idIN' disabled size='2'; ? /td What am I doing wrong? (The query works and returns the right nr. but what do I have to do to add 1 to that number and then display it in the on page and post it to the table? -- Phil Jourdan --- p...@ptahhotep.com http://www.ptahhotep.com http://www.ptahhotep.com/ http://www.chiccantine.com http://www.chiccantine.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=g...@primeexalia.com Yeah, this sort of situation is really what auto increment is for. If you get two people visiting the page with this code on at the same time then you'll screw up your database. Ash www.ashleysheridan.co.uk 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 -- Phil Jourdan --- p...@ptahhotep.com http://www.ptahhotep.com http://www.chiccantine.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: [PHP] RE: non-auto increment question
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. [JS] Being rather old to all this, I can tell you that if something is even remotely possible it will happen just before your performance review. Never depend upon this. Regards, Jerry Schwartz The Infoshop by Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 www.the-infoshop.com www.giiexpress.com www.etudes-marche.com By using MAX + 1 I keep the id number in the $idIn and can reuse it in other INSERTS -- Phil Jourdan --- p...@ptahhotep.com http://www.ptahhotep.com http://www.chiccantine.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=jschwa...@the- infoshop.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: [PHP] RE: non-auto increment question
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. Regards, Jerry Schwartz The Infoshop by Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 www.the-infoshop.com www.giiexpress.com www.etudes-marche.com -- Phil Jourdan --- p...@ptahhotep.com http://www.ptahhotep.com http://www.chiccantine.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=jschwa...@the- infoshop.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org